Gyakorlat – Engedélyezés kezelése oszlop- és sorszintű biztonsággal

  • 9 perc

Ebben a gyakorlatban példákat mutatunk be az engedélyezés oszlop- és sorszintű biztonságon keresztüli kezelésére.

Példa az oszlopszintű biztonságra

Az alábbi példa bemutatja, hogyan korlátozhatja TestUser a SSN tábla oszlopának Membership elérését:

A társadalombiztosítási számok tárolására használt SSN-oszlopot tartalmazó táblázat létrehozása Membership :

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);

A TestUser bizalmas adatokkal rendelkező SSN-oszlop kivételével az összes oszlopot elérheti:

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

A végrehajtott lekérdezések sikertelenek TestUser lesznek, ha tartalmazzák az SSN oszlopot:

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'.

Példa sorszintű biztonságra

Ez a forgatókönyv egy azure Synapse külső tábla sorszintű biztonságára mutat példát.

Ez a rövid példa három felhasználót és egy hat sorból álló külső táblát hoz létre. Ezután létrehoz egy beágyazott táblaértékű függvényt és egy biztonsági szabályzatot a külső táblához. A példa bemutatja, hogyan szűrhetők a választó utasítások a különböző felhasználókra.

Előfeltételek

  • Sql-készletet kell létrehoznia. Lásd: Synapse SQL-készlet létrehozása
  • Az SQL-készletet üzemeltető kiszolgálót regisztrálni kell az AAD-ben, és rendelkeznie kell egy Storage-blog közreműködői engedélyekkel rendelkező Azure Storage-fiókkal. Kövesse az alábbi lépéseket.
  • Hozzon létre egy fájlrendszert az Azure Storage-fiókhoz. A Tárkezelővel megtekintheti a tárfiókot. Kattintson a jobb gombbal a tárolókra, és válassza a Fájlrendszer létrehozása lehetőséget.

Ha már rendelkezik az előfeltételekkel, hozzon létre három felhasználói fiókot, amelyek különböző hozzáférési képességeket mutatnak be.

--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 ;

Hozzon létre egy táblát az adatok tárolásához.

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

Töltse fel a táblázatot hat adatsorsal, amelyek három megrendelést mutatnak az egyes értékesítési képviselők számára.

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;

Hozzon létre egy Külső Azure Synapse-táblát az imént létrehozott Sales táblából.

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;

Adja meg a Standard kiadás LECT-et a létrehozott külső tábla három felhasználójának Sales_ext.

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

Hozzon létre egy új sémát és egy beágyazott táblaértékű függvényt, ezt az A példában fejezhette be. A függvény 1 értéket ad vissza, ha a SalesRep oszlop egy sora megegyezik a lekérdezést végrehajtó felhasználóval (@SalesRep = USER_NAME()), vagy ha a lekérdezést végrehajtó felhasználó a Manager-felhasználó (USER_NAME() = 'Manager').

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';

Hozzon létre egy biztonsági szabályzatot a külső táblán a beágyazott táblaértékelt függvény használatával szűrő-predikátumként. A szabályzat engedélyezéséhez az állapotot BE értékre kell állítani.

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

Most tesztelje a szűrési predikátumot a Sales_ext külső tábla kiválasztásával. Jelentkezzen be felhasználóként, Sales1, Sales2 és Managerként. Futtassa a következő parancsot minden felhasználóként.

SELECT * FROM Sales_ext;

A kezelőnek mind a hat sort látnia kell. A Sales1 és a Sales2 felhasználóknak csak az értékesítésüket kell látniuk.

Módosítsa a biztonsági szabályzatot a szabályzat letiltásához.

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);

Most a Sales1 és a Sales2 felhasználók mind a hat sort láthatják.

Csatlakozás az Azure Synapse-adatbázisba az erőforrások törléséhez

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;

Csatlakozás logikai főkiszolgálóra az erőforrások törléséhez.

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