Veritabanı ve nesne izinlerini açıklama

Tamamlandı

Tüm İlişkisel Veritabanı Yönetimi platformları, veri işleme dili (DML) işlemlerini denetleyen dört temel izne sahiptir. Bu izinler , SELECT, INSERTve UPDATEizinleridir DELETEve tüm SQL Server platformları için geçerlidir. Bu izinlerin tümü tablolarda ve görünümlerde verilebilir, iptal edilebilir veya reddedilebilir. deyimi kullanılarak GRANT bir izin verilirse, izin deyiminde GRANT başvuruda bulunan kullanıcıya veya role verilir. Kullanıcılara da komutu kullanılarak DENY izinler reddedilebilir. Bir kullanıcıya izin verilirse ve aynı izin reddedilirse, DENY her zaman vermenin yerini alır ve kullanıcının belirli nesneye erişimi reddedilir.

Örnekte, kullanıcı Demo'nun SELECT tablosu üzerinde SELECT izni verilir ve ardından izni reddedilir. Kullanıcı dbo'dan seçen bir sorgu yürütmeye çalıştığında. Şirket tablosu, kullanıcı iznin reddedildiğini belirten SELECT bir hata alır.

Tablo ve görüntüleme izinleri

Tablolar ve görünümler, veritabanında izin verilebilecek nesneleri temsil eder. Bu tablolar ve görünümler içinde, belirli bir güvenlik sorumlusunun (kullanıcı veya oturum açma) erişebileceği sütunları da kısıtlayabilirsiniz. SQL Server ve Azure SQL Veritabanı, erişimi daha fazla kısıtlamak için kullanılabilen satır düzeyi güvenlik de içerir.

İzin	Tanım
SELECT	Kullanıcının nesne (tablo veya görünüm) içindeki verileri görüntülemesine izin verir. Reddedildiğinde, kullanıcının nesne içindeki verileri görüntülemesi engellenir.
INSERT	Kullanıcının nesneye veri eklemesine izin verir. Reddedildiğinde, kullanıcının nesneye veri eklemesi engellenir.
UPDATE	Kullanıcıya nesne içindeki verileri güncelleştirme izni verir. Reddedildiğinde, kullanıcının nesnedeki verileri güncelleştirmesi engellenir.
DELETE	Kullanıcının nesne içindeki verileri silmesine izin verir. Reddedildiğinde, kullanıcının nesneden veri silmesi engellenir.

Azure SQL Veritabanı ve Microsoft SQL Server'ın gerektiğinde verilebilen, iptal edilebilen veya reddedilebilen başka izinleri vardır.

İzin	Tanım
CONTROL	Nesnelere tüm hakları verir. Bu izne sahip olan kullanıcının nesne üzerinde istediği eylemi gerçekleştirmesine izin verir ve nesneyi siler.
REFERENCES	Kullanıcıya nesnedeki yabancı anahtarları görüntüleme olanağı verir.
TAKE OWNERSHIP	Kullanıcının nesnenin sahipliğini almasını sağlar.
VIEW CHANGE TRACKING	Kullanıcının nesnenin değişiklik izleme ayarını görüntülemesine izin verir.
VIEW DEFINITION	Kullanıcının nesnenin tanımını görüntülemesine izin verir.

İşlev ve saklı yordam izinleri

Tablolar ve görünümler gibi işlevler ve saklı yordamlar da verilebilen veya reddedilebilen çeşitli izinlere sahiptir.

İzin	Tanım
ALTER	Kullanıcıya nesnenin tanımını değiştirme olanağı verir.
CONTROL	Kullanıcıya nesne için tüm hakları verir.
EXECUTE	Kullanıcıya nesneyi yürütme olanağı verir.
VIEW CHANGE TRACKING	Kullanıcının nesnenin değişiklik izleme ayarını görüntülemesine izin verir.
VIEW DEFINITION	Kullanıcının nesnenin tanımını görüntülemesine izin verir.

FARKLı YÜRÜT

EXECUTE AS [user name]veya EXECUTE AS [login name] (yalnızca SQL Server ve Azure SQL Yönetilen Örneği kullanılabilir) komutları, kullanıcı bağlamlarının değiştirilmesine olanak tanır. Sonraki komutlar ve deyimler bu bağlama verilen izinlerle yeni bağlam kullanılarak yürütülür.

Bir kullanıcının izni varsa ve kullanıcının artık bu izne sahip olması gerekmiyorsa, REVOKE komutu kullanılarak izinler kaldırılabilir (verir veya reddeder). revoke komutu, belirtilen kullanıcı için belirtilen hakka ait GRANT veya DENY izinlerini kaldırır.

Sahiplik Zincirleri

Zincirleme adlı bir kavram, kullanıcıların diğer nesnelerden izinleri devralmasına olanak tanıyan izinler için geçerlidir. Zincirlemenin en yaygın örneği, yürütmesi sırasında bir tabloya erişen bir işlev veya saklı yordamdır. Yordamın sahibi tabloyla aynıysa, kullanıcının tabloya doğrudan erişme hakkı olmasa bile saklı yordam yürütülür ve tabloya erişebilir. Kullanıcı tabloya saklı yordamdan erişme haklarını devraldığından, ancak yalnızca saklı yordamın yürütülmesi sırasında ve yalnızca saklı yordam yürütme bağlamında olduğundan bu erişim kullanılabilir.

Örnekte, veritabanı sahibi veya sunucu yöneticisi olarak çalıştırıldığında, yeni bir kullanıcı oluşturulur ve yeni bir SalesReader rolünün üyesi olarak eklenir. Bu rol, herhangi bir nesneden seçim yapmak ve Sales şemasındaki herhangi bir yordamı yürütmek için izin verilir. Ardından Satış şemasında, Üretim şemasındaki bir tabloya erişen bir saklı yordam oluşturulur.

Örnek daha sonra içeriği yeni kullanıcı olarak değiştirir ve doğrudan Üretim şemasındaki tablodan seçim yapmaya çalışılır.

USE AdventureWorks2016;
GO

CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd';
GO

CREATE ROLE [SalesReader];
GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
GO

GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader];
GO

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

GO

EXECUTE AS USER = 'DP300User1';

SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

Sorgu, kullanıcının ait olduğu rolün Üretim şemasında herhangi bir ayrıcalığı olmadığından DP300User1SELECT kullanıcısının izni olmadığını belirten bir hatayla sonuçlanır. Şimdi saklı yordamı yürütmeyi deneyebiliriz:

EXECUTE AS USER = 'DP300User1';

EXECUTE Sales.DemoProc;

Dp300User1 kullanıcısının Sales şemasındaki saklı yordam üzerinde izni vardırEXECUTE, çünkü kullanıcının rolünün Sales şeması üzerinde izni vardırEXECUTE. Tablo, prosedürle aynı sahip olduğundan kesintisiz bir sahiplik zincirine sahibiz, bu nedenle yürütme başarılı olur ve sonuçlar geri döner.

Saklı yordamlarda dinamik SQL kullanılırken izin değişiklikleri uygulanmaz. Dinamik SQL'in izin zincirini kesmesinin nedeni, dinamik SQL'in çağıran saklı yordamın bağlamının dışında yürütülmesidir. Aşağıdaki gibi, saklı yordamı dinamik SQL kullanarak yürütülecek şekilde değiştirerek bu davranışı görebilirsiniz.

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
DECLARE @sqlstring NVARCHAR(MAX)

SET @sqlstring = '
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales, 
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, SOH.OrderDate'

EXECUTE sp_executesql @sqlstring
GO

--

EXECUTE AS USER = 'DP300User1'

EXECUTE Sales.DemoProc

DP300User1 kullanıcısı, kullanıcının sorguyu doğrudan yürütmeyi denediği gibi SELECT tablosunda izni olmadığını belirten bir hata alır. İzin zincirleri uygulanmaz ve dinamik SQL'i yürüten kullanıcı hesabının dinamik SQL'de kod tarafından kullanılan tablolar ve görünümler için hakları olmalıdır.

En az ayrıcalık ilkesi

En az ayrıcalık ilkesi oldukça basittir. Kavramın ardındaki temel fikir, kullanıcılara ve uygulamalara yalnızca görevi tamamlamaları için gereken izinlerin verilmesi gerektiğidir. Uygulamaların yalnızca eldeki görevi tamamlamak için yapması gereken izinlere sahip olması gerekir.

Örneğin, bir uygulama saklı yordamlar aracılığıyla tüm verilere erişiyorsa, uygulamanın tablolara erişimi olmadan yalnızca saklı yordamları yürütme iznine sahip olması gerekir.

Dinamik SQL

Dinamik SQL, sorguların program aracılığıyla derlendiği bir kavramdır. Dinamik SQL, T-SQL deyimlerinin saklı yordamda veya sorguda oluşturulmasına olanak tanır.

SELECT 'BACKUP DATABASE ' + name + ' TO DISK =''\\backup\sql1\' + name + '.bak'''
FROM sys.databases

deyimi, sunucudaki tüm veritabanını yedeklemek için T-SQL deyimlerinin listesini oluşturur. Genellikle, oluşturulan bu T-SQL, sp_executesql kullanılarak yürütülür veya yürütülmesi için başka bir programa aktarılır.