SQL Login ve User operasyonları

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?

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

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

SQL Server Authentication

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

CREAT LOGIN [login]
WITH PASSWORD='!aA123',
DEFAULT_DATABASE=[NORTHWND], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

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

Management Studio ile nasıl yapılır?

*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?

O zaman oluşturduğun LOGIN‘e veritabanı yetkisi vermedin sen kesin. Yani veritabanına USER oluşturmadın. Hemen onu yapalım.

Login : SQL Server’a bağlanmak 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.

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.

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 komutu kullanmamız gerekiyor. Bunlar GRANT, REVOKE ve DENY. Teker teker örneklerle inceleyelim.

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

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.
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 login kullanıcısına yetkili derecelendirme verdin o kullanıcı 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.

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.

Kaynak : 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.