【AWS】RDSのSQLServerでユーザにシステム権限を付与する方法

AWS

こんにちわ。ますの(@masno_soy)です。
普段データベースを触る機会が無いのですが、先日よりSQLServerをRDSで作る機会を頂きました。
MySQLとは勝手が違くて発狂寸前であります。

そんな中、アプリ部門の方から「rootと同じ権限のユーザがほしい」って言われたのです。
よゆうだぜぇ！！といざ実施しますが案の定失敗のあらしです。

RDSで作成したSQLServerでは特別な権限は付与することがGUI上で出来ないようです。
ということで諸々調べたところSQLServer接続後のクエリ内でアカウント作成すれば行けるようで備忘録です。

目次

実施時のエラー内容：「VIEW SERVER STATE permission was denied」

依頼内容：実行されたSQL文のログを確認したいので、コマンド通るようにしてほしいのです。
参考：SQL Server で実行された SQL を SQL で取得する方法

SELECT st.text
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
The user does not have permission to perfom this action.
VIEW SERVER STATE permission was denied on object ‘server’,database’master’.
「VIEW SERVER STATE」の権限がないよって言われてるっぽいのです。
RDSのマスターユーザーアカウント特権表を調べると、確かにシステム権限となっていてマスター特権箇所となっているようです。
データベースエンジン システム権限 データベースロール
Microsoft SQL Server ADMINISTER BULK OPERATIONS、ALTER ANY CONNECTION、ALTER ANY LINKED SERVER、ALTER ANY LOGIN、ALTER SERVER STATE、ALTER TRACE、CONNECT SQL、CREATE ANY DATABASE、VIEW ANY DATABASE、VIEW ANY DEFINITION、VIEW SERVER STATE、ALTER ANY SERVER ROLE、ALTER ANY USER、ALTER ON ROLE SQLAgentOperatorRole DB_OWNER (データベースレベルロール) PROCESSADMIN (サーバーレベルロール) SETUPADMIN (サーバーレベルロール) SQLAgentUserRole (サーバーレベルロール)

参照：RDSマスターユーザーアカウント特権

実行はSSMS(SQL Server Management Studio)から行う

まずSSMSって何＾ｑ＾っていう状態の筆者でした。

SQL Server から Azure SQL Database まで、SQL インフラストラクチャを管理するための統合環境です。
SSMS には、SQL Server とデータベースのインスタンスを構成、監視、および管理するためのツールが備わっています。
参照：SQL Server Management Studio (SSMS) のダウンロード

とりあえずここからSQLServerに接続するためのソフトウェアをダウンロードして、クライアント端末へインストールしました。

SQLServer：権限を変更したいユーザを確認する

  1. SSMSを起動し、SQLServerへログインします。
  2. セキュリティ＞ログイン＞変更したいユーザーをダブルクリックします。
  3. セキュリティ保護可能なリソースをクリックし、下図のように許可に何も入っていない状態が確認できます。
明示的タブ内の箇所にチェックを入れて権限を付与したいのであります！
頑張ってGUI上で設定しようとしてもエラーが出てしまって付与が出来なかったのです。

クエリを実行して指定ユーザにシステム権限を付与する

どうにかして権限付与できないのでしょうかと調べたところ海外の書き込みから発見出来たのであります。
参考：「データベースに完全なアクセス権を持つ別のユーザアカウントを作成したい」
AWS RDS – Microsoft SQL Server Express – cannot add users with permissions to see/create databases

  1. SSMSよりRDSのSQLServerへRDS作成時に設定したマスターアカウントでログインする
  2. データベース＞適当なDBを右クリック＞新しいクエリをクリックする
  3. 下記のクエリを入力して実行する

※RDS SQLServerに対して「完全なアクセス権限」を付与するので状況に応じて適応ください。

USE master;
 GO

GRANT ALTER ANY CONNECTION TO [UserName] WITH GRANT OPTION;
GRANT ALTER ANY LINKED SERVER TO [UserName] WITH GRANT OPTION;
GRANT ALTER ANY LOGIN TO [UserName] WITH GRANT OPTION;
GRANT ALTER SERVER STATE TO [UserName] WITH GRANT OPTION;
GRANT ALTER TRACE TO [UserName] WITH GRANT OPTION;
GRANT CREATE ANY DATABASE TO [UserName] WITH GRANT OPTION;
GRANT VIEW ANY DATABASE TO [UserName] WITH GRANT OPTION;
GRANT VIEW ANY DEFINITION TO [UserName] WITH GRANT OPTION;
GRANT VIEW SERVER STATE TO [UserName] WITH GRANT OPTION;

※クエリ実行に成功すると「コマンドは正常に完了しました。」と表示されます。

実行後、該当ユーザの「セキュリティ保護可能なリソース」を確認してみます。
権限の許可者：クエリ実行時のユーザが許可したことに変化していました。

今回はシステム権限も欲しいと別途言われたので色々付与したのであります。
エラーでは「VIEW SERVER STATE permission was denied」とあったので「GRANT VIEW SERVER STATE」だけでも行けるのではないかなと所感でございまする。
USE master;
 GO
GRANT VIEW SERVER STATE TO [UserName] WITH GRANT OPTION;

権限を外す場合：「権限の許可者」のユーザでGUI上からチェックを外すだけ

権限を付与するのは大変だったのですが、権限を外す場合はGUI上のチェックを触れたのです。
今回の画像でいうとこんな感じの手順

  1. RDSマスターアカウントでSSMSを使いログイン
  2. セキュリティ＞ログイン＞変更したいユーザーをダブルクリック
  3. セキュリティ保護可能なリソースをクリックし
  4. チェックをGUI上からポチポチして外していく
  5. OKをクリック
「権限の許可者」に書かれているユーザ以外で行うと「OK」を押した際にrebokeのエラーが出るので注意であります！

補足：今回の実行した際に付与される権限一覧について

参照した海外の投稿サイトを見る限り「データベースに完全なアクセス権を持つ別のユーザアカウントを作成したい」と記載されており、以下のサーバレベルのロールを付与したいと言っているようです。

  • bulkadmin
  • dbcreator
  • diskadmin
  • securityadmin
  • serveradmin
  • sysadmin

ちなみにAWSの公式ドキュメントでこれらのロールに対しては「現在 Amazon RDS では使用できません。」と記載されています。
参照：Microsoft SQL Server セキュリティより

「アプリケーションに必要な最小の特権で作成されたデータベースユーザーを使用するというベストプラクティスに従ってください。」ともRDSマスターユーザーアカウント特権に記載されているので、付与しすぎるのは控えたほうが良さそうです。

初めてSQLServerを触ったであります！
RDSだと権限周りで制限が多く掛かるので、構築する機会が別途あった際には注意したいとことですね。
維持費高いこのサーバを利用することがそんなに多くはなさそうですが。。。＾ｑ＾
最新情報をチェックしよう！

AWSの最新記事