Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Aralık 2016  >>
PaSaÇaPeCuCuPa
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678
Keywords

SQL Server’a windows authentication yani windows account’lar ile bağlantı kurulurken önerilen bağlantı protokolü KERBEROS’tur. Bu yazımda güvenlik açısından oldukça önemli olan bu best practice üzerine konuşuyor olacağız.

[more]

Kerberos Nedir?

Kerberos Windows 2000 ile beraber gelmiş bir güvenlik protokolüdür. NTLM’e oranla daha güvenli olduğu için windows authentication’ların KERBEROS üzerinden bağlantısı kurması best practice’dir.

Kerberos bağlantı protokolü authentication esnasında SPN’leri kullanır. SPN (Server Principal Name), bir servis ya da instance’ı tanımlayan unique bir addır. Eğer SQL Server service account’u için uygun bir SPN oluşturulmadıysa Kerberos bağlantı isteği düzgün bir şekilde karşılanamayacak ve bağlantı NTLM’e dönecektir.

Bağlantılarım Kerberos mu?

SQL Server’a kurulmuş windows authentication bağlantıların Kerberos protokolünü kullanıp kullanmadığı sys.dm_exec_connections DMV’sinden sorgulanabilir.

select es.login_name, ec.auth_scheme, *
from sys.dm_exec_connections ec
left join sys.dm_exec_sessions es on es.session_id = ec.session_id
where auth_scheme <> 'SQL' and net_transport = 'TCP'

Bu sorgunun benim sistemimdeki örnek çıktısı aşağıdaki gibidir.

image

Sorgu sonucundaki auth_scheme değerlerinin KERBEROS olmasını bekliyoruz. Eğer değil ise SPN problemimiz olduğunu düşünebiliriz.

SPN’ler Nasıl Sorgulanır?

Bir service account’una tanımlanmış SPN’leri sorgulamak için aşağıdaki komut kullanılabilir.

SetSPN -L <domain>\<account>

Bir SPN’in olup olmadığına ise aşağıdaki komut ile bakılabilir.

setspn -Q <SPN>

Örneğin MySQLServer isimli sunucumuzun default instance’ında MyDomain\MyServiceAccount service account’unun kullanıldığını düşünelim. Bu durumda aşağıdaki komutlar ile olması gereken SPN’lerin olup olmadığını sorgulayabiliriz.

setspn -Q MSSQLSvc/MySQLServer

setspn -Q MSSQLSvc/MySQLServer.MyDomain.com

setspn -Q MSSQLSvc/MySQLServer.MyDomain.com:1433

Eğer default değil de named instance (örneğin INST1) kullanılıyorsa bu durumda SPN kayıtları şu şekilde olacaktır.

setspn -Q MSSQLSvc/MySQLServer:INST1

setspn -Q MSSQLSvc/MySQLServer.MyDomain.com INST1

SPN’lerin olması kadar doğru service account’u üzerinde olması da önemlidir. Örneğin şöyle bir senaryo düşünelim. SQL Server “A” service account’u ile çalışıyordu ve gerekli SPN’leri create etmiştik. Daha sonra service account’unu “B” yapıyoruz. Bu durumda SPN kayıtları var gözükecek ama yanlış service account’u üzerinde olduğundan dolayı Kerberos protokolü kullanılamayacaktır. Bu yüzden hem SPN’leri kontrol etmeli hem de aşağıdaki komut ile ilgili service account’una bağlı SPN’ler sorgulanmalı ve SQL Server service SPN’lerinin bu listede olduğu teyit edilmelidir.

setspn -L MyDomain\MyServiceAccount

SPN Nasıl Oluşturulur?

SQL Server service’i için SPN oluşturmanın 2 yolu vardır.

  • İlk yöntem SQL Server service account’una active directory üzerinden “read servicePrincipalName” ve “Write servicePrincipalName” yetkilerinin verilmesidir. Bu yetkiler verildikten sonra service restart edilir. Service açıldığında otomatik olarak SPN kaydı oluşturulur. Service kapandığında ise ilgili SPN kaydı otomatik olarak silinir.

    SQL Server service account’una nasıl yetki verileceği ilgili şu dokümandan bilgi alabilirsiniz. (http://support.microsoft.com/kb/319723 - Step 3)

    Örnek bir ekran görüntüsü aşağıdaki gibidir.

    image
  • İkinci yöntem ise SPN’lerin manuel olarak yönetilmesi yani create, drop edilmesidir. Bu yöntem her ne kadar best practice olsa da sıkı bir şekilde yönetilmesi gereken bir konudur. Yanlış SPN’lerin olması ya da SPN’lerin hiç olmaması Kerberos kullanımını imkansız hale getirecektir.

 

Manuel SPN Oluşturma

Manuel olarak SPN oluşturmak için aşağıdaki komutu kullanabilirsiniz.

setspn -a mssqlsvc/MySQLServer.MyDomain.com:1433 MyDomain\MyServiceAccount

Bu komutun aynı zamanda MySQLServer ve MySQLServer.MyDomain.com için de çalıştırılması gerekmektedir.

Daha önce de dediğim gibi security açısından servis account’una SPN create/drop hakkı verilmektense SPN’lerin yukarıdaki şekilde manuel olarak create edilmesi best practice’dir

SPN Silme

SPN’leri manuel olarak silmek için aşağıdaki komutu kullanabilirsiniz.

setspn -d mssqlsvc/MySQLServer.MyDomain.com:1433 MyDomain\MyServiceAccount

Gene gerekiyorsa bu komutu MySQLServer ve MySQLServer.MyDomain.com için de çalıştırabilirsiniz.

Sonuç

SQL Server’da windwos authentication bağlantıların Kerberos protokolü üzerinden kurulması önemli bir security best practice’idir. Bağlantılar periyodik olarak kontrol edilmeli, eğer Kerberos yerine NTLM protokolü görülüyorsa SPN’ler kontrol edilmelidir.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server 2012 ile beraber gelen güvenlik yeniliklerinden biri de windows group’lar için default schema atayabilme özelliğidir. Bugünkü makalemde bu konu üzerine konuşuyor olacağız.

[more]

SQL Server 2012’den önceki sürümlerde herhangi bir windows ya da domain group SQL Server üzerinde yetkilendirilirken default schema ataması yapılamıyordu. Dolayısıyla bu group’un içerisinde bulunan bir kullanıcı eğer dbo yetkisinde değil ise oluşturduğu objelerin scheması kendi adını alıyordu. Bu da yönetilebilirliği azaltan bir durumdu.

SQL Server 2012 ile beraber artık windows group’lara default schema atayabilmekteyiz. Dolayısıyla artık aynı grup vasıtasıyla yetkilendirilen 100’lerce login kendi schema’larını değil default schema olarak atanan schema’yı kullanabilecekler.

Şimdi bu dediklerimizi ufak bir örnek yaparak pekiştirelim. Örnekleri hem 2008 hem 2012 instance’ımda yapacağım ve aralarındaki farkı göstermeye çalışacağım.

İlk olarak aşağıdaki TSQL komutları vasıtasıyla WinGroup1 adındaki windows login’i için yeni bir login create ediyoruz ve daha sonra da bu login için deneme1 veritabanında yeni bir user create ediyoruz.

USE [master]
GO
CREATE LOGIN [TURGAYS03\WinGroup1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [deneme1]
GO
CREATE USER [TURGAYS03\WinGroup1] FOR LOGIN [TURGAYS03\WinGroup1]
GO

Farklılık bu noktadan sonra başlamakta. Aşağıdaki script ile WinGroup1 user’ına default schema ataması yapmak istiyorum. SQL Server 2012’de çalışan bu kod, SQL Server 2008’de bu özellik desteklenmediği için hata almakta.

USE [deneme1]
GO
CREATE SCHEMA [WinGroup1] AUTHORIZATION [TURGAYS03\WinGroup1]
GO
USE [deneme1]
GO
ALTER USER [TURGAYS03\WinGroup1] WITH DEFAULT_SCHEMA=[WinGroup1]
GO

SQL Server 2008’de alınan hata aşağıdaki gibi.

Msg 15259, Level 16, State 2, Line 1
The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.

Aynı işlemi SSMS üzerinden yaparsak ;

image

Default schema atama işlemini SQL Server 2008’de yapamadığımızı gördük. Peki bu durum tam olarak ne gibi bir sıkıntı oluşturmakta.

Aynı user’a table create etme hakkı verip daha sonra bu grubun içerisinde olan bir user ile SSMS’I açıp tablo create ediyoruz.

use [deneme1]
GO
GRANT CREATE TABLE TO [TURGAYS03\WinGroup1]
GO

--Aşağıdaki işlem WinGroup1 windows grubunun içinde bulunan
--WinUser1 kullanıcı ile SSMS açılıp yapılmıştır.
use [deneme1]
GO
create table denemetbl(a int)

Bu işlemi SQL Server 2012’de yaptığımızda gruba hangi schemayı default olarak atadıysak yeni create ettiğimiz table’ın scheması’da bu schema oluyor.

Fakat aynı işlemi SQL Server 2008’de yaptığımızda, default schema ataması yapamadığımızdan dolayı yeni create edilen user’ın schema’sı kullanıcı adı (WinUser1) olmakta.

Grubun içinde 100’lerce kullanıcı olduğunu düşünün. Bu durumda her kullanıcı için ayrı schema oluşturulacağı için yönetilebilirlik oldukça azalacaktır. Ayrıca kullanıcının silinmesi gerekliliğinde de schema değişiklikleri yapılması gerekmekte ki bu durum çoğu durumda oldukça sıkıntı çıkarmakta.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server’da tanımlı bir login’in şifresinin en son ne zaman değiştirildiğini bulmak için aşağıdaki fonksiyonu kullanabilirsiniz.

[more]

SELECT LOGINPROPERTY('LoginName', 'PasswordLastSetTime')

Örneğin “turgay” adlı login’in create edilme ve en son şifre değiştirilme tarihlerine aşağıdaki kod ile erişebiliriz.

SELECT create_date as CreateDate, 
	LOGINPROPERTY(name, 'PasswordLastSetTime') as PasswordLastChangeTime
FROM sys.server_principals
where name = 'turgay'

image

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Güvenlik açısından bir login’in şifresinin boş olması ya da login adı ile aynı olması bir sıkıntıdır. Bu makalemde paylaşacağım script’ler ile şifresi boş olan ya da şifresi login adı ile aynı olan SQL Login’leri bulabilirsiniz.

[more]

Daha önce pwdcompare fonksiyonunu incelemiştik. Konu önemli olduğu için tekrar detaylı bir script hazırlamak istedim.

Şifresi boş olan SQL Login'ler

--Şifresi boş olan SQL Login'ler
select  serverproperty('machinename') as 'Server Name'
	,isnull(serverproperty('instancename'),serverproperty('machinename')) as 'Instance Name'
    ,name as 'Login With Blank Password'
from master.sys.sql_logins
where pwdcompare('',password_hash)=1
order by name
option (maxdop 1)
go

 

Şifresi login adı ile aynı olan SQL Login'ler

--Şifresi login adı ile aynı olan SQL Login'ler
select serverproperty('machinename') as 'Server Name'
	,isnull(serverproperty('instancename'),serverproperty('machinename')) as 'Instance Name'
    ,name as 'Login With Password Same As Name'
from master.sys.sql_logins
where pwdcompare(name,password_hash) = 1
order by name
option (maxdop 1)

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server 2012 ile SQL Server Audit tarafında bazı geliştirmeler yapılmış durumda. Bu makalemde bu yenilikleri detaylı olarak inceliyor olacağız.

[more]

Sürüm Değişikliği

Bildiğiniz gibi SQL Server 2008 ile beraber gelen SQL Server Audit özelliği sadece enterprise edition’da desteklenmekteydi. SQL Server 2012’de bu konuda bazı değişiklikler yapılmış durumda.

Artık Server Level Auditing işlemleri bütün sürümlerde desteklenmekte. Database Level Auditing tarafında ise bir değişiklik yapılmadı ve sadece Enterprise edition’da desteklenmekte.

User-Defined Audit

SQL Server 2012 ile beraber audit loglarına kullanıcı bazlı olarak log atabilme özelliği geliyor. sp_audit_write sistem prosedürü vasıtasıyla kendi prosedürlerimiz sonucu oluşan logları audit loglarına yazdırabilir ve audit loglarını izlerken bu logları da görüntüleyebiliriz.

Şimdi bu işlemi bir örnek ile detaylandıralım.

--Yeni bir audit nesnesi tanımlıyoruz.
USE [master]
GO
CREATE SERVER AUDIT [Audit1]
TO FILE 
(	FILEPATH = N'D:\audit'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
GO

--Audit nesnesini enable ediyoruz
Alter Server AUDIT [Audit1] WITH (STATE=ON)

--Yeni bir Server Audit Specifications oluşturuyoruz
--Audit Action Type olarak USER_DEFINED_AUDIT_GROUP seçeneğini ekliyoruz.
--SQL Server 2012 ile beraber gelen bu seçenek UserDefined Audit yapmak için kullanılmakta.
CREATE SERVER AUDIT SPECIFICATION [sas1]
FOR SERVER AUDIT [Audit1]
ADD (USER_DEFINED_AUDIT_GROUP)
GO

--Server Audit Specifications nesnesini enable ediyoruz
ALTER Server AUDIT SPECIFICATION [sas1] WITH (STATE=ON)

--Şimdi SQL Server Audit log'una manuel olarak bir log yazıyoruz.
--Örneğin aşağıdaki kod Audit log'una bir ekranın açıldığını loglamakta. 
EXEC sp_audit_write @user_defined_event_id =  27
            , @succeeded =  1 
            , @user_defined_information = N'Monitoring Tool''una erişim' ;

 

Yazdığımız bu log kaydını audit nesnesinin log viewer’ından izleyebiliriz.

1(1)

Filtreleme

SQL Server 2012 ile beraber Audit tarafında gelen en büyük yeniliklerden biri de oluşturulacak olan log kayıtlarında filtreleme özelliği. Örneğin bir tablo üzerinde kullanıcılar tarafından yapılan delete işlemlerini log’lamak istiyorum. Fakat programın kullandığı login’in loglanmasını istemiyorum. Çünkü benim izlemek istediğim program değil diğer kullanıcıların yaptığı delete işlemleri.

İşte bu aşamada Audit nesnesinde filtering özelliğini kullanarak sadece istediğim user’ların yaptığı işlemlerin loglanmasını sağlayabilirim.

Tabi burada şu şekilde bir parantez açmak istiyorum. Audit nesnesinde filtering kullanıldığında log hala daha oluşmaya devam eder ama filtering özelliğine uyuyorsa log dosyasına yazılır.

Şimdi şu şekilde bir örnek yapalım. Aşağıdaki şekilde oluşturulan audit nesnesi sadece principal_id’si 259 olan user’ının yaptığı işlemleri loglayacaktır.

USE [master]
GO
CREATE SERVER AUDIT [Audit2]
TO FILE 
(	FILEPATH = N'D:\audit'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
WHERE ([server_principals_id]=(259))

 

Bunun haricinde SQL Server 2012 Audit’de FAIL_OPERATION ve MAX_FILES özelliklerinde de bazı yenilikler yapılmış durumda. Bu yeniliklerin detayına da şu linkten erişebilirsiniz.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bildiğiniz gibi SQL Server’da şifreler hash’lenmiş olarak saklanırlar. Dolayısıyla login’lerin şifrelerinin ne olduklarını, güçlü mü yoksa zayıf mı oldukları direk olarak bilmemiz mümkün değildir. İşte bu amaçla PWDCOMPARE fonksiyonunu kullanabiliriz. Bu fonksiyon ile şifreyi istediğimiz bir şifre ile karşılaştırıp zayıf olup olmadığını kontrol edebiliriz.

[more]

Örneğin sistemde şifresi boş olan login’leri bulmak için;

SELECT name FROM sys.sql_logins 
WHERE PWDCOMPARE('', password_hash) = 1 ;

 

Ya da şifresi “password” olan login’leri bulmak için;

SELECT name FROM sys.sql_logins 
WHERE PWDCOMPARE('password', password_hash) = 1 ;

 

sorgularını kullanabiliriz.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server varsayılan olarak, deny verilmiş bir tablo üzerinde bir kolona grant verildiyse bu kolonun sorgulanmasına izin vermektedir. “Common Criteria Compliance Enabled” parametresi ile bu davranış değiştirilebilir.

[more]

Common Criteria Compliance Enabled parametresi sp_configure ile değiştirilebilir.

sp_configure 'common criteria compliance enabled', 1;
GO
RECONFIGURE
GO

 

Sadece Enterprise Edition’da bulunan bu parametrenin default değeri 0 yani Disabled’dır.

Şimdi bu parametrenin enabled ya da disabled olması durumuna göre table-column level permission’ların ne şekilde etkili olduklarını görelim.

“'common criteria compliance enabled” Enabled – 1

Bu durumda table level permission column level permission’ı ezer. Yani eğer tabloya deny verildiyse, kolonlara grant verilse dahi bu kolonlar sorgulanamaz.

“'common criteria compliance enabled” Disabled – 0 (Default değer 0’dır)

Bu durumda column level permission table level permission’ı ezer. Yani tabloya deny verilmesine rağmen grant verilen kolonlar sorgulanabilir.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server’da yetkilendirmeler sunucu, veritabanı, nesne ve kolon seviyesinde yapılabilmektedir. Bu makalede kolon seviyesinde yetkilendirmenin nasıl yapıldığına bakıyor olacağız.

[more]

Şöyle bir ihtiyacımız olduğunu düşünelim. Veritabanında maaş bilgilerinin tutulduğu bir tablo olsun. Bu tabloyu görmeye yetkisi olan kişilerin tüm kolonları görmesini ama maaş bilgilerinin tutulduğu kolonları görmesini istemiyoruz. Böyle bir durumda kolon bazında yetkilendirme özelliğini kullanabiliriz.

Örnekleri AdventureWorks veritabanında bulunan Person.Contact tablosu üzerinde yapacağız. Yeni bir kullanıcı oluşturup bu kullanıcıya Person.Contact tablosu için select hakkı tanımlayıp daha sonra EMailAddress kolonunu görememesi için bu kolon için deny tanımlaması yapacağız.

Yeni bir kullanıcı tanımlıyoruz.

USE [master]
GO
CREATE LOGIN [UserPermission] WITH PASSWORD=N'u'
GO
USE [AdventureWorks]
GO
CREATE USER [UserPermission] FOR LOGIN [UserPermission]
GO

 

Bu kullanıcıyı Person.Contact tablosunu select çekecek şekilde yetkilendiriyoruz.

use [AdventureWorks]
GO
GRANT SELECT ON [Person].[Contact] TO [UserPermission]
GO

 

Ve son olarak EMailAddress kolonunu sorgulayamaması için bu kolona deny veriyoruz.

use [AdventureWorks]
GO
DENY SELECT ON [Person].[Contact] ([EmailAddress]) TO [UserPermission]
GO

 

Şimdi oluşturduğumuz bu kullanıcı ile bağlantı kurup EMailAddress kolonunu sorgulamaya çalışalım.

select EmailAddress from Person.Contact

 

Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column 'EmailAddress' of the object 'Contact", database 'AdventureWorks', schema 'Person'.

Aynı tablodan deny verilmemiş kolonları sorgulamak istersek herhangi bir hata mesajı almayacağız.

select FirstName,LastName from Person.Contact

 

Herhangi bir kolona deny verildiğinda artık select * şeklinde de sorgulamaya izin verilmemektedir.

select * from Person.Contact

 

Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column 'EmailAddress' of the object 'Contact", database 'AdventureWorks', schema 'Person'.

Yukarıda TSQL ile yaptığımız bu yetkilendirme işlemini SSMS kullanarak ta yapabiliriz. Bunun için yetkilendirme ekranında bulunan Object Permission butonunu kullanabiliriz.

ss3

Object Permission butonuna bastığımızda gelen ekranda deny vermek istediğimiz kolonları belirleyebiliriz.

ss4

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Atakları minimum hale getirmek ve veritabanı sunucusunu daha güvenli hale getirmek için Shared Memory ya da Named Pipes gibi kullanılmayan protokolleri disable etmekte hatta kullanıcıları TCP/IP’den bağlanmaya zorlamakta fayda vardır.

Bugünkü yazımda hangi protokollerin kullanılmadığını nasıl monitor edeceğimizi ve kullanılmayan protokollerin nasıl disable edileceğini görüyor olacağız.

[more]

Kullanılmayan veya kullanılmadığını düşündüğünüz protokolleri disable etmeden önce bu protokollerin gerçekten kullanılmadığını monitor etmenizde fayda vardır. Bunun için sys.dm_exec_connections DMV’sine belirli aralıklarla sorgu çekilip TCP/IP protokolü hariç kullanılan protokoller var ise bu bilgiler bir temp tabloya kaydedilebilir.

Hatta bir SQL Server job’ı ile yukarıda bahsettiğim sorgu örneğin 1 dakika ara ile otomatik olarak çalıştırılıp, sorgu sonucu bir tabloyo loglanabilir. Job yeterli bir süre çalıştıktan sonra temp tablo kontrol edilerek hangi protokollerin kullanıldığı belirlenebilir.

Şimdi bu monitoring işlemini gerçekleştirelim. İlk olarak sorgu sonucunda çıkan kayıtları loglamak için bir tablo create ediyorum.

USE AdventureWorks2008R2
GO

CREATE TABLE [dbo].[dmexecconnections_perminute](
	[CollectionTime] [datetime] NOT NULL,
	[session_id] [int] NULL,
	[connect_time] [datetime] NOT NULL,
	[net_transport] [nvarchar](40) NOT NULL,
	[protocol_type] [nvarchar](40) NULL,
	[auth_scheme] [nvarchar](40) NOT NULL,
	[client_net_address] [varchar](48) NULL,
	[client_tcp_port] [int] NULL,
	[local_net_address] [varchar](48) NULL,
	[local_tcp_port] [int] NULL,
	[recent_sql_text] [varchar](max) NULL
)
GO

 

1 dakikada bir çalışıp TCP/IP haricinde protokol kullanılarak bağlantı kurulmuş connection’ları loglayan job ise şu şekilde;

USE [msdb]
GO

/****** Object:  Job [dmexeconnections_perminute]    Script Date: 08/03/2011 17:38:21 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 08/03/2011 17:38:21 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'dmexeconnections_perminute', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Gereksiz protokollerin disable edilmesinden önce monitor edilmesi', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [s1]    Script Date: 08/03/2011 17:38:21 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N's1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'insert AdventureWorks2008R2.dbo.dmexecconnections_perminute
select GETDATE() as CollectionTime,session_id,connect_time, net_transport,protocol_type,auth_scheme,client_net_address,client_tcp_port,local_net_address,local_tcp_port
	,st.text
from sys.dm_exec_connections c
cross apply sys.dm_exec_sql_text (c.most_recent_sql_handle) st
where net_transport != ''TCP''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N's1', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=5, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20110720, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'66452ed0-9229-4eb5-a0aa-8da19ade87de'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Bu job belirli bir süre çalıştıktan sonra log tablosunu kontrol edebiliriz.

image

Gördüğünüz gibi Shared Memory kullanılmakta. Ama Named Pipes kullanılmamakta. Bu durumda Named Pipes protokolünü disable etmeye karar verebilirim.

Ayrıca sorgu sonucunda gördüğünüz recent_sql_text kolonu vasıtasıyla ilgili connection'dan yapılan son sorguyu görmekte bir nebzede olsa connection’ın hangi uygulama için açıldığını anlayabilmekteyiz.

Protokol disable işlemi SQL Server Configuration Manager aracından yapılmaktadır.

image

Yapılan değişikliklerin geçerli olması için Database Engine servisinin restart edilmesi gerektiğini belirterek yazıma son veriyorum.

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server 2008 R2, sunucu ve veritabanı seviyesinde daha güvenlikli bir ortam oluşturabilmek için bütün gerekli araçları sağlamaktadır. Görevler ayrılığı ilkesi kapsamında, SQL Server sunucusu üzerinde yetkili olan kişilerin minimum yetkiler ile ayarlanması, görevlerini yapabilecek kadar yetki ile donatılması yeterlidir.

Örneğin SQL Server DBA’lerin bile sysAdmin server role’un de olması pek güvenli bir ortam sağlamamaktadır. sysAdmin server role’u sunucu üzerinde yapılabilecek bütün işlemleri yapabildiği için çok zorda kalınmadıkça bu gruba kullanıcı dahil edilmemelidir.

SQL Server’da Görevler Ayrılığı'nın detaylarının anlatıldığı aşağıdaki whitepaper’ın ilginizi çekeceğini umuyorum.

SQL Server Seperation of Duties

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bugünkü yazımda Database seviyesinde Select,Update gibi işlemler için verilen GRANT,DENY gibi yetkilerin, sys.sysprotects tablosundan nasıl sorgulanacağına bakıyor olacağız.

[more]

AdventureWorks için aşağıdaki yetki verme scriptlerini çalıştıralım.

--ka user'ı için Sales.Customer tablosunda delete deny
DENY DELETE ON [Sales].[Customer] TO [ka]
--ka user'ı için Production.Product tablosunda insert deny
DENY INSERT ON [Production].[Product] TO [den]
--ka user'ı için Person.Contact tablosunda select grant
GRANT UPDATE ON [Person].[Contact] TO [def]

 

Şimdi verdiğimiz bu yetkileri sorgulayalım.

select OBJECT_SCHEMA_NAME(id) as SchemaName
	,OBJECT_NAME(id) as ObjectName
	,su1.name as UserName
	,sp.action
	,(case sp.action when 26 then 'REFERENCES' 
					when 178 then 'CREATE FUNCTION' 
					when 193 then 'SELECT' 
					when 195 then 'INSERT' 
					when 196 then 'DELETE' 
					when 197 then 'UPDATE' 
					when 198 then 'CREATE TABLE' 
					when 203 then 'CREATE DATABASE' 
					when 207 then 'CREATE VIEW' 
					when 222 then 'CREATE PROCEDURE' 
					when 224 then 'EXECUTE' 
					when 228 then 'BACKUP DATABASE' 
					when 233 then 'CREATE DEFAULT' 
					when 235 then 'BACKUP LOG' 
					when 236 then 'CREATE RULE' 
					else 'na' end) as Action_desc	
    ,sp.protecttype
	,(case sp.protecttype when 204 then 'GRANT_W_GRANT' 
					when 205 then 'GRANT' 
					when 206 then 'DENY' 
					else 'na' end) as ProtectType_desc	
	,sp.columns										
	,su2.name as Grantor
from sys.sysprotects sp
LEFT join sysusers su1 on su1.uid=sp.uid
LEFT join sysusers su2 on su2.uid=sp.grantor
where su1.name<>'public'

 

aa1(1)

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Troubleshoot amaçlı, Visual Studio ile SQL Server üzerinde debug yapılmak istendiğinde aşağıdaki gibi bir hata alınabilir.

[more]

image

Unable to start T-SQL Debugging. Could not attach to SQL Server process on … The RPC server is unavailable.

Bu hatanın nedeni, debug yapmaya çalışan windows account’un SQL Server üzerinde yeterli hakka sahip olmamasıdır.

Debug yapan windows account’un SQL Server üzerinde sysAdmin olması gerekmektedir. Aşağıdaki örnek script ile bu yetki verilebilir.

sp_addsrvrolemember 'Domain\Name', 'sysadmin'

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bugün üzerinde çalıştığım bir konudan bahsetmek istiyorum. Sunucularımdan birinde bir login windows authentication olarak bağlantı kurmasına rağmen, SQL Server Login’leri kontrol ettiğimde bu login’i göremedim.

[more]

Belli ki bir windows grubundan bu yetkiyi almaktaydı. Yani bu login bir windows grubunun içinde ve bu windows grubuda SQL Server Login’leri içerisinde yetkilendirilmiş.

Peki ama hangi Windows Grubu içinde yer almakta bu login?

SQL Server Login listenizde çok fazla windows grubu var ise tek tek bu grup’ların içine bakmak işkence olacaktır. Oysa aşağıdaki kod ile ilgili login’in hangi gruptan yetkiyi aldığını sorgulamamız mümkün.

exec xp_logininfo 'DomainName\LoginName'

 

image

Gördüğünüz gibi ilgili login BUILTIN\Administrators’tan bu yetkiyi almakta ve ayrıca bu grupta SQL Server’da sysAdmin olarak yetkilendirilmiş.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bir Login’i drop ettiğimizde bu login’e bağlı olan user’lar DB altında tanımla kalmaya devam etmektedir. Bugün paylaşacağım script ile bir Login’i tamamen silmemiz mümkün.

[more]

SP şu şekilde;

CREATE PROC [dbo].[DropLogin] @LoginName varchar(15)
AS    

declare @strx varchar(max)=''

--Yeni Session açılmasın diye login'i disable yap
select @strx = 'use master; if exists(select * from sys.sql_logins where name = '''+@LoginName+''') ALTER LOGIN ['+@LoginName+'] DISABLE; '

--Login'in bütün session'larını kill et
select @strx = @strx + 'kill ' + cast(spid as varchar(10)) + '; '
from sys.sysprocesses where loginame = ''+@LoginName+''

--Login'i drop et
select @strx =@strx+' use master; if exists(select * from sys.sql_logins where name = '''+@LoginName+''') DROP LOGIN ['+@LoginName+']; '

--DB'lerde bulunan user'ları drop et
select @strx=@strx+'use ' + name + '; if exists(select * from sys.database_principals where name = '''+@LoginName+''') DROP USER ['+@LoginName+'] ' 
from sys.databases

exec (@strx)

 

Kullanımı ise şu şekilde;

exec dbo.DropLogin 'DenemeLogin' 

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bugün sizlerle paylaşacağım script ile sunucuda bulunan bütün DB’ler için hangi user’ların db_owner grubunda olduğunu sorgulayabilirsiniz.

[more]

Script aşağıdaki gibi;

CREATE TABLE #tmp_result (DBName sysname, name sysname)

exec sp_msforeachdb '
set NOCOUNT OFF
use ?;
CREATE TABLE #tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)
declare @generation int
set @generation = 0
INSERT INTO #tmp_role_member_ids (id) SELECT
rl.principal_id AS [ID]
FROM
sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name=''db_owner'')
UPDATE #tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@generation
WHILE ( 1=1 )
BEGIN
	INSERT INTO #tmp_role_member_ids (id, role_id, sub_role_id, generation)
		SELECT a.member_principal_id, b.role_id, a.role_principal_id, @generation + 1
            FROM sys.database_role_members AS a INNER JOIN #tmp_role_member_ids AS b
			ON a.role_principal_id = b.id
            WHERE b.generation = @generation
	if @@ROWCOUNT <= 0
		break
	set @generation = @generation + 1
END
DELETE #tmp_role_member_ids WHERE id in (SELECT
rl.principal_id AS [ID]
FROM
sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name=''db_owner'') )
UPDATE #tmp_role_member_ids SET generation = 0;
INSERT INTO #tmp_role_member_ids (id, role_id, generation) 
    SELECT distinct id, role_id, 1 FROM #tmp_role_member_ids
DELETE #tmp_role_member_ids WHERE generation = 0

INSERT #tmp_result
SELECT
db_name(),
u.name AS [Name]
FROM
sys.database_principals AS rl
INNER JOIN #tmp_role_member_ids AS m ON m.role_id=rl.principal_id
INNER JOIN sys.database_principals AS u ON u.principal_id = m.id
WHERE
(rl.type = ''R'')and(rl.name=''db_owner'')
ORDER BY
[Name] ASC

drop table #tmp_role_member_ids
'

select *,'use '+DBName+'; EXEC sp_droprolemember N''db_owner'', N'''+Name+''' ' as DropScript 
from #tmp_result

drop table #tmp_result

 

Örnek çıktı aşağıdaki gibi;

a

Drop script kolonunda ilgili yetkiyi kaldırabilmek için gerekli script’i bulabilirsiniz.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server 11 “Denali” ile gelen yeni özelliklerden biride Server Role create edebilmek. Bildiğiniz gibi SQL Server 2008 ve önceki sürümlerde olan server role’lerinde bir değişiklik yapamıyor yeni server role’leri create edemiyorduk. Denali ile beraber artık kendi server role’lerimizi create edebiliyoruz.

[more]

Konuyu daha iyi anlamak açısından Server Role’leri ile alakalı daha önce yazdığım aşağıdaki yazımı okumanızı tavsiye ederim.

http://www.turgaysahtiyan.com/post/SQL-Server-da-Kullanc4b1cc4b1-Yaratma-ve-Yetkilendirme.aspx

 

Giriş paragrafında da belirttiğim gibi Denali ile birlikte artık kendi server role’lerimizi create edebiliriz.

Örneğin TSQL ile bir server role create edip, bazı yetkilendirmeler yapalım. Son olarakta bir login’i bu server role’e dahil edelim.

USE [master]
GO
--Server Role'ü create et.
CREATE SERVER ROLE [myServerRole] AUTHORIZATION [sa]
GO
--DBUser login'ini bu role'e dahil et
ALTER SERVER ROLE [myServerRole] ADD MEMBER [DBUser1]
GO
--Server Side Permission
GRANT ALTER ANY DATABASE TO [myServerRole]
GO
--Server Side Permission
GRANT CREATE ANY DATABASE TO [myServerRole]
GO
--Server Side Permission
GRANT VIEW ANY DATABASE TO [myServerRole]
GO
--Server Side Permission
GRANT VIEW ANY DEFINITION TO [myServerRole]
GO

 

Aynı işlemi SSMS ile yapmak istersek;

1.Security >> Server Roles Sağ tık ile New Server Role yazısını tıklayalım.

1

2.İstediğimiz permission’ları verelim.

2

3.Login’leri Add butonu vasıtasıyla role’e ekleyelim.

3

4.Oluşturduğumuz bu role’ü istersek standart server role’lerinden birinin üyesi yapıp, bu server role’ün yetkilerinden faydalandırabiliriz.

4

5.Son olarak create işleminden sonra Server Role listesi aşağıdaki gibi gözükecektir.

5

Gördüğünüz gibi standart server role’leri ile user defined server role’lerini yanlarındaki iconlar vasıtasıyla ayırmak oldukça kolay.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Başlıktanda rahatça anlaşılacağı üzere bugün göreceğimiz sorgu ile Server’da bulunan bütün login’lerin server ve database role’lerini sorgulayacağız.

[more]

Server Role kapsamında aşağıdaki role’leri sorgulayacağız.

ServerRole         Description
========       ============
sysadmin           System Administrators
securityadmin     Security Administrators
serveradmin       Server Administrators
setupadmin        Setup Administrators
processadmin     Process Administrators
diskadmin          Disk Administrators
dbcreator           Database Creators
bulkadmin          Bulk Insert Administrators

Database Role kapsamında ise aşağıdaki gibi database rollerini sorgulayacağız.

1

Dolayısıyla bu role’ler dışındaki örneğin object bazında verilmiş yetkilendirmeler bu sorgulama dışındadır.

create table #ServerLogins (name sysname, is_disabled int, type_desc varchar(60), create_date datetime, modify_date datetime, server_roles varchar(500), db_roles varchar(max))
create table #DBLevelPermissions (DBName sysName, DBRole sysname, MemberName sysName)
declare @str1 varchar(max)=''
	   ,@str2 varchar(max)=''
	   ,@name sysname
	   ,@is_disabled int
	   ,@type_desc varchar(60)
	   ,@create_date datetime
	   ,@modify_date datetime

select lgn.name as Name, SUSER_NAME(rm.role_principal_id) as RoleName
into #ServerLevelPermissions
FROM SYS.SERVER_ROLE_MEMBERS RM, SYS.server_principals LGN  
WHERE RM.role_principal_id >=3 AND RM.role_principal_id <=10 AND  
  RM.member_principal_id = LGN.principal_id  
      			
select name,is_disabled,type_desc,create_date,modify_date
into #ServerPrincipals
from sys.server_principals
where type not in ('C','R')
and name not in ('sa','##MS_PolicyTsqlExecutionLogin##','##MS_PolicyEventProcessingLogin##','NT AUTHORITY\SYSTEM'
	,'NT AUTHORITY\NETWORK SERVICE','NT SERVICE\MSSQLSERVER','NT SERVICE\SQLSERVERAGENT')
order by name

exec sp_msforeachdb 'use ?; 
	insert #DBLevelPermissions
	select ''?'',g.name as DBRole, u.name as MemberName
		from sys.database_principals u, sys.database_principals g, sys.database_role_members m  
	where g.principal_id = m.role_principal_id  
		and u.principal_id = m.member_principal_id  
	order by 1, 2'

declare CursorX cursor for
select name,is_disabled,type_desc,create_date,modify_date from #ServerPrincipals
open Cursorx
fetch from Cursorx into @name,@is_disabled,@type_desc,@create_date,@modify_date
while @@FETCH_STATUS=0
begin
  set @str1=''
  select @str1+=RoleName+',' from #ServerLevelPermissions where name=@name
  if @str1<>''
    select @str1=SUBSTRING(@str1,0,len(@str1))
  
  set @str2=''
  select @str2+=DBName+'('+DBRole+')'+',' from #DBLevelPermissions where MemberName=@name
  if @str2<>''
    select @str2=SUBSTRING(@str2,0,len(@str2))
    
  insert #ServerLogins
    select @name,@is_disabled,@type_desc,@create_date,@modify_date,@str1,@str2
  
  fetch next from Cursorx into @name,@is_disabled,@type_desc,@create_date,@modify_date
end
close CursorX
deallocate CursorX
   
select * from #ServerLogins order by name

drop table #ServerPrincipals
drop table #ServerLevelPermissions
drop table #ServerLogins
drop table #DBLevelPermissions

 

Sorgu sonucu ise şu şekilde birşey olacaktır.

2

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Yazılım geliştiricilerin en büyük sıkıntılarından biri geliştirdikleri kodların başkaları tarafından çalınma riskidir. Execute file lar ile bu çalınma riski büyük ölçüde azaltılıyorken .Net yeni mimari yapısından kaynaklanan durum gereği .Net executable file ları bildiğiniz üzere reverse engineering yapılabilmektedir. Gerçi bu durumada önlem olarak obfuscator programları var ki bu konu başlı başına bir konu ve daha önce bloğumuzda işlenmiş durumda.

Peki ya Database Devolopment ? Geliştirdiğimiz SP, Function ve trigger ları müşteriye deploy etmemiz gerektiğinde kodlarıyla beraber apaçık şekilde mi vereceğiz. Ya da kendi firmamızda yazdığımız kodların gözükmesini istemiyorsak.

İşte bu tarz bir istek için SQL Server bize WITH Encryption key word unu sunmakta. Bu parametre ile SP, View, Function ve Trigger ları şifrelemek mümkün. 

WITH Encryption ile SP,View,Function ve Trigger ları Şifrelemek


4 obje içinde şifreleme tekniği aynı olduğu için ben sadece view i anlatacağım. Diğerlerinde aynı kod yapısı kullanılabilir.

Ufak bir view i WITH Encryption parametresi ile yazalım ve neler değiştiğini görelim.

use AdventureWorks2008
GO
CREATE VIEW VEncSample WITH ENCRYPTION
AS
  Select FirstName,LastName from Person.Person

 

View e sorgu çekmeyi deneyelim.

select * from VEncSample

 

Gördüğünüz gibi sorgu sonucunun gelme kısmında herhangi bir değişiklik yok.

Şimdi view i modify etmeye çalışalım bunun için AdventureWorks2008 >> Views kısmından view i bulalım.

view1

Burada ilk dikkatinizi çekmek istediğim konu VEncSample view inin yanındaki kilit işareti. Bu işaret bu view in encrypted olduğunu belirtmekte.

View e sağ tıkladığımızda Design in disable olduğunu göreceksiniz. Bununda sebebi encrypted olması. Dolayısıyla view i modify edemiyoruz.

view2

Peki birde view in Script ini oluşturmaya çalışalım. Bu seferde aşağıdaki gibi bir hata ile karşı karşıya kalıyoruz.

Property TextHeader is not available for View '[dbo].[VEncSample]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  The text is encrypted. (Microsoft.SqlServer.Smo)

view3


Gördüğünüz gibi view in içeriğini görüntüleyemiyoruz.

Son bir not. Şifrelediğiniz obje lerin kodlarını kaybetmemek için script lerini açık bir halde yedeklemenizde fayda var.


İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server da xp_cmdshell ile windows komutları çalıştırabilmektedir. Örneğin diskte bir noktaya erişmek ve dosyaları bir yereden bir yere kopyalamak gibi windows üzerinde yapabildiğiniz her şeyi SQL Server üzerinden yetkili bir user ile yapabilirsiniz.

SQL Server üzerinde bu işlem yapılırken SQL Server Service account unun yetkileri kullanılır. Dolayısıyla bu account yetkili olduğu kadar hakkınız vardır. Ve genellikle bu account server ın üzerinde admin hakkına sahip olduğu için server ın üzerinde dilediğiniz işlemi yapabilirsiniz.

SQL Server da xp_cmdshell i kullanabilmek için sysAdmin rolüne sahip olmak gerekmektedir. Ya da proxy account kullanmak gerekmektedir.

Bugünkü makalemizin konusuda proxy account kullanarak sysAdmin olmayan user lara xp_cmdshell i kullandırabilmek için yapılması gereken ayarların ne olduğunu anlatmak olacaktır.

Adımlar halinde yapılması gereken işlemleri anlatalım.

  1. SQL Server configuration ayarlarından xp_cmdshell i kullanıma açalım. SQL Server kurulumunda bu özellik default olarak disable gelmektedir.
    sp_configure 'show advanced options',1
    reconfigure
    GO
    sp_configure 'xp_cmdshell',1
    reconfigure
      
  2. Adım olarak denemeler yapacağız bir sql login create edelim ve master a map yapalım. Kullanıcı adı xpDeneme ve şifresi x.
    USE [master]
    GO
    CREATE LOGIN [xpDeneme] WITH PASSWORD=N'x', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [master]
    GO
    CREATE USER [xpDeneme] FOR LOGIN [xpDeneme]
    GO
  3. Şu an da xpDeneme user ı ile SQL Server üzerinden xp_cmdshell 'dir' komutunu çalıştırırsanız aşağıdaki gibi bir hata alacaksınızdır.

    The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.


    Bu hatayı almaktayız, çünkü xpDeneme login i sysAdmin değil. Dolayısıyla proxy account kullanmamız gerekmekte. Bunun için aşağıdaki kodu server üzerinde yetkili bir windows yada domain user çalıştıralım.
    EXEC sp_xp_cmdshell_proxy_account 'windowsusername', 'password';
    GO
  4. xpDeneme login i için master db sinde bulunan xp_cmdshell e execute yetkisi verelim
    GRANT exec ON xp_cmdshell TO xpDeneme
    GO
  5. xpDeneme login i ile tekrar xp_cmdshell 'dir' kodunu çalıştırırsak sonucun geldiğini göreceksiniz. Dolayısıyla işlemlerimizi tamamlamış oluyoruz.

 

Burada dikkat etmeniz gereken şöyle bir şey var. Bütün ayarlamaları yapmış olmanıza rağmen aşağıdaki gibi bir hata almanız mümkün.

An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential

Bunun sebebide 4.adım ı server üzerinde yetkili olan bir user için yapmamış olmanız. Bu adıma da dikkat ederseniz bir sorun yaşayacağınızı düşünmüyorum. En azından bende böyle oldu :)

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Login leri database lere yetkilendirirken Database Role lar kullanarak gruplarız.

Örneğin Developer lar için bir database i role ü oluşturur ve bu role e yetkilendirmeler yaparız. Developer User larıda bu role e ekleyerek otomatik yetkilendirmiş oluruz. Daha sonra developer yetkilerinde bir değişiklik yapmak istediğimizde tek tek developer user larda oynama yapmak yerine developer role ünü değiştiririz ve bütün developer user lar yansımasını sağlarız.

Böyle bir yapı kurgulamamız user ları daha iyi ve hızlı yönetmemizi sağlar.

Bugünkü konumuz ise bir DB için tanımlanmış Database Role de hangi user lar olduğunu listelemek.

Ortamlar arası DB taşıması yaparken, role lerde ki userlarıda bu makaledeki SP i sayesinde rahatça taşıyor olacağız.

İlk olarak listelemeyi yapacak olan SP ye bakalım.

USE [master]
GO
CREATE PROCEDURE [GetRoleMembers] @DBName sysname, @RoleName sysname
As

CREATE TABLE ##tmp_role_member_ids (id int not null, role_id int null, sub_role_id int null, generation int null)
CREATE TABLE ##tmp_names (name sysname)

declare @str varchar(max)=''
set @str='
declare @generation int
set @generation = 0
INSERT INTO ##tmp_role_member_ids (id) SELECT
rl.principal_id AS [ID]
FROM 
'+@DBName+'.sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name='''+@RoleName+''')
UPDATE ##tmp_role_member_ids SET role_id = id, sub_role_id = id, generation=@generation
WHILE ( 1=1 )
BEGIN
	INSERT INTO ##tmp_role_member_ids (id, role_id, sub_role_id, generation)
		SELECT a.member_principal_id, b.role_id, a.role_principal_id, @generation + 1
            FROM '+@DBName+'.sys.database_role_members AS a INNER JOIN ##tmp_role_member_ids AS b
			ON a.role_principal_id = b.id
            WHERE b.generation = @generation
	if @@ROWCOUNT <= 0
		break
	set @generation = @generation + 1
END
DELETE ##tmp_role_member_ids WHERE id in (SELECT
rl.principal_id AS [ID]
FROM
'+@DBName+'.sys.database_principals AS rl
WHERE
(rl.type = ''R'')and(rl.name='''+@RoleName+''') )
UPDATE ##tmp_role_member_ids SET generation = 0;
INSERT INTO ##tmp_role_member_ids (id, role_id, generation) 
    SELECT distinct id, role_id, 1 FROM ##tmp_role_member_ids
DELETE ##tmp_role_member_ids WHERE generation = 0

insert ##tmp_names
SELECT
u.name AS [Name]
FROM
'+@DBName+'.sys.database_principals AS rl
INNER JOIN ##tmp_role_member_ids AS m ON m.role_id=rl.principal_id
INNER JOIN '+@DBName+'.sys.database_principals AS u ON u.principal_id = m.id
WHERE
(rl.type = ''R'')and(rl.name='''+@RoleName+''')
ORDER BY
[Name] ASC
'
exec (@str)
--print @str

select Name from ##tmp_names

drop table ##tmp_role_member_ids
drop table ##tmp_names

 

Gördüğünüz gibi SP yi Master DB sinde create ediyoruz. Her DB için ayrı SP kullanmaktansa DB yi parametre olarak göndereceğiz.

2. parametre ise role parametresi. Hangi DB içindeki hangi Role de ki user lar?

Kullanımına bakacak olursak;

Use Master
GO
exec GetRoleMembers 'AdventureWorks','My_Role'
GO

 

Şimdi örneğimize geçelim.

İlk önce tanımlamaları yapıyoruz.

User ları create edelim.

use Master
GO
CREATE LOGIN User1 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User2 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User3 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User4 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
CREATE LOGIN User5 WITH PASSWORD=N'u',DEFAULT_DATABASE=[master]
GO

 

User ları AdventureWorks DB sinde açalım.

Use AdventureWorks
GO
CREATE User [User1] FOR LOGIN [User1];
CREATE User [User2] FOR LOGIN [User2];
CREATE User [User3] FOR LOGIN [User3];
CREATE User [User4] FOR LOGIN [User4];
CREATE User [User5] FOR LOGIN [User5];
GO

 

Yeni bir DB Role tanımlayalım.

Use AdventureWorks
GO
CREATE ROLE [My_Role] AUTHORIZATION [dbo]
GO

 

User ları bu Role e ekleyelim.

Use AdventureWorks
GO
EXEC sp_addrolemember N'My_Role', N'User1';
EXEC sp_addrolemember N'My_Role', N'User2';
EXEC sp_addrolemember N'My_Role', N'User3';
EXEC sp_addrolemember N'My_Role', N'User4';
EXEC sp_addrolemember N'My_Role', N'User5';
GO

 

Tanımlamalar bitti. Şimdi SP yi kullanarak AdventureWorks DB sindeki My_Role DB Role üne eklenmiş user ları listeleyelim.

Use Master
GO
exec GetRoleMembers 'AdventureWorks','My_Role'
GO

 

Name
============
User1
User2
User3
User4
User5

Bu SP yi kullanarak user lar için Role e ekleme script i de oluşturabilirsiniz. Bunuda size bırakıyorum J

İyi çalışmalar,

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan