SQLServerのデータベースユーザついて質問、特定DB内特定テーブルのみ更新可能に権限を狭めたい

Question

実現したいこと

SQLServer(14.0.2070.1)内のあるデータベース内の「各テーブル」が
ACCESS経由のODBC接続で、やりたい放題に更新されてしまっているのが現況です。

ODBC接続される際に利用されているデータベースユーザ（ユーザアカウント）に　より厳格な権限を与え　本来ユーザ部門で　更新許可を与えているテーブルだけが更新可能なよう　今回統制を図りたいです。

発生している問題・分からないこと

正直、SQLServer自体のログインとデータベースユーザの　両者の関係もよく分かっていない身です。
だいぶ　マスキングだらけな　画像ですが、現在話題にしている　ログインとデータベースユーザについての　現況を参考に添付致します。

どのような手続きを行えば　認められたテーブルだけ　ユーザ部門に更新が許可されるようになるのか教えて頂きたいです。
現況の問題のユーザアカウントを無効化して、新たなログイン・データベースユーザを追加してもよいし、当該ユーザアカウントに　何らかの定義変更を行って　目標を達成する方式　どちらでもOKです。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

こちら　が関連する記事の印象ですが、自分のレベルの低さから　何をすべきなのか読み解くことができませんでした。ギャップを理解できていないんだと思います。

補足

特になし

Comments

[sazi]

現状ユーザーは１つという事でしょうか？
であれば、ユーザーを新設して、権限を使い分けるという事になると思いますが、Accessで使用しているユーザーを新たなものにしたとしても、旧のユーザーがそのまま残っている状況では、既知の情報を使用して接続する人は残りそうですので、
①全削除して新たに作成する
②Access以外でアクセスするユーザーを新設すると共に、既存ユーザーは権限を変更する
のパターンが考えられますが、共にAccess以外でアクセスしているユーザー名を変更する必要が出てきますが、それは対応（既存のプログラムに対する改修等)可能なんでしょうか？

Accessの使用者がルールを順守する前提であれば、以下が無難ですが。
③Accessで利用する新規ユーザーを作成する。

[saya24]

いつもお世話になっております。

ACCESSでの接続はマスターメンテナンスを目的としたもので、この用途のユーザアカウントとパスワードをユーザ部門に公表しています。

当該データベース内の実績系トランザクションテーブルと、先言及のマスターのテーブルを結合したレポートを　Webアプリで照会する運用を行なってきています。

このアプリ内でDBアクセスするためのユーアカウントが別途あり、実績データを該当テーブルへの投入するプログラム内部でもこれが活用されており、いわばIT部門よりのユーザアカウントが別途ある、という感じです。

権限の管理構造がイメージできていないの一言ですが、まず、私が申し上げたテーブル単位で更新許可を与えることができるのでしょうか？
テーブルのプロパティ側で何かの定義を行うのでしょうか？

スキーマとかいうものもよくわかっていません、すみません。

[utm.]

ログインとデータベースユーザーは同じ意味です、
データベースユーザーとして登録されていて、そのユーザーがテーブルを見たり更新したりできるので、それにログインするとその動作ができるということです。

データベースにて、ユーザーにテーブルの閲覧権限や更新権限がないとそういう操作は出来なくなっていますから、テーブル単位で更新許可を与えることは出来ます出来ます。

データベースはユーザー管理用のテーブルを持っていて、そこにユーザーの権限やパスワードなどが格納されているので、そのテーブルを更新可能なユーザーで入り権限を与えることになります。

(ユーザーとアカウントは同じ意味で今回使っています)

大体イメージできたのであればあとは検索しつつやって行けると思います。

ちなみにスキーマというのは簡単に言うとただのテーブルの列のことです。

[saya24]

ご見解ありがとうございます。

>ちなみにスキーマというのは簡単に言うとただのテーブルの列のことです。
レイアウトの意味はでは　理解しておりますが、　dbo　とかいうのが　何？という感じなんです。

>大体イメージできたのであればあとは検索しつつやって行けると思います。
限定的に利用するための、ユーザアカウントを新設して、新設できたら　既存の問題のユーザアカウントを無効化するイメージですが、実際の操作面は　検索で有用な情報をいける、とのことですね？

なんかいい記事がみつかるといいんですけれどぉ．．．もう少し探してみます。

[utm.]

ちょっと無駄な情報が多いのと、CUIでSQL操作をしたことがある前提なのかキーワードが出てきてピンと来ない部分があるかもしれませんが、以下の情報を頼りにすると行けるかもしれません
https://learn.microsoft.com/ja-jp/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-ver16

(そういえばSSMSのディレクトリの下の方にそんなのあったの検索して思い出した...)

例えば、ALTERみたいなキーワードは設定を変更するためのコマンドで、開発者ならピンと来ます。

まあ難しかったら担当者にお願いすればいい気がします。
(もっと具体的な回答を求めてるのは分かるんですが、このサイトで詳しく説明していいことが起きた試しがない)

[saya24]

MSのサイトですね、イメージが省略されているのと　関連情報がちりばめられていて　いつも理解に苦しみますね。ご紹介ありがとうございました、必要に応じ参加させて頂きます。

Answer 1

本来ユーザ部門で　更新許可を与えているテーブルだけが更新可能なよう　今回統制を図りたいです。

という事なので、権限の付与はユーザーではなく、統制のパターンをロールとして定義してロールに権限付与とする方がメンテナンスはし易そうです。
※以下はAIからの回答を元に作成しています。細かいところまでチェックはしておりませんので悪しからず。
１．ロールの作成
パターンごとにロールを作成します。

USE [YourDatabase];
GO
-- ロールの作成（パターンごとに分ける）
CREATE ROLE MasterUpdate_PatternA;
CREATE ROLE MasterUpdate_PatternB;
CREATE ROLE MasterUpdate_PatternC;
GO

２．テーブル単位で権限を付与
各ロールに対して、更新対象のテーブルごとに権限を付与します。

USE [YourDatabase];
GO

-- パターンA用のロールにテーブルAへの権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.MasterTable_A TO MasterUpdate_PatternA;

-- パターンB用のロールにテーブルBへの権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.MasterTable_B TO MasterUpdate_PatternB;

-- パターンC用のロールにテーブルCへの権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.MasterTable_C TO MasterUpdate_PatternC;
GO

３．ユーザーの作成
各パターンの処理用にユーザーを作成します。

-- ユーザーの作成（Windows認証を使用しない場合）
CREATE LOGIN UserA WITH PASSWORD = 'StrongPasswordA';
CREATE LOGIN UserB WITH PASSWORD = 'StrongPasswordB';
CREATE LOGIN UserC WITH PASSWORD = 'StrongPasswordC';
GO

USE [YourDatabase];
GO

CREATE USER UserA FOR LOGIN UserA;
CREATE USER UserB FOR LOGIN UserB;
CREATE USER UserC FOR LOGIN UserC;
GO

４．ユーザーをロールに追加
ユーザーをそれぞれのパターン用ロールに追加します。

USE [YourDatabase];
GO

ALTER ROLE MasterUpdate_PatternA ADD MEMBER UserA;
ALTER ROLE MasterUpdate_PatternB ADD MEMBER UserB;
ALTER ROLE MasterUpdate_PatternC ADD MEMBER UserC;
GO

Comments

[saya24]

saziさん　いつも丁寧で分かりやすい説明を頂き誠にありがとうございます。
たしかにできる気がします。
明日か明後日に会社でやってみますね。

本当に頼りなります！　先ずはお礼まで

[sazi]

更新のパターンを作成する場合には、リレーションシップに注意して、デッドロックにならないように注意する必要があります。

[saya24]

「デッドロック」、たまに聞きますね、この単語

今回を機会に　しっかり理解して、権限のはく奪・追加から　なぜこれが引起こされるのか　確認してみます。理解できるといいなぁ．．．

[sazi]

デッドロックは表現が相応しくありませんでした。

現状で、主となるマスターのメンテナンスに於いて他のマスターも同時に更新する必要があって更新している部分も、やりたい放題に見えているかもしれません。
同時に更新する必要のあるマスターの更新権限を剥奪することにより、現状のメンテナンスが機能しなくなる可能性について注意が必要という事です。

[saya24]

saziさん、補足ありがとうございました。
こちらのDBの利用実態をsaziさんは不明なので、権限剥奪が意図せず　業務に支障を招くこともありうる、ことを一応に述べられた　というように私は　解釈しました。

またトライ結果を報告します、ありがとうございました(まだ未試行)

[saya24]

saziさんのアドバイスが功を奏して、特定テーブル限定で更新を許可するユーザアカウント（ログイン）を本日無事追加することができました。

従来使ってきた　ユーザアカウントの無効化は　ログイン側で対処の予定です。これは2/22～24の休日のシステム移行作業の一貫で対処予定です。

今回もお世話になりました。
RDBに関して今まで何回お世話になったことか．．．saziさんに足を向けて寝られませんね。