概要
Azure SQL Databaseの認証にAzure Active Directorグループを設定、ロール付与、DML権限付与・剥奪の手順を紹介します。
Azure Active DirectoryのAADグループでAzure SQL Databaseのユーザを作成
以下のコマンドを実行すると、Azure Active DirectoryのAADグループでAzure SQL Databaseのユーザを作成する事が出来ます。ポイントは[FROM EXTERNAL PROVIDER]です。
/** 各データベースで実行 **/
CREATE USER '<AADグループ名>' FROM EXTERNAL PROVIDER;
ロールの付与
作成したユーザーにAzure SQL Databaseのロールを割り当てます。権限は目的に合わせて付与して下さい。
/** 各データベースで実行 **/
ALTER ROLE db_datareader ADD MEMBER '<AADグループ名>';
ALTER ROLE db_datawriter ADD MEMBER '<AADグループ名>';
上記の例では対象のAADグループに「db_datareader」、「db_datawriter」の権限を付与しています。
テーブル毎にDML権限付与
/* SELECT権限付与 */
GRANT SELECT ON OBJECT::<スキーマ名>.<テーブル名> TO '<AADグループ名>';
/* INSERT権限付与 */
GRANT INSERT ON OBJECT::<スキーマ名>.<テーブル名> TO '<AADグループ名>';
/* UPDATE権限付与 */
GRANT UPDATE ON OBJECT::<スキーマ名>.<テーブル名> TO '<AADグループ名>';
/* DELETE権限付与 */
GRANT DELETE ON OBJECT::<スキーマ名>.<テーブル名> TO '<AADグループ名>';
上記の例では対象のテーブルのAADグループに「SELECT」、「UPDATE」、「INSERT」、「DELETE」の実行権限を付与しています。
テーブル毎にDML権限剥奪
/* SELECT権限剥奪 */
REVOKE SELECT ON OBJECT::<スキーマ名>.<テーブル名> FROM '<AADグループ名>';
/* INSERT権限剥奪 */
REVOKE INSERT ON OBJECT::<スキーマ名>.<テーブル名> FROM '<AADグループ名>';
/* UPDATE権限剥奪 */
REVOKE UPDATE ON OBJECT::<スキーマ名>.<テーブル名> FROM '<AADグループ名>';
/* DELETE権限剥奪 */
REVOKE DELETE ON OBJECT::<スキーマ名>.<テーブル名> FROM '<AADグループ名>';
付与した権限の確認方法
/* 確認 */
SELECT dp.major_id, dp.permission_name, dp.state_desc, dp.grantee_principal_id, pr.name _
FROM sys.database_permissions AS dp CROSS JOIN sys.database_principals AS pr _
WHERE pr.principal_id = dp.grantee_principal_id;
SELECT rm.role_principal_id, dp.name AS role_name, dp.type_desc, rm.member_principal_id, _
(SELECT dp.name from sys.database_principals dp WHERE dp.principal_id = rm.member_principal_id) AS member_name _
FROM sys.database_principals dp CROSS JOIN sys.database_role_members rm _
WHERE dp.principal_id = rm.role_principal_id;