Login’lere View_Server_State Yetkisi Vermeden dmw ve dmf’lere Kısıtlı Bir Şekilde Eriştirmek

“Admin olmayan Loginlerin dmw’leri ve dmf’leri okuyabilmesi için gerekli yetkiyi vermek” isimli makalede Login’lerin dmw ve dmf’eri okuyabilmesi için gerekli yetkinin nasıl vereceğini anlattım.

Fakat bahse konu makalede verilen yetki ile loginler instance’taki tüm veritabanlarına gelen sorguları görebiliyorlar. Eğer instance üzerinde birden fazla kişiye ait veritabanı varsa bu bir güvenlik açığıdır. Bu makalede her uygulamacıya kendi veritabanına gelen sorguları görecek kadar yetkiyi nasıl vereceğimizi öğreneceğiz.

Öncelikle Test isminde bir veritabanı ve bu veritabanında yetkisi olan TestLogin isminde bir login oluşturalım. İlgili adımları nasıl yapacağınızı bilmiyorsanız aşağıdaki makaleler yardımcı olacaktır.

“Veritabanı Oluşturmak Deyip Geçmeyin!“,

“Login oluşturmak ve yetkilendirmek“

Öncelikle instance üzerinde TRUSTWORTH özelliği aktif edilmiş bir veritabanına ihtiyacımız var. Veritabanını oluşturduktan sonra kendi veritabanında TRUSTWORTHY özelliğini aktif hale getirebilirsiniz fakat bunu tavsiye etmiyorum. Çünkü TRUSTWORTHY özelliği aktif edilmiş bir veritabanı SQL Server için güvenilir olarak set edilmiş olur.

Bu özellik aktif iken veritabanı içersinden EXECUTE AS komutu ile sysadmin hakkında sahip bir kullanıcı ile işlem yapılabilir. Yani uygulama veritabanında db_owner hakkına sahip birine, aslında sysadmin olmadığı halde sysadmin yetkisi vermiş olursunuz.

Ayrıca TRUSTWORTH özelliği aktif edilmiş bir veritabanında .NET ile derlenmiş kod’lar çalıştırılabilir. Buda ayrı bir güvenlik açığıdır.

Bu sebeplerden dolayı bu işlemler için yeni ve kimsenin yetkisinin olmadığı bir veritabanı oluşturalım. Veritabanı adına da  TRUSTDB diyelim(Siz başka bir isim verebilirsiniz).

Ayrıca instance üzerinde sysadminUser(siz başka bir isim verebilirsiniz) isminde sysadmin hakkına sahip bir sql login oluşturalım.

Bu veritabanında TRUSTWORTHY özelliğini aşağıdaki script yardımıyla aktif hale getirelim.

ALTER DATABASE TRUSTDB SET TRUSTWORTHY ON;

Daha sonra aşağıdaki script yardımıyla da ilgili kullanıcıya sadece kendi veritabanına gelen sorguları görebileceği bir stored procedure oluşturalım. sysadminUser yerine sizin instance’ınız üzerinde sysadmin hakkına sahip bir login ismi, Test yerine de ilgili login’e ait veritabanı ismini yazmalısınız. İlgili login burada yazacağınız veritabanına gelen sorguları görebilecek.

USE [TRUSTDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[X_Veritabani_Icin_Sorgu_Performansi]
WITH EXECUTE AS 'sysadminUser'
AS
select r.total_elapsed_time / 1000.0 as total_elapsed_s,percent_complete,
r.blocking_session_id,r.last_wait_type,s.login_name,
'thisrequests_sessionid=' + cast(r.session_id as varchar) SessionID,
DB_NAME(r.database_id) as DatabaseName,command
,SUBSTRING(t.text, (r.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(t.text)
        ELSE r.statement_end_offset END
            - r.statement_start_offset)/2) + 1) AS statement_text
,r.status,blocking_session_id,wait_time
,wait_type,wait_resource,text,start_time,r.percent_complete,s.program_name
,r.last_wait_type,s.host_name,r.granted_query_memory * 8 / 1024 as memory_mb
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t
where r.session_id <> @@SPID
and (r.database_id=DB_ID('Test'))
order by start_time asc

Daha sonra yetki vermek istediğiniz kullanıcıya oluşturduğunuz bu stored procedure üzerinde aşağıdaki gibi execute hakkı vermeniz gerekiyor.

Öncelikle TestLogin’e TRUSTDB üzerinde sadece public hakkı veriyoruz.

Daha sonra TRUSTDB’ye sağ tıklayarak aşağıdaki gibi ilgili stored procedure üzerinde Properties diyoruz.

Açılan ekranda Permission tab’ına gelerek Search diyoruz. Açılan ekranda Browse diyerek aşağıdaki gibi TestLogin’i seçiyoruz ve ok diyoruz.

Alt taraftaki ekranda da ok diyoruz ve karşımıza gelen ekranda TestLogin seçili iken aşağıdaki gibi Execute hakkı veriyoruz.

Artık TestLogin’e sadece Test veritabanındaki anlık sorguları getirecek stored procedure üzerinde execute yetkisi vermiş olduk.

Bir sonraki adımda test etmek için TestLogin ile login olup aşağıdaki gibi stored procedure’ü çalıştırabilirsiniz.

USE [TRUSTDB]
GO
EXEC [dbo].[X_Veritabani_Icin_Sorgu_Performansi]

Bu örnekleri çoğaltabilirsiniz. Örneğin ben bu makalede anlattığım şekilde bir stored procedure oluşturup ilgili kişilere bu stored procedure’ü execute etme yetkisi vermiştim.

Onlar da bu sp’nin sonucunda gelmeyen bir sorgunun diğer sorguları lock’ladığını ve locklayan sorgunun spid’sini görebildiklerini fakat sorguyu göremediklerini söylemişlerdi.

Bende aşağıdaki gibi bir stored procedure oluşturdum.

CREATE PROCEDURE [dbo].[AssociatedQueryForVeritabaniAdiniz]
@AssociatedQueryId int
WITH EXECUTE AS 'sysadminUser'
AS
DECLARE @sqlbinary varbinary(1024)
select @sqlbinary=sql_handle from sys.sysprocesses where dbid=DB_ID('VeritabaniAdiniz') AND spid=@AssociatedQueryId

select * from sys.dm_exec_sql_text(@sqlbinary)

Bu sp’yi de aşağıdaki şekilde çalıştırmaları gerekiyor.

Aşağıdaki sorguda 196 numaraları spid’nin hangi sorguya ait olduğu bilgisi öğrenilmek isteniyor.

Execute [AssociatedQueryForVeritabaniAdiniz] 196