Mulai menggunakan izin Mesin Database
Berlaku untuk: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics Analytics
Platform System (PDW)
Izin di Mesin Database dikelola di tingkat server melalui login dan peran server, dan di tingkat database melalui pengguna database dan peran database. Model untuk SQL Database mengekspos sistem yang sama dalam setiap database, tetapi izin tingkat server tidak tersedia. Artikel ini meninjau beberapa konsep keamanan dasar lalu menjelaskan implementasi umum izin.
Catatan
ID Microsoft Entra sebelumnya dikenal sebagai Azure Active Directory (Azure AD).
Prinsip keamanan
Prinsip keamanan adalah nama resmi identitas yang menggunakan SQL Server dan yang dapat ditetapkan izin untuk mengambil tindakan. Mereka biasanya orang atau sekelompok orang, tetapi bisa menjadi entitas lain yang berpura-pura menjadi orang. Prinsip keamanan dapat dibuat dan dikelola menggunakan Transact-SQL yang tercantum, atau dengan menggunakan SQL Server Management Studio.
Masuk
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 (Transact-SQL). 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 (Transact-SQL). SQL Database tidak mendukung peran server yang ditentukan pengguna.
Pengguna database
Login diberikan akses ke database dengan membuat pengguna database dalam database dan memetakan pengguna database tersebut untuk masuk. Biasanya nama pengguna database sama dengan nama masuk, 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 di 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 Pengguna Database Mandiri - Membuat Database Anda Portabel.
Ada 12 jenis pengguna dengan sedikit perbedaan dalam cara mereka mengautentikasi, dan siapa yang mereka wakili. Untuk melihat daftar pengguna, lihat CREATE USER (Transact-SQL).
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 (Transact-SQL).
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 (Transact-SQL).
Prinsipal lainnya
Prinsip keamanan tambahan yang tidak dibahas di sini mencakup 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
Buat pengguna untuk setiap orang.
Buat grup Windows yang mewakili unit kerja dan fungsi kerja.
Tambahkan pengguna Windows ke grup Windows.
Jika orang yang menyambungkan akan tersambung ke banyak database
Buat login untuk grup Windows. (Jika menggunakan autentikasi SQL Server, lewati langkah-langkah Direktori Aktif, dan buat login autentikasi SQL Server di sini.)
Di database pengguna, buat pengguna database untuk login yang mewakili grup Windows.
Dalam database pengguna, buat satu atau beberapa peran database yang ditentukan pengguna, masing-masing mewakili fungsi serupa. Misalnya analis keuangan, dan analis penjualan.
Tambahkan pengguna database ke satu atau beberapa peran database yang ditentukan pengguna.
Berikan izin ke peran database yang ditentukan pengguna.
Jika orang yang tersambung hanya akan menyambungkan ke satu database
Di database pengguna, buat pengguna database mandiri untuk grup Windows. (Jika menggunakan autentikasi SQL Server, lewati langkah-langkah Direktori Aktif, dan buat autentikasi SQL Server pengguna database mandiri di sini.
Dalam database pengguna, buat satu atau beberapa peran database yang ditentukan pengguna, masing-masing mewakili fungsi serupa. Misalnya analis keuangan, dan analis penjualan.
Tambahkan pengguna database ke satu atau beberapa peran database yang ditentukan pengguna.
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:
AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;
AUTHORIZATION
harusGRANT
,REVOKE
atauDENY
.Menetapkan
PERMISSION
tindakan apa yang diizinkan atau dilarang. Jumlah izin yang tepat berbeda antara SQL Server dan SQL Database. Izin tercantum dalam artikel Izin (Mesin Database) dan dalam bagan yang dirujuk di bawah ini.ON SECURABLE::NAME
adalah jenis yang dapat diamankan (server, objek server, database, atau objek database) dan namanya. Beberapa izin tidak diperlukanON SECURABLE::NAME
karena tidak ambigu atau tidak pantas dalam konteks. Misalnya,CREATE TABLE
izin tidak memerlukanON 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 pemberian berikut, memberikan UPDATE
izin pada Parts
tabel atau tampilan yang terkandung dalam Production
skema ke peran bernama PartsTeam
:
GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;
Contoh pernyataan pemberian berikut memberikan UPDATE
izin pada Production
skema, dan dengan ekstensi pada tabel atau tampilan apa pun yang terkandung dalam skema ini ke peran bernama ProductionTeam
, yang merupakan pendekatan yang lebih efektif dan salable 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.
Tip
Kesalahan umum adalah mencoba menghapus GRANT
dengan menggunakan DENY
alih-alih REVOKE
. Ini dapat menyebabkan masalah ketika pengguna menerima izin dari beberapa sumber; yang cukup umum. Contoh berikut menunjukkan prinsipal.
Grup Penjualan menerima SELECT
izin pada tabel OrderStatus melalui pernyataan GRANT SELECT ON OBJECT::OrderStatus TO Sales;
. Pengguna Jae adalah anggota peran Penjualan. Jae juga telah diberikan SELECT
izin ke tabel OrderStatus dengan nama pengguna mereka sendiri melalui pernyataan GRANT SELECT ON OBJECT::OrderStatus TO Jae;
. Anggap saja administer ingin menghapus GRANT
ke peran Penjualan.
Jika administrator menjalankan
REVOKE SELECT ON OBJECT::OrderStatus TO Sales;
dengan benar , maka Jae akan mempertahankanSELECT
akses ke tabel OrderStatus melalui pernyataan individualGRANT
mereka.Jika administrator salah menjalankan
DENY SELECT ON OBJECT::OrderStatus TO Sales;
maka Jae, sebagai anggota peran Penjualan, akan ditolakSELECT
izinnya karenaDENY
to Sales mengambil alih individuGRANT
mereka.
Catatan
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 izin
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; artinya, jika Anda memberikan SELECT
izin pada database, itu termasuk SELECT
izin pada semua skema (anak), dan semua tabel dan tampilan (cucu).
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 (Wilayah), dalam skema (Pelanggan), dalam database (SalesDB).
CONTROL
izin pada Wilayah tabel mencakup semua izin lain pada Wilayah tabel, termasukALTER
, ,SELECT
,INSERT
UPDATE
,DELETE
, dan beberapa izin lainnya.SELECT
pada skema Pelanggan yang memiliki tabel Wilayah menyertakanSELECT
izin pada tabel Wilayah.
Jadi SELECT
izin pada tabel Wilayah dapat dicapai melalui salah satu 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 di atas (GRANT SELECT ON OBJECT::Region TO Jae;
) adalah yang paling terperinci, yaitu pernyataan itu adalah izin paling sedikit yang memberikan SELECT
. Tidak ada izin untuk objek subordinat yang disertakan. Ini adalah prinsip yang baik untuk selalu memberikan izin sekecil mungkin (Anda dapat membaca lebih lanjut tentang Prinsip Hak Istimewa Paling Sedikit), tetapi pada saat yang sama (bertentangan dengan itu) mencoba 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 akan berfungsi paling baik ketika database Anda dirancang sehingga objek yang membutuhkan izin yang identik disertakan dalam satu skema.
Tip
Saat merancang database dan objeknya, sejak awal, merencanakan siapa atau aplikasi mana yang akan mengakses objek mana dan berdasarkan objek tempat itu, yaitu tabel tetapi juga melihat, fungsi, dan prosedur tersimpan dalam skema sesuai dengan wadah jenis akses sebanyak mungkin.
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 serupa tetapi tidak sama persis dengan izin terperinci. Misalnya, anggota sysadmin
peran server tetap memiliki semua izin pada instans SQL Server, seperti halnya CONTROL SERVER
login dengan izin. Tetapi memberikan CONTROL SERVER
izin tidak membuat login sebagai anggota peran server tetap sysadmin, dan menambahkan login ke sysadmin
peran server tetap tidak secara eksplisit memberikan izin masuk CONTROL SERVER
. Terkadang prosedur tersimpan akan memeriksa izin dengan memeriksa peran tetap dan tidak memeriksa izin terperinci. Misalnya, melepaskan database memerlukan keanggotaan dalam db_owner
peran database tetap. 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.
Memantau izin
Tampilan berikut mengembalikan informasi keamanan.
Login dan peran server yang ditentukan pengguna di server dapat diperiksa dengan menggunakan
sys.server_principals
tampilan. Tampilan ini tidak tersedia di SQL Database.Pengguna dan peran yang ditentukan pengguna dalam database dapat diperiksa dengan menggunakan
sys.database_principals
tampilan.Izin yang diberikan untuk masuk dan peran server tetap yang ditentukan pengguna dapat diperiksa dengan menggunakan
sys.server_permissions
tampilan. Tampilan ini tidak tersedia di SQL Database.Izin yang diberikan kepada pengguna dan peran database tetap yang ditentukan pengguna dapat diperiksa dengan menggunakan
sys.database_permissions
tampilan.Keanggotaan peran database dapat diperiksa dengan menggunakan
sys.database_role_members
tampilan.Keanggotaan peran server dapat diperiksa dengan menggunakan
sys.server_role_members
tampilan. Tampilan ini tidak tersedia di SQL Database.Untuk tampilan terkait keamanan tambahan, lihat Tampilan Katalog Keamanan (Transact-SQL).
Contoh
Pernyataan berikut mengembalikan informasi yang berguna tentang izin.
J. Daftar izin database untuk setiap pengguna
Untuk mengembalikan izin eksplisit yang diberikan atau ditolak dalam database (SQL Server dan SQL Database), jalankan pernyataan 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
JOIN sys.database_principals AS dp
ON perms.grantee_principal_id = dp.principal_id
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 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
JOIN sys.database_principals AS dp
ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
ON dRo.role_principal_id = dRole.principal_id;
Lihat juga
- Security Center untuk Mesin Database SQL Server 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 (T-SQL)
- Menentukan Izin Mesin Database yang Efektif