Alapvetően két esetben érdekes a jogosultság delegálása:
- magasabb szintű jogosultság ellenőrzött használata esetén és
- fejlesztői jogosultság kiterjesztése.
Míg az előbbire az adatbázis biztonságának növelése, jogszabályi követelmények, egyéb szabályozások (pl. PCI, SOX, stb.) miatt, addig az utóbbi eset a fejlesztői munka elősegítése lehet a cél. Az SQL Server 2005 verziótól működik az un. security trimming, ami azt jelenti, hogy minden felhasználó annyit lát a rendszerből, amennyihez jogosultsága van a DMV, DMF és a system catalog view lekérdezések során. Például egy “egyszerű” felhasználó nem láthatja az összes session-t a sys.dm_exec_sessions DMV-ben, csak a sajátját. Itt jön be a jogosultság delegálása: ha azt szeretném, hogy minden session-t lásson a felhasználó – maradva a fenti példánál – akkor adhatok neki VIEW SERVER STATE jogot is. Igen ám, de ekkor hozzáférhet olyan információkhoz/DMV-khez is, amikhez nem akartam jogot adni neki. Az alábbi példa során csak a sys.dm_os_ring_buffers DMV-hez akarok jogosultságot adni, egy “egyszerű” felhasználónak:
Első lépésben létrehozok egy teszt adatbázist.
USE [master];
GO
CREATE DATABASE [DelegateDb]
GO
A delegáláshoz szükségem lesz egy tanúsítványra, mely a hitelesítő szerepet fogja betölteni:
USE [master];
GO
CREATE CERTIFICATE [DelegateCert] ENCRYPTION BY PASSWORD = 'password'
WITH SUBJECT = 'Delegate cert',
EXPIRY_DATE = '20201231';
GO
Következő lépésben két felhasználót hozok létre:
- HighPrivilegedLogin: ez lesz, akinek megadom a VIEW SERVER STATE jogosultságot, de közvetlenül nem csatlakozhat az SQL Server-hez – visszavonom a CONNECT jogát – csak az előbbi jog delegálásához fogom használni. Ez a login a DelegateCert tanúsítványból lesz létrehozva.
- LowPrivilegedUser: ez lesz, akinek csak EXEC joga lesz a DelegateDb adatbázisban
USE [master];
GO
CREATE LOGIN [HighPrivilegedLogin] FROM CERTIFICATE [DelegateCert];
GO
REVOKE CONNECT SQL FROM [HighPrivilegedLogin];
GO
GRANT VIEW SERVER STATE TO [HighPrivilegedLogin];
GO
CREATE LOGIN [LowPrivilegedUser] WITH PASSWORD = 'password';
GO
Eljutottam odáig, hogy van egy teszt adatbázisom és két loginom, illetve egy tanúsítványom. Most elkezdem felhasználni ezeket: a DelegateCert tanúsítványra a DelegateDb adatbázisban is szükségem van, ezzel fogom “aláírni” az egyik eljárásomat, amely segítségével a LowPrivilegedUser hozzá fog férni a DMV tartalmához. Ehhez elmentem a tanúsítványt a privát kulcsával együtt:
USE [master];
GO
BACKUP CERTIFICATE [DelegateCert] TO FILE = 'c:\temp\sql.cer'
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'password' ,
FILE = 'c:\temp\sql.pvk' ,
ENCRYPTION BY PASSWORD = 'password' );
GO
Ezek után visszaállítom a DelegateDb-ben a DelegateCert tanúsítványt. Azonban ehhez előbb létre kell hoznom az adatbázis MASTER KEY-t.
USE [DelegateDb];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
GO
CREATE CERTIFICATE [DelegateCert] FROM FILE = 'c:\temp\sql.cer'
WITH PRIVATE KEY
(FILE = 'c:\temp\sql.pvk',
DECRYPTION BY PASSWORD= 'password',
ENCRYPTION BY PASSWORD='password')
GO
Most létrehozok egy eljárást, ami visszaadja a fenti DMV sorait, illetve a “hívó” és a “futtató” felhasználók neveit.
USE [DelegateDb];
GO
CREATE PROCEDURE [DelegateElevatedThingsToDo]
AS
SELECT ORIGINAL_LOGIN() AS [Caller], SUSER_NAME() AS [Executor];
SELECT * FROM sys.dm_os_ring_buffers;
GO
Az adatbázisban létrehozom a LowPrivilegedUser felhazsnálót és adok EXEC jogot neki.
USE [DelegateDb];
GO
CREATE USER [LowPrivilegedUser] FOR LOGIN [LowPrivilegedUser];
GO
GRANT EXEC ON SCHEMA::dbo TO [LowPrivilegedUser];
GO
Ha most megpróbálom ezzel a felhasználóval futtatni a fenti eljárást, hibát fogok kapni:
Msg 297, Level 16, State 1, Procedure DelegateElevatedThingsToDo, Line 4
The user does not have permission to perform this action.
Ez a hiba a DMV lekérdezésre vonatkozik. Ahhoz, hogy legyen jogosultsága, “aláírom” az eljárást a DelegateCert tanúsítvánnyal.
ADD SIGNATURE TO [DelegateElevatedThingsToDo]
BY CERTIFICATE [DelegateCert] WITH PASSWORD = 'password'
GO
Ezek után már működni fog, el tudja érni a LowPrivilegedUser a DMV tartalmát:
EXECUTE AS LOGIN = 'LowPrivilegedUser';
GO
EXEC [DelegateElevatedThingsToDo];
GO
REVERT
Hurrá, nem kell sysadmin jog, sem semmilyen más elevált jog, így a DBA is nyugodt lehet, hogy a fejlesztő nem “rongálja” az éles szervert :-).
Az adatbázist és a fentieket, az alábbi kód segítségével lehet kitörölni, ha nincs már rá szükség:
USE [master];
GO
ALTER DATABASE [DelegateDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [DelegateDb];
GO
DROP LOGIN [HighPrivilegedLogin]
GO
DROP LOGIN [LowPrivilegedUser]
GO
DROP CERTIFICATE [DelegateCert];
GO