SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2014 自習書 No.5 Microsoft Azure SQL Database 入門

SQL Server 2014 自習書シリーズ（HTML 版）
「No.5 Microsoft Azure SQL Database 入門」

松本美穂と松本崇博が執筆した SQL Server 2014 自習書シリーズの「No.5 Microsoft Azure SQL Database 入門」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

目次へ | 前のページへ | 次のページへ

2.20　行レベルセキュリティ（プレビュー版）を利用する

（2015年1月時点での情報）

Azure SQL Database は、V12（Version 12）では、行レベルセキュリティ（プレビュー版）機能を利用することができます。これは、SQL Server 2014 にはまだ実装されていない機能で、将来のバージョンの SQL Server で搭載される予定のものです。執筆時点（2015年1月時点）ではプレビュー版になりますが、SQL Server よりも早く、Azure SQL Database 上に実装された形です。

行レベルセキュリティを利用すれば、次のようにユーザーごとに、見せたい行を制限できるようになります。

00164

この図のような行レベルセキュリティを実装するには、次のように操作します。

-- データベースユーザーの作成
CREATE USER Manager WITH PASSWORD = 'P@ssword'
CREATE USER User1 WITH PASSWORD = 'P@ssword'
CREATE USER User2 WITH PASSWORD = 'P@ssword'
-- テーブルの作成。colUserName 列にアクセス可能なユーザーの名前を格納するようにする
CREATE TABLE rsTest1
( colUserName sysname
,colA int
,colB varchar(200) )
-- データを 4件追加
INSERT rsTest1
VALUES ('User1', 1, 'AAA')
       ,('User1', 2, 'BBB')
       ,('User2', 3, 'CCC')
       ,('User2', 4, 'DDD')
-- データの確認
SELECT * FROM rsTest1
-- データベースユーザーに、テーブルに対する SELECT 権限を付与
GRANT SELECT ON rsTest1 TO Manager
GRANT SELECT ON rsTest1 TO User1
GRANT SELECT ON rsTest1 TO User2
go
-- ユーザー定義関数の作成
-- ユーザーはユーザーのデータ、Manager は全データを参照できるように制御
CREATE FUNCTION rsFunc1(@UserName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
   SELECT 1 AS result
    WHERE USER_NAME() = @UserName
       OR USER_NAME() = 'Manager'
go
-- フィルターの作成
-- 上で作成したユーザー定義関数に、該当テーブルの colUserName 列を与える
CREATE SECURITY POLICY rsFilter1
ADD FILTER PREDICATE dbo.rsFunc1(colUserName)
  ON dbo.rsTest1
WITH (STATE = ON)
go

このように、USER_NAME() 関数を利用したユーザー定義関数を作成して（上の例では rsFunc1 という名前で作成）、CREATE SECURITY POLICY ステートメントで ADD FILTER PREDICATE を指定してフィルターを作成します。該当テーブル（rsTest1）に対して、rsFunc1 関数でフィルターをかけ、関数へ与える引数には colUserName 列を与えるという構成です。

まずは、何も権限が与えられていないユーザーでテーブルを SELECT してみます。

-- 何も権限が与えられていないユーザー
SELECT * FROM rsTest1

結果には、何も返ってきません。このように、テーブルに対してフィルターを設定していると、ユーザー定義関数で設定したユーザー以外は、テーブルデータを参照できない形になります。

次に、Manager ユーザーでアクセスをしてみます。Azure SQL Database では、SQL Server と同様、次のように EXECUTE AS USER ステートメントを利用して、指定したユーザーをシミュレートすることができます。また、REVERT ステートメントを実行することでシミュレートを終了することができます。