Tutorial: Rantai Kepemilikan dan Pengalihan Konteks

Artikel
08/11/2023

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

Bagikan melalui