Azure SQL Databaseのモニタリング ユーザ(ログイン)を最小権限で設定する方法

Azure SQL Databaseのデータベースの状況をモニタリングするためのアプリケーションを開発したときに、使用するデータベースのログイン(ユーザー)についてのお話です。

モニタリング用なので、データベースからユーザーデータが読めたり、データベースの設定をいじれたりしないように、安心でセキュアで信頼できるように最小の権限で設定したいので、最小にする方法が知りたいですよね?
調べた結果をMSDN Blog「Setting up Logins to monitor Azure SQL Databases」に投稿されていたので紹介します。

手順

手順1:LOGINの作成

Create LOGIN APPLOGIN with password=’abcd123?’

手順2:Msterデータベースにユーザー作成

CREATE USER [APPUSER] FOR LOGIN [APPLOGIN] WITH DEFAULT_SCHEMA=[guest]

手順3:対象データベースにユーザー作成

CREATE USER [APPUSER] FOR LOGIN [APPLOGIN] WITH DEFAULT_SCHEMA=[guest]

手順4:対象データベースのユーザーに権限付与

GRANT VIEW DATABASE STATE TO APPUSER

結論

これで、APPLOGINでログインして上げれば、対象のデータベースの動的管理ビューなどは参照できるけど、ユーザーテーブルは参照できないというミニマム権限のモニタリング用ログイン(ユーザー)が作成できました。

次のようなクエリも流せるよ!

データベースサイズのモニタリング

SELECT SUM(reserved_page_count)*8.0/1024
FROM sys.dm_db_partition_stats;
GO

SELECT SUM(reserved_page_count)*8.0/1024
FROM sys.dm_db_partition_stats;
GO

トップ5クエリの抽出

SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END
– QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO