Oefening: autorisatie beheren via beveiliging op kolom- en rijniveau

  • 9 minuten

In deze oefening ziet u voorbeelden hoe u autorisatie kunt beheren via beveiliging op kolom- en rijniveau.

Een voorbeeld van beveiliging op kolomniveau

In het volgende voorbeeld ziet u hoe u de toegang tot de SSN kolom van de Membership tabel kunt beperkenTestUser:

Tabel maken Membership met de SSN-kolom die wordt gebruikt voor het opslaan van burgerservicenummers:

CREATE TABLE Membership
  (MemberID int IDENTITY,
   FirstName varchar(100) NULL,
   SSN char(9) NOT NULL,
   LastName varchar(100) NOT NULL,
   Phone varchar(12) NULL,
   Email varchar(100) NULL);

Toegang TestUser tot alle kolommen toestaan, met uitzondering van de SSN-kolom, met de gevoelige gegevens:

GRANT SELECT ON Membership(MemberID, FirstName, LastName, Phone, Email) TO TestUser;

Query's die TestUser als volgt worden uitgevoerd, mislukken als ze de SSN-kolom bevatten:

SELECT * FROM Membership;

-- Msg 230, Level 14, State 1, Line 12
-- The SELECT permission was denied on the column 'SSN' of the object 'Membership', database 'CLS_TestDW', schema 'dbo'.

Een voorbeeld van beveiliging op rijniveau

In dit scenario ziet u een voorbeeld voor beveiliging op rijniveau in een externe Azure Synapse-tabel.

In dit korte voorbeeld worden drie gebruikers en een externe tabel met zes rijen gemaakt. Vervolgens wordt een inline-tabelwaardefunctie en een beveiligingsbeleid voor de externe tabel gemaakt. In het voorbeeld ziet u hoe select-instructies worden gefilterd voor de verschillende gebruikers.

Vereisten

  • U moet een SQL-pool hebben. Zie Een Synapse SQL-pool maken
  • De server die als host fungeert voor uw SQL-pool, moet zijn geregistreerd bij AAD en u moet beschikken over een Azure-opslagaccount met de machtiging Inzender voor het opslagblog. Volg de stappen hier.
  • Maak een bestandssysteem voor uw Azure Storage-account. Gebruik Storage Explorer om uw opslagaccount weer te geven. Klik met de rechtermuisknop op containers en selecteer Bestandssysteem maken.

Zodra u aan de vereisten voldoet, maakt u drie gebruikersaccounts die verschillende toegangsmogelijkheden demonstreren.

--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO

--run in master and your SQL pool database
CREATE USER Manager FOR LOGIN Manager;  
CREATE USER Sales1  FOR LOGIN Sales1;  
CREATE USER Sales2  FOR LOGIN Sales2 ;

Maak een tabel voor het opslaan van gegevens.

CREATE TABLE Sales  
    (  
    OrderID int,  
    SalesRep sysname,  
    Product varchar(10),  
    Qty int  
    );

Vul de tabel met zes rijen met gegevens, met drie orders voor elke vertegenwoordiger.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table  
SELECT * FROM Sales;

Maak een externe Azure Synapse-tabel op basis van de tabel Sales die u zojuist hebt gemaakt.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);
  
CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

Verwijs SELECT toe voor de drie gebruikers in de externe tabel Sales_ext die u hebt gemaakt.

GRANT SELECT ON Sales_ext TO Sales1;  
GRANT SELECT ON Sales_ext TO Sales2;  
GRANT SELECT ON Sales_ext TO Manager;

Maak een nieuw schema en een inline-tabelwaardefunctie. Mogelijk hebt u dit in voorbeeld A voltooid. De functie retourneert 1 wanneer een rij in de kolom SalesRep hetzelfde is als de gebruiker die de query uitvoert (@SalesRep = USER_NAME()) of als de gebruiker die de query uitvoert de Manager-gebruiker (USER_NAME() = 'Manager') is.

CREATE SCHEMA Security;  
GO  
  
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

Maak een beveiligingsbeleid voor uw externe tabel met behulp van de inline-tabelwaardefunctie als filterpredicaat. De status moet worden ingesteld op AAN om het beleid in te schakelen.

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext  
WITH (STATE = ON);

Test nu het filterpredicaat door te selecteren in de Sales_ext externe tabel. Meld u aan als elke gebruiker, Sales1, Sales2 en Manager. Voer de volgende opdracht uit als elke gebruiker.

SELECT * FROM Sales_ext;

De manager moet alle zes rijen zien. De gebruikers Verkoop1 en Verkoop2 mogen alleen hun verkoop zien.

Wijzig het beveiligingsbeleid om het beleid uit te schakelen.

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);

Nu kunnen de gebruikers Sales1 en Sales2 alle zes rijen zien.

Verbinding maken met de Azure Synapse-database om resources op te schonen

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred; 
DROP MASTER KEY;

Maak verbinding met logische master om resources op te schonen.

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;