SQL Server Kullanıcı Oluşturma ve Yetki Yönetimi

Bilindiği üzere SQL Server’da varsayılan olarak sa (system administrator) kullanıcısı bulunur fakat bu kullanıcıyı uygulamalarda kullanmamalıyız. Ya da uygulamanın erişebileceği tabloları, yapabileceği eylemleri kısıtlamak isteyebiliriz. İşte tam bu noktada bize yeni kullanıcılar gerekir…

Giriş Hesabı Oluşturma

Öncelikle master DB’yi seçip aşağıdaki komut ile giriş hesabı oluşturmalıyız.

CREATE LOGIN "<LOGIN_NAME>" WITH PASSWORD = '<PASSWORD>';

Örneğin;

CREATE LOGIN "api-app" WITH PASSWORD = 'FYRqk9dxRkxCCHeKU6';

Yukarıdaki örnek ile api-app adında ve FYRqk9dxRkxCCHeKU6 şifresine sahip bir giriş hesabı oluşturduk.

Kullanıcı Oluşturma

Şimdi bir kullanıcı oluşturup giriş hesabımız ile bağlamalıyız.

CREATE USER <USER_NAME> FROM LOGIN [<LOGIN_NAME>];

Örneğin;

CREATE USER "api-app" FROM LOGIN [api-app];

Yukarıdaki örnek ile api-app adında bir kullanıcı oluşturduk ve yine aynı isme sahip api-app giriş hesabına bağladık.

Sonrasında erişim sağlamasını istediğimiz veritabanına geçip aynı sorguyu tekrar çalıştırmamız gerekiyor ki ilgili DB içerisinde de kullanıcıyı tanımlamış olalım.

Kullanıcı Yetkilendirme

Bu noktada iki seçeneğimiz var. Eğer aynı yetkiye sahip birden fazla kullanıcı olacaksa Rol oluşturabilir ve kullanıcıları bu rol altında toplayabiliriz. Eğer kullanıcı bazında spesifik bir yetki tanımlamasına ihtiyaç duyuyorsak rol kullanmak yerine doğrudan yetki tanımlayabiliriz.

Rol Ekleme/Kaldırma

docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15 sayfasını inceleyerek hali hazırda kullanabileceğimiz rolleri ve roller hakkında bilgi edinebilirsiniz.

Eğer hali hazırdaki rollerden ya da kendi oluşturduğunuz rollerden atama yapmak istersek, rol eklemek için sp_addrolemember adındaki SP (stored procedure) kullanacağız.

Veri Okuma Rol Tanımlama

EXEC sp_addrolemember 'db_datareader', 'api-app';

Veri Yazma Rol Tanımlama

EXEC sp_addrolemember 'db_datawriter', 'api-app';

Veritabanı Sahipliği Rol Tanımlama

EXEC sp_addrolemember 'db_datawriter', 'api-app';

Aynı söz dizimi ile sp_droprolemember SP’sini kullanırsak tanımladığımız rolü kaldırabiliriz.

Veri Okuma Rol Kaldırma

EXEC sp_droprolemember 'db_datareader', 'api-app';

Yetki Ekleme/Kaldırma

Bir kullanıcı için tablo ve eylem seviyesinde yetki tanımlaması yapabiliriz. Yani api-app kullanıcısı Brands tablosunda sadece SELECT sorgusu çalıştırabilir diyebiliriz.

Eğer standart gelen roller dışında yeni rol oluşturursak ilgili rol içinde aynı şekilde yetki tanımlaması yapmamız gerekmektedir.

Yukarıda belirttiğim örneğin sorgusu aşağıdaki gibidir;

GRANT SELECT on Brands to [api-app];

Burada yalnızca SELECT yetkisi verdik, eğer birden fazla yetki vermek istiyorsak bunları virgül ile ayırarak belirtmeliyiz. Yani hem SELECT hemde UPDATE vermek istersek sorgumuz aşağıdaki gibi olmaldır.

GRANT SELECT,UPDATE on Brands to [api-app];

Eğer eklediğimiz yetkiyi kaldırmak istersek GRANT yerine REVOKE kullanacağız yani; eğer son sorguda verdiğimiz UPDATE yetkisini kaldırmak istersek komutumuz aşağıdaki şekilde olacaktır.

REVOKE UPDATE on Brands to [api-app];

Verilmiş Rolleri Görüntüleme

Tüm kullanıcılar için görüntülemek isterseniz bu sorguyu db_owner rolüne sahip bir kullanıcı ile çalıştırmanız gerekir. (Eğer kısıtlanmış bir kullanıcı ile giriş yaparsanız yalnızca kendi rol tanımlamalarınızı görebilirsiniz.)

select dp2.name, dp.name from sys.database_role_members as drm
INNER JOIN sys.database_principals as dp ON dp.principal_id=drm.role_principal_id
INNER JOIN sys.database_principals as dp2 ON dp2.principal_id=drm.member_principal_id
order by dp2.name asc;

Tanımlanmış Yetkileri Görüntüleme

Tüm kullanıcılar için görüntülemek isterseniz bu sorguyu db_owner rolüne sahip bir kullanıcı ile çalıştırmanız gerekir. (Eğer kısıtlanmış bir kullanıcı ile giriş yaparsanız yalnızca kendi yetki tanımlamalarınızı görebilirsiniz.)

SELECT
	dp.name, so.type_desc,ss.name, so.name,dbp.state_desc, dbp.permission_name, dp.create_date,dp.modify_date
FROM sys.database_permissions as dbp
	JOIN sys.objects as so ON dbp.major_id = so.OBJECT_ID
	JOIN sys.schemas as ss ON so.SCHEMA_ID = ss.SCHEMA_ID
	JOIN sys.database_principals dp ON dbp.grantee_principal_id = dp.principal_id
order by dbp.grantee_principal_id desc;

Peki ama kullanıcıları neden ayırmalıyız? (Aslında bu sorulmaması gereken bir sorudur.)

Bu sorunun bir çok yanıtı var fakat eğer ikna olmak istiyorsanız;

• Hangi kullanıcının hangi sorguyu çalıştırdığını kayıt altına almaya ihtiyaç duyuyorsanız kullanıcılarınızı ayırmalısınız.
• Veritabanına erişebilen kişilere tablo vs. bazında erişim vermek istiyorsanız kullanıcılarınızı ayırmalısınız.
• Performans analizi vs. yaptığınızda isteğin hangi kullanıcıdan geldiğini bilmezseniz; sorgunun nereden gönderildiğini yani istemci kaynağını anlamanız ve haliyle müdahele etmeniz zorlaşır.

Bu arada hata yapma ihtimali yüksek olan biz insanların zaten veritabanına erişiminin olmaması, eğer şartlar nedeniyle olması gerekiyorsa da yalnızca okuma yapacak yetkiye sahip olması gerektiğini hatırlatmakta fayda var.

Update/Delete eylemlerinde where koşullarınızı doğru belirlediğiniz, optimizasyon sorunu yaşamadığınız ve yedeklerinizin erişilebilir olduğu ölçeklenme sorunları yaşamadığınız güzel günler geçirmeniz dileğiyle…