権限未設定時の解釈は？

Question

SQL Serve 2008を利用しています。
権限について教えて下さい。


データベース：test_db
スキーマ：test_db

■パターン1
ログインユーザ：test_db
データベースユーザ：test_db
既定のスキーマ：test_db

■パターン2
ログインユーザ：new_user
データベースユーザ：new_user
既定のスキーマ：test_db

2つのユーザを作成し、データベースの権限に関してはデフォルトとします。

例えば、PHPからSELECTした場合、
パターン1→SELECT可
パターン2→SELECT不可
になります。

そこで、パターン2のログインユーザの test_db（データベース）にデータベースロール db_datareader を付与するとSELECT出来るようになります。
パターン1とパターン2の違いは、データベース、スキーマ、ログインユーザ、データベースユーザが全て一致しているか、そうでないかという事になり、全て一致している場合は、省略時権限が付与されるという結果に見えます。

そこで、
1.データベース、スキーマ、ログインユーザ、データベースユーザが一致した場合、省略時権限が付与されるのか？
2.1の認識が正しい場合、どのような権限が付与されるのか？
3.1のように全て一致した場合、省略時権限を付与しない方法はあるのか？
について教えて下さい。

Answer 1

こんにちは。

>1.データベース、スキーマ、ログインユーザ、データベースユーザが一致した場合、省略時権限が付与されるのか？
違います。

SQL Serverのセキュリティの基本で重要なものは、大まかに言うと以下の3つになります。

1.サーバー認証
2.データーベース認証
3.オブジェクト権限

質問文にある
「ログインユーザ」は、サーバー認証
「データベースユーザ」は、データーベース認証
の範疇で、
「SELECT可・不可」は、オブジェクト権限の範疇です。

「既定のスキーマ」とは、
"SELECT * FROM Table"のように、スキーマ名を省略してオブジェクトへアクセスした場合に既定で使用されるスキーマのことで、
権限とは何の関係もありません。

ではなぜ、
>パターン1とパターン2の違いは、データベース、スキーマ、ログインユーザ、データベースユーザが全て一致しているか、そうでないかという事になり、全て一致している場合は、省略時権限が付与されるという結果見えます。

のような誤解をしてしまうのかというと、おそらく
「ログインユーザ－名をデータベースユーザー名と同じにすることが多い」ことと
「スキーマ名を、スキーマの所有者名と同じにすることが多い（多かった？）」ことのためだと思います。

データベースユーザーとログインユーザーは、マッピングがされてさえいれば同名である必要はありませんし、
スキーマの所有者は同名のユーザーである必要はありません。

>2.1の認識が正しい場合、どのような権限が付与されるのか？
>3.1のように全て一致した場合、省略時権限を付与しない方法はあるのか？

1の認識が正しくないのであれですが、「デフォルト」について気になったので少し。

「デフォルト」は何を指すかにもよりますが、
通常SSMSからログイン、データベースユーザーを作成した場合、何もしなければ権限は何も付与されません。
ただし、ログインユーザーは必ずサーバーロール「public」のメンバになるので、publicに何かしらの権限がつけられていればそれに従うことになります。


# セキュリティ・権限に関しては
# SQL Server自習書シリーズの「ログイン権限とオブジェクト権限」が詳しいですので、読んでみてください。
# http://www.microsoft.com/japan/sqlserver/2008/r2/technology/self-learning.mspx#05

以上、参考になりましたら幸いです。

Answer 2

情報ありがとうございます。

市販の書籍でセキュリティに関する知識を得ていたつもりでしたが、改めて、紹介していただいた SQL Server自習書シリーズの「ログイン権限とオブジェクト権限」を一通り読み返しました。
セキュリティに関して、基本的な部分は理解できている事を確認できました。

その上で、改めて確認しましたが、最初の質問にも書かせていただいた通りパターン1とパターン2の設定は一緒ですが、挙動は異なります。

■パターン1
ログインユーザ：test_db
データベースユーザ：test_db
既定のスキーマ：test_db

----------- 追記 -----------
データベースのプロパティ
[権限]→[接続]権限に許可のチェック（権限の許可者：dbo）
ログインユーザのプロパティ
[サーバーロール]publicにチェック
[セキュリティ保護可能なリソース]→指定なし
データベースユーザのプロパティ
[全般]データベースロールにチェックなし
[セキュリティ保護可能なリソース]→指定なし
スキーマのプロパティ
[権限]→指定なし
----------- /追記 -----------

■パターン2
ログインユーザ：new_user
データベースユーザ：new_user
既定のスキーマ：test_db

----------- 追記 -----------
データベースのプロパティ
[権限]→[接続]権限に許可のチェック（権限の許可者：dbo）
ログインユーザのプロパティ
[サーバーロール]publicにチェック
[セキュリティ保護可能なリソース]→指定なし
データベースユーザのプロパティ
[全般]データベースロールにチェックなし
[セキュリティ保護可能なリソース]→指定なし
スキーマのプロパティ
[権限]→指定なし
----------- /追記 -----------

パターン1ではSELECTでき、パターン2ではSELECT出来ないのですが、パターン2のログインユーザのプロパティ、全般、データベースロールとして、db_datareaderメンバーにするとSELECTできます。

このような結果からログインユーザ名、データベースユーザ名、データベース名が同一の場合、デフォルトの権限があるのでは？という事を書かせていただきました。
書き出した項目以外に、権限に差がつく、設定項目の見落としはあるでしょうか？

Answer 3

こんにちは。

>このような結果からログインユーザ名、データベースユーザ名、データベース名が同一の場合、デフォルトの権限があるのでは？という事を書かせていただきました。

繰り返しになりますが、名前によって隠し権限がつくようなことはありません。

>書き出した項目以外に、権限に差がつく、設定項目の見落としはあるでしょうか？

以下、予想ですが、、
1.「データベースユーザ」→「プロパティ」→「全般」の
「このユーザーが所有するスキーマ」欄に、「test_db」のチェックが入っている。
（スクロールで隠れている）

※　「既定のスキーマ」ではありません。繰り返しになりますが「既定のスキーマ」は権限に何の関係もありません。

2.SELECT対象のテーブルに権限がついている。

じゃないかと思います。

よろしくお願い致します。

Answer 4

改め権限の確認をおこないました。

> 1.「データベースユーザ」→「プロパティ」→「全般」の

> 「このユーザーが所有するスキーマ」欄に、「test_db」のチェックが入っている。
> （スクロールで隠れている）

パターン1、パターン2のユーザ共に、所有するスキーマを持っていませんでした。

また、下記に関しても、パターン1、パターン2共に同じ設定になっています

サーバ名→[プロパティ]→[権限]→[SQLの接続]にチェック（権限の許可者:sa）
データベース→[プロパティ]→[権限]→[接続]にチェック（権限の許可者:dbo）
テーブル→[プロパティ]→[権限]→表示なし（権限の明示的付与なし）

> 2.SELECT対象のテーブルに権限がついている。

上記はどこで確認すればよいでしょうか？

以前のバージョン（SQL Server 2000）で、ユーザ毎にデータベースへの権限（SELECT、UPDATE...）を付与する一覧があり、チェックしていたことを思い出しました。

現サーバは、SQL Server 2000からのバージョンアップなのですが、データベースへの権限（SELECT、UPDATE...）を付与するGUIが見つかりません。

GUIで操作できない設定があるのでしょうか？

Answer 5

>> 2.SELECT対象のテーブルに権限がついている。

>上記はどこで確認すればよいでしょうか？

対象テーブルを右クリック→プロパティ→権限からです。

ついていませんか？

また、下記手順で権限および所有スキーマを何も付けずにユーザーを作るとどうなるでしょうか？

前提：１つのインスタンス、データベース上で行うものとする。

1.スキーマ「a」を作成する。

2.スキーマ「a」にテーブル「Table1」を作成する。

3.ログイン「a」を作成する。

4.ログイン「b」を作成する。

5.ログイン「a」にマップされたデータベースユーザ「a」を作成する。
既定のスキーマは「a」とする。

6.ログイン「b」にマップされたデータベースユーザ「b」を作成する。
既定のスキーマは「a」とする。

上記までで質問文にある想定と同じ状態にあると思いますが、
「a」でログインしても「b」でログインしても「Table1」に対する参照権限が無いのでSELECTできないと思います。

この状態でSELECTできず、質問文の状態でSELECTできるということは、何かしら気づかないところで権限を付けてしまっているのだと思います。

 

Answer 6

>>> 2.SELECT対象のテーブルに権限がついている。
>
>>上記はどこで確認すればよいでしょうか？
>
>対象テーブルを右クリック→プロパティ→権限からです。
>
>ついていませんか？

はい、ついていません。

 

教えて頂いた方法で検証しましたが、名称が一致している事で何らかの権限が自動的に付与されるような事にはなりませんでした。

デフォルトの権限というものが無いことは確認できましたが、現状なぜ権限が付与されているか分かりません。

GUI上から見る権限が与えられないように見えるのですが、確認する方法は無いのでしょうか？

Answer 7

権限に関してひとつ分かったことがあります。
スキーマの所有するデータベースユーザでログインするば、特に権限を付与しなくてもSELECTできるようです。

具体的な例として、下記のように設定でログインユーザとスキーマを作成します。
（データベースユーザはログインユーザ作成時に自動的に生成される）
■ユーザ1
ログインユーザ：user1
データベースユーザ：user1
既定スキーマ：schema1

■ユーザ2
ログインユーザ：user2
データベースユーザ：user2
既定スキーマ：schema1

既定スキーマは、同一（schema1）にします。
特別な権限の付与せずにデフォルトとします。
schema1の所有者はuser1とします。
schema1にテーブルを作成します。

この状態でプログラムからテーブルを参照すると、ユーザ1は参照可能で、ユーザ2は参照できません。
今度は、schema1の所有者をuser1からuser2に変更すると上記と逆の結果になります。
以上の結果からスキーマの所有者には何らかの権限が与えられることが分かりました。

ただ、どのような権限が付与されているのか分かりません。
どなたか情報は無いでしょうか？

Answer 8

所有者は常に db_owner です。所有者はログイン ユーザであって、データベース ユーザではないことにも注意が必要かもしれません。

サーバ権限 db_owner は、所有者ではないユーザに対して所有者とおなじ権限を与えるために使うので、実際に所有者が db_owner に所属するというわけではなかったかと思います。（実際は ＧＲＡＮＴ ＡＬＬ されているだけだったか？）

このあたりかな？  

http://msdn.microsoft.com/ja-jp/library/bb669061(v=VS.90).aspx

Answer 9

スキーマの所有者は、db_owner 権限として扱われます。

そのため、現在は Select しか試されていないと思いますが、Insert、Delete、update もできるはずです。

また、スキーマの所有者に対して、権限のはく奪は確かできなかったように思います。