Home > Articoli > Un metodo efficace di gestione delle autorizzazioni utente

Un metodo efficace di gestione delle autorizzazioni utente

Davide Mauri 7 Giugno 2010 Articoli 1,054 Views

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

 

Tags

Chi รจ Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

Il tuo laboratorio virtuale per SQL Server con Azure Lab Services!

Recentemente ho avuto l’occasione di preparare un workshop rivolto alle figure professionali che intendono acquisire …

User Group Italiano SQL Server - Via Del Capanno 26, Lodi - P.IVA: 03015800968
Tutti i contenuti (c) UGISS, 2021