İçeriğe geç

Yunus UYANIK

Microsoft SQL Database Consultant
  • Blog
  • Masters Of Scripts

Yunus UYANIK

Microsoft SQL Database Consultant
  • Blog
  • Masters Of Scripts

SQL Login ve User operasyonları

  • Yunus UYANIK Yunus UYANIK
  • 26 Eylül 201616 Mayıs 2020
  • 1 yorum
  • Login & User

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?

login

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?

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

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 nameDescription
db_ownerMembers 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_securityadminMembers 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_accessadminMembers 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_backupoperatorMembers of the db_backupoperator fixed database role can back up the database.
db_ddladminMembers of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriterMembers of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareaderMembers of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriterMembers of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareaderMembers 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 görüntüde 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 komutu kullanmamız gerekiyor. Bunlar GRANT, REVOKE ve DENY. Teker teker örneklerle inceleyelim.

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

Verilebilecek izinler;

PrivilegeDescription
SELECTTabloya SELECT yetkisi atamak için
INSERTTabloya INSERT yetkisi atamak için
UPDATETabloya UPDATE yetkisi atamak için
DELETETabloya DELETE yetkisi atamak için
REFERENCESAbility to create a constraint that refers to the table.
ALTERTablo tanımlarını değiştirmek için verilen yetki
ALLBütün tablo yetkileri aktif eder.
CREATECREATE etme yetkisi için
*WITH GRANT OPTIONBu 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.

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.

Kaynak : https://msdn.microsoft.com/en-us/library/ms188659.aspx

Fixed server-level roleDescription
sysadminMembers of the sysadmin fixed server role can perform any activity in the server.
serveradminMembers of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadminMembers 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.

processadminMembers of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadminMembers 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.)
bulkadminMembers of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadminThe diskadmin fixed server role is used for managing disk files.
dbcreatorMembers of the dbcreator fixed server role can create, alter, drop, and restore any database.
publicEvery 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

FeatureTypeDescription
sp_helpsrvrole (Transact-SQL)MetadataReturns a list of server-level roles.
sp_helpsrvrolemember (Transact-SQL)MetadataReturns information about the members of a server-level role.
sp_srvrolepermission (Transact-SQL)MetadataDisplays the permissions of a server-level role.
IS_SRVROLEMEMBER (Transact-SQL)MetadataIndicates whether a SQL Server login is a member of the specified server-level role.
sys.server_role_members (Transact-SQL)MetadataReturns one row for each member of each server-level role.
sp_addsrvrolemember (Transact-SQL)CommandAdds a login as a member of a server-level role. Deprecated. Use ALTER SERVER ROLE instead.
sp_dropsrvrolemember (Transact-SQL)CommandRemoves 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)CommandCreates a user-defined server role.
ALTER SERVER ROLE (Transact-SQL)CommandChanges the membership of a server role or changes name of a user-defined server role.
DROP SERVER ROLE (Transact-SQL)CommandRemoves a user-defined server role.
IS_SRVROLEMEMBER (Transact-SQL)FunctionDetermines 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.
Etiketler:grant selectgrant select onlogin yetkimssql yetkisql grantsql grant loginsql kullanıcısql kullanıcı işlemlerisql kullanıcı oluşturmasql kullanıcı yetkisql rolesql tablo yetkisisql usersql user authoritysql user createsql userssql yetkilendirme
öncekiSQL Server Tablo Boyutları
sonrakiDBA Günlük Checklist

“SQL Login ve User operasyonları” hakkında 1 yorum

  1. Ömer Tortumlu 5 Ocak 2018, 22:20

    Yazınızı çok beğendim teşekkürler.

    Yanıtla

Bir cevap yazın Cevabı iptal et

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

This site uses Akismet to reduce spam. Learn how your comment data is processed.

profile for Yunus UYANIK on Stack Exchange, a network of free, community-driven Q&A sites

Kategoriler

  • AlwaysOn (2)
  • Checklist (1)
  • Database Ayarları (9)
  • Database Corruption (1)
  • Execution Plan (6)
  • Index (2)
  • Locking & Blocking (1)
  • Login & User (2)
  • MongoDB (1)
  • Query Optimization (7)
  • Script (3)
  • SQL Server Konfigürasyonu (1)
  • SQL Server Tools (2)
  • SQL Server Yenilikleri (3)

En çok görüntülenenler

  • SQL Login ve User operasyonları - 14.270 görüntülenme
  • DBA Günlük Checklist - 2.200 görüntülenme
  • SSMS kısayolları - 2.011 görüntülenme
  • SQL Server Tablo Boyutları - 1.588 görüntülenme
  • SQL Collation nedir? #DatabaseOptions - 1.270 görüntülenme

Son Yazılar

  • SQL Server Execution Plan Çeşitleri
  • SQL Server Lock ve Lock Escalation
  • SQL Server SELECT Nasıl Çalışır?
  • MongoDB – M001
  • Implicit Conversion nedir? #QueryOptimization
© - Yunus UYANIK - 2021