SQL Server rolleri. Veritabanında yetki verilecek kullanıcıların güvenlik amacıyla çeşitli rollerle kısıtlamalar yapmamız gerekmektedir. Yapılmak istenen işlemlere göre bu rolleri şekillendirmemiz gerekir. Bu makalemde Server ve Database düzeyindeki SQL Server rolleri detaylarına değineceğim.
1. SQL Server Rolleri : Sunucu Düzeyinde Roller
Alttaki grafikte Server rollerinin yetkinliklerini görebiliriz:
Resim-1
Tek tek server seviyesinde rollerimizi inceleyecek olursak, Security sekmesi altında Server Roles altında mevcut rollerimizi görüntüleyebiliriz.
Resim-2
Komutlar
Bu rollerimizin yetkinliklerini detaylı olarak görebilmek için aşağıdaki scripti çalıştırabiliriz:
- sp_srvrolepermission ‘ServerLevelRolesName’
Server düzeyinde aşağıdaki script ile isteniler yetki ataması yapılabilir:
- EXEC sp_addsrvrolemember @loginame=’…’, @rolename=’…’
Verilen yetkiyi almak istediğimizde de aşağıdaki scriptten faydalanabiliriz:
- EXEC sp_dropsrvrolemember @loginame=’…’, @rolename=’…’
Yetki atayıp, geri alma işlemlerini SecurityàLogins sekmesinden istediğimiz yetkiyi de server seviyesinde tanımlayabiliriz.
Roller
Server seviyesinde rollerimize geçecek olursak:
Bulkadmin:
Bu role sahip kullanıcılar BULK INSERT deyimini çalıştırabilir.
Genelde başka bir veri kaynağından ya da Excel’den veri çekme işlemleri için kullanılır.
sp_srvrolepermission ‘bulkadmin’ dediğimizde de aşağıdaki gibi yetkinliklerini görebiliyoruz.
| ServerRole | Permission |
| bulkadmin | Add member to bulkadmin |
| bulkadmin | BULK INSERT |
Dbcreator:
Bu role sahip olan kullanıcı başlıca olarak herhangi bir veritabanı Create edebilir, Alter edip düzenleyebilir, Drop edebilir veya herhangi bir veritabanını Restore edebilir.
Yazılımcılara test amaçlı bu yetki verilebilir.
| ServerRole | Permission |
| dbcreator | Add member to dbcreator |
| dbcreator | ALTER DATABASE |
| dbcreator | CREATE DATABASE |
| dbcreator | DROP DATABASE |
| dbcreator | Extend database |
| dbcreator | RESTORE DATABASE |
| dbcreator | RESTORE LOG |
| dbcreator | sp_renamedb |
Diskadmin:
Bu role sahip kullanıcılar disk dosyalarını yönetebilir.
| ServerRole | Permission |
| diskadmin | Add member to diskadmin |
| diskadmin | DISK INIT |
| diskadmin | sp_addumpdevice |
| diskadmin | sp_diskdefault |
| diskadmin | sp_dropdevice |
Processadmin:
Bu role sahip kullanıcılar tüm processleri görebilir, yönetebilir ve Kill komutuyla istediği tüm processleri sonlandırabilir.
| ServerRole | Permission |
| processadmin | Add member to processadmin |
| processadmin | KILL |
Public:
Tüm SQL Server kullanıcıları, gruplarına vs. varsayılan olarak public rolü atanır. Bu rol ile sadece SQL Server’a bağlanabilir ancak herhangi bir işlem yapamaz.
Securityadmin:
Bu role sahip kullanıcıların girişleri ve özelliklerini yönetir. GRANT, DENY ve REVOKE sunucu düzeyinde izinleri atayabilirler. Ayrıca GRANT, DENY ve REVOKE veritabanı düzeyinde izinleri de atayabilirler. SQL Server girişleri için şifreleri sıfırlayabilirler ve SQL Server hesabı oluşturup silebilirler. Rol oluşturamazlar. Kısacası SQL Server login bilgilerini yönetir.
| ServerRole | Permission |
| securityadmin | Add member to securityadmin |
| securityadmin | Grant/deny/revoke CREATE DATABASE |
| securityadmin | Read the error log |
| securityadmin | sp_addlinkedsrvlogin |
| securityadmin | sp_addlogin |
| securityadmin | sp_defaultdb |
| securityadmin | sp_defaultlanguage |
| securityadmin | sp_denylogin |
| securityadmin | sp_droplinkedsrvlogin |
| securityadmin | sp_droplogin |
| securityadmin | sp_dropremotelogin |
| securityadmin | sp_grantlogin |
| securityadmin | sp_helplogins |
| securityadmin | sp_password |
| securityadmin | sp_remoteoption (update) |
| securityadmin | sp_revokelogin |
Serveradmin:
Bu role sahip kullanıcılar sunucu genelinde yapılandırma seçeneklerini değiştirebilir ve sunucuyu kapatabilir.
| ServerRole | Permission |
| serveradmin | Add member to serveradmin |
| serveradmin | dbcc freeproccache |
| serveradmin | RECONFIGURE |
| serveradmin | SHUTDOWN |
| serveradmin | sp_configure |
| serveradmin | sp_fulltext_service |
| serveradmin | sp_tableoption |
Setupadmin:
Bu role sahip kullanıcılar linked server ekleyebilir, kaldırabilir ve bazı sistem stored procedure lerini yürütebilir.
| ServerRole | Permission |
| setupadmin | Add member to setupadmin |
| setupadmin | Add/drop/configure linked servers |
| setupadmin | Mark a stored procedure as startup |
Sysadmin:
En yetkin rolümüz system admindir. Bu role sahip kullanıcılar her işlemi gerçekleştirebilme yetkisine sahiptir. Bu yetkinin database adminleri dışında diğer kullanıcılarda bulunması önerilmez.
| ServerRole | Permission |
| sysadmin | Add extended procedures |
| sysadmin | Add member to bulkadmin |
| sysadmin | Add member to dbcreator |
| sysadmin | Add member to diskadmin |
| sysadmin | Add member to processadmin |
| sysadmin | Add member to securityadmin |
| sysadmin | Add member to serveradmin |
| sysadmin | Add member to setupadmin |
| sysadmin | Add member to sysadmin |
| sysadmin | Add/drop to/from db_accessadmin |
| sysadmin | Add/drop to/from db_backupoperator |
| sysadmin | Add/drop to/from db_datareader |
| sysadmin | Add/drop to/from db_datawriter |
| sysadmin | Add/drop to/from db_ddladmin |
| sysadmin | Add/drop to/from db_denydatareader |
| sysadmin | Add/drop to/from db_denydatawriter |
| sysadmin | Add/drop to/from db_owner |
| sysadmin | Add/drop to/from db_securityadmin |
| sysadmin | Add/drop/configure linked servers |
| sysadmin | All DDL but GRANT, REVOKE, DENY |
| sysadmin | ALTER DATABASE |
| sysadmin | BACKUP DATABASE |
| sysadmin | BACKUP LOG |
| sysadmin | BULK INSERT |
| sysadmin | CHECKPOINT |
| sysadmin | Complete SETUSER SQL user |
| sysadmin | Constraints on System tables |
| sysadmin | CREATE DATABASE |
| sysadmin | Create indices on system tables |
| sysadmin | Create/delete/modify system tables |
| sysadmin | dbcc change ‘on’ rules |
| sysadmin | dbcc checkalloc |
| sysadmin | dbcc checkdb |
| sysadmin | dbcc checkfilegroup |
| sysadmin | dbcc checkident |
| sysadmin | dbcc checktable |
| sysadmin | dbcc cleantable |
| sysadmin | dbcc dbreindex |
| sysadmin | dbcc dropcleanbuffers |
| sysadmin | dbcc freeproccache |
| sysadmin | dbcc inputbuffer |
| sysadmin | dbcc outputbuffer |
| sysadmin | dbcc pintable |
| sysadmin | dbcc proccache |
| sysadmin | dbcc setcpuweight |
| sysadmin | dbcc setioweight |
| sysadmin | dbcc show_statistics |
| sysadmin | dbcc showcontig |
| sysadmin | dbcc showoptweights |
| sysadmin | DBCC ShrinkDatabase |
| sysadmin | dbcc shrinkfile |
| sysadmin | dbcc traceon/off |
| sysadmin | dbcc updateusage |
| sysadmin | DELETE permission on any object |
| sysadmin | DENY |
| sysadmin | DISK INIT |
| sysadmin | DROP DATABASE |
| sysadmin | EXECUTE any procedure |
| sysadmin | Extend database |
| sysadmin | GRANT |
| sysadmin | Grant/deny/revoke CREATE DATABASE |
| sysadmin | INSERT permission on any object |
| sysadmin | KILL |
| sysadmin | Mark a stored procedure as startup |
| sysadmin | Raiserror With Log |
| sysadmin | Read the error log |
| sysadmin | RECONFIGURE |
| sysadmin | REFERENCES permission on any table |
| sysadmin | RESTORE DATABASE |
| sysadmin | RESTORE LOG |
| sysadmin | REVOKE |
| sysadmin | SELECT permission on any object |
| sysadmin | SHUTDOWN |
| sysadmin | sp_addapprole |
| sysadmin | sp_addlinkedsrvlogin |
| sysadmin | sp_addlogin |
| sysadmin | sp_addrole |
| sysadmin | sp_addrolemember |
| sysadmin | sp_addumpdevice |
| sysadmin | sp_adduser |
| sysadmin | sp_altermessage |
| sysadmin | sp_approlepassword |
| sysadmin | sp_change_users_login |
| sysadmin | sp_changedbowner |
| sysadmin | sp_changeobjectowner |
| sysadmin | sp_configure |
| sysadmin | sp_dbcmptlevel |
| sysadmin | sp_defaultdb |
| sysadmin | sp_defaultlanguage |
| sysadmin | sp_denylogin |
| sysadmin | sp_diskdefault |
| sysadmin | sp_dropapprole |
| sysadmin | sp_dropdevice |
| sysadmin | sp_droplinkedsrvlogin |
| sysadmin | sp_droplogin |
| sysadmin | sp_dropremotelogin |
| sysadmin | sp_droprole |
| sysadmin | sp_droprolemember |
| sysadmin | sp_dropuser |
| sysadmin | sp_fulltext_catalog |
| sysadmin | sp_fulltext_column |
| sysadmin | sp_fulltext_database |
| sysadmin | sp_fulltext_service |
| sysadmin | sp_fulltext_table |
| sysadmin | sp_grantdbaccess |
| sysadmin | sp_grantlogin |
| sysadmin | sp_helplogins |
| sysadmin | sp_password |
| sysadmin | sp_recompile |
| sysadmin | sp_refreshview |
| sysadmin | sp_remoteoption |
| sysadmin | sp_remoteoption (update) |
| sysadmin | sp_rename |
| sysadmin | sp_renamedb |
| sysadmin | sp_revokedbaccess |
| sysadmin | sp_revokelogin |
| sysadmin | sp_tableoption |
| sysadmin | sp_updatestats |
| sysadmin | TRUNCATE TABLE |
| sysadmin | UPDATE permission on any object |
| sysadmin | USE to a suspect database |
2. SQL Server Rolleri : Veritabanı Düzeyinde Roller
Veritabanı düzeyindeki roller, tanımlanmış olduğu veritabanı kapsamlarındadır.
İlgili veritabanı altında SecurityàRoles dediğimizde de bu rolleri görüntüleyebiliriz.
Resim-3
Ya da sabit veritabanı rollerini görebilmek için Security sekmesi altında Logins kısmında yetki vermek istediğimiz kullanıcıya tıklayarak User Mapping sayfasında kullanıcıya ilgili veritabanlarına roller tanımlayabiliriz. Veritabanını sadece işaretlemek varsayılan olarak public yetkisini tanımlar. Bu yetki ile sadece ilgili veritabanına erişebiliriz herhangi bir işlem yapamayız.
Resim-4
Komutlar
Aşağıdaki script ile de yetkilendirmek istediğimiz veritabanında kullanıcıya yetki tanımlayabiliriz:
- EXEC sp_addrolemember @membername=’…’, @rolename=’…’
Verilen yetkiyi geri almak için de aşağıdaki scriptten yararlanabiliriz:
-
EXEC sp_droprolemember @membername=’…’, @rolename=’…’
Veritabanı düzeyindeki rollerin yetkinliklerini aşağıdaki grafikle görebiliriz.
Resim-5
Veritabanı düzeyindeki rollerin yetkinliklerini detaylı olarak görebilmek için aşağıdaki scriptten yararlanabiliriz:
- EXEC sp_dbfixedrolepermission ‘DatabaseLevelRolesName’
Roller
Rollerimize geçecek olursak:
db_owner:
Veritabanı düzeyinde verilebilecek en kapsamlı roldür Bu role sahip kullanıcılar veritabanındaki tüm yapılandırma ve bakım etkinliklerini gerçekleştirebilir.
| DbFixedRole | Permission |
| db_owner | Add/drop to/from db_accessadmin |
| db_owner | Add/drop to/from db_backupoperator |
| db_owner | Add/drop to/from db_datareader |
| db_owner | Add/drop to/from db_datawriter |
| db_owner | Add/drop to/from db_ddladmin |
| db_owner | Add/drop to/from db_denydatareader |
| db_owner | Add/drop to/from db_denydatawriter |
| db_owner | Add/drop to/from db_owner |
| db_owner | Add/drop to/from db_securityadmin |
| db_owner | All DDL but GRANT, REVOKE, DENY |
| db_owner | BACKUP DATABASE |
| db_owner | BACKUP LOG |
| db_owner | CHECKPOINT |
| db_owner | dbcc checkalloc |
| db_owner | dbcc checkdb |
| db_owner | dbcc checkfilegroup |
| db_owner | dbcc checkident |
| db_owner | dbcc checktable |
| db_owner | dbcc cleantable |
| db_owner | dbcc dbreindex |
| db_owner | dbcc proccache |
| db_owner | dbcc show_statistics |
| db_owner | dbcc showcontig |
| db_owner | dbcc shrinkdatabase |
| db_owner | dbcc shrinkfile |
| db_owner | dbcc updateusage |
| db_owner | DELETE permission on any object |
| db_owner | DENY |
| db_owner | EXECUTE any procedure |
| db_owner | GRANT |
| db_owner | INSERT permission on any object |
| db_owner | REFERENCES permission on any table |
| db_owner | REVOKE |
| db_owner | SELECT permission on any object |
| db_owner | sp_addapprole |
| db_owner | sp_addrole |
| db_owner | sp_addrolemember |
| db_owner | sp_approlepassword |
| db_owner | sp_change_users_login |
| db_owner | sp_changeobjectowner |
| db_owner | sp_dbcmptlevel |
| db_owner | sp_dropapprole |
| db_owner | sp_droprole |
| db_owner | sp_droprolemember |
| db_owner | sp_dropuser |
| db_owner | sp_fulltext_catalog |
| db_owner | sp_fulltext_column |
| db_owner | sp_fulltext_database |
| db_owner | sp_fulltext_table |
| db_owner | sp_grantdbaccess |
| db_owner | sp_recompile |
| db_owner | sp_refreshview |
| db_owner | sp_rename |
| db_owner | sp_revokedbaccess |
| db_owner | sp_tableoption |
| db_owner | TRUNCATE TABLE |
| db_owner | UPDATE permission on any object |
db_securityadmin:
Bu role sahip kullanıcılar, yalnızca özel roller için rol üyeliğini değiştirebilir ve izinleri yönetebilir. Bu rolün üyeleri potansiyel olarak ayrıcalıklarını yükseltebilir ve eylemleri izlenmelidir.
| DbFixedRole | Permission |
| db_securityadmin | DENY |
| db_securityadmin | GRANT |
| db_securityadmin | REVOKE |
| db_securityadmin | sp_addapprole |
| db_securityadmin | sp_addrole |
| db_securityadmin | sp_addrolemember |
| db_securityadmin | sp_approlepassword |
| db_securityadmin | sp_changeobjectowner |
| db_securityadmin | sp_dropapprole |
| db_securityadmin | sp_droprole |
| db_securityadmin | sp_droprolemember |
db_accessadmin:
Bu role sahip kullanıcılar, Windows oturum açma işlemleri, Windows grupları ve SQL Server oturum açma işlemleri için veritabanına erişim ekleyebilir veya bu veritabanına erişimi kaldırabilir.
| DbFixedRole | Permission |
| db_accessadmin | sp_dropuser |
| db_accessadmin | sp_grantdbaccess |
| db_accessadmin | sp_revokedbaccess |
db_backupoperator:
Bu role sahip kullanıcılar, veritabanını yedekleyebilir.
| DbFixedRole | Permission |
| db_backupoperator | BACKUP DATABASE |
| db_backupoperator | BACKUP LOG |
| db_backupoperator | CHECKPOINT |
db_ddladmin:
Bu role sahip kullanıcılar, bir veritabanındaki herhangi bir Veri Tanımlama Dili (DDL) komutunu çalıştırabilir.
| DbFixedRole | Permission |
| db_ddladmin | All DDL but GRANT, REVOKE, DENY |
| db_ddladmin | dbcc cleantable |
| db_ddladmin | dbcc show_statistics |
| db_ddladmin | dbcc showcontig |
| db_ddladmin | REFERENCES permission on any table |
| db_ddladmin | sp_changeobjectowner |
| db_ddladmin | sp_fulltext_column |
| db_ddladmin | sp_fulltext_table |
| db_ddladmin | sp_recompile |
| db_ddladmin | sp_rename |
| db_ddladmin | sp_tableoption |
| db_ddladmin | TRUNCATE TABLE |
db_datawriter:
Bu role sahip kullanıcılar, tüm kullanıcı tablolarındaki verileri ekleyebilir, silebilir veya değiştirebilir.
| DbFixedRole | Permission |
| db_datawriter | DELETE permission on any object |
| db_datawriter | INSERT permission on any object |
| db_datawriter | UPDATE permission on any object |
db_datareader:
Bu role sahip kullanıcılar, tüm kullanıcı tablolarından tüm verileri okuyabilir.
| DbFixedRole | Permission |
| db_datareader | SELECT permission on any object |
db_denydatawriter:
Bu role sahip kullanıcılar, bir veritabanı içindeki kullanıcı tablolarındaki herhangi bir veriyi ekleyemez, değiştiremez veya silemez.
| DbFixedRole | Permission |
| db_denydatawriter | No DELETE permission on any object |
| db_denydatawriter | No INSERT permission on any object |
| db_denydatawriter | No UPDATE permission on any object |
db_denydatareader:
Bu role sahip kullanıcılar, bir veritabanı içindeki kullanıcı tablolarındaki herhangi bir veriyi okuyamaz.
| DbFixedRole | Permission |
| db_denydatareader | No SELECT permission on any object |
public:
Tüm veritabanı kullanıcılarına varsayılan olarak gelen bir roldür. En düşük izin ve ayrıcalıklarını taşımaktadır. Bir kullanıcıya public dışında atadığınız her rol, yeni ayrıcalık ve izin ekler.
Verilen bu yetkiler tüm veritabanı için geçerli yetkilerdir. Örneğin kullanıcı sadece Insert işlemi yapıyorsa bu kullanıcıya db_datawriter rolünü tanımlarsak bu kullanıcıya aynı zamanda kayıtlar için ALTER ve DELETE yetkisi de tanımlamış oluruz. Her zaman kullanıcının ihtiyacı kadar, minimum yetkiler vererek hareket etmeliyiz. Bu yüzden sadece INSERT yetkisi tanımlamak istediğimizde ilgili database üzerine sağ tık àPropertiesàPermissions sayfasına gelirsek,
Resim-6
Permission for alanında Insert yetkisini vermek için ilgili alana Grant diyebiliriz. Insert dışında aşağıdaki yetkiler de tanımlanabilir:
| Permission List |
| ALTER ANY MASK |
| ALTER ANY EXTERNAL DATA SOURCE |
| ALTER ANY EXTERNAL FILE FORMAT |
| ALTER |
| ALTER ANY ASYMMETRIC KEY |
| ALTER ANY APPLICATION ROLE |
| ALTER ANY ASSEMBLY |
| ALTER ANY CERTIFICATE |
| ALTER ANY DATABASE AUDIT |
| ALTER ANY DATASPACE |
| ALTER ANY DATABASE EVENT NOTIFICATION |
| ALTER ANY FULLTEXT CATALOG |
| ALTER ANY MESSAGE TYPE |
| ALTER ANY ROLE |
| ALTER ANY ROUTE |
| ALTER ANY REMOTE SERVICE BINDING |
| ALTER ANY CONTRACT |
| ALTER ANY SYMMETRIC KEY |
| ALTER ANY SCHEMA |
| ALTER ANY SECURITY POLICY |
| ALTER ANY SERVICE |
| ALTER ANY DATABASE DDL TRIGGER |
| ALTER ANY USER |
| AUTHENTICATE |
| BACKUP DATABASE |
| BACKUP LOG |
| CONTROL |
| CONNECT |
| CONNECT REPLICATION |
| CHECKPOINT |
| CREATE AGGREGATE |
| CREATE ASYMMETRIC KEY |
| CREATE ASSEMBLY |
| CREATE CERTIFICATE |
| CREATE DEFAULT |
| CREATE DATABASE DDL EVENT NOTIFICATION |
| CREATE FUNCTION |
| CREATE FULLTEXT CATALOG |
| CREATE MESSAGE TYPE |
| CREATE PROCEDURE |
| CREATE QUEUE |
| CREATE ROLE |
| CREATE ROUTE |
| CREATE RULE |
| CREATE REMOTE SERVICE BINDING |
| CREATE CONTRACT |
| CREATE SYMMETRIC KEY |
| CREATE SCHEMA |
| CREATE SYNONYM |
| CREATE SERVICE |
| CREATE TABLE |
| CREATE TYPE |
| CREATE VIEW |
| CREATE XML SCHEMA COLLECTION |
| DELETE |
| EXECUTE |
| INSERT |
| REFERENCES |
| SELECT |
| SHOWPLAN |
| SUBSCRIBE QUERY NOTIFICATIONS |
| TAKE OWNERSHIP |
| UNMASK |
| UPDATE |
| VIEW DEFINITION |
| VIEW ANY COLUMN ENCRYPTION KEY DEFINITION |
| VIEW ANY COLUMN MASTER KEY DEFINITION |
| VIEW DATABASE STATE |
SQL Server’da bir kullanıcıya kısıtlı yetki vermek
Kullanıcıya tüm veritabanını kapsayacak şekilde yetki vermek istemiyorsak yani veritabanındaki herhangi bir nesne özelinde bir yetki tanımlamak istiyorsak ilgili database altında Security sekmesi à İlgili kullanıcı user ına çift tık ile Securables sayfasına gidiyoruz. Burada Search deyip Specific Objects… işaretliyken OK diyoruz.
Resim-7
Bu sayfada hangi objeye özel bir yetki tanımlayacağımızı seçeriz. Object Type sayfasında örneğin Stored Procedures ve Tables alanını işaretleyelim. Örneğin yapmak istediğim ilgili kullanıcıya bir stored procedure için View Definition yetkisi tanımlamak ve seçtiğim bir tablo için de Update yetkisi vermek. Belirleyeceğim nesneyi Enter the object names to select alanında Object Types alanine belirledikten sonra Browse… diyerek arayabilir ve işaretleyebilirim.
Resim-8
Seçilmiş olan nesneler OK dedikten sonra aşağıdaki gibi karşımıza gelir. Burada Stored Procedure ün üstüne tıklayıp alt alandan View Definition yetkisini, Table ın üzerine tıklayıp da Update yetkisini Grant alanından işaretleyerek işlemi tamamlayabilirim.
Resim-9
Grant ile bu yetkileri sağlayabilirken,
With Grant seçeneği ile yetki verilen kullanıcıların, aynı yetkileri bir başka kullanıcıya da tanımlama hakkı sağlamış oluruz.
Deny seçeneği ile de kullanıcı için istenilen yetkiler engellenebilir.
Özelleştirerek vermiş olduğumuz yetkiler için kullanıcımızda Database düzeyinde public rolünde kalmış olması yeterlidir.
SQL Server rolleri için umarım faydalı bir makale olmuştur.
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar
Server and Database Roles in SQL Server
TAGs: SQL Server rolleri, SQL Server , Server Level Roles, Database Level Roles, Permissions, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator,
db_owner, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader, public