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 = 1beginset @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 = 1beginset @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 = 1beginset @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 = 1beginset @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 = 1beginset @sql_string = @command + ‘ ALTER ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_delete = 1beginset @sql_string = @command + ‘ DELETE ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_execute = 1beginset @sql_string = @command + ‘ EXECUTE ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_insert = 1beginset @sql_string = @command + ‘ INSERT ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_references = 1beginset @sql_string = @command + ‘ REFERENCES ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_select = 1beginset @sql_string = @command + ‘ SELECT ON SCHEMA::[‘ + @schema_name + ‘] TO [‘ + @user_db_role + ‘]’;exec sp_executesql @sql_string;print ‘—- ‘ + @sql_string;end;if @flag_update = 1beginset @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