Mulai menggunakan izin Mesin Database

Berlaku untuk:SQL ServerDatabase Azure SQLInstans Terkelola Azure SQLAzure Synapse AnalyticsSistem Platform Analitik (PDW)Database SQL di Microsoft Fabric

Artikel ini meninjau beberapa konsep keamanan dasar lalu menjelaskan implementasi izin yang khas. Izin di Mesin Database dikelola di tingkat server melalui login dan peran server, dan di tingkat database melalui pengguna database dan peran database.

Database SQL dan database SQL di Microsoft Fabric menyediakan opsi yang sama dalam setiap database, tetapi izin tingkat server tidak tersedia.

• Di SQL Database, lihat Tutorial: Mengamankan database di Azure SQL Database. Autentikasi ID Microsoft Entra disarankan. Untuk informasi selengkapnya, lihat Tutorial: Membuat pengguna Microsoft Entra menggunakan aplikasi Microsoft Entra.

• Dalam database SQL di Microsoft Fabric, satu-satunya metode autentikasi yang didukung untuk pengguna database adalah ID Microsoft Entra. Peran dan izin tingkat server tidak tersedia. Untuk informasi selengkapnya, lihat Otorisasi dalam database SQL di Microsoft Fabric.

Note

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

Prinsip keamanan

Prinsip keamanan adalah identitas yang digunakan SQL Server, yang dapat ditetapkan izin untuk mengambil tindakan. Prinsipal keamanan biasanya adalah orang, atau sekelompok orang, tetapi dapat berupa entitas lain yang bertindak seolah-olah sebagai orang. Prinsip keamanan dapat dibuat dan dikelola menggunakan contoh Transact-SQL yang ditampilkan dalam artikel ini, atau dengan menggunakan SQL Server Management Studio.

Logins

Login adalah akun pengguna individual untuk masuk ke Mesin Database SQL Server. SQL Server dan SQL Database mendukung login berdasarkan autentikasi Windows, dan login berdasarkan autentikasi SQL Server. Untuk informasi tentang dua jenis login, lihat Memilih mode autentikasi.

Peran server tetap

Di SQL Server, peran server tetap adalah sekumpulan peran yang telah dikonfigurasi sebelumnya yang menyediakan sekelompok izin tingkat server yang nyaman. Login dapat ditambahkan ke peran menggunakan ALTER SERVER ROLE ... ADD MEMBER pernyataan . Untuk informasi selengkapnya, lihat MENGUBAH PERAN SERVER. SQL Database tidak mendukung peran server tetap, tetapi memiliki dua peran dalam master database (dbmanager dan loginmanager) yang bertindak seperti peran server.

Peran server yang ditentukan pengguna

Di SQL Server, Anda dapat membuat peran server Anda sendiri dan menetapkan izin tingkat server untuk peran tersebut. Login dapat ditambahkan ke peran server menggunakan ALTER SERVER ROLE ... ADD MEMBER pernyataan . Untuk informasi selengkapnya, lihat MENGUBAH PERAN SERVER. SQL Database tidak mendukung peran server yang ditentukan pengguna.

Pengguna database

Untuk memberikan akses masuk ke sebuah database, Anda membuat pengguna database di dalam database tersebut dan menghubungkan pengguna database dengan login. Nama pengguna database biasanya sama dengan nama masuk menurut konvensi, meskipun tidak harus sama. Setiap pengguna database memetakan ke satu login. Login hanya dapat dipetakan ke satu pengguna dalam database, tetapi dapat dipetakan sebagai pengguna database dalam beberapa database yang berbeda.

Pengguna database juga dapat dibuat yang tidak memiliki login yang sesuai. Pengguna ini disebut pengguna database mandiri. Microsoft mendorong penggunaan pengguna database mandiri, karena mempermudah pemindahan database Anda ke server yang berbeda. Seperti login, pengguna database mandiri dapat menggunakan autentikasi Windows atau autentikasi SQL Server. Untuk informasi selengkapnya, lihat Membuat database Anda portabel dengan menggunakan database mandiri.

Ada 12 jenis pengguna dengan sedikit perbedaan dalam cara mereka mengautentikasi, dan siapa yang mereka wakili. Untuk melihat daftar pengguna, lihat MEMBUAT PENGGUNA.

Peran database tetap

Peran database tetap adalah sekumpulan peran yang telah dikonfigurasi sebelumnya yang menyediakan grup izin tingkat database yang nyaman. Pengguna database dan peran database yang ditentukan pengguna dapat ditambahkan ke peran database tetap menggunakan pernyataan .ALTER ROLE ... ADD MEMBER Untuk informasi selengkapnya, lihat MENGUBAH PERAN.

Peran database yang ditentukan pengguna

Pengguna dengan CREATE ROLE izin dapat membuat peran database baru yang ditentukan pengguna untuk mewakili grup pengguna dengan izin umum. Biasanya izin diberikan atau ditolak untuk seluruh peran, menyederhanakan manajemen dan pemantauan izin. Pengguna database dapat ditambahkan ke peran database dengan menggunakan ALTER ROLE ... ADD MEMBER pernyataan . Untuk informasi selengkapnya, lihat MENGUBAH PERAN.

Prinsipal lainnya

Prinsip keamanan lain yang tidak dibahas di sini termasuk peran aplikasi, dan login dan pengguna berdasarkan sertifikat atau kunci asimetris.

Untuk grafik yang memperlihatkan hubungan antara pengguna Windows, grup Windows, login, dan pengguna database, lihat Membuat pengguna database.

Skenario umum

Contoh berikut mewakili metode umum dan direkomendasikan untuk mengonfigurasi izin.

Di Windows Active Directory atau ID Microsoft Entra

1. Buat pengguna untuk setiap orang.
2. Buat grup Windows yang mewakili unit kerja dan fungsi kerja.
3. Tambahkan pengguna Windows ke grup Windows.

Jika pengguna akan menyambungkan ke banyak database

1. Buat login untuk grup Windows. (Jika Anda menggunakan autentikasi SQL Server, lewati langkah-langkah Direktori Aktif, dan buat login autentikasi SQL Server di sini.)

2. Di database pengguna, buat pengguna database untuk login yang mewakili grup Windows.

3. Dalam database pengguna, buat satu atau beberapa peran database yang ditentukan pengguna, masing-masing mewakili fungsi serupa. Misalnya, Anda mungkin memiliki peran analis keuangan, dan peran analis penjualan.

4. Tambahkan pengguna database ke satu atau beberapa peran database yang ditentukan pengguna.

5. Berikan izin ke peran database yang ditentukan pengguna.

Jika pengguna hanya akan tersambung ke satu database

1. Di database pengguna, buat pengguna database mandiri untuk grup Windows. (Jika Anda menggunakan autentikasi SQL Server, lewati langkah-langkah Direktori Aktif, dan buat autentikasi SQL Server pengguna database mandiri di sini.)

2. Dalam database pengguna, buat satu atau beberapa peran database yang ditentukan pengguna, masing-masing mewakili fungsi serupa. Misalnya, Anda mungkin memiliki peran analis keuangan, dan peran analis penjualan.

3. Tambahkan pengguna database ke satu atau beberapa peran database yang ditentukan pengguna.

4. Berikan izin ke peran database yang ditentukan pengguna.

Hasil umum pada titik ini adalah bahwa pengguna Windows adalah anggota grup Windows. Grup Windows memiliki login di SQL Server atau SQL Database. Login dipetakan ke identitas pengguna di database pengguna. Pengguna adalah anggota peran database. Sekarang Anda perlu menambahkan izin ke peran.

Tetapkan izin

Sebagian besar pernyataan izin memiliki format berikut:

<authorization> <permission> ON <securable>::<name> TO <principal>;

• <authorization> harus GRANT, , REVOKEatau DENY.

• <permission> menetapkan tindakan yang Anda izinkan atau larang. Jumlah izin yang tepat berbeda antara SQL Server dan Azure SQL Database. Untuk informasi tentang izin, lihat Izin (Mesin Database), dan lihat bagan nanti di artikel ini.

• ON <securable>::<name> adalah jenis yang dapat diamankan (server, objek server, database, atau objek database) dan namanya. Beberapa izin tidak diperlukan <securable>::<name> karena tidak ambigu atau tidak pantas dalam konteks. Misalnya, CREATE TABLE izin tidak memerlukan <securable>::<name> klausul (GRANT CREATE TABLE TO Mary; memungkinkan Mary untuk membuat tabel).

• <principal> adalah prinsip keamanan (login, pengguna, atau peran) yang menerima atau kehilangan izin. Berikan izin ke peran jika memungkinkan.

Contoh pernyataan berikut memberikan izin UPDATE pada tabel atau tampilan Parts yang ada dalam skema Production kepada peran yang dinamakan PartsTeam:

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

Contoh pernyataan berikut memberikan UPDATE izin, pada Production skema dan secara otomatis pada tabel atau pandangan manapun yang terkandung dalam skema ini, ke peran bernama ProductionTeam, yang merupakan pendekatan yang lebih efektif dan skalabel untuk menetapkan izin daripada pada tingkat objek individual.

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Izin diberikan kepada prinsip keamanan (login, pengguna, dan peran) dengan menggunakan pernyataan .GRANT Izin secara eksplisit ditolak dengan menggunakan DENY perintah . Izin yang diberikan atau ditolak sebelumnya dihapus dengan menggunakan REVOKE pernyataan . Izin bersifat kumulatif, dengan pengguna menerima semua izin yang diberikan kepada pengguna, masuk, dan keanggotaan grup apa pun; namun setiap penolakan izin mengambil alih semua pemberian.

Caution

Kesalahan umum adalah mencoba menghapus GRANT dengan menggunakan DENY alih-alih REVOKE. Ini dapat menyebabkan masalah ketika pengguna menerima izin dari beberapa sumber, yang dapat menjadi skenario umum. Contoh berikut menunjukkan prinsip.

Grup Penjualan menerima SELECT hak akses pada tabel OrderStatus melalui pernyataan GRANT SELECT ON OBJECT::OrderStatus TO Sales;. Pengguna Jae adalah anggota dari peran Sales. Jae juga telah diberikan SELECT izin ke tabel OrderStatus atas nama pengguna mereka sendiri melalui pernyataan GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Presume adminstrator ingin menghapus GRANT dari peran Sales.

• Jika administrator menjalankan REVOKE SELECT ON OBJECT::OrderStatus TO Sales; dengan benar, maka Jae tetap memiliki akses ke tabel SELECT melalui pernyataan individual mereka OrderStatus.

• Jika administrator salah menjalankan DENY SELECT ON OBJECT::OrderStatus TO Sales;, maka Jae, sebagai anggota jabatan Sales, ditolak izin SELECT karena DENY untuk Sales mengesampingkan GRANT individu mereka.

Note

Izin dapat dikonfigurasi menggunakan Management Studio. Temukan yang dapat diamankan di Object Explorer, klik kanan yang dapat diamankan, lalu pilih Properti. Pilih halaman Izin . Untuk bantuan tentang menggunakan halaman izin, lihat Izin atau Halaman Yang Dapat Diamankan.

Hierarki otorisasi

Izin memiliki hierarki induk/anak. Artinya, jika Anda memberikan SELECT izin pada database, izin tersebut menyertakan SELECT izin pada semua skema (turunan) dalam database. Jika Anda memberikan SELECT izin pada skema, itu menyertakan SELECT izin pada semua tabel (turunan) dan tampilan dalam skema. Izin bersifat transitif: jika Anda memberikan SELECT izin pada database, izin tersebut juga mencakup SELECT izin pada semua skema bawahan, serta semua tabel dan tampilan bawahan lebih lanjut.

Izin juga memiliki izin yang mencakup. Izin CONTROL pada objek biasanya memberi Anda semua izin lain pada objek.

Karena hierarki induk/anak dan hierarki penutup dapat bertindak berdasarkan izin yang sama, sistem izin bisa menjadi rumit. Misalnya, mari kita ambil tabel (Region), dalam skema (Customers), dalam database (SalesDB).

• CONTROLizin pada tabel Region mencakup semua izin lain pada tabel Region, termasuk ALTER, , SELECT, INSERTUPDATE, DELETE, dan beberapa izin lainnya.

• SELECT pada skema Customers yang memiliki tabel Region menyertakan izin SELECT pada tabel Region.

Jadi izin SELECT pada tabel Region dapat dicapai melalui salah satu atau lebih dari enam pernyataan ini:

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

Memberikan izin paling sedikit

Izin pertama yang tercantum sebelumnya (GRANT SELECT ON OBJECT::Region TO Jae;) adalah yang paling terperinci. Pernyataan itu adalah izin seminimal mungkin yang memberikan SELECT. Tidak ada izin untuk objek subordinat yang disertakan. Ini adalah prinsip yang baik untuk selalu memberikan izin sekecil mungkin, tetapi Anda harus mempertimbangkan untuk memberikan pada tingkat yang lebih tinggi, untuk menyederhanakan sistem pemberian.

Jadi jika Jae membutuhkan izin ke seluruh skema, berikan SELECT sekali di tingkat skema, alih-alih memberikan SELECT pada tingkat tabel atau tampilan berkali-kali. Desain database dapat secara dramatis memengaruhi seberapa sukses strategi ini. Strategi ini berfungsi paling baik saat database Anda dirancang sehingga objek yang membutuhkan izin yang identik disertakan dalam satu skema.

Tip

Saat Anda merancang database dan objeknya, rencanakan dari awal bagaimana aplikasi dan pengguna mengakses objek tersebut. Gunakan informasi ini untuk mengontrol akses ke tabel, tampilan, fungsi, dan prosedur tersimpan menggunakan skema. Skema memungkinkan Anda mengelompokkan jenis akses dengan lebih mudah.

Diagram izin

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.

Untuk grafik yang memperlihatkan hubungan di antara prinsipal Mesin Database dan objek server dan database, lihat Hierarki Izin (Mesin Database).

Izin vs. peran server tetap dan database tetap

Izin peran server tetap dan peran database tetap mirip dengan, tetapi tidak sama persis dengan, izin terperinci. Misalnya, anggota dari peran server tetap sysadmin memiliki semua izin pada instans SQL Server, begitu juga dengan login yang memiliki izin CONTROL SERVER.

Tetapi, memberikan izin CONTROL SERVER tidak membuat login menjadi anggota dari peran server tetap sysadmin, dan menambahkan login ke peran server tetap sysadmin tidak secara eksplisit memberikan izin CONTROL SERVER kepada login. Terkadang prosedur tersimpan memeriksa izin dengan memeriksa peran tetap dan tidak memeriksa izin terperinci.

Misalnya, memutus koneksi database memerlukan keanggotaan dalam peran tetap database db_owner. Izin yang CONTROL DATABASE setara tidak cukup. Kedua sistem ini beroperasi secara paralel tetapi jarang berinteraksi satu sama lain. Microsoft merekomendasikan penggunaan sistem izin terperinci yang lebih baru alih-alih peran tetap jika memungkinkan.

Mengawasi izin

Tampilan berikut mengembalikan informasi keamanan. Untuk semua tampilan terkait keamanan, lihat Tampilan Katalog Keamanan (Transact-SQL).

View	Description
sys.server_principals 1	Login dan peran server yang ditentukan pengguna di server
sys.database_principals	Pengguna dan peran yang ditentukan pengguna dalam database
sys.server_permissions 1	Izin yang diberikan untuk akses masuk dan peran server tetap yang ditentukan oleh pengguna
sys.database_permissions	Izin yang diberikan kepada pengguna dan peran database tetap yang ditentukan pengguna
sys.database_role_members	Keanggotaan anggota database
sys.server_role_members 1	Keanggotaan peran server

¹ Tampilan ini tidak tersedia di SQL Database.

Examples

Pernyataan berikut mengembalikan informasi yang berguna tentang izin.

A. Daftar izin database untuk setiap pengguna

Untuk mengembalikan izin eksplisit yang diberikan atau ditolak dalam database (SQL Server dan SQL Database), jalankan pernyataan Transact-SQL berikut dalam database.

SELECT perms.state_desc AS State,
       permission_name AS [Permission],
       obj.name AS [on Object],
       dp.name AS [to User Name]
FROM sys.database_permissions AS perms
     INNER JOIN sys.database_principals AS dp
         ON perms.grantee_principal_id = dp.principal_id
     INNER JOIN sys.objects AS obj
         ON perms.major_id = obj.object_id;

B. Mencantumkan anggota peran server

Untuk mengembalikan anggota peran server (hanya SQL Server), jalankan pernyataan berikut.

SELECT roles.principal_id AS RolePrincipalID,
       roles.name AS RolePrincipalName,
       server_role_members.member_principal_id AS MemberPrincipalID,
       members.name AS MemberPrincipalName
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
     LEFT OUTER JOIN sys.server_principals AS members
         ON server_role_members.member_principal_id = members.principal_id;

C. Mencantumkan semua prinsipal database yang merupakan anggota peran tingkat database

Untuk mengembalikan anggota peran database (SQL Server dan SQL Database), jalankan pernyataan berikut dalam database.

SELECT dRole.name AS [Database Role Name],
       dp.name AS [Members]
FROM sys.database_role_members AS dRo
     INNER JOIN sys.database_principals AS dp
         ON dRo.member_principal_id = dp.principal_id
     INNER JOIN sys.database_principals AS dRole
         ON dRo.role_principal_id = dRole.principal_id;

Konten terkait

• Keamanan untuk SQL Server Database Engine dan Azure SQL Database
• Fungsi Keamanan (T-SQL)
• Tampilan dan Fungsi Manajemen Dinamis Terkait Keamanan (Transact-SQL)
• Tampilan Katalog Keamanan (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• Menentukan izin Mesin Database yang efektif
• Tutorial: Mulai Menggunakan Mesin Database
• Pelajaran 1: Membuat dan mengkueri objek database
• Tutorial : SQL Server Management Studio
• Tutorial: Menulis pernyataan T-SQL