SQL Server Management Studioでアクセス権情報を作る

泣いていてもしかたがないので、SQL Serverのアクセス権をクエリで設定する方法を調べてみると、

grant select on [テーブル名] to [ロール]

な感じで GRANT を使います。ちなみに、削除をすると場合は、↓な感じ。

revoke select on [テーブル名] to [ロール]

# 実は至高の技のワンポイントに書いていたよ…自分で忘れてた。
さて、手作業で設定はできることは分かった。が、GUIで設定した後にアクセス権の構成情報を取得する手段がない。

そう、気付いたのだが、何も SQL Server Management Studio だけが出力だけが出力できないのではなくて、Oracle や MySQL の場合も出力できない。というわけで、何も SQL Server Management Studio だけが悪いってわけではないんだね。う～む。

業務で DBA の真似ごとをしていて必要なのは、順序付ければ以下のようになります。

a) テーブル、インデックスの構成情報
b) データベースの再構成
c) ログインユーザーの構成情報
d) ストアドプロシージャの構成情報
e) アクセス権の構成情報

このあたり、SQL Server の場合は手厚くて、a)からd)まで出してくれる。だけども、Oracleの場合、a)もひと苦労で、b),c)に至っては初期の情報がないと手に入れるのが難しく、d)はスクリプトがあるんだが、という状態。
まぁ、ソフトウェア開発者的には必要ないんだが、SE(システムエンジニア)イコール大手の社員(プロパー)を示すようになってしまった現在においては、DBAなり設計者なりの能力は低いと判断して構わない。インフラ整備をしている個人事業主のSEの実力はよくわからないのだけど。
少なくとも、インフラ関係の情報は google などでは取得しにくいのは確か。

閑話休題。

実はアクセス権の設定は優先順位が低い。というのも、大抵のプロジェクトはa)からc)で力尽きて（？）しまうわけで、ストアドプロシージャとかアクセス権まで手が廻らないからです。
ただし、ストアドプロシージャに関しては、

・適切な分業体制を作る。
・WEBインフラ、C/Sシステムにて適切な実行環境を得る。

ってな訳で相当重要なことなのだが、忘れ去られつつある（ように見える）。

アクセス権に関しては更に重要で、

・情報の保護
・バックアップの重要度により分離

なところがあるにも関わらず、かなりいい加減なログインユーザーの設定をしていたりする。あいにく、銀行系のデータベースを触ったことがないので、そうなのかもしれないが、情報系のデータベースはかなりいい加減だ（社内で使うからどうでもいいという話もあるが）。

というわけで、（もし）アクセス権を設定しているならば、sp_helprotect を使うと取得できる。

ネタ元
http://q.hatena.ne.jp/1122874335

sp_helprotect @username='limit'

何も付けないとすべてのアクセス権を拾ってしまうので、@username にログイン名/ロール名を指定する。この場合は limit ロールのアクセス権を拾うわけだ。

結果をみると↓のようになる。

Owner Object Grantee Grantor ProtectType Action Column
dbo t_person limit dbo Grant      Select (All+New)
dbo t_在庫 limit dbo Grant      Select (All+New)
dbo t_商品 limit dbo Grant      Select (All+New)

主に見ないといけないのは、ProtectType列とAction列らしい。
ここでは設定として、limitロールに次の３つのテーブルに対して参照権限（SELECT）を与えている。
・t_person
・t_在庫
・t_商品
逆に言えば、ほかのテーブルを見ることができない。
与えるほうはProtectType列が「Grant」、拒否が「Revoke」になる。

# ちなみに以下のマニュアルを見ると、「GRANT」「REVOKE」と大文字になっている。
# SQL Serverの初期値では大文字小文字を区別しないから、どちらでもいいのだが、
# 設定次第だと区別することになるので、この記述は問題だなぁ。

Transact-SQL リファレンス
sp_helprotect
http://msdn.microsoft.com/ja-jp/library/aa933420(SQL.80).aspx

 

実データは、sysprotects テーブルらしいので、こっちから取得するのもあり。

Transact-SQL リファレンス
sysprotects
http://msdn.microsoft.com/ja-jp/library/aa260449(SQL.80).aspx

というわけで、元ネタが分かったので簡単なクエリを書いてみよう。

begin
-- 対象のロールを設定
 declare @name sysname = 'limit'
-- 一時テーブルを作成
 create table #temp (
  [owner] sysname,
  [object] sysname,
  [grantee] sysname,
  [grantor] sysname,
  [protecttype] char(10),
  [action] varchar(20),
  [column] sysname
  )
 insert into #temp execute sp_helprotect @username=@name
-- スクリプトを作成
 select
  [protecttype] + ' ' + [action]
  + ' on [' + [object] + ']' +
  + ' to [' + [grantee] + ']' as query
 from #temp
-- 一時テーブルを削除
 drop table #temp
end

sp_helprotect ストアドプロシージャの結果を直接 FROM 句では受けられないので、一時テーブルを使う。この一時テーブルに対して、文字列を加工して GRANT のスクリプトを生成すればOK。括弧で括ってあるのは念のため。スクリプトを加工する場合は置換で面倒なので外したほうがベターでしょう（括弧は正規表現の予約語だからね）。

結果はこんな感じ

Grant      Select on [t_person] to [limit]
Grant      Select on [t_在庫] to [limit]
Grant      Select on [t_商品] to [limit]

# 一時テーブルのアイデアは下記より借用

なにやらかにやらメモ SQLServer
http://www31.atwiki.jp/memo77/pages/22.html
中の技術日誌ブログ – ストアドプロシージャって難しいなぁ
http://blogs.wankuma.com/naka/archive/2004/03/07/1607.aspx

メンテ専用のストアドプロシージャを作るのもよいのだが、まあ、こんな風にブログにぺたと貼り付けておいて、コピー＆ペーストで実行するのもありかな、と。