（前回→http://blogs.wankuma.com/kaya/archive/2007/01/03/54520.aspx )
さて、スキーマを分けたところで 終わりました。
trnスキーマはプログラムなどから自由に読み書きできるスキーマと考えることも出来るのですが、
mstスキーマに対しては、個人情報漏洩などの観点から、簡単に何でも出来ちゃ困りますよね？
なので、この回ではアクセス権限の設定をします。
まずは、trnuserに対してmstスキーマの全権限を拒否します。
DENY ALTER ON SCHEMA::[mst] TO [trnuser]
GO
DENY CONTROL ON SCHEMA::[mst] TO [trnuser]
GO
DENY DELETE ON SCHEMA::[mst] TO [trnuser]
GO
DENY EXECUTE ON SCHEMA::[mst] TO [trnuser]
GO
DENY INSERT ON SCHEMA::[mst] TO [trnuser]
GO
DENY REFERENCES ON SCHEMA::[mst] TO [trnuser]
GO
DENY SELECT ON SCHEMA::[mst] TO [trnuser]
GO
DENY TAKE OWNERSHIP ON SCHEMA::[mst] TO [trnuser]
GO
DENY UPDATE ON SCHEMA::[mst] TO [trnuser]
GO
DENY VIEW DEFINITION ON SCHEMA::[mst] TO [trnuser]
GO
この状態でtrnuserで接続し、SELECT * FROM MST.USERINFOを発行すると以下のエラーになります。
メッセージ 229、レベル 14、状態 5、行 1
SELECT 権限がオブジェクト 'userinfo'、データベース 'データベース名'、スキーマ 'mst' で拒否されました。
これで見れなくなりましたね。
って、さて困りました。
trnスキーマにあるユーザ購入情報(親)・(子)テーブルには
mstスキーマにあるユーザ情報、商品情報を参照するためのキーを持っています。
だからtrnuserにとってmstスキーマの情報がまったく見れないのは困るんです。
仕方がないので、trnuserには参照するだけの権限を与えてあげましょう。
GRANT CONTROL ON SCHEMA::[mst] TO [trnuser] AS [mstuser]
GO
GRANT SELECT ON SCHEMA::[mst] TO [trnuser] AS [mstuser]
GO
GRANT REFERENCES ON SCHEMA::[mst] TO [trnuser] AS [mstuser]
GO
SELECT文発行
select * from mst.userinfo;
| ID |
LN |
FN |
POST |
ADDRESS |
| 100 |
NULL |
今川 |
1234567 |
神奈川県 |
| 110 |
NULL |
大木 |
7654321 |
東京都 |
| 120 |
NULL |
林 |
9873216 |
千葉県 |
※ 参照するだけなんですが、この3種類の権限を与えてあげなければいけません。
さて、見えるようになりましたね。
でも、この状態ではユーザ情報(userinfo)に格納されている住所などの個人情報もみえてしまいます。
なので、ユーザ情報のある特定列だけ参照を許すようにしてあげましょう。
GRANT REFERENCES ON [mst].[userinfo] ([id]) TO [trnuser] AS [mstuser]
GO
GRANT SELECT ON [mst].[userinfo] ([id]) TO [trnuser] AS [mstuser]
GO
GRANT REFERENCES ON [mst].[userinfo] ([fn]) TO [trnuser] AS [mstuser]
GO
GRANT SELECT ON [mst].[userinfo] ([fn]) TO [trnuser] AS [mstuser]
GO
GRANT REFERENCES ON [mst].[userinfo] ([ln]) TO [trnuser] AS [mstuser]
GO
GRANT SELECT ON [mst].[userinfo] ([ln]) TO [trnuser] AS [mstuser]
GO
DENY REFERENCES ON [mst].[userinfo] ([post]) TO [trnuser]
GO
DENY SELECT ON [mst].[userinfo] ([post]) TO [trnuser]
GO
DENY REFERENCES ON [mst].[userinfo] ([address]) TO [trnuser]
GO
DENY SELECT ON [mst].[userinfo] ([address]) TO [trnuser]
GO
ここで 参照を許可してあげたのは ID, FN, LNの3つ
参照を拒否したままなのはPOST、ADDRESSの２つの列です。
※ ここではSELECTとREFERENCESの許可を設定しています。
この状態で2種類のSQLを発行しましょう。
(1) SELECT * FROM MST.USERINFO ;
(2) SELECT ID, FN, LN FROM MST.USERINFO ;
これで(1)を実行すると
メッセージ 229、レベル 14、状態 5、行 1
SELECT 権限がオブジェクト 'userinfo'、データベース 'データベース名'、スキーマ 'mst' で拒否されました。
が表示されます。
(2)を実行すると以下の結果が取得されます。
| ID |
FN |
LN |
| 100 |
NULL |
今川 |
| 110 |
NULL |
大木 |
| 120 |
NULL |
林 |
と結果が取れるようになりました。
これでtrnuserには大切な情報の住所を見せずに、ユーザ情報を参照させることが出来ますね。
さて、使い勝手のことを考えたりすると、いちいち列名指定するの面倒だったりもしますよね。
なので、ここでViewをmstスキーマに作成します。
create view [mst].[iteminfov] as
( select itemid, price, itemname from mst.iteminfo) ;
create view [mst].[userinfov] as
( select id , fn, ln from mst.userinfo ) ;
で trnuserから見た使用を考えると FROM句にいちいち mst.iteminfov とか書くの面倒ですよね。
なので、次の手！
シノニムをtrnスキーマに作成します。
CREATE SYNONYM [trn].[iteminfos] FOR [sec].[mst].[iteminfov];
GO
CREATE SYNONYM [trn].[userinfos] FOR [sec].[mst].[userinfov];
GO
SQLをシノニムに対して発行します！
SELECT * FROM iteminfos ;
| ID |
FN |
LN |
| 100 |
NULL |
今川 |
| 110 |
NULL |
大木 |
| 120 |
NULL |
林 |
これでtrnuserに対してmstスキーマにあるデータへのアクセスを意識しなくても出来る状態になりました。
最後に全テーブルJOINします♪
#あくまで使用例としてね～
select
userinfos.ln,
buyinfoP.buydate,
iteminfos.itemname,
buyinfoC.num ,
iteminfos.price ,
buyinfoC.num * iteminfos.price as totalSub
from
buyinfoP
join userinfos
on buyinfoP.id = userinfos.id
join buyinfoC
on buyinfoP.id = buyinfoC.id
and buyinfoP.buydate = buyinfoC.buydate
join iteminfos
on buyinfoC.itemid = iteminfos.itemid ;
| LN |
BUYDATE |
ITEMNAME |
NUM |
PRICE |
TOTALSUB |
| 今川 |
2007-01-01 00:00:00.000 |
ラケット |
2 |
30000 |
60000 |
| 今川 |
2007-01-01 00:00:00.000 |
ボール(1ダース) |
1 |
3000 |
3000 |
| 今川 |
2007-01-02 00:00:00.000 |
シューズ |
1 |
15000 |
15000 |
| 今川 |
2007-01-02 00:00:00.000 |
ソックス |
1 |
1000 |
1000 |
| 大木 |
2007-01-02 00:00:00.000 |
ボール(2個入り) |
2 |
300 |
600 |
| 大木 |
2007-01-02 00:00:00.000 |
ソックス |
3 |
1000 |
3000 |
| 林 |
2007-01-03 00:00:00.000 |
シューズ |
2 |
15000 |
30000 |
| 林 |
2007-01-03 00:00:00.000 |
ソックス |
2 |
1000 |
2000 |
いかがでしょうか？
こんな感じでテーブルをスキーマ分けし、アクセス権限を設定することで
データの保護をしたり出来ます。
またスキーマを分けたことによるめんどくささもViewやシノニムを用いて解消することが出来るんです。
うまく活用してデータの運用にも着目した設計に役立てたら幸いです。
#っと、こんな感じでよろしいでしょうか？＞依頼者様
#結構 いっぱいいっぱいですよぉ(ｗ