Yetki Sorgulama Scriptleri - 3 - Obje Bazında Verilen Yetkilerin Sorgulanması
Çok kullanıcı sistemlerde güvenlik açısından her zaman tanımlı rollerin yetki olarak verilmesi uygun olmayabilir. Örneğin bir uygulama, veritabanında bulunan sadece bir tablodan veri okuyacaksa böyle bir durumda db_datareader yetkisi vermektense sadece ilgili tabloya yetki vermek daha anlamlı olacaktır. Tabii obje bazında yetki vermenin güvenlik açısından avantajı olduğu gibi yönetilebilirlik açısından dezavantajları bulunmaktadır. İşte bugün paylaşacağım sorgu ile obje bazında verilen yetkileri daha hızlı bir şekilde yönetebilirsiniz.
Obje bazında verilen yetkileri analiz etmek için kullanacağımız sorgu şu şekilde;
--paramaters
declare @Databases varchar(Max)='', –- istediğiniz DB ler için araştırma yapabilirsiniz.
@GranteeUserName varchar(100)='', –- Hangi userların yetkisine bakmak istiyorsunuz?
@GrantorUserName varchar(100)='dbo', -- Hangi user ın verdiği yetkilere bakmak istiyorsunuz?
@ObjectName varchar(100)='' -- Hangi obje için verilmiş yetkilere bakmak istiyorsunuz?
--
--variables
declare @sql varchar(max)=''
declare @DBName varchar(MAx)
declare @DatabasesTmp varchar(max)=@Databases
Declare @Tbl_DB Table(DB varchar(Max))
Create Table ##DB_Permissions(DBName sysname,Obj_id int,ObjSchemaName sysname,Obj_Name sysname,UserID int
,UserName sysname,state_desc nvarchar(60),permission_name nvarchar(128))
--
--Split Databases
if @DatabasesTmp<>'' begin
while @DatabasesTmp <> '' begin
if CHARINDEX(',',@DatabasesTmp)>0 begin
insert @Tbl_DB select SUBSTRING(@DatabasesTmp,1,CHARINDEX(',',@DatabasesTmp)-1)
set @DatabasesTmp = SUBSTRING(@DatabasesTmp,CHARINDEX(',',@DatabasesTmp)+1,LEN(@DatabasesTmp))
end else begin
insert @Tbl_DB select @DatabasesTmp
set @DatabasesTmp = ''
end
end
end else begin
insert @Tbl_DB select name from sys.databases where database_id>4
end
--
--Code
declare Cursorx cursor for
select DB from @Tbl_DB
open Cursorx
fetch from Cursorx into @DBName
while @@FETCH_STATUS=0
begin
set @sql=
'insert ##DB_Permissions ' + CHAR(10) +
'select '''+@DBName+''',b.object_id as Obj_id,d.[name] as ObjSchemaName,b.[name] as Obj_Name ' + CHAR(10) +
' ,grantee_principal_id as UserID,c.[name] as UserName,a.[state_desc],a.[permission_name] ' + CHAR(10) +
'from ['+@DBName+'].sys.database_permissions A ' + CHAR(10) +
'JOIN ['+@DBName+'].[sys].[objects] b ON A.major_id = B.object_id ' + CHAR(10) +
'JOIN ['+@DBName+'].[sys].[database_principals] c ON grantee_principal_id = c.principal_id ' + CHAR(10) +
'JOIN ['+@DBName+'].[sys].[database_principals] c2 ON grantor_principal_id = c2.principal_id ' + CHAR(10) +
'JOIN ['+@DBName+'].sys.schemas d ON b.schema_id = d.schema_id ' + CHAR(10) +
'where ((isnull('''+@GranteeUserName+''','''')='''') or (c.name='''+@GranteeUserName+''')) ' + CHAR(10) +
' and ((isnull('''+@GrantorUserName+''','''')='''') or (c2.name='''+@GrantorUserName+''')) ' + CHAR(10) +
' and ((isnull('''+@ObjectName+''','''')='''') or (b.name='''+@ObjectName+''')) '
--print @sql
exec(@sql)
fetch next from Cursorx into @DBName
end
close Cursorx
deallocate Cursorx
select * from ##DB_Permissions
drop table ##DB_Permissions
Örnek rapor çıktısı şu şekilde olacaktır:
