Azure SQL Database peran server untuk manajemen izin

  • Artikel

Berlaku untuk:Azure SQL Database

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

Catatan

Peran tingkat server tetap dalam artikel ini berada 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 pelaku utama keamanan yang mengelompokkan pelaku utama lainnya.

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 ##MS_DatabaseConnector##tingkat server , yang diberikan CONNECT ke semua database, atau memiliki akun pengguna dalam database individual. master virtual.

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 dua database tersebut.

Catatan

Setiap izin dapat ditolak dalam database pengguna, pada dasarnya, mengesampingkan hibah di seluruh server melalui keanggotaan peran. Namun, dalam master database sistem, 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 database tetap dapat menambahkan pengguna 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.

Memperbaiki peran tingkat server

Tabel berikut menunjukkan peran tingkat server tetap dan kemampuannya.

Peran tingkat server tetap Deskripsi
##MS_DatabaseConnector## ##MS_DatabaseConnector## Anggota peran server tetap dapat tersambung ke database apa pun tanpa memerlukan akun Pengguna dalam database untuk disambungkan.

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 yang GRANT CONNECT berasal dari peran ini.
##MS_DatabaseManager## Anggota ##MS_DatabaseManager## peran server tetap dapat membuat dan menghapus database. Anggota ##MS_DatabaseManager## peran yang membuat database, menjadi pemilik database tersebut, yang memungkinkan pengguna tersebut untuk menyambungkan ke database tersebut dbo sebagai pengguna. 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 melalui peran tingkat database dbmanager yang ada di master.
##MS_DefinitionReader## ##MS_DefinitionReader## Anggota peran server tetap dapat membaca semua tampilan katalog yang dicakup oleh VIEW ANY DEFINITION, masing-masing VIEW DEFINITION pada database apa pun di mana anggota peran ini memiliki akun pengguna.
##MS_LoginManager## Anggota ##MS_LoginManager## peran server tetap dapat membuat dan menghapus login. Anda harus menggunakan peran server ini melalui peran tingkat database loginmanager yang ada di master.
##MS_SecurityDefinitionReader## Anggota ##MS_SecurityDefinitionReader## peran server tetap dapat membaca semua tampilan katalog yang dicakup oleh VIEW ANY SECURITY DEFINITION, dan masing-masing memiliki VIEW SECURITY DEFINITION izin pada database apa pun di mana anggota peran ini memiliki akun pengguna. Ini adalah subset kecil dari apa yang ##MS_DefinitionReader## dapat diakses oleh peran server.
##MS_ServerStateManager## Anggota ##MS_ServerStateManager## peran server tetap memiliki izin yang sama dengan peran.##MS_ServerStateReader## Selain itu, ia memegang ALTER SERVER STATE izin, yang memungkinkan akses ke beberapa operasi manajemen, seperti: DBCC FREEPROCCACHE, , DBCC FREESYSTEMCACHE ('ALL')DBCC SQLPERF();
##MS_ServerStateReader## Anggota ##MS_ServerStateReader## peran server tetap dapat membaca semua tampilan manajemen dinamis (DMV) dan fungsi yang dicakup oleh VIEW SERVER STATE, masing-masing VIEW DATABASE STATE pada database mana pun di mana anggota peran ini 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 ke peran 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 DEFINITIONVIEW 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 STATEVIEW SERVER PERFORMANCE STATE,VIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE, , VIEW SERVER PERFORMANCE STATEVIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE

Izin

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 Metadata Menunjukkan apakah login SQL adalah anggota dari peran tingkat server yang ditentukan.
sys.server_role_members Metadata Mengembalikan satu baris untuk setiap anggota dari setiap peran tingkat server.
sys.sql_logins Metadata Mengembalikan satu baris untuk setiap SQL login.
UBAH PERAN SERVER Perintah Mengubah keanggotaan peran server.

Contoh

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

J. 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 tampilansys.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 peran dan izin, dan menjalankan kueri pengujian

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 adalah hasil yang ditetapkan.

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

Masuk sebagai login Jiao dan menyambungkan 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 Prinsipal server Microsoft Entra.

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 tunggu dan memastikan bahwa penetapan peran server saat ini dalam database, administrator server, atau administrator Microsoft Entra dapat berjalan DBCC FLUSHAUTHCACHE di database pengguna tempat login memiliki akses. Saat ini yang dicatat pada pengguna masih harus terhubung kembali setelah berjalan DBCC FLUSHAUTHCACHE agar perubahan keanggotaan berlaku pada mereka.

  • IS_SRVROLEMEMBER() tidak didukung dalam master database.

Konten terkait