Menentukan izin Mesin Database yang efektif

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

Artikel ini menjelaskan cara menentukan siapa yang memiliki izin ke berbagai objek di Mesin Database SQL Server. SQL Server menerapkan dua sistem izin untuk Mesin Database. Sistem peran tetap yang lebih lama memiliki izin yang telah dikonfigurasi sebelumnya. Dimulai dengan SQL Server 2005 (9.x) sistem yang lebih fleksibel dan tepat tersedia.

Catatan

Informasi dalam artikel ini berlaku untuk SQL Server 2005 (9.x) dan versi yang lebih baru. Beberapa jenis izin tidak tersedia di beberapa versi SQL Server.

Anda harus selalu mengingat poin-poin berikut:

• Izin yang efektif adalah agregat dari kedua sistem izin.
• Penolakan izin mengambil alih pemberian izin.
• Jika pengguna adalah anggota peran server tetap sysadmin, izin tidak diperiksa lebih lanjut, sehingga penolakan tidak akan diberlakukan.
• Sistem lama dan sistem baru memiliki kesamaan. Misalnya, keanggotaan dalam sysadmin peran server tetap mirip dengan memiliki CONTROL SERVER izin. Tapi sistemnya tidak identik. Misalnya, jika login hanya memiliki CONTROL SERVER izin, dan prosedur tersimpan memeriksa keanggotaan dalam sysadmin peran server tetap, maka pemeriksaan izin akan gagal. Begitu pula sebaliknya.

Ringkasan

• Izin tingkat server dapat berasal dari keanggotaan dalam peran server tetap atau peran server yang ditentukan pengguna. Semua orang termasuk dalam public peran server tetap dan menerima izin apa pun yang ditetapkan di sana.
• Izin tingkat server dapat berasal dari pemberian izin untuk masuk atau peran server yang ditentukan pengguna.
• Izin tingkat database dapat berasal dari keanggotaan dalam peran database tetap atau peran database yang ditentukan pengguna di setiap database. Semua orang termasuk dalam public peran database tetap dan menerima izin apa pun yang ditetapkan di sana.
• Izin tingkat database dapat berasal dari pemberian izin kepada pengguna atau peran database yang ditentukan pengguna di setiap database.
• Izin dapat diterima dari guest pengguna masuk atau guest database jika diaktifkan. Login guest dan pengguna dinonaktifkan secara default.
• Pengguna Windows bisa menjadi anggota grup Windows yang bisa memiliki log masuk. SQL Server mempelajari keanggotaan grup Windows ketika pengguna Windows tersambung dan menyajikan token Windows dengan pengidentifikasi keamanan grup Windows. Karena SQL Server tidak mengelola atau menerima pembaruan otomatis tentang keanggotaan grup Windows, SQL Server tidak dapat dengan andal melaporkan izin pengguna Windows yang diterima dari keanggotaan grup Windows.
• Izin dapat diperoleh dengan beralih ke peran aplikasi dan menyediakan kata sandi.
• Izin dapat diperoleh dengan menjalankan prosedur tersimpan yang menyertakan EXECUTE AS klausa.
• Izin dapat diperoleh oleh login atau pengguna dengan IMPERSONATE izin.
• Anggota grup administrator komputer lokal selalu dapat meningkatkan hak istimewa mereka menjadi sysadmin. (Tidak berlaku untuk SQL Database.)
• Anggota securityadmin peran server tetap dapat meningkatkan banyak hak istimewa mereka dan dalam beberapa kasus dapat meningkatkan hak istimewa menjadi sysadmin. (Tidak berlaku untuk SQL Database.)
• Administrator SQL Server dapat melihat informasi tentang semua login dan pengguna. Pengguna yang kurang istimewa biasanya melihat informasi hanya tentang identitas mereka sendiri.

Sistem izin peran tetap yang lebih lama

Memperbaiki peran server dan peran database tetap memiliki izin yang telah dikonfigurasi sebelumnya yang tidak dapat diubah. Untuk menentukan siapa anggota peran server tetap, jalankan kueri berikut:

Catatan

Tidak berlaku untuk SQL Database atau Azure Synapse Analytics di mana izin tingkat server tidak tersedia. Kolom is_fixed_rolesys.server_principals ditambahkan di SQL Server 2012 (11.x). Ini tidak diperlukan untuk versi SQL Server yang lebih lama.

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

Catatan

Semua login adalah anggota peran publik dan tidak dapat dihapus. Kueri memeriksa tabel dalam master database, tetapi dapat dijalankan dalam database apa pun untuk produk lokal.

Untuk menentukan siapa anggota peran database tetap, jalankan kueri berikut di setiap database.

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

Untuk memahami izin yang diberikan kepada setiap peran, lihat deskripsi peran pada ilustrasi di Buku Online (Peran tingkat server, dan peran tingkat Database).

Sistem izin terperinci yang lebih baru

Sistem ini fleksibel, yang berarti dapat menjadi rumit jika orang-orang mengaturnya ingin menjadi tepat. Untuk menyederhanakan hal-hal yang membantu membuat peran, tetapkan izin ke peran, lalu tambahkan grup orang ke peran. Dan lebih mudah jika tim pengembangan database memisahkan aktivitas berdasarkan skema dan kemudian memberikan izin peran ke seluruh skema alih-alih tabel atau prosedur individual. Skenario dunia nyata kompleks dan kebutuhan bisnis dapat menciptakan persyaratan keamanan yang tidak terduga.

Gambar berikut menunjukkan izin dan hubungannya satu sama lain. Beberapa izin tingkat yang lebih tinggi (seperti CONTROL SERVER) dicantumkan berkali-kali. Dalam artikel ini, poster terlalu kecil untuk dibaca. Anda dapat mengunduh Poster Izin Mesin Database berukuran penuh dalam format PDF.

Kelas keamanan

Izin dapat diberikan di tingkat server, tingkat database, tingkat skema, atau tingkat objek, dll. Ada 26 level (disebut kelas). Daftar lengkap kelas dalam urutan alfabet adalah: APPLICATION ROLE, , , ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUPCERTIFICATE, DATABASESCOPED CREDENTIALDATABASEENDPOINTFULLTEXT STOPLISTFULLTEXT CATALOGLOGINROLEMESSAGE TYPEOBJECTREMOTE SERVICE BINDINGCONTRACTROUTE, SERVICESERVER ROLESCHEMASEARCH PROPERTY LISTSERVER, , SYMMETRIC KEY, TYPEUSERXML SCHEMA COLLECTION, . (Beberapa kelas tidak tersedia di beberapa jenis SQL Server.) Untuk memberikan informasi lengkap tentang setiap kelas memerlukan kueri yang berbeda.

Principals

Izin diberikan kepada prinsipal. Prinsipal dapat berupa peran server, login, peran database, atau pengguna. Login dapat mewakili grup Windows yang menyertakan banyak pengguna Windows. Karena grup Windows tidak dikelola oleh SQL Server, SQL Server tidak selalu tahu siapa yang merupakan anggota grup Windows. Ketika pengguna Windows tersambung ke SQL Server, paket masuk berisi token keanggotaan grup Windows untuk pengguna.

Ketika pengguna Windows tersambung menggunakan login berdasarkan grup Windows, beberapa aktivitas mungkin mengharuskan SQL Server untuk membuat login atau pengguna untuk mewakili pengguna Windows individual. Misalnya, grup Windows (Insinyur) berisi pengguna (Mary, Todd, Pat) dan grup Insinyur memiliki akun pengguna database. Jika Mary memiliki izin dan membuat tabel, pengguna (Mary) mungkin dibuat untuk menjadi pemilik tabel. Atau jika Todd ditolak izin yang dimiliki grup Insinyur lainnya, maka pengguna Todd harus dibuat untuk melacak penolakan izin.

Ingatlah bahwa pengguna Windows mungkin merupakan anggota lebih dari satu grup Windows (misalnya, Insinyur dan Manajer). Izin yang diberikan atau ditolak untuk masuk Insinyur, ke login Manajer, diberikan atau ditolak kepada pengguna secara individual, dan diberikan atau ditolak untuk peran tempat pengguna menjadi anggota, semuanya akan diagregasi dan dievaluasi untuk izin yang efektif. Fungsi ini HAS_PERMS_BY_NAME dapat mengungkapkan apakah pengguna atau login memiliki izin tertentu. Namun, tidak ada cara yang jelas untuk menentukan sumber pemberian atau penolakan izin. Pelajari daftar izin dan mungkin bereksperimen menggunakan percobaan dan kesalahan.

Kueri yang berguna

Izin server

Kueri berikut mengembalikan daftar izin yang telah diberikan atau ditolak di tingkat server. Kueri ini harus dijalankan dalam master database.

Catatan

Izin tingkat server tidak dapat diberikan atau dikueri di SQL Database atau Azure Synapse Analytics.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

Izin database

Kueri berikut mengembalikan daftar izin yang telah diberikan atau ditolak di tingkat database. Kueri ini harus dijalankan di setiap database.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

Setiap kelas izin tabel izin dapat digabungkan ke tampilan sistem lain yang memberikan informasi terkait tentang kelas yang dapat diamankan. Misalnya, kueri berikut ini menyediakan nama objek database yang dipengaruhi oleh izin.

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

HAS_PERMS_BY_NAME Gunakan fungsi untuk menentukan apakah pengguna tertentu (dalam hal TestUserini ) memiliki izin. Contohnya:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

Untuk detail sintaks, lihat HAS_PERMS_BY_NAME.

Langkah berikutnya

• Mulai menggunakan Izin Mesin Database
• Tutorial: Memulai Mesin Database