Mulai menggunakan izin Mesin Database

Artikel
03/18/2023

Berlaku untuk:SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure 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 harus GRANT, REVOKE atau DENY.
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 diperlukan ON SECURABLE::NAME karena tidak ambigu atau tidak pantas dalam konteks. Misalnya, CREATE TABLE izin tidak memerlukan ON 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 mempertahankan SELECT akses ke tabel OrderStatus melalui pernyataan individual GRANT mereka.
Jika administrator salah menjalankan DENY SELECT ON OBJECT::OrderStatus TO Sales; maka Jae, sebagai anggota peran Penjualan, akan ditolak SELECT izinnya karena DENY to Sales mengambil alih individu GRANTmereka.

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).

CONTROLizin pada Wilayah tabel mencakup semua izin lain pada Wilayah tabel, termasuk ALTER, , SELECT, INSERTUPDATE, DELETE, dan beberapa izin lainnya.
SELECT pada skema Pelanggan yang memiliki tabel Wilayah menyertakan SELECT 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.

A screenshot from the Database Engine permissions 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;