Questo articolo presenta una semplice stored procedure che consente di autorizzare un utente/ruolo di database ad accedere in una specifica modalità (es. SELECT, DELETE, ALTER, ..) a tutti gli oggetti appartenenti ad uno specifico schema di database oppure a tutti gli schema esistenti nel database al momento dell'esecuzione della stored procedure.

Suddividere un database in schema comporta diversi vantaggi, uno fra questi è senza dubbio la semplificazione della gestione delle autorizzazioni utente. Proprio da questa feature trae spunto questa stored procedure, la cui idea di fondo è di incoraggiare anche gli amministratori di database più pigri a mettere da parte politiche di gestione delle autorizzazioni che fanno uso di semplificazioni estreme che vanno contro i principio di sicurezza di base (..vedi sysAdmin e db_owner).
 
/*
** Creo una login di tipo SQL.
*/
use master
go
create login [Francesco]
with 
password=N'Password1', 
check_expiration=off, 
check_policy=off
go
 
/*
** Mi sposto nel database utente in cui mi interessa autorizzare all'accesso
** la login precedentemente creata, quindi creo la user associata a tale login.
*/
use AdventureWorksLT2008
go
 
create user [Francesco] for login [Francesco]
go
 
/*
** Creo un database role a cui attribuisco la user precedentemente creata.
*/
 
create role [AW_Role]
go
exec sp_addrolemember N'AW_Role', N'Francesco'
go
 
 
/*
** Sempre nel database utente di interesse creo la stored procedure denominata
** "dbo.stp_set_role_security_on_schema"
*/
use AdventureWorksLT2008
go
 
 
/*
** Questa stored procedure consente di comandare la GRANT, la REVOKE o la DENY
** (a seconda di quello che passo al parametro @command) di tutti o solo alcuni permessi
** così come specificato nei vari parametri @flag_control,...,@flag_update, dove passando
** il valore 1 si intende includere quel tipo di permesso nella GRANT/REVOKE/DENY comandata,
** mentre passando 0 di escluderlo. 
** Il comando di autorizzazione riguarderà il database role specificato nel parametro 
** @user_db_role - quindi tutti gli utenti che in un dato momento ne fanno parte -,
** e coinvolgerà tutti gli oggetti di database (tabelle, viste, stored proc, etc..)
** di un dato schema di database specificato nel parametro @schema_name, oppure tutti gli 
** schema se al parametro @schema_name viene passato NULL.
*/
create proc dbo.stp_set_role_security_on_schema (
@user_db_role sysname,
@schema_name sysname = NULL,
@command varchar(6) = 'GRANT', --GRANT or REVOKE or DENY
@flag_control bit = 0,
@flag_take_ownership bit = 0,
@flag_view_change_tracking bit = 0,
@flag_view_definition bit = 0,
@flag_alter bit = 0,
@flag_delete bit = 0,
@flag_execute bit = 0,
@flag_insert bit = 0,
@flag_references bit = 0, 
@flag_select bit = 0,
@flag_update bit = 0
)
as
declare @sql_string nvarchar(400);
 
declare c_schemas cursor static

for

select name from sys.schemas 
where name not in (
'guest', 
'INFORMATION_SCHEMA', 
'sys', 
'db_owner', 
'db_accessadmin', 
'db_securityadmin', 
'db_ddladmin', 
'db_backupoperator', 
'db_datareader', 
'db_datawriter', 
'db_denydatareader', 
'db_denydatawriter',
@user_db_role
)
and

(@schema_name is null or @schema_name = name);

 

open c_schemas;
 
fetch next from c_schemas into @schema_name;
 
print 'Set permissions to [' + @user_db_role + ']';
 
while @@FETCH_STATUS = 0
begin 
print '-- ' + @command + ' permissions on [' + @schema_name + ']';
 
if @flag_control = 1
begin
set @sql_string = @command + ' CONTROL ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
 
if @flag_take_ownership = 1
begin
set @sql_string = @command + ' TAKE OWNERSHIP ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
if @flag_view_change_tracking = 1
begin
set @sql_string = @command + ' VIEW CHANGE TRACKING ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
if @flag_view_definition = 1
begin
set @sql_string = @command + ' VIEW DEFINITION ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
 
if @flag_alter = 1
begin
set @sql_string = @command + ' ALTER ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
if @flag_delete = 1
begin
set @sql_string = @command + ' DELETE ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
 
if @flag_execute = 1
begin
set @sql_string = @command + ' EXECUTE ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
 
if @flag_insert = 1
begin
set @sql_string = @command + ' INSERT ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
 
if @flag_references = 1
begin
set @sql_string = @command + ' REFERENCES ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
 
if @flag_select = 1
begin
set @sql_string = @command + ' SELECT ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
 
if @flag_update = 1
begin
set @sql_string = @command + ' UPDATE ON SCHEMA::[' + @schema_name + '] TO [' + @user_db_role + ']';
exec sp_executesql @sql_string;
print '---- ' + @sql_string;
end;
 
 
fetch next from c_schemas into @schema_name;
end;
 
close c_schemas;
deallocate c_schemas;
 
go
 
 
/*
** Esempi d'uso.
** a) Autorizzo il ruolo [AW_Role] ad accedere in SELECT, INSERT, DELETE, UPDATE, EXECUTE
** sugli oggetti dello schema [SalesLT]
*/
use AdventureWorksLT2008
go
exec dbo.stp_set_role_security_on_schema
@user_db_role = 'AW_Role',
@schema_name = 'SalesLT',
@command = 'GRANT',
@flag_delete = 1,
@flag_execute = 1,
@flag_insert = 1,
@flag_select = 1,
@flag_update = 1;

go

 

/*
** b) Nego al ruolo [AW_Role] l'autorizzazione ad accedere in ALTER
** sugli oggetti di tutti gli schema di database
*/
use AdventureWorksLT2008
go
exec dbo.stp_set_role_security_on_schema
@user_db_role = 'AW_Role',
@schema_name = NULL,
@command = 'DENY',
@flag_alter = 1;

go