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.
Yazınızı çok beğendim teşekkürler.