练习 - 通过列级别和行级别安全性管理授权

9 分钟

此练习演示了如何通过列级别和行级别安全性来管理授权。

列级别安全性示例

下面的示例展示了如何限制 TestUser 访问 Membership 表的 SSN 列：

创建 Membership 表，其中包含用于存储社会安全号码的“SSN”列：

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

允许 TestUser 访问所有列，包含敏感数据的“SSN”列除外：

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

如果包含“SSN”列，以 TestUser 身份执行的查询便会失败：

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

行级别安全性示例

此方案提供了 Azure Synapse 外部表上的行级别安全性示例。

此简短示例创建三个用户，以及一个包含六行的外部表。然后，它为外部表创建内联表值函数和安全策略。该示例演示如何为各种用户筛选选择语句。

先决条件

必须具有一个 SQL 池。请参阅创建 Synapse SQL 池
托管 SQL 池的服务器必须向 AAD 注册，并且你应具有一个包含存储博客参与者权限的 Azure 存储帐户。按照此处的步骤操作。
为 Azure 存储帐户创建一个文件系统。使用存储资源管理器查看存储帐户。右键单击容器，然后选择“创建文件系统”。

满足先决条件后，创建三个用户帐户来演示不同的访问功能。

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

创建用于保留数据的表。

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

使用六行数据填充该表（对于每个销售代表显示三个订单）。

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;

根据刚创建的 Sales 表，创建 Synapse Analytics 外部表。

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;

为已创建的外部表 Sales_ext 上的三个用户授予 SELECT 权限。

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

创建一个新架构和一个内联表值函数，你可能已在示例 A 中完成了此操作。该函数在 SalesRep 列中的行与执行查询的用户相同时 (@SalesRep = USER_NAME())，或者在执行查询的用户是 Manager 用户 (USER_NAME() = 'Manager') 时返回 1。

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

将内联表值函数用作筛选谓词，对外部表创建安全策略。状态必须设置为 ON 以启用该策略。

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

现在，通过从 Sales_ext 外部表中进行选择，测试筛选谓词。以每个用户（Sales1、Sales2 和管理员）的身份登录。以每个用户的身份运行以下命令。

SELECT * FROM Sales_ext;

管理员应看到所有六行。 Sales1 和 Sales2 用户应只能看到自己的销售额。

更改安全策略以禁用策略。

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);

现在 Sales1 和 Sales2 用户可以看到所有六行。

连接到 Azure Synapse 数据库以清理资源

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;

连接到逻辑主数据库以清理资源。

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

练习 - 通过列级别和行级别安全性管理授权

列级别安全性示例

行级别安全性示例

先决条件

反馈