SQL Server ではデータベースの操作を行うためには sysadmin 固定サーバーロールまたは db_owner 固定データベースロールの権限が必要となることがあります。
これらの権限はインスタンスまたはデータベースの管理者の権限が付与されるため様々な操作が可能となります。
特定の操作を可能にするため、sysadmin / db_owner の権限を付与する必要があるが、データの秘匿性を担保するためデータへのアクセスについては拒否したいというケースもあるのではないでしょうか。
本投稿では管理者の権限を付与した状態でデータへのアクセスを拒否する方法についてまとめておきたいと思います。
この方法は SQL Server だけでなく SQL Database でも使用することができます。
sysadmin ではなく db_owner の権限を付与する
前提となりますが sysadmin 固定サーバーロールが必要な操作がある場合には、管理者権限の付与とデータの参照権限の拒否を両立することは難しいかと思います。
sysadmin の権限が付与されている場合、DENY によりデータアクセス操作を拒否していても sysadmin の権限が優先されデータの参照ができてしまいます。
そのため、管理者権限とデータの参照のコントロールについては db_owner を使用したデータベースレベルの権限で対応をする必要があります。
sysadmin の権限が必要となる場合はストアドプロシージャでラップして、実行したい操作を可能にするというような考慮が必要になってくるのではないでしょうか。
スキーマに対しての DENY の設定によりデータ参照を拒否する
SELECT / INSERT / UPDATE のようなデータ操作の権限ですが、テーブル / スキーマの CONTROL を上位とした権限の階層となっています。
そのため、SCHEMA に対して CONTROL を DENY することで DB の管理者権限を付与した状態でデータアクセスを拒否することができます。
サンプルとしては以下のようなクエリとなります。
USE [<対象 DB 名>] GO -- DB の db_owner 権限を付与 ALTER ROLE [db_owner] ADD MEMBER [<対象となるユーザー>] GO -- スキーマに対しての CONTROL を拒否し、データアクセスができないようにする -- 以下の例は dbo スキーマを対象としているが、任意のユーザースキーマを作成している場合は適切なスキーマを設定 DENY CONTROL ON SCHEMA::dbo TO [<対象となるユーザー>] GO /* -- 設定の解除 REVOKE CONTROL ON SCHEMA::dbo TO [<対象となるユーザー>] */
これで db_owner の権限を持っているが、データのアクセスを拒否するということができます。
厳密にはスキーマの制御ができなくなっているので、スキーマに対してはデータのアクセス以外も幅広い操作が拒否された状態となっています。
まとめ
sysadmin 固定サーバーロールが付与されている場合は、このような制御はできないのですが、db_owner 固定データベースロールであれば、このような制御が可能となります。
sysadmin または db_owner の権限が必要となる操作のアクセス許可については db_owner で権限を付与しておくと、許可したい操作の制御は実施しやすくなるのではないでしょうか。
細かな権限制御を実施していると、「自分のログインにどのような権限が付与されているのか」が分からなくなるケースもあると思いますので、その際は sys.fn_my_permissions の存在を覚えておくことで、自身に付与されている権限を確認できますので、権限の調整の際にはこのシステム関数も活用するとよいかと思います。