🤖

Azure SQL DBの認証について-【SQL認証】

2024/02/11に公開

やること

Azure SQL DBのSQL認証についてまとめます

前提

下記で構築したSQLDB及びテーブルを使用する
https://zenn.dev/headwaters/articles/eac3eeddced613

SSMSは、ダウンロード済みであること
https://learn.microsoft.com/ja-jp/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16

Azure SQLDBの認証2選

1.SQL認証
https://zenn.dev/headwaters/articles/81114a00dfc192
2.Microsoft Entra認証
の2つです

SQL認証

ユーザー名とパスワードで認証を行います
Azure SQL Serverを構築時に、管理者を作成します
あくまでも管理者なので、それ以外のユーザーはSQLDBに接続して作成する必要があります

ログインユーザー作成手順

  1. SSMSを立ち上げる
  2. 下記のように入力し、「接続(C)」をクリック
  3. 「新しいクエリ(N)」をクリック
  4. masterを選択し、下記のクエリを実行する
CREATE LOGIN ReadUser WITH PASSWORD='Hogehoge2024';	

  1. セキュリティ>ログインに4で作成したユーザーがいることを確認

データベースユーザー作成手順

  1. 下記のクエリを実行し、masterにユーザーを作成する
CREATE USER ReadUser FROM LOGIN ReadUser;

  1. [データベース] > [システムデータベース] > [master] > [セキュリティ] > [ユーザー]にReadUserが追加されていることを確認
  2. 「master」から「SQLDatabase名」に変更する
  3. 下記のクエリを実行し、SQL DataBaseにユーザーを作成する
CREATE USER ReadUser FROM LOGIN ReadUser;
  1. [データベース] > [SQL Database名] > [セキュリティ] > [ユーザー]にReadUserが追加されていることを確認
  2. Azure portalから5で作成したユーザー名とパスワードでログインする
  3. ログインできることを確認

データベースユーザーにロールを付与する手順

  1. 下記のクエリを実行し、権限を付与する
EXEC sp_addrolemember N'db_datareader', N'ReadUser';
  1. 下記のクエリを実行し、ReadUserにロールが付与されたかを確認する
SELECT 
    sp_role.name AS role_name, 
    sp_member.name AS member_name 
FROM 
    sys.database_role_members rm 
    LEFT JOIN 
        sys.database_principals sp_role 
    ON 
        rm.role_principal_id = sp_role.principal_id 
    LEFT JOIN 
        sys.database_principals sp_member 
    ON 
        rm.member_principal_id = sp_member.principal_id

  1. 下記のクエリを実行する
CREATE TABLE test(
  id INT PRIMARY KEY,
  sex VARCHAR(10) ,
  age INT,
  comment VARCHAR(255)
);
  1. Permission Errorになることを確認
  2. 下記のクエリを実行する
SELECT * FROM Product_review;
  1. テーブルを確認できる

ロールの名前と説明

ロールの名前 説明
db_owner db_owner 固定データベース ロールのメンバーは、データベースでのすべての構成とメンテナンス作業を実行でき、SQL Server でデータベースを drop することもできます。 (SQL Database と Azure Synapse では、一部のメンテナンス作業にサーバー レベルのアクセス許可が必要であり、db_owners は実行できません。)
db_securityadmin db_securityadmin 固定データベース ロールのメンバーは、カスタム ロールのロール メンバーシップのみの変更、および権限の管理を実行できます。 このロールのメンバーは、特権を昇格させる可能性があり、そのアクションを監視する必要があります。
db_accessadmin db_accessadmin 固定データベース ロールのメンバーは、Windows ログイン、Windows グループ、および SQL Server ログインのデータベースに対するアクセスを追加または削除できます。
db_backupoperator db_backupoperator 固定データベース ロールのメンバーは、データベースをバックアップできます。
db_ddladmin db_ddladmin 固定データベース ロールのメンバーは、すべての DDL (データ定義言語) コマンドをデータベースで実行できます。 このロールのメンバーは、高い特権で実行される可能性があるコードを操作することで、特権を昇格させ、そのアクションを監視する必要があります。
db_datawriter db_datawriter 固定データベース ロールのメンバーは、すべてのユーザー テーブルのデータを追加、削除、または変更できます。 ほとんどのユース ケースでは、このロールが db_datareader メンバーシップと組み合わされ、変更されるデータの読み取りが可能になります。
db_datareader db_datareader 固定データベース ロールのメンバーは、すべてのユーザー テーブルとビューからすべてのデータを読み取ることができます。 ユーザーオブジェクトは、sys と INFORMATION_SCHEMA を除くどのスキーマにも存在できます。
db_denydatawriter db_denydatawriter 固定データベース ロールのメンバーは、データベース内のユーザー テーブルのデータを追加、変更、または削除することはできません。
db_denydatareader db_denydatareader 固定データベース ロールのメンバーは、データベース内のユーザー テーブルとビューのデータを読み取ることはできません。

https://learn.microsoft.com/ja-jp/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16

ヘッドウォータース

Discussion