Tutorial: Rantai Kepemilikan dan Pengalihan Konteks
Berlaku untuk:
SQL Server Azure SQL Managed Instance
Tutorial ini menggunakan skenario untuk mengilustrasikan konsep keamanan SQL Server yang melibatkan rantai kepemilikan dan peralihan konteks pengguna.
Catatan
Untuk menjalankan kode dalam tutorial ini, Anda harus memiliki keamanan Mode Campuran yang dikonfigurasi dan database terinstal AdventureWorks2022 . Untuk informasi selengkapnya tentang keamanan Mode Campuran, lihat Memilih Mode Autentikasi.
Skenario
Dalam skenario ini, dua pengguna memerlukan akun untuk mengakses data pesanan pembelian yang AdventureWorks2022 disimpan dalam database. Persyaratannya adalah sebagai berikut:
- Akun pertama (TestManagerUser) harus dapat melihat semua detail di setiap pesanan pembelian.
- Akun kedua (TestEmployeeUser) harus dapat melihat nomor pesanan pembelian, tanggal pesanan, tanggal pengiriman, nomor ID produk, dan item yang dipesan dan diterima per pesanan pembelian, dengan nomor pesanan pembelian, untuk item di mana pengiriman parsial telah diterima.
- Semua akun lain harus mempertahankan izin mereka saat ini.
Untuk memenuhi persyaratan skenario ini, contohnya dipecah menjadi empat bagian yang menunjukkan konsep rantai kepemilikan dan peralihan konteks:
- Mengonfigurasi lingkungan.
- Membuat prosedur tersimpan untuk mengakses data dengan pesanan pembelian.
- Mengakses data melalui prosedur tersimpan.
- Mengatur ulang lingkungan.
Setiap blok kode dalam contoh ini dijelaskan secara sejalan. Untuk menyalin contoh lengkap, lihat Contoh Lengkap di akhir tutorial ini.
Prasyarat
Untuk menyelesaikan tutorial ini, Anda memerlukan SQL Server Management Studio, akses ke server yang menjalankan SQL Server, dan AdventureWorks2022 database.
- Instal SQL Server Management Studio.
- Instal Edisi Pengembang SQL Server 2017.
- Unduh database sampel AdventureWorks.
Untuk petunjuk tentang memulihkan database di SQL Server Management Studio, lihat Memulihkan database.
1. Mengonfigurasi Lingkungan
Gunakan SQL Server Management Studio dan kode berikut untuk membuka AdventureWorks2022 database, dan gunakan CURRENT_USER pernyataan Transact-SQL untuk memeriksa apakah pengguna dbo ditampilkan sebagai konteks.
USE AdventureWorks2022;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
Untuk informasi selengkapnya tentang pernyataan CURRENT_USER, lihat CURRENT_USER (Transact-SQL).
Gunakan kode ini sebagai pengguna dbo untuk membuat dua pengguna di server dan dalam AdventureWorks2022 database.
CREATE LOGIN TestManagerUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';
GO
CREATE USER TestManagerUser
FOR LOGIN TestManagerUser
WITH DEFAULT_SCHEMA = Purchasing;
GO
CREATE LOGIN TestEmployeeUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
GO
CREATE USER TestEmployeeUser
FOR LOGIN TestEmployeeUser;
GO
Untuk informasi selengkapnya tentang pernyataan CREATE USER, lihat CREATE USER (Transact-SQL). Untuk informasi selengkapnya tentang pernyataan CREATE LOGIN, lihat MEMBUAT LOGIN (Transact-SQL).
Gunakan kode berikut untuk mengubah kepemilikan Purchasing skema ke TestManagerUser akun. Ini memungkinkan akun tersebut untuk menggunakan semua akses pernyataan Data Manipulation Language (DML) (seperti SELECT dan INSERT izin) pada objek yang dikandungnya. TestManagerUser juga diberikan kemampuan untuk membuat prosedur tersimpan.
/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION
ON SCHEMA::Purchasing
TO TestManagerUser;
GO
GRANT CREATE PROCEDURE
TO TestManagerUser
WITH GRANT OPTION;
GO
Untuk informasi selengkapnya tentang pernyataan GRANT, lihat GRANT (Transact-SQL). Untuk informasi selengkapnya tentang prosedur tersimpan, lihat Prosedur Tersimpan (Mesin Database). Untuk poster semua izin Mesin Database, lihat https://aka.ms/sql-permissions-poster.
2. Buat Prosedur Tersimpan untuk Mengakses Data
Untuk beralih konteks dalam database, gunakan pernyataan EXECUTE AS. EXECUTE AS memerlukan izin IMPERSONATE.
EXECUTE AS Gunakan pernyataan dalam kode berikut untuk mengubah konteks menjadi TestManagerUser dan membuat prosedur tersimpan yang hanya memperlihatkan data yang diperlukan oleh TestEmployeeUser. Untuk memenuhi persyaratan, prosedur tersimpan menerima satu variabel untuk nomor pesanan pembelian dan tidak menampilkan informasi keuangan, dan klausul WHERE membatasi hasil ke pengiriman parsial.
EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN
SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate
, b.ProductID, b.OrderQty, b.ReceivedQty
FROM Purchasing.PurchaseOrderHeader a
INNER JOIN Purchasing.PurchaseOrderDetail b
ON a.PurchaseOrderID = b.PurchaseOrderID
WHERE b.OrderQty > b.ReceivedQty
AND @ProductID = b.ProductID
ORDER BY b.ProductID ASC
END
GO
Saat ini TestEmployeeUser tidak memiliki akses ke objek database apa pun. Kode berikut (masih dalam TestManagerUser konteks) memberi akun pengguna kemampuan untuk mengkueri informasi tabel dasar melalui prosedur tersimpan.
GRANT EXECUTE
ON OBJECT::Purchasing.usp_ShowWaitingItems
TO TestEmployeeUser;
GO
Prosedur tersimpan adalah bagian Purchasing dari skema, meskipun tidak ada skema yang ditentukan secara eksplisit, karena TestManagerUser ditetapkan secara default ke Purchasing skema. Anda dapat menggunakan informasi katalog sistem untuk menemukan objek, seperti yang ditunjukkan dalam kode berikut.
SELECT a.name AS 'Schema'
, b.name AS 'Object Name'
, b.type AS 'Object Type'
FROM sys.schemas a
INNER JOIN sys.objects b
ON a.schema_id = b.schema_id
WHERE b.name = 'usp_ShowWaitingItems';
GO
Dengan bagian contoh ini selesai, kode mengalihkan konteks kembali ke dbo menggunakan REVERT pernyataan .
REVERT;
GO
Untuk informasi selengkapnya tentang pernyataan REVERT, lihat REVERT (Transact-SQL).
3. Akses Data Melalui Prosedur Tersimpan
TestEmployeeUser tidak memiliki izin pada AdventureWorks2022 objek database selain login dan hak yang ditetapkan ke peran database publik. Kode berikut mengembalikan kesalahan saat TestEmployeeUser mencoba mengakses tabel dasar.
EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* This won't work */
SELECT *
FROM Purchasing.PurchaseOrderHeader;
GO
SELECT *
FROM Purchasing.PurchaseOrderDetail;
GO
Kesalahan yang dikembalikan:
Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'PurchaseOrderHeader', database 'AdventureWorks2022', schema 'Purchasing'.
Karena objek yang direferensikan oleh prosedur tersimpan yang dibuat di bagian terakhir dimiliki oleh TestManagerUser kebajikan Purchasing kepemilikan skema, TestEmployeeUser dapat mengakses tabel dasar melalui prosedur tersimpan. Kode berikut, masih menggunakan TestEmployeeUser konteks, meneruskan pesanan pembelian 952 sebagai parameter.
EXEC Purchasing.usp_ShowWaitingItems 952
GO
4. Reset Lingkungan
Kode berikut menggunakan REVERT perintah untuk mengembalikan konteks akun saat ini ke dbo, lalu mengatur ulang lingkungan.
REVERT;
GO
ALTER AUTHORIZATION
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems;
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO
Contoh Lengkap
Bagian ini menampilkan kode contoh lengkap.
Catatan
Kode ini tidak menyertakan dua kesalahan yang diharapkan yang menunjukkan ketidakmampuan TestEmployeeUser untuk memilih dari tabel dasar.
/*
Script: UserContextTutorial.sql
Author: Microsoft
Last Updated: Books Online
Conditions: Execute as DBO or sysadmin in the AdventureWorks database
Section 1: Configure the Environment
*/
USE AdventureWorks2022;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Create server and database users */
CREATE LOGIN TestManagerUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';
GO
CREATE USER TestManagerUser
FOR LOGIN TestManagerUser
WITH DEFAULT_SCHEMA = Purchasing;
GO
CREATE LOGIN TestEmployeeUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
GO
CREATE USER TestEmployeeUser
FOR LOGIN TestEmployeeUser;
GO
/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION
ON SCHEMA::Purchasing
TO TestManagerUser;
GO
GRANT CREATE PROCEDURE
TO TestManagerUser
WITH GRANT OPTION;
GO
/*
Section 2: Switch Context and Create Objects
*/
EXECUTE AS LOGIN = 'TestManagerUser';
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN
SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate
, b.ProductID, b.OrderQty, b.ReceivedQty
FROM Purchasing.PurchaseOrderHeader AS a
INNER JOIN Purchasing.PurchaseOrderDetail AS b
ON a.PurchaseOrderID = b.PurchaseOrderID
WHERE b.OrderQty > b.ReceivedQty
AND @ProductID = b.ProductID
ORDER BY b.ProductID ASC
END;
GO
/* Give the employee the ability to run the procedure */
GRANT EXECUTE
ON OBJECT::Purchasing.usp_ShowWaitingItems
TO TestEmployeeUser;
GO
/* Notice that the stored procedure is located in the Purchasing
schema. This also demonstrates system catalogs */
SELECT a.name AS 'Schema'
, b.name AS 'Object Name'
, b.type AS 'Object Type'
FROM sys.schemas AS a
INNER JOIN sys.objects AS b
ON a.schema_id = b.schema_id
WHERE b.name = 'usp_ShowWaitingItems';
GO
/* Go back to being the dbo user */
REVERT;
GO
/*
Section 3: Switch Context and Observe Security
*/
EXECUTE AS LOGIN = 'TestEmployeeUser';
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
EXEC Purchasing.usp_ShowWaitingItems 952;
GO
/*
Section 4: Clean Up Example
*/
REVERT;
GO
ALTER AUTHORIZATION
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems;
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO
Lihat Juga
Security Center untuk Mesin Database SQL Server dan Azure SQL Database
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk