SQL Kullanıcı işlemleri, oluşturma, yetki atamaları ve güvenlik

–SQL Server’da User Management’ı öğreniyorum, sizin de unutursam ilerde benim de haberim olsun ha.

SQL Server’da iki çeşit login authentication mode bulunuyor. SQL Server Authentication ve Windows Authentication.

Windows Authentication

Bu şekilde yapılan login işleminde şifreleme tarafı Windows’a bırakılır. Windows Authentication login işlemini Microsoft best practice olarak sunar.

T-SQL ile nasıl yapılır?

--PCNAME yazan kısma bilgisayarın WORKGROUP'ta ise bilgisayar adını, etki alanında isen etki alanının adını yaz.
--USERNAME kısmına windows giriş kullanıcı adını yaz.
--DEFAULT_DATABASE atanılır ise kullanıcı oturumunu açtığında hangi DB atanmış ise (tabii değiştirmez ise) onda çalışacaktır.
SELECT * FROM sys.databases --Bu query ile DB'leri görebilirsin.

CREATE LOGIN [PCNAME\USERNAME] FROM WINDOWS
WITH DEFAULT_DATABASE=[NORTHWND]

 

SQL Server’da nasıl yapılır?

login

 

*İki türlü de denedim, tek farkları Default Language’i T-SQL ile yaparken belirlemediğim için English olarak geldi.

SQL Server Authentication

T-SQL ile nasıl yapılır?

CREAT LOGIN [login] --Kullanıcı adı
WITH PASSWORD='!aA123', --Parola
DEFAULT_DATABASE=[NORTHWND], CHECK_EXPIRATION=OFF, -- Şifre sınırsız olsun,belli bir süre sonra değişmesi gerekmesin
CHECK_POLICY=ON --Şifre karmaşıklık kontrolü yapılsın.

 

Parametre ve Argümanlar

domain_name : The name of the Windows domain account.
login_name  :The name of the Login.
database_name : The default database to assign to the Login.
language_name : The default language to assign to the Login.
CHECK_EXPIRATION : By default, it set to OFF. This option determines whether password expiration policy is enforced. You must specifiy CHECK_EXPIRATION = ON when you use the MUST_CHANGE option.
password : The password to assign to the Login.
hashed_password : The hashed value of the password to assign to the Login.
MUST_CHANGE : It is used when you want to force the password to be changed the first time that the Login is used.
sid_value : The GUID of the login. If this parameter is omitted, SQL Server will assign a GUID to the Login.
credential_name : The name of a credential to assign to the Login.
certificate_name : The name of the certificate to assign to the Login.
asym_key_name : The name of an asymmetric key to assign to the Login.

*Kaynak : techonthenet.com/sql_server/users/create_login.php

SQL Server’da nasıl yapılır?
login

*Confirm password alanını da doldur 🙂

Yetkilendirme

Loginleri oluşturdun ama halen giriş yapamıyor musun? Aşağıdaki hata patladı mı yüzüne?

login3

O zaman oluşturduğun logine DB yetkisi vermedin sen kesin. Yani DB’ye User Create etmedin. Hemen onu yapalım.

Login diye bahsettiğim SQL Server’ı açmak için kullanılan, User diye bahsettiğim DB işleri için kullanılandır. Login’e Server Role’lar atanabilirken User’a atanamaz. Aslında ikisi birbirine bağlıdır, ancak kullanım amaçlarında farklılıklar var.

Use [NORTHWND]
GO
CREATE USER [login] FOR
LOGIN [login]
GO

 

*Default Schema olarak dbo’yu getirdi.

login4

Role’ler 3’e ayrılıyor : Database Role, Server Role ve Application Role

1- DATABASE ROLE : Kullanıcının DB üzerinde işlem yapmasını sağlamak yada engellemek için kullanılan role biçimidir.

Bu şekilde de giriş yapmaktan başka bir şey yapamıyor, NORTHWND DB’inin tablolarını bile göremiyorsun. Verilebilecek tüm database yetkileri aşağıda var.

*Microsoft’un sitesinden alındı kendisi.

Fixed-Database role name Description
db_owner Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and SQL Data Warehouse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
db_securityadmin Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

 

database role

Yine Microsoft’un sitesinden aldığım, database role’lerde hangisi ne iş yapıyor hangisi hangisini kapsıyor hepsi bu resimde belli oluyor zaten.

exec sp_helpdbfixedrole -- DB'deki tanımlanmış Role'leri gösterir.

exec sp_helpdbfixedrole --Role'lerin yetkilerini gösterir.

exec sp_helprole -- SQL Server'da tanımlanmış Role'leri ve Aplication Role'leri gösterir.

exec sp_addrolemember @rolename='db_owner',@membername='username' --Kullanıcıya DB Role atamak için kullanılır.

--Kullanım şekline bir örnek login kullanıcısına backup yetkisi vermek ve geri almak.
exec sp_addrolemember 'db_backupoperator','login'

exec sp_droprolemember 'db_backupoperator', 'login'

 

Yukarıda ki role’ler dışında kendi role’ümüzü de oluşturabiliriz. Hemen okuma ve yazma işlemlerinin yapılabileceği bir role oluşturayım.

CREATE ROLE okumayazma AUTHORIZATION db_owner --db_owner kullanıcısı oluşturulan role'de düzenleme yapabilir.

--writer ve reader yetkilerini oluşturduğum role'e atıyorum
ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO okumayazma
GO
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO okumayazma

--Oluşturduğum role'u loginime atıyorum
exec sp_addrolemember 'okumayazma','login'

 

Şimdi ise User’ımıza tablo oluşturma, okuma, yazma, silme yetkileri verip alalım. Bu işlemleri yapabilmek için 3 ana komut kullanmamız gerekiyor. Bunlar GRANT, REVOKE ve DENY. Teker teker örneklerle inceleyelim.

1 – GRANT : Yetki vermek için kullanılan komut.

Syntax/Sözdizimi : GRANT izin ON izinverilenobje TO kullanıcı;

Verilebilecek izinler;

Privilege Description
SELECT Tabloya SELECT yetkisi atamak için
INSERT Tabloya INSERT yetkisi atamak için
UPDATE Tabloya UPDATE yetkisi atamak için
DELETE Tabloya DELETE yetkisi atamak için
REFERENCES Ability to create a constraint that refers to the table. – Ne işe yaradığını bilmiyorum, öğrenince yazıcam : )
ALTER Tablo tanımlarını değiştirmek için verilen yetki
ALL Bütün tablo yetkileri aktif eder.
CREATE CREATE etme yetkisi için
*WITH GRANT OPTION Bu yetkike dereceli yetkilendirme denir. Bir kullanıcıya verdiğin yetkileri, bu kullanıcının da başkasına verebilmesi için kullanılır. Örneğin; Ahmet kullanıcısına bir tabloya SELECT ve UPDATE işlemleri için yetki verdin ayrıca GRANT OPTION yetkisi verdin. Ahmet kullanıcı Ali kullanıcısına aynı yetkileri verebilir. Ahmet, Ali kullanıcısına DELETE yetkisi veremez çünkü kendisine böyle bir yetki verilmedi.
GRANT CREATE TABLE to [login] -- Tablo create yetkisini veriyoruz. Kullanıcımın adı login olduğu için köşeli parantez içine almadan işlem yapamıyorum çünkü login bir SQL Syntax'ı.

GRANT SELECT (ID, Name) ON Cust TO [login] -- Cust tablosunda sadece ID ve Name kolonlarını okusun.

GRANT SELECT ON Cust TO [login] -- Kullanıcıma Cust tablosunu SELECT atma yetkisi verdim, bunun yanında UPDATE yetkisi de vermek için SELECT,UPDATE şekilde kullanabilirsin.

GRANT SELECT ON Cust TO [login] -- Yetkili derecelendirme
WITH GRANT OPTION

 

2- DENY : Engellemek için kullanılır.

DENY CREATE TABLE to [login] -- Tablo create yetkisini kullanıcımdan geri aldım.

DENY SELECT,UPDATE ON Orders TO [login] -- Orders tablosuna SELECT ve UPDATE yapamasın.

 

3- REVOKE : GRANT ile verilen yetkileri default’a çeker, yani eski haline döndürür.

REVOKE ALL ON Cust TO [login]

 

Şimdi sen diyeceksin ki sen login kullanıcısına yetkili derecelendirme verdin o kullanıcısı Ahmet kullanıcısına yetki verdiyse ben bunu nasıl geri alacağım?
-Tamam sakin ol, gel bi CASCADE yapalım senle. Zaten REVOKE yaptığında da sana söylüyor aslında.

cascade

REVOKE ALL TO [login] CASCADE -- CASCADE işlemi de bu şekilde yapılır.

 

 

2 – SERVER ROLE : Kullanıcının server kısmında yani maintance kısmında yapabilecekleri işlemleri kısıtlayın yada yetki verin.

Role’ler hakkında bilgi edinebileceğim tablo. (Microsoft : https://msdn.microsoft.com/en-us/library/ms188659.aspx)

Fixed server-level role Description
sysadmin Members of the sysadmin fixed server role can perform any activity in the server.
serveradmin Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadmin Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

** Security Note ** The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.

processadmin Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadmin Members of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using Management Studio.)
bulkadmin Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmin The diskadmin fixed server role is used for managing disk files.
dbcreator Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
public Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You cannot change membership in public.

Note: public is implemented differently than other roles. However, permissions can be granted, denied, or revoked from public.

EXEC sp_addsrvrolemember 'login', 'sysadmin' -- loginime sysadmin yetkisi atadım.

CREATE SERVER ROLE auditors AUTHORIZATION securityadmin -- Audit yetkisi için role oluşturma

ALTER SERVER ROLE [sysadmin] DROP MEMBER [login] --Verilen yetkiyi geri almak
GO

--Hangi kullanıcıya hangi Server Role atanmış

SELECT sys.server_role_members.role_principal_id, role.name AS RoleName,
sys.server_role_members.member_principal_id, member.name AS MemberName
FROM sys.server_role_members
JOIN sys.server_principals AS role
ON sys.server_role_members.role_principal_id = role.principal_id
JOIN sys.server_principals AS member
ON sys.server_role_members.member_principal_id = member.principal_id

 

 

Server Role için kullanılabilecek syntax’lar

Feature Type Description
sp_helpsrvrole (Transact-SQL) Metadata Returns a list of server-level roles.
sp_helpsrvrolemember (Transact-SQL) Metadata Returns information about the members of a server-level role.
sp_srvrolepermission (Transact-SQL) Metadata Displays the permissions of a server-level role.
IS_SRVROLEMEMBER (Transact-SQL) Metadata Indicates whether a SQL Server login is a member of the specified server-level role.
sys.server_role_members (Transact-SQL) Metadata Returns one row for each member of each server-level role.
sp_addsrvrolemember (Transact-SQL) Command Adds a login as a member of a server-level role. Deprecated. Use ALTER SERVER ROLE instead.
sp_dropsrvrolemember (Transact-SQL) Command Removes a SQL Server login or a Windows user or group from a server-level role. Deprecated. Use ALTER SERVER ROLE instead.
CREATE SERVER ROLE (Transact-SQL) Command Creates a user-defined server role.
ALTER SERVER ROLE (Transact-SQL) Command Changes the membership of a server role or changes name of a user-defined server role.
DROP SERVER ROLE (Transact-SQL) Command Removes a user-defined server role.
IS_SRVROLEMEMBER (Transact-SQL) Function Determines membership of server role.

Son olarak bu login ve user’ı nasıl sileceğime değineceğim

USE [NORTHWND] -- User'ı siliyorum, database için oluşturduğumuz user demiştik.
GO
DROP USER [login]
GO

USE [master] -- Login'i siliyorum.
GO
DROP LOGIN [login]
GO

-- Eğer login giriş yapılı haldeyse yada bir şekilde bir session attıysa bu session'u bulup kill etmeden silemezsin.
SELECT * FROM sys.dm_exec_sessions WHERE login_name = 'login'

KILL 52

SELECT * FROM master.sys.sql_logins -- Login olanları görmek için.

 

1 Yorum

Leave a Comment.