演習 - 列と行のレベルのセキュリティを使用して認可を管理する

9 分

この演習では、列と行のレベルのセキュリティを使用して認可を管理する方法を示します。

列レベルのセキュリティの例

次の例では、TestUser が Membership テーブルの SSN 列にアクセスするのを制限する方法を示します。

社会保障番号を格納するために使用される SSN 列が含まれた 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);

機密データを含む SSN 列以外のすべての列へのアクセスを TestUser に許可します。

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

TestUser として実行されたクエリは、SSN 列が含まれている場合に失敗します。

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 外部テーブルの行レベルのセキュリティの例を示します。

この簡単な例では、3 人のユーザーと 6 行の外部テーブルを作成します。その後、インラインテーブル値関数と外部テーブルのセキュリティポリシーが作成されます。この例では、select ステートメントがさまざまなユーザーに対してどのようにフィルター処理されるかが示されます。

前提条件

SQL プールが必要です。 Synapse SQL プールを作成する方法に関するページを参照してください。
SQL プールをホストしているサーバーが AAD に登録されている必要があります。Storage Blob Contributor のアクセス許可がある Azure Storage アカウントを持っている必要があります。それには、こちらの手順に従います。
自分の Azure Storage アカウントにファイルシステムを作成します。 Storage Explorer を使用して自分のストレージアカウントを表示します。コンテナーを右クリックし、 [ファイルシステムの作成] を選択します。

前提条件を満たしたら、別のアクセス機能を示す 3 つのユーザーアカウントを作成します。

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

そのテーブルに、各営業担当者の 3 つの注文を表示する、6 つのデータ行を設定します。

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 テーブルから Azure Synapse 外部テーブルを作成します。

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 外部テーブルで、3 人のユーザーに 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())、またはクエリを実行しているユーザーがマネージャーユーザーである場合 (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;

マネージャーには、6 つの行すべてが表示されるはずです。 Sales1 と Sales2 のユーザーには、各自の売上のみ表示されます。

セキュリティポリシーを変更してポリシーを無効にします。

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);

これで、Sales1 と Sales2 のユーザーに 6 つの行すべてが表示されます。

リソースをクリーンアップする 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;

演習 - 列と行のレベルのセキュリティを使用して認可を管理する

列レベルのセキュリティの例

行レベルのセキュリティの例

前提条件

フィードバック