質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.31%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

Q&A

解決済

1回答

5471閲覧

ビューのみSELECT可能として元テーブルの参照を不可にしたい

kutsulog

総合スコア985

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

0グッド

0クリップ

投稿2022/03/17 11:03

編集2022/03/18 08:34

SqlServerでビューの元テーブルに対するSELECT制限をかけたいのですが、
やり方がわからないので方法がわかる方教えていただけないでしょうか?

やりたいこと

  • 複数の接続元が存在するDBでほかの接続元用のデータが見えないようにしたい
  • 同じ形式のテーブルデータの更新は回数を抑えたい
  • それぞれの接続元からは同じデータ種別であれば同じテーブル名(またはビュー名)でアクセスできるようにしたい

例)
接続元A,B,Cがあるとします。
製品マスタがあるとして、大元のテーブルには接続元A,B,Cすべてのデータを登録します。
接続元Aには専用のDB(同一インスタンス別DB)を用意し、
そのDBには大元の製品マスタに対するビューを作成して接続元A用のデータだけが検索できるようにしたいです。

このとき、接続元Aには大元の製品マスタは見せたくありません。

接続元B,Cも接続元Aと同様にしたいです。
接続元A,B,Cはそれぞれのデータを「製品マスタ」から取得しようとします。

試したこと

  • 特になにも設定しない:大元の製品マスタを参照できないが、ビューで参照エラーになる(権限不足)
  • 大元の表に参照権限をつける:ビューは参照可能になるが、大元の製品マスタを参照できてしまう。
  • 専用DB側にビュー参照のユーザーロールを作ってユーザに付与する:大元の製品マスタを参照できないが、ビューで参照エラーになる(権限不足)
  • 大元の表があるDBにテーブル参照のユーザーロールを作ってユーザに付与する:ビューは参照可能になるが、大元の製品マスタを参照できてしまう。

解決方法がわかりましたらご教示いただけますと幸いです。
やりたいことが実現できればDBを分ける以外の方法でも構いません
よろしくお願いします。

2022/03/18 補足

専用DB側にビュー参照のユーザーロールを作ってユーザに付与する

こちらを行った際の手順ですが、
1.ロールを作る
・ロール名をつける
・同じ種類のオブジェクトすべてを対象としてViewを選択
・'Select'の'Grant'にチェックを入れる
2.ロールをログインに紐づける
・ログインの対象ユーザで作成したロールにチェックを入れる

上記の手順で実施しています。

このとき、大元のDBにログインユーザをマップしていないとき以下のエラーとなりました。

現在のセキュリティ コンテキストでは、サーバー プリンシパル "<ログインユーザー名>" はデータベース "<大元データベース名>" にアクセスできません。

大元のDBにログインユーザーをマップすると以下のエラーとなりました。

SELECT 権限がオブジェクト '製品マスタ、データベース '<大元データベース名>'、スキーマ 'dbo' で拒否されました。

(製品マスタはデフォルトスキーマ'dbo'に作成しています)
(作成したロールの所有者は'dbo'になっています)

大元のDBで製品マスタに対するSELECT権限をつけるとエラーは発生しなくなりますが、直接製品マスタを参照できてしまいます。

どこか手順に不足、誤りがあるのでしょうか?

またビューに参照権限を与えるとは、ユーザーやロールに参照権限をつけるのではなく、
ビューに製品マスタへの参照権限をつけるということでしょうか?
この操作の方法がわからないので方法または参考URLを教えていただけないでしょうか?
環境
SQL Server 2019
SQL Management Studio v18.10

2022/03/18 補足2
提示いただいたURLを参考に以下のクエリを実行しましたが、やはり先の捕捉に記載した通りのエラーとなってしまいます。

SQL

1use <専用DB> 2GRANT SELECT ON dbo.<製品マスタのビュー> TO <接続先専用ユーザー> AS dbo 3

2022/03/18 補足3
試したことへの記載をしておりませんでしたが、
同一インスタンス、同一DB環境において接続先専用のViewが参照可能かつ、大元のテーブルが参照不可になる確認はできています。
今回実施したいのは同一インスタンス、別DB環境でのView参照になります。
(参考URLの例で行くとSERVER1のインスタンス内に大元のテーブルを持つSampleDBがあって、そこにアクセスするViewはSampleDB2,SampleDB3のような別のDBに配置したいです)
別DBにすると実現できないということであれば、その旨を教えていただけますと幸いです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2022/03/17 11:24

>専用DB側にビュー参照のユーザーロールを作ってユーザに付与する 方針はこれでいいと思う。 何かを間違えている。
退会済みユーザー

退会済みユーザー

2022/03/17 11:56

ビューに参照権限を与える...
guest

回答1

0

自己解決

DBを分ける方法ではビュー参照可かつ元テーブル参照不可が実現できませんでしたが、
同一DBでスキーマを分ける方法でしたら実現できましたので
こちらの方法を採用することとしました。

投稿2022/03/30 23:57

kutsulog

総合スコア985

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.31%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問