Salı , 25 Temmuz 2017

SQL Server 2016 Row Level Security

Her şirket için kendi verileri hayati önem taşımaktadır. Bu sebeple veritabanı güvenliği büyük önem arz etmektedir. SQL Server üzerinde verilerimizin güvenliğini sağlamak için birçok yöntem kullanılıyor olsa bile en önemlisi yetkilendirme konusudur. Yani veritabanımızda tutulan verilere hangi kullanıcının hangi yetkiler erişebileceğidir. SQL Serverda yetkilendirme yaparken bir kullanıcıya var olan tüm tablolar için okuma yetkisi verebileceğimiz gibi, sadece istediğimiz tabloları sorgulaması içinde yetki verebiliyoruz. Hatta öyle ki bir kullanıcıya bir tablo üzerinde sorgulama yetkisi değil, ilgili tablonun sadece istediğimiz kolonlarına erişim için yetkilendirme yapabiliyoruz.

Yetkilendirme mekanizması bu kadar esnek olmasına rağmen bazı uygulamalarda tablolarımızda bulunan kayıt(Row) üzerinde yetkilendirme yapmamız gerekebilir. Örneğin bir kullanıcı tablosu düşünürsek bu tablo üzerinde her kullanıcının yetkisi olacak fakat sadece kendine ait kaydı sorgulayabilecek. İşte böyle durumlarda genellikle SQL Server tarafında yeni bir katman oluşturularak, Stored Procedure veya Function gibi objelerle ilgili kullanıcının adı parametre olarak kullanılır ve böylece sadece kendisine ait veriye erişmiş olurdu.

SQL Server 2016 sürümü ile beraber gelen Row-Level Security(RLS) yeniliği ile beraber kolayca bir kullanıcıya tablo üzerinde yetki verirken tüm kayıtlara değil sadece kendisini ilgilendiren kayıtlara erişme imkânı sağlandı. Şimdi satır bazında kullanıcıya yetki verme işlemini bir uygulama üzerinde görelim. Bunun için öncelikle örnek bir veritabanı ve yetkilendirme yapacağımız kullanıcılarımız ile bir tanede örnek tablo oluşturalım.

CREATE DATABASE RowLevelDb
GO
 
USE RowLevelDb
GO
 
CREATE USER aliduru WITHOUT LOGIN
CREATE USER ahmetkorkmaz WITHOUT LOGIN
CREATE USER ismailadar WITHOUT LOGIN
GO

Yukarıdaki kodumuzu incelediğimizde öncelikle RowLevelDb adında örnek bir veritabanımızı oluşturulduktan sonra bu veritabanımızda yetkilendirme için kullanacağımız aliduru,ahmetkorkmaz ve ismailadar adında 3 tane de kullanıcı oluşturduk. Şimdi ise tablomuzu oluşturalım.

CREATE TABLE Sales (
	SalesId INT Identity PRIMARY KEY NOT NULL
	,ProductId INT
	,OrderQty INT
	,UserName VARCHAR(50)
	)

INSERT INTO Sales VALUES
(34564,2,'aliduru'),
(13456,3,'ahmetkorkmaz'),
(21564,1,'ahmetkorkmaz'),
(23543,3,'aliduru'),
(98765,3,'ismailadar'),
(23444,5,'ismailadar'),
(12876,1,'emreyildiz'),
(23432,2,'aliduru')

Yukarıdaki kodumuzu incelediğimizde öncelikle tablomuzu oluşturup daha sonra tablomuza 8 tane kayıt insert ettik. Ayrıca dikkat ederseniz tablomuzu oluşturduğumuzda UserName adında bir kolon ekledik. Bu kolondaki verimize göre sorguyu o an çalıştıran kullanıcı için tanımlanacak yetkiye göre ilgili satır için yetkilendirme yapacağız. Şimdi öncelikle kullanıcılarımıza aşağıdaki gibi tablomuz üzerinde Select yetkisi verelim ve kontrol edelim.

GRANT SELECT ON dbo.Sales TO aliduru
GRANT SELECT ON dbo.Sales TO ahmetkorkmaz
GRANT SELECT ON dbo.Sales TO ismailadar

Yukarıdaki gibi kullanıcılarımıza yetki verdikten sonra bir kullanıcımızın yetkisi olup olmadığını kontrol edelim.

EXECUTE AS USER = 'aliduru'
SELECT * FROM Sales;
REVERT
GO

sql_server_2016_row_level_security_1

Yukarıdaki resimde gördüğümüz sorgumuzun sonucunu incelediğimizde aliduru adlı kullanıcının yetkileriyle sorgumuzu çalıştırdığımızda bu kullanıcının ilk durumda tüm kayıtlara erişebildiğini görüyoruz. SQL Server 2016 ile beraber gelen Row Level Security özelliğini kullanabilmek için öncelikle ilgili kullanıcı sorgu çalıştırdığında True dönen bir Inline table Value Function oluşturalım. Daha sonra oluşturduğumuz bu fonksiyonumuzu oluşturacağımız Security Policy için FILTER PREDICATE olarak ekleyelim.

CREATE FUNCTION dbo.rowLevelSecurityFunction (@userName as sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS rowLevelResult
WHERE @userName = USER_NAME()
GO

CREATE SECURITY POLICY rowLevelUserFilter
ADD FILTER PREDICATE dbo.rowLevelSecurityFunction(UserName)
ON dbo.Sales
WITH (STATE = ON);
GO

Yukarıdaki gibi fonksiyonumuzu ve bu fonksiyonumuzla ilişkili olarak Security Policy objemizi oluşturduk. Dikkat ederseniz fonksiyonumuz parametre olarak bir kullanıcı adı alıyor ve buna göre içeride işlem yaparak geriye 1(True) veya NULL dönüyor. Ayrıca oluşturmuş olduğumuz Security Policy objemizi incelediğimizde ADD FILTER PREDICATE dbo.rowLevelSecurityFunction(UserName) ifadesi ile oluşturduğumuz fonksiyonumuzun tablomuz üzerinde sorgu çalıştırıldığında otomatik olarak Filtreleme için kullanılacağını belirtiyoruz. Bir alttaki ON dbo.Sales ifadesi ile işlemin hangi tablo üzerinde uygulanacağını belirtiyoruz. Kodumuzu açıkladıktan sonra aynı sorgumuzu çalıştıralım.

EXECUTE AS USER = 'aliduru'
SELECT * FROM Sales;
REVERT
GO

sql_server_2016_row_level_security_2

Yukarıdaki resimde gördüğümüz gibi sorgumuzu aliduru adlı kullanıcının yetkileriyle çalıştırdığımızda sadece ona ait kayıtların geldiğini görebiliyoruz.
Çok kullanılışlı olan Row Level Security özelliğinde gördüğünüz gibi biz sorgumuza herhangi bir parametre eklemesek bile SQL Server bizim için arka planda kullanıcı adını parametre olarak ekliyor. Bunu yaparken de bildiğini gibi daha önceden oluşturduğumuz Security Policy objesini kullanıyor. Burada dikkat etmemiz gereken nokta SQL Serverın bizim yerimize eklemiş olduğu parametre performans sorununa yol açabilir. Bunu test etmek için şimdi sorgumuzu tekrar çalıştıralım ve Execution Planımızı inceleyelim. Sorgumuza Actual Execution planı dâhil ettikten sonra aliduru kullanıcısının yetkileri ile çalıştırdığımızda aşağıdaki gibi bir hata ile karşılaşırız.

sql_server_2016_row_level_security_3

Yukarıdaki hatayı çözmek için kullanıcımıza SHOW PLAN yetkisi verelim.

GRANT SHOWPLAN to aliduru

Yetkimizi verdikten sonra sorgumuzu tekrar çalıştırıp Execution planımızı inceleyelim.

EXECUTE AS USER = 'aliduru'
SELECT * FROM Sales;
REVERT
GO

sql_server_2016_row_level_security_4

Execution planımızı incelediğimizde ilk olarak göze çarpan nokta sorgumuza herhangi bir Filtre eklememiş olmamıza rağmen Execution Planımızda Filter operatörünü görebiliyoruz. İşte bu operatör ile yapılan filtreleme işlemi ile sorgumuzu çalıştıran kullanıcı adı sorgumuza dinamik olarak ekleniyor. Daha ayrıntılı görebilmek için Execution planımız üzerinde Filter operatörünün üzerine gelerek açılan pencereyi inceleyelim.

sql_server_2016_row_level_security_5

Yukarıdaki resimde gördüğümüz Filter operatörümüzün detayında Predicate kısmını incelediğimizde koşul olarak tablomuzda UserName kolonu ile o an sorguyu çalıştıran aktif kullanıcının adını biz veren user_name() fonksiyonun sonucu karşılaştırılmaktadır.
Execution planımızı biraz daha detaylı incelediğimizde Select operatörü üzerinde Warning işareti olduğunu görüyoruz. Şimdi de bu operatörümüzü yakından inceleyelim.

sql_server_2016_row_level_security_6

Yukarıdaki resimde gösterildiği gibi SQL Server tarafından eklenen filtre için yapılan karşılaştırmada Bilinçsiz tür dönüşümü yapılıyor ki bu da eğer uygun indeksimiz olsa dahi bu indeks üzerinde Seek değil Scan işleminin yapılmasına sebep olacaktı. Bu durumun sebebi ise tablomuzda kullandığımız kolonumu(UserName) ile karşılaştırma yapılan user_name() fonksiyonundan dönen sonuçların tiplerinin farklı olmasıdır. Daha açık bir ifade ile tablomuzu oluştururken UserName kolonu için varchar veri tipini seçtik oysa fonksiyonumuzdan nvarchar dönüyor. İşte bu veri tipi uyumsuzluğunu gidermek adına SQL Server otomatik olarak veri tipi dönüşümü yapıyor ki biz buna bilinçsiz tür dönüşümü diyoruz. Özellikle büyük boyutlu tablolarda performans problemi yaratan böyle durumlar için tablomuzu oluştururken tip uyumluğunu da göz önüne almalıyız.

Hakkında ismailadar

Cevapla

E-posta adresiniz yayınlanmayacak. Required fields are marked *

*


*

Şu HTML etiketlerini ve özelliklerini kullanabilirsiniz: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>