Database- en objectmachtigingen beschrijven

  • 10 minuten

Alle relationele databasebeheerplatforms hebben vier basismachtigingen, die DML-bewerkingen (Data Manipulat Language) beheren. Deze machtigingen zijnSELECT, INSERTen UPDATEDELETEzijn van toepassing op alle SQL Server-platforms. Al deze machtigingen kunnen worden verleend, ingetrokken of geweigerd voor tabellen en weergaven. Als een machtiging wordt verleend met behulp van de GRANT instructie, wordt de machtiging verleend aan de gebruiker of rol waarnaar in de GRANT instructie wordt verwezen. Gebruikers kunnen ook machtigingen weigeren met behulp van de DENY opdracht. Als een gebruiker een machtiging krijgt en dezelfde machtiging wordt geweigerd, wordt de DENY toekenning altijd vervangen en wordt de gebruiker de toegang tot het specifieke object geweigerd.

A T-SQL Example of a DENY overriding a GRANT

In het bovenstaande voorbeeld wordt de demo van de gebruiker verleend SELECT en vervolgens machtigingen voor de dbo geweigerdSELECT. Bedrijfstabel. Wanneer de gebruiker een query probeert uit te voeren die uit de dbo wordt geselecteerd. Bedrijfstabel , de gebruiker ontvangt een foutmelding dat SELECT de machtiging is geweigerd.

Machtigingen voor tabellen en weergaven

Tabellen en weergaven vertegenwoordigen de objecten waarvoor machtigingen kunnen worden verleend in een database. In deze tabellen en weergaven kunt u ook de kolommen beperken die toegankelijk zijn voor een bepaalde beveiligingsprincipaal (gebruiker of aanmelding). SQL Server en Azure SQL Database bevatten ook beveiliging op rijniveau, die kan worden gebruikt om de toegang verder te beperken.

Machtiging Definitie
SELECT Hiermee kan de gebruiker de gegevens in het object (tabel of weergave) weergeven. Wanneer de gebruiker wordt geweigerd, wordt voorkomen dat de gegevens in het object worden weergegeven.
INSERT Hiermee kan de gebruiker gegevens invoegen in het object. Wanneer de gebruiker wordt geweigerd, wordt voorkomen dat gegevens in het object worden ingevoegd.
UPDATE Hiermee staat u de gebruiker de updategegevens in het object toe. Wanneer deze wordt geweigerd, wordt voorkomen dat de gebruiker gegevens in het object bijwerkt.
DELETE Hiermee kan de gebruiker gegevens in het object verwijderen. Wanneer de gebruiker wordt geweigerd, wordt voorkomen dat gegevens uit het object worden verwijderd.

Azure SQL Database en Microsoft SQL Server hebben andere machtigingen, die indien nodig kunnen worden verleend, ingetrokken of geweigerd.

Machtiging Definitie
CONTROL Verleent alle rechten aan de objecten. Hiermee kan de gebruiker met deze machtiging elke gewenste actie uitvoeren op het object, inclusief het verwijderen van het object.
REFERENCES Hiermee verleent de gebruiker de mogelijkheid om de refererende sleutels voor het object weer te geven.
TAKE OWNERSHIP Hiermee kan de gebruiker eigenaar worden van het object.
VIEW CHANGE TRACKING Hiermee kan de gebruiker de instelling voor het bijhouden van wijzigingen voor het object bekijken.
VIEW DEFINITION Hiermee kan de gebruiker de definitie van het object weergeven.

Machtigingen voor functies en opgeslagen procedures

Net als tabellen en weergaven hebben functies en opgeslagen procedures verschillende machtigingen, die kunnen worden verleend of geweigerd.

Machtiging Definitie
ALTER Verleent de gebruiker de mogelijkheid om de definitie van het object te wijzigen.
CONTROL Verleent de gebruiker alle rechten aan het object.
EXECUTE Verleent de gebruiker de mogelijkheid om het object uit te voeren.
VIEW CHANGE TRACKING Hiermee kan de gebruiker de instelling voor het bijhouden van wijzigingen voor het object bekijken.
VIEW DEFINITION Hiermee kan de gebruiker de definitie van het object weergeven.

UITVOEREN ALS

Met de EXECUTE AS [user name]opdrachten , of EXECUTE AS [login name] (alleen beschikbaar in SQL Server en Azure SQL Managed Instance), kan de gebruikerscontext worden gewijzigd. Als volgende opdrachten en instructies worden uitgevoerd met behulp van de nieuwe context met de machtigingen die aan die context zijn verleend.

Als een gebruiker een machtiging heeft en de gebruiker die machtiging niet meer nodig heeft, kunnen machtigingen worden verwijderd (verlenen of weigeren) met behulp van de opdracht REVOKE. Met de opdracht intrekken worden alle GRANT of DENY machtigingen voor het recht verwijderd dat is opgegeven aan de opgegeven gebruiker.

Eigendomsketens

Een concept dat ketening wordt genoemd, is van toepassing op machtigingen, waarmee gebruikers machtigingen van andere objecten kunnen overnemen. Het meest voorkomende voorbeeld van koppelen is een functie of opgeslagen procedure die tijdens de uitvoering toegang heeft tot een tabel. Als de procedure dezelfde eigenaar heeft als de tabel, kan de opgeslagen procedure worden uitgevoerd en toegang krijgen tot de tabel, ook al heeft de gebruiker geen rechten om rechtstreeks toegang te krijgen tot de tabel. Deze toegang is beschikbaar omdat de gebruiker de rechten over neemt voor toegang tot de tabel vanuit de opgeslagen procedure, maar alleen tijdens de uitvoering van de opgeslagen procedure, en alleen binnen de context van de uitvoering van de opgeslagen procedures.

In het onderstaande voorbeeld wordt als database-eigenaar of serverbeheerder een nieuwe gebruiker gemaakt en toegevoegd als lid van een nieuwe SalesReader-rol , die vervolgens wordt gemachtigd om een object te selecteren en een procedure uit te voeren in het verkoopschema. Vervolgens wordt een opgeslagen procedure gemaakt in het verkoopschema dat toegang heeft tot een tabel in het productieschema.

In het voorbeeld wordt vervolgens de inhoud gewijzigd in de nieuwe gebruiker en wordt geprobeerd om rechtstreeks in de tabel in het productieschema te selecteren.

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;

De bovenstaande query resulteert in een fout dat de gebruiker DP300User1 geen machtiging heeft SELECT , omdat de rol waartoe de gebruiker behoort geen bevoegdheden heeft in het productieschema. Nu kunnen we proberen de opgeslagen procedure uit te voeren:

EXECUTE AS USER = 'DP300User1';

EXECUTE Sales.DemoProc;

De gebruiker DP300User1 heeft EXECUTE machtigingen voor de opgeslagen procedure in het verkoopschema , omdat de rol van de gebruiker machtigingen heeft EXECUTE voor het verkoopschema . Omdat de tabel dezelfde eigenaar heeft als de procedure, hebben we een niet-verbroken eigendomsketen en wordt de uitvoering voltooid en worden de resultaten geretourneerd.

Machtigingswijzigingen zijn niet van toepassing wanneer dynamische SQL wordt gebruikt binnen opgeslagen procedures. De reden waarom dynamische SQL de machtigingsketen onderbreekt, is omdat de dynamische SQL wordt uitgevoerd buiten de context van de aanroepende opgeslagen procedure. U kunt dit gedrag zien door de bovenstaande opgeslagen procedure te wijzigen om uit te voeren met dynamische SQL, zoals hieronder wordt weergegeven.

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

De gebruiker DP300User1 krijgt een foutmelding dat de gebruiker niet gemachtigd is SELECT voor de tabel Production.Product , net zoals de gebruiker heeft geprobeerd de query rechtstreeks uit te voeren. Machtigingsketens zijn niet van toepassing en het gebruikersaccount dat de dynamische SQL uitvoert, moet rechten hebben voor de tabellen en weergaven die worden gebruikt door de code in de dynamische SQL.

Principe van de minste bevoegdheden

Het principe van minimale bevoegdheden is redelijk eenvoudig. Het basisidee achter het concept is dat gebruikers en toepassingen alleen de benodigde machtigingen moeten krijgen om de taak te kunnen voltooien. Toepassingen mogen alleen machtigingen hebben die ze moeten doen om de taak te voltooien.

Als een toepassing bijvoorbeeld toegang heeft tot alle gegevens via opgeslagen procedures, mag de toepassing alleen de machtiging hebben om de opgeslagen procedures uit te voeren, zonder toegang tot de tabellen.

Dynamic SQL

Dynamische SQL is een concept waarbij een query programmatisch wordt gebouwd. Met dynamische SQL kunnen T-SQL-instructies worden gegenereerd binnen een opgeslagen procedure of een query zelf. Hieronder ziet u een eenvoudig voorbeeld.

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

Met de bovenstaande instructie wordt een lijst met T-SQL-instructies gegenereerd om een back-up te maken van alle databases op de server. Normaal gesproken wordt deze gegenereerde T-SQL uitgevoerd met sp_executesql of doorgegeven aan een ander programma dat moet worden uitgevoerd.