C# kitabı

SQL Server’de bir kullanıcıya verdiğimiz yetkileri başka bir kullanıcıya nasıl kopyalayabiliriz. T-SQL’de bunu doğrudan yapabileceğimiz bir komut yapısı bulunmamaktadır. Her defasında her kullanıcı için GRANT ve DENY ifadelerini kullanarak manual olarak aynı yetkilerin verilmesi gerekir. Veya Management Studio veya Enterprise Manager üzerindeki Generate Script bölümü kullanılarak veritabanına ait script kodları oluşturulur. Orada ilgili kopyalama ve değiştirme işlemi yapılır. Bu yazıda nasıl bir script yazılarak bu işlemin daha kolay yapılacağını örneklendireceğiz.

Öncelikle Login1 ve Login2 isminde iki tane SQL Server bağlantı kullanıcısı tanımlayalım.

CREATE LOGIN Login1 WITH PASSWORD = '123';
GO
CREATE LOGIN Login2 WITH PASSWORD = '123';

Bu kullanıcıları Deneme veritabanına bağlanmaları için ilişkilendirelim yani Deneme veritabanı için user tanımlayacağız.

CREATE USER [Kullanici1] FOR LOGIN [Login1]
GO
CREATE USER [Kullanici2] FOR LOGIN [Login2]

Kullanici1 isimli kullanıcıyı Deneme veritabanındaki Musteri tablosunu SELECT edebilecek ama INSERT ve DELETE yapamayacak şekilde yetkilendirelim. Musteri tablosundaki MusteriId kolonunu SELECT etmesin sadece AdSoyad kolonunu SELECT edebilsin.

DENY DELETE ON [dbo].[Musteri] TO [Kullanici1]
DENY INSERT ON [dbo].[Musteri] TO [Kullanici1]
DENY SELECT ON [dbo].[Musteri] ([MusteriId]) TO [Kullanici1]
GRANT SELECT ON [dbo].[Musteri] ([AdSoyad]) TO [Kullanici1] AS [dbo]

Bu işlemi kontrol etmek için Login1 ile SQL Server’e bağlanıp Musteri tablosundan kayıt silelim. Bu durumda The DELETE permission was denied on the object ‘Musteri’, database ‘Deneme’, schema ‘dbo’. hata mesajıyla karşılaşırız. Veya tablodaki tüm kolonları listelemek istediğimiz zaman The SELECT permission was denied on the column ‘MusteriId’ of the object ‘Musteri’, database ‘Deneme’, schema ‘dbo’. hata mesajı verilir.

SQL Server 2005′in güvenlikle ilgili sorgulanacak catalog view’leri şunlardır;
sys.objects : Mevcut veritabanı içerisindeki table, view, stored procedure nesnelerini listeler.
sys.columns : Table, view gibi nesnelerin kolonlarını listeler.
sys.database_role_members : Veritabanı kullanıcısıyla veritanı rolleri arasındaki ilişkiyi listeler.
sys.database_permissions : Kullanıcı ve rollere atanmış olan database seviyesindeki yetkileri listeler.
sys.database_principals : Database kullanıcı ve rollerini listeler.

Bu katalogları kullanarak tüm yetkileri ilişkili oldukları nesnelerle aşağıdaki script aracılığıyla listeleyebiliriz.

select   database_permissions.permission_name,
           coalesce(objects.type_desc,database_permissions.class_desc)
                           + case when objects.type_desc is not null and minor_id > 0 then '-COLUMN'
                                      else '' end as object_type,
           case database_permissions.class_desc
                  when 'SCHEMA' then schema_name(major_id)
                  when 'OBJECT_OR_COLUMN' then
                                  case when minor_id = 0 then object_name(major_id)
                                   else (select object_name(object_id) + '.'+ name
                                          from sys.columns
                                          where object_id = database_permissions.major_id
                                             and column_id = database_permissions.minor_id) end
                  else 'other' end as object_name,
           database_principals.name as database_principal,
           database_permissions.state_desc as grant_state
from    sys.database_permissions
               join sys.database_principals
                     on database_permissions.grantee_principal_id = database_principals.principal_id
               left join sys.objects
                     on objects.object_id = database_permissions.major_id
where database_permissions.major_id > 0
    and permission_name in ('SELECT','INSERT','UPDATE','DELETE')
order by object_name 

SQL Server 2000′de konuyla ilgili olarak syspermissions, sysprotects, sysobjects, sysusers, syscolumns katalog nesneleri kullanılmaktadır.

Aşağıdaki kodlar bir kullanıcıya ait yetkileri diğer kullanıcıya kopyalamak için ikinci kullanıcıya göre uyarlanmış scriptleri oluşturur.

SET NOCOUNT ON

DECLARE	@EskiKullanici sysname, @YeniKullanici sysname

SET	@EskiKullanici = 'Kullanici1'
SET	@YeniKullanici = 'Kullanici2'

SELECT '--'+ QUOTENAME(DB_NAME()) +' veritabanını seç'
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME())

SELECT '--'+ QUOTENAME(@EskiKullanici) + ' kullanıcısının yetkileri '+
	QUOTENAME(@YeniKullanici) +'''ye kopyalanacak'

SELECT	'EXEC sp_addrolemember @rolename ='
	+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')
	+ ', @membername =' + SPACE(1) + QUOTENAME(@YeniKullanici, '''') AS '--Rol yetkileri'
FROM	sys.database_role_members AS rm
WHERE	USER_NAME(rm.member_principal_id) = @EskiKullanici
ORDER BY rm.role_principal_id ASC

SELECT	CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
	+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON '
	+ QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
	+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
	+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@YeniKullanici) COLLATE database_default
	+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END
	AS '--Nesne Seviyesindeki Yetkiler'
FROM	sys.database_permissions AS perm
	INNER JOIN
	sys.objects AS obj
	ON perm.major_id = obj.[object_id]
	INNER JOIN
	sys.database_principals AS usr
	ON perm.grantee_principal_id = usr.principal_id
	LEFT JOIN
	sys.columns AS cl
	ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE	usr.name = @EskiKullanici
ORDER BY perm.permission_name ASC, perm.state_desc ASC

SELECT	CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
	+ SPACE(1) + perm.permission_name + SPACE(1)
	+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@YeniKullanici) COLLATE database_default
	+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1)
	+ 'WITH GRANT OPTION' END AS '--Database Seviyesindeki Yetkiler'
FROM	sys.database_permissions AS perm
	INNER JOIN
	sys.database_principals AS usr
	ON perm.grantee_principal_id = usr.principal_id
WHERE	usr.name = @EskiKullanici
AND	perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC

Bu script aşağıdaki gibi script yapısını oluşturur.

--[Deneme] veritabanını seç
----------------------------
USE [Deneme]

--Database Seviyesindeki Yetkiler
-----------------------------
GRANT CONNECT TO [Kullanici2]

Bu script’i Query Analyzer’a kopyalayıp çalıştırmamız yeterlidir.

Şimdi tüm yetkileri listeleyecek olursak aşağıdaki gibi Kullanici2 de dahil edilmiş olur.

Kaynak : vyaskn.tripod.com