Truncate Table tiesības

Rakstā vairāki varianti, kā nodrošināt tiesības dzēst tabulas datus izmantojot "Truncate" atslēgvārdu lietotājiem, kam šādas privilēģijas netiek dotas tiešā veidā (pašā minimālajā gadījumā, lietotājam jābūt Alter tiesībām uz attiecīgo tabulu). Lai arī raksts ir tieši par "Truncate Table", praktiski šeit aprakstītie piemēri ir attiecināmi uz daudzām situācijām, kad lietotājam īslaicīgi jānodrošina augstākas tiesības ne kā tiem normāli pienākas.

Sagatavošanās darbi
Šajā rakstā izmantošu datu bāzi DBTest un šajā datu bāzē lietotāju "TestUser", kam jāpiešķir tiesības izpildīt "Truncate table" attiecīgajā datu bāzē (jābūt tiesībām veidot datu bāzi un pievienot lietotājus):

use master;
Create DataBase DBTest;
Create Login TestLogin With Password = N'MyPassword';
Go
use DBTest;
Create User TestUser From Login TestLogin;

1. Tiesību piešķiršana izpildīt "Truncate Table"
Pats pirmais variants- tiesību piešķiršana izpildīt Truncate Table tiešā veidā. Šāds variants, iespējams, kādos gadījos ir apmierinošs, tomēr vienlaicīgi tas nodrošina lietotāju ar lielākām tiesībām kā nepieciešams (piemēram- pievienot/dzēst kolonnu attiecīgajai tabulai). Patiesībā šo variantu nemaz neieteiktu izmantot. Lai nu kā, tas izskatītos šādi:

use DBTest;
Create Table dbo.TestTable
(
    i int
)
Grant Alter On dbo.TestTable to TestUser
Go
-- pārbaude
Execute As User = 'TestUser'
Truncate Table dbo.TestTable
Revert
Go
Drop Table dbo.TestTable

2. Tiesību piešķiršana caur procedūru- Execute As Self
Šis jau ir daudz labāks variants ar to, ka tiesības lietotājam netiek nodrošinātas pa tiešo- tiek izveidota procedūra dbo.TruncateTestTable, kurai tiek norādīts "Execute as Self"- tātad, procedūras iekšienē visiem skriptiem ir tādas pašas tiesības, kā procedūras izveidotājam (msdn). Šeit varētu izmantot arī alternatīvu variantu- "Execute As Owner". Niansēs šeit neiegrimstot, jānorāda, ka "Self" un "Owner" nav viens un tas pats, līdz ar to derētu saprast, kurš variants ir vairāk piemērots attiecīgajai situācijai.

use DBTest;
Create Table dbo.TestTable
(
    i int
);
Go
Create Procedure dbo.TruncateTestTable
With execute As Self
As
    select USER_NAME(), SUSER_SNAME()
    Truncate Table dbo.TestTable
Go
Grant Exec on dbo.TruncateTestTable To TestUser
Go
-- pārbaude
Execute As User = 'TestUser'
Exec dbo.TruncateTestTable
Revert
Go
Drop Table dbo.TestTable
Drop Proc dbo.TruncateTestTable

3. Izmantojot datu bāzes lietotāju bez pieslēguma vārda (Proxy user)
Salīdzinoši ar Execute as Self/Execute as Owner šis variants ir labāks ar to, ka "ProxyUser" var piešķirt mazāk tiesības nekā tās ir procedūras izveidotājam (tās varētu būt sa) vai shēmas īpašniekam (db_owner). Šajā gadījumā izveidoju lietotāju bez pieslēguma vārda (without login), šim lietotājam nodrošinu tiesības izpildīt "Truncate Table" un mūsu "TestUser" piešķiru tiesības izpildīt procedūru (varētu teikt, ka šis variants ir hibrīds starp 1. un 2. variantu):

use DBTest;
Create Table dbo.TestTable
(
    i int
);
Create User ProxyUser Without Login
Grant Alter On dbo.TestTable to ProxyUser
Go
Create Procedure dbo.TruncateTestTable
With Execute As 'ProxyUser'
As
    select USER_NAME(), SUSER_SNAME()
    Truncate Table dbo.TestTable
Go
Grant Exec on dbo.TruncateTestTable To TestUser
Go
-- pārbaude
Execute As User = 'TestUser'
Exec dbo.TruncateTestTable
Revert
Go
Drop Table dbo.TestTable
Drop Proc dbo.TruncateTestTable
Drop User ProxyUser

4. Izmantojot sertifikātus
Viennozīmīgi šis variants man patīk vislabāk. Atšķirībā no iepriekšējiem diviem gadījumiem, kad procedūras izpildes laikā, lietotājs tika nomainīts uz kādu citu lietotāju, tad šajā gadījumā lietotājam, kas izpilda procedūru tiek piešķirtas privilēģijas uz procedūras izpildes laiku.
Par sertifikātiem var vairāk lasīt šeit- Sertifikāti SQL Server. Piemērs, kā parakstīt procedūras msdn.
Arī šeit tiek izveidots lietotājs datu bāzē (uz sertifikātu bāzēts) un šim lietotājam tiek piešķirtas tiesības izpildīt "Truncate Table". Prodūra tiek parakstīta ar sertifikātu, tādējādi procedūras iekšienē procedūras izpildītājam tiek piešķirtas arī sertifikāta lietotāja tiesības (pieliktas klāt).
Šeit jāņem vērā, ka procedūra ir atkārtoti jāparaksta, ja tā tiek mainīta.

use DBTest;
Create Table dbo.TestTable
(
    i int
);
CREATE CERTIFICATE [MansSertifikats]
ENCRYPTION BY PASSWORD = N'DrosaParole'
WITH SUBJECT = N'sqlblog.lv piemērs';
Create User [CertUser] From Certificate [MansSertifikats];
Grant Alter On dbo.TestTable to [CertUser]
Go
Create Procedure dbo.TruncateTestTable
As
    select USER_NAME(), SUSER_SNAME()
    Truncate Table dbo.TestTable
Go
ADD SIGNATURE TO dbo.TruncateTestTable BY CERTIFICATE [MansSertifikats]
    WITH PASSWORD = 'DrosaParole'
ALTER CERTIFICATE [MansSertifikats] REMOVE PRIVATE KEY;
Go
Grant Exec on dbo.TruncateTestTable To TestUser
Go
-- pārbaude
Execute As User = 'TestUser'
Exec dbo.TruncateTestTable
Revert
Go
Drop Table dbo.TestTable
Drop Proc dbo.TruncateTestTable
Drop User [CertUser]
Drop Certificate [MansSertifikats];

Satīrīšanas darbi
Lai izdzēstu raksta sākumā izveidoto datu bāzi un lietotāju jāizpilda skripts:

use master;
Drop DataBase DBTest;
Drop Login TestLogin