SE の雑記

現状、SQL Database の設定管理は T-SQL で実施することになります。
どんなオブジェクトが作成されているかは GUI から確認できますが、細かな設定に関しては SQL で設定 / 取得する必要があります。

セキュリティ関連の設定について、どのように設定するかの基本パターンをメモとして。

ログインとユーザーの情報については
SQL Azure におけるデータベースとログインの管理
Windows Azure SQL データベースにおけるデータベースとログインの管理

■ログインとユーザー

SQL Database のユーザー管理は、

• ログイン
• ユーザー

の 2 種類になります。

ログインはサーバーにログインするためのオブジェクトとなりサーバーの操作として何ができるかを設定するためのオブジェクトとなります。
サーバーにアクセスするための権限についてはログインで管理することになります。
SQL Database の場合は、

• loginmanager
• dbmanager
• ロールを付与しない

のどれかを選ぶことになるかと思います。

ユーザーは操作するためのオブジェクトを制御するための設定となり、ログイン後にデータベースのオブジェクトに対してどのような操作ができるかを制御するためのオブジェクトとなります。
一般的にはログイン名と同様のユーザーを使用することになると思いますが、テーブルの SELECT の権限などはユーザーに対して付与をします。

固定データベースロールもいくつか用意されていますが、個別に権限を設定することで細かなアクセス制御をすることができます。

権限系に関しては以下のシステムビューから確認をすることができます。

• sys.database_principals
• sys.database_permissions
• sys.database_role_members

■master データベースにログインとユーザーを作成

サーバーに接続するためにはログインが必要ですが、初回のログイン時は master データベースに接続されますので、master にもユーザーを作成しておきます。
# ユーザーデータベースを直接指定した場合は不要になりますが、SSMS 等でデータベースを指定しないで接続をした場合は master に接続されますので。

-- ログインの作成 (master)
CREATE LOGIN user01
WITH PASSWORD = '<パスワード>'
GO
CREATE LOGIN user02
WITH PASSWORD = '<パスワード>'
GO
-- ユーザーの作成 (master)
CREATE USER user01
FOR LOGIN user01
GO
CREATE USER user02
FOR LOGIN user02
GO

これで、作成したログインで SQL Database にログインすることができるようになります。

■スキーマとデータベースユーザーを使用した参照権限の設定

スキーマに関してはデフォルトの [dbo] を使用することもできますが、今回は違うパターンを載せておきたいと思います。

以下のように、各ユーザー用のスキーマを用意し、ビュー経由でテーブルを参照する構成を設定してみたいと思います。

# 参照テーブルは直接 SELECT ができないセキュリティ設定となります。

まずはユーザーデータベースにユーザーを作成します。

この際にユーザーの規定のスキーマは各ユーザー用のスキーマにしています。

-- ユーザーの作成 (ユーザーデータベース)
CREATE USER user01
FOR LOGIN user01
WITH DEFAULT_SCHEMA = user01
GO
CREATE USER user02
FOR LOGIN user02
WITH DEFAULT_SCHEMA = user02
GO

次にスキーマを作成します。

スキーマの所有者は dbo を設定し、各ユーザーにはこのスキーマの SELECT 権限を付与しています。

-- スキーマの作成
CREATE SCHEMA user01
AUTHORIZATION dbo
GRANT SELECT ON SCHEMA::user01 TO user01
GO
CREATE SCHEMA user02
AUTHORIZATION dbo
GRANT SELECT ON SCHEMA::user02 TO user02
GO

これで基本的な環境が作成できましたので次にテーブルを作成します。

このテーブルは dbo のスキーマ配下にあるテーブルですので、user01 / user02 は直接参照することはできません。

-- テーブルの作成
IF OBJECT_ID('dbo.BaseTable') IS NOT NULL
  DROP TABLE dbo.BaseTable
GO
CREATE TABLE dbo.BaseTable
(
	Col1 int NOT NULL,
	Col2 nvarchar(50) NOT NULL,
    CONSTRAINT PK_BaseTable PRIMARY KEY (Col1)
)
GO
CREATE INDEX IX_BaseTable_Col2
ON dbo.BaseTable
(
	Col2
)
GO
-- 初期データの投入
INSERT INTO BaseTable VALUES (1, N'user01')
INSERT INTO BaseTable VALUES (2, N'user02')
INSERT INTO BaseTable VALUES (3, N'user01')
INSERT INTO BaseTable VALUES (4, N'user02')
INSERT INTO BaseTable VALUES (5, N'user01')
INSERT INTO BaseTable VALUES (6, N'user02')

参照元のテーブルが作成できたら各ユーザーのビューを作成します。

ビューでは各ユーザーのデータのみを表示するように制限をしています。

-- ビューの作成
IF object_id(N'user01.RefView', 'V') IS NOT NULL
	DROP VIEW user01.RefView
GO
CREATE VIEW user01.RefView AS
SELECT * FROM dbo.BaseTable
WHERE Col2 = USER_NAME()
GO
IF object_id(N'user02.RefView', 'V') IS NOT NULL
	DROP VIEW user02.RefView
GO
CREATE VIEW user02.RefView AS
SELECT * FROM dbo.BaseTable
WHERE Col2 = USER_NAME()
GO

今回は画面キャプチャ用に EXECUTE AS で実行していますが、同じクエリを実行してもユーザーによって異なるデータを返すせってとなっています。

# デフォルトのスキーマを利用した参照オブジェクトの変更を利用したものです。

ビューからのみデータを参照するための設定としてはこれで実現できるかと思います。

私の場合、テーブルの設計等から入ることは珍しいためセキュリティの設定は都度思い出さないとなかなか理解が追い付かないですね…。