SQL Server の権限設定ですが、基本的な考え方は プリンシパル (データベース エンジン) に記載されている内容となります。
権限としては、次の二種類に分けられます。
- ログイン : インスタンスレベルでの操作権限
- ユーザー : データベースレベルでの操作権限
「ログイン」と「ユーザー」という複数のプリンシパルに分かれているため、理解しづらい構成になっているのですが、SSMS を使用しながらであればわかりやすいかなと思いましたので軽くまとめてみようかと。
今回使用するログインですが、次のクエリで作成したものを使用しています。
CREATE LOGIN tpch WITH PASSWORD='tpch', CHECK_POLICY=OFF
「sysadmin」固定サーバーロールや「db_owner」固定データベースロールがついてしまっていると、状況が変わってくるので、「初期実行の権限を明示的に使用していないログイン」を基点としています。
SQL Server で設定可能な権限ですが、次のドキュメントを調べるのが基本となります。
- 許可 : GRANT (Transact-SQL)
- 拒否 : DENY (Transact-SQL)
- 取消 : REVOKE (Transact-SQL)
これらの操作を行いながら、適切な権限の付与を行っていきます。
権限は階層構造になっているのですが、細かな内容については https://aka.ms/sql-permissions-poster で確認することができます。
Contents
ログインの権限を確認する
このログインを使用して、SSMS で接続を行ってみます。
SSMS で接続を実施すると、正常に接続することができるはずです。
「作成した直後のログインが接続できた」のはなぜでしょうか？
それでは、「ログイン」にどのような権限が付与されているのかを見てみましょう。
ログインの権限については、次のようなクエリで確認することができます。
SELECT
spr.name,
spe.class_desc,
spe.type,
spe.permission_name,
spe.state,
spe.state_desc
FROM
sys.server_principals spr
left join
sys.server_permissions spe
ON
principal_id = grantee_principal_id
WHERE
name in('tpch')
「CONNECT SQL」が許可されていますので、「SQL Server に接続する権限」は付与されていることが確認できました。
「CREATE USER」により作成したユーザーは「CONNECT SQL」の権限がデフォルトで付与されており、「SQL Server のインスタンスに接続するための権限」を持った状態となっているため接続を行うことができました。
SSMS で接続を行うと、データベースの下には作成済みのデータベースが存在していることが確認できます。
「ログイン : tpch」には、「CONNECT SQL」しか権限が付与されていないのですが、なぜデータベースの一覧が表示されているのでしょうか？
「SQL Server で作成した全てのログイン」は 「public」という固定サーバーロールのメンバーとなります。
それでは、先ほど実行したクエリを次のように書き換えてみましょう。
SELECT
spr.name,
spe.class_desc,
spe.type,
spe.major_id,
spe.permission_name,
spe.state,
spe.state_desc
FROM
sys.server_principals spr
left join
sys.server_permissions spe
ON
principal_id = grantee_principal_id
WHERE
name in('tpch', 'public')
取得結果がこちらになります。
「public」に対して「SERVER」レベルの「VIEW ANY DATABASE」が付与されていることが確認できますね。
これにより、「どのようなデータベースが存在しているか」を確認する権限が付与されていることになります。
それでは、次のクエリで「public」から権限を取り消してみるとどうでしょう。
REVOKE VIEW ANY DATABASE TO public
権限の取り消しを行い、SSMS の表示を最新にすると「データベースの一覧」が表示されなくなりました。
このように「ログインの権限」は「自ログインの権限」+「public の権限」の二つにより制御が行われています。
まずは、これでログインの権限を確認することができましたので、次は「ユーザー」の権限を見ていきましょう。
ユーザーの権限を確認する
今回の環境では「tpch」というデータベースがすでに作成されています。
それでは先ほどまで、使用していた「ログイン : tpch」でこのデータベースにアクセスしようとするとどうなるでしょうか。
現在の状態では、次のようなエラーが発生します。
「ログイン : tpch」は「SQL Server に接続をし、DB の一覧を表示する権限」は持っていることになります。
しかし「データベース : tpch にアクセスする権限」は付与していません。
データベースにアクセスるためには、「アクセス対象のデータベースに「ユーザー」を作成し、適切な権限を付与する」必要があります。
(この辺が SQL Server の権限周りで分かりづらい場所ではあるのですが)
ユーザーは次のクエリで作成することができます。
USE [tpch] GO CREATE USER [tpch] FOR LOGIN [tpch] GO
これでデータベースに接続することができました。
現状、テーブルには何も表示されていない状態ですね。
何もテーブルが作成されていない状態なのかと言うと、そういうことではありません。
「sysadmin」固定サーバーロールのログインで接続した場合はテーブルの表示が行われています。
「ユーザーによって表示されている内容が異なっている状態」になっていますね。
それでは「ユーザー」にどのような権限が付与されているか見てみましょう。
SELECT
dpr.name,
spe.class_desc,
spe.type,
spe.major_id,
CASE spe.type
WHEN 'EX' THEN (SELECT name From sys.all_objects WHERE object_id = major_id)
WHEN 'SL' THEN (SELECT name From sys.all_objects WHERE object_id = major_id)
ELSE CAST(major_id AS sysname)
END AS object_info,
spe.permission_name,
spe.state,
spe.state_desc
FROM
sys.database_principals dpr
left join
sys.database_permissions spe
ON
principal_id = grantee_principal_id
WHERE
name in('tpch', 'public')
ORDER BY
name ASC
「public」には、いくつかの権限が付与されているのですが、「tpch」には、特に権限の付与は行われていない状態です。
今回、既定のスキーマは「dbo」なのですが、「dbo」というスキーマに対してはなにも権限が付与されていない状態となっています。
それではデータベースの権限について考えてみましょう。
データベースの権限は次のようになっています。
この権限の一覧の中で、ログインについていた「VIEW ANY DATABASE」の権限を確認しても、それにより付与されている「データベース権限」は存在していません。
つまに、「VIEW ANY DATABASE」の権限を付与したとしても、データベースに対しての権限が付与されていない状態となります。
それでは「データベースにどのようなオブジェクトが含まれているか？」についてはどのように考えればよいでしょうか。
この情報を確認すると次のような表があります。
定義を確認するためには「VIEW DEFINITION」という権限があればよさそうですね。
USE [tpch] GO GRANT VIEW DEFINITION TO tpch
付与した結果がこちらになります。
定義を参照するための権限を付与しましたので、テーブルが表示されるようになりました。
これは「定義を表示するための権限」ですので、「SELECT」を使用としても次のようなエラーとなります。
それでは、SELECT を行うためにはどのような権限が必要となるのでしょうか？
ドキュメントを確認してみます。
大抵のステートメントには、「アクセス許可」という項目があります。
これが、ユーザー (またはログイン) が必要となる権限です。
今回の場合は「SELECT 権限」が必要ということが確認できますね。
SELECT はオブジェクトに対して実行しますので、次のドキュメントを確認します。
SQL Server のテーブルは「スキーマの中にテーブルが含まれる」という階層構造になっていますので、「オブジェクト単体」または「スキーマ全体」に対して権限を付与することでアクセスを許可することができます。
先ほどの customer というテーブルは「dbo」のスキーマ配下ですので次のようないずれかの権限付与でアクセスを許可することができます。
(SELECT を付けることで、定義も見えるようになり、VIEW DEFINITION を取り消しても定義は見えた状態になるかと)
USE [tpch] GO GRANT SELECT ON OBJECT::dbo.customer TO tpch または、 USE [tpch] GO GRANT SELECT ON SCHEMA::dbo TO tpch
これで、「ログイン」「ユーザー」に対しての権限の基本的な設定は終わりです。
あとは、必要となる操作に対して個別に権限の付与を実施したり、「ロール」という形で事前定義を行い再利用できる形で定義を行いながらセキュリティの設定を行います。
古めのドキュメントとなるのですが、SQL Server Best Practices ? Implementation of Database Object Schemas に権限の設定例を交えながら、セキュリティの設定の一例が記載されていますので、一度こちらを確認してみても良いのではないでしょうか。