VIEWしか読み取れないユーザの作成方法について

質問

SQL Server 2008R2/2012でのVIEWのアクセス権限の付与について

パケージのシステムを構築しており、ユーザにVIEWの読み取り権限だけを公開したいと考えています。
ユーザは、SQL Server Management StudioでそのVIEWに対してSELECTだけできるようにしたいのですが、やってみるとVIEWに対しての権限の他に元となるテーブルのアクセス権限も必要となり、見せたくないテーブルとそのテーブルの列が見えてしまいます。

なんとか、DBへの特定のログインユーザだけVIEWに対して読み取り（SELECT）ができようなユーザが作れないでしょうか？

アドバイスをお願いします。

回答No.2

toshih2000 · ベストアンサー

詳細な情報が無いので、
「できますよ」としか言えませんが、

注意として、create view するユーザーは
元テーブルへのアクセス権限が必要です。

お礼

ありがとうございます。

できるということであれば、やって検証してみます。

補足

元テーブルへのアクセス権限がナイトのエラーが発生してうまく見れませんでした。
元テーブルを見せずにVIEWだけを公開したいと考えており、それをやる方法は無いという認識でいます。

発生したエラー

1> select count(*) from viewSchema.viewdept;
2> go
メッセージ 916、レベル 14、状態 1、サーバー DRIFTER、行 1
現在のセキュリティ コンテキストでは、サーバー プリンシパル "testuser" はデータベ
ース "HOGEHOGE" にアクセスできません。

こうなるとtruncateして、insert selectでテーブルコピーをしてviewではなく、テーブルで公開するという方法しか無いのかなぁって思っています。

考え方はあっているでしょうか？

回答No.1

bin-chan

キーワード「GRANT」でググる。

そのユーザーにオブジェクトを与えすぎてるからテーブルまで見えちゃう。

お礼

ありがとうございます。
調べてやってみます。

補足

皆様のアドバイスを頂いて、VIEWが見ている先のDBにもSELECT権限を付与して参照用のログインユーザを作りました。ちょっと複雑ですが、これで行きます。ありがとうございました。

create database viewdb;
go
create login viewlogin with password = 'test',check_expiration=off,check_policy=on,default_database=viewdb;
go
use viewdb;
go
create user viewuser for login viewlogin;
go

use mst
create user viewuser for login viewlogin;

--create schema viewSchema authorization viewuser;
--create view viewSchema.viewDept as (select *, 1 hoge, 2 hogehoge from mst.dbo.department);

use viewdb;
go
--drop view viewLine;
create view viewLine as (select l.sLINE_CD, l.sDEPT_CD, l.sNMJ, 1 hoge, 2 hogehoge from mst.dbo.Line l);
go

use viewdb;
--alter role viewRole drop member viewuser;
--drop role viewRole;
create role viewRole authorization dbo;
alter role viewRole add member viewuser;
grant select on object::viewLine to viewRole;

--ユーザ付与でもいい
--grant select on object::viewLine to viewuser;

use mst;
--alter role viewRole drop member viewuser;
--drop role viewRole ;
create role viewRole authorization dbo;
alter role viewRole add member viewuser;
--revoke select on object::Line to viewRole;
grant select on object::Line(sLINE_CD, sDEPT_Cd, sNMJ) to viewRole;

--ユーザ付与でもいい
--grant select on object::Line(sLINE_CD, sDEPT_Cd, sNMJ) to viewuser;