Bagikan melalui

Peran server Azure SQL Database untuk pengelolaan izin

Berlaku untuk:Azure SQL Database

Artikel ini menjelaskan peran tingkat server tetap di Azure SQL Database.

Catatan

Peran pada tingkat server yang tetap dalam artikel ini sedang dalam pratinjau publik untuk Azure SQL Database. Peran tingkat server ini juga merupakan bagian dari rilis untuk SQL Server 2022.

Gambaran Umum

Di Azure SQL Database, server adalah konsep logis dan izin tidak dapat diberikan di tingkat server. Untuk menyederhanakan manajemen izin, Azure SQL Database menyediakan satu set peran tingkat server tetap untuk membantu Anda mengelola izin pada server logis. Peran adalah prinsipal keamanan yang mengelompokkan login.

Catatan

Konsep peran dalam artikel ini seperti kelompok dalam sistem operasi Windows.

Peran tingkat server tetap khusus ini menggunakan awalan ##MS_ dan akhiran ## untuk membedakan dari prinsipal reguler yang dibuat pengguna lainnya.

Seperti SQL Server di tempat, izin server diatur secara hierarkis. Izin yang dipegang oleh peran tingkat server ini dapat menyebar ke izin database. Agar izin berguna secara efektif di tingkat database, login harus menjadi anggota peran tingkat server ##MS_DatabaseConnector##, yang memberikan CONNECT ke semua database, atau memiliki akun pengguna di setiap database secara individual. Ini juga berlaku untuk database virtual master.

Misalnya, peran ##MS_ServerStateReader## tingkat server memegang izin VIEW SERVER STATE. Jika login yang merupakan anggota peran ini memiliki akun pengguna dalam database master dan WideWorldImporters, pengguna ini memiliki izin VIEW DATABASE STATE dalam kedua database tersebut.

Catatan

Setiap izin dapat ditolak dalam database pengguna, pada dasarnya, mengesampingkan pemberian izin secara menyeluruh di server melalui keanggotaan pada peran. Namun, dalam database mastersistem , izin tidak dapat diberikan atau ditolak.

Azure SQL Database saat ini menyediakan tujuh peran server tetap. Izin yang diberikan ke peran server tetap tidak dapat diubah dan peran ini tidak dapat memiliki peran tetap lainnya sebagai anggota. Anda dapat menambahkan login tingkat server sebagai anggota ke peran tingkat server.

Penting

Setiap anggota peran server tetap dapat menambahkan login lain ke peran yang sama.

Untuk informasi selengkapnya tentang login dan pengguna Azure SQL Database, lihat Mengautorisasi akses basis data ke SQL Database, instans terkelola SQL, dan Azure Synapse Analytics.

Peran tingkat server yang ditetapkan

Tabel berikut menunjukkan peran tingkat server tetap dan kemampuannya.

Peran tingkat server tetap Deskripsi
##MS_DatabaseConnector## Anggota dari peran server tetap dapat terhubung ke database mana pun tanpa memerlukan akun pengguna dalam database.

Untuk menolak CONNECT izin ke database tertentu, pengguna dapat membuat akun pengguna yang cocok untuk masuk ini dalam database lalu DENYCONNECT izin ke pengguna database. Izin ini DENY mengesampingkan izin GRANT CONNECT yang berasal dari peran ini.
##MS_DatabaseManager## Anggota peran server tetap ##MS_DatabaseManager## dapat membuat dan menghapus database. Seorang anggota peran ##MS_DatabaseManager## yang membuat database akan menjadi pemilik database tersebut, sehingga memungkinkan pengguna tersebut untuk terhubung ke database itu sebagai pengguna dbo. Pengguna dbo memiliki semua izin database dalam database. ##MS_DatabaseManager## Anggota peran tidak selalu memiliki izin untuk mengakses database yang tidak mereka miliki. Anda harus menggunakan peran server ini daripada peran tingkat database dbmanager yang ada di master.
##MS_DefinitionReader## Anggota peran server tetap dapat membaca semua tampilan katalog yang dicakup oleh ##MS_DefinitionReader## dan VIEW ANY DEFINITION pada setiap database di mana anggota peran ini memiliki akun pengguna.
##MS_LoginManager## Anggota peran server yang tetap dapat membuat dan menghapus login. Anda harus menggunakan peran server ini melalui peran tingkat database loginmanager yang ada di master.
##MS_SecurityDefinitionReader## Anggota peran server tetap dapat membaca semua tampilan katalog yang dicakup oleh , dan masing-masing memiliki izin pada setiap database tempat anggota peran ini memiliki akun pengguna. Ini adalah subset kecil dari apa yang dapat diakses oleh peran server ##MS_DefinitionReader##.
##MS_ServerStateManager## Anggota ##MS_ServerStateManager## peran server tetap memiliki izin yang sama dengan peran ##MS_ServerStateReader##. Selain itu, ia memiliki izin ALTER SERVER STATE, yang memungkinkan akses ke beberapa operasi manajemen, seperti: DBCC FREEPROCCACHEDBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF();
##MS_ServerStateReader## Anggota peran server tetap ##MS_ServerStateReader## dapat membaca semua tampilan manajemen dinamis (DMV) dan fungsi yang masing-masing dicakup oleh VIEW SERVER STATE dan VIEW DATABASE STATE pada database mana pun yang dimiliki anggota peran ini dan yang memiliki akun pengguna.

Izin peran Server Tetap

Setiap peran tingkat server tetap memiliki izin tertentu yang ditetapkan untuk peran tersebut. Tabel berikut menunjukkan izin yang ditetapkan pada peran-peran di tingkat server. Ini juga menunjukkan izin tingkat database, yang diwariskan selama pengguna dapat tersambung ke database individual.

Peran tingkat server tetap Izin tingkat server Izin tingkat database (jika pengguna database yang cocok dengan login ada)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE, ALTER ANY DATABASE ALTER
##MS_DefinitionReader## VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW ANY SECURITY DEFINITION VIEW DEFINITION, VIEW SECURITY DEFINITION
##MS_LoginManager## CREATE LOGIN, ALTER ANY LOGIN T/A
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateManager## ALTER SERVER STATE,VIEW SERVER STATE,VIEW SERVER PERFORMANCE STATE,VIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

Hak Akses

Hanya akun admin server atau akun admin Microsoft Entra (yang dapat menjadi grup Microsoft Entra) yang dapat menambahkan atau menghapus login lain ke atau dari peran server. Ini khusus untuk Azure SQL Database.

Catatan

ID Microsoft Entra sebelumnya dikenal sebagai Azure Active Directory (Azure AD).

Bekerja dengan peran tingkat server

Tabel berikut menjelaskan tampilan sistem, dan fungsi yang dapat Anda gunakan untuk bekerja dengan peran tingkat server dalam Azure SQL Database.

Fitur Tipe Deskripsi
IS_SRVROLEMEMBER Metainformasi Menunjukkan apakah akun login SQL adalah anggota dari peran tingkat server yang ditentukan.
sys.server_role_members Metainformasi Mengembalikan satu baris untuk setiap anggota dari setiap peran tingkat server.
sys.sql_logins Metainformasi Mengembalikan satu baris untuk setiap SQL login.
Ubah Role Server Perintah Membuat perubahan pada keanggotaan dari sebuah peran server.

Contoh

Contoh di bagian ini menunjukkan cara bekerja dengan peran tingkat server dalam Azure SQL Database.

Sebuah. Menambahkan login SQL ke peran tingkat server

Contoh berikut menambahkan login Jiao SQL ke peran ##MS_ServerStateReader##tingkat server . Pernyataan ini harus dijalankan dalam database master virtual.

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B. Mencantumkan semua prinsipal (autentikasi SQL) yang merupakan anggota peran tingkat server

Pernyataan berikut mengembalikan semua anggota dari setiap peran tingkat server tetap menggunakan tampilan sys.server_role_members dan sys.sql_logins katalog. Pernyataan ini harus dijalankan dalam database master virtual.

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

C. Contoh lengkap: Menambahkan login ke peran tingkat server, mengambil metadata untuk keanggotaan dan izin peran, serta menjalankan kueri uji coba.

Bagian 1: Menyiapkan keanggotaan peran dan akun pengguna

Jalankan perintah ini dari database master virtual.

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

Berikut kumpulan hasilnya.

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

Jalankan perintah ini dari database pengguna.

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

Bagian 2: Menguji keanggotaan peran

Login sebagai Jiao dan sambungkan ke database pengguna yang digunakan dalam contoh.

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D. Periksa peran tingkat server untuk login Microsoft Entra

Jalankan perintah ini di database virtual master untuk melihat semua login Microsoft Entra yang merupakan bagian dari peran tingkat server di SQL Database. Untuk informasi selengkapnya tentang login server Microsoft Entra, lihat Microsoft Entra server principals.

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. Periksa peran database virtual master untuk login tertentu

Jalankan perintah ini di database master virtual untuk memeriksa peran yang dimiliki bob, atau ubah nilainya agar sesuai dengan prinsipal Anda.

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

Keterbatasan peran tingkat server

  • Penetapan peran mungkin memerlukan waktu hingga 5 menit agar efektif. Juga untuk sesi yang ada, perubahan tugas peran server tidak berlaku sampai koneksi ditutup dan dibuka kembali. Hal ini disebabkan oleh arsitektur terdistribusi antara master database dan database lain di server logis yang sama.

    • Solusi parsial: untuk mengurangi periode menunggu dan memastikan bahwa penugasan peran server saat ini tetap terkini dalam sebuah database, seorang administrator server, atau administrator Microsoft Entra dapat menjalankan DBCC FLUSHAUTHCACHE di database pengguna tempat login memiliki akses. Pengguna yang sedang masuk masih perlu masuk kembali setelah menjalankan DBCC FLUSHAUTHCACHE agar perubahan keanggotaan dapat diterapkan.

  • IS_SRVROLEMEMBER() tidak didukung dalam master database.

Konten terkait

  • Last updated on