新しいことにはウェルカム

技術 | 電子工作 | ガジェット | ゲーム のメモ書き

SQL Server(Azure SQL Database)に読み取り専用ユーザーを追加する方法

SQL Serverのデータを、BIツールで読み込んでグラフを作ったりしています。

その場合、データは読み取りしかしないし、色んな人がグラフを作るので、書き込み権限を持ったユーザーは使いたくなく、共有用に読み取り専用ユーザーを作成しました。

今回、SQL Serverでユーザー関連を触るのは初めてだったので、備忘録として、SQL Serverのユーザーについてのザックリとしたまとめと、SQL Server(Azure SQL Database)に読み取り専用ユーザーを追加する方法を記載しました。

仕組み

基本

SQL Serverには、「ログイン」と「ユーザー」という、2種類のアカウントがあります。

「ログイン」は、外部からSQL Serverに接続する時に使うIDのことで、「SQL Server認証」とも呼ばれています。

SQL Server(Azure SQL Database)に読み取り専用ユーザーを追加する方法
SQL Serverにログインする時に入力するIDが「ログイン」です

SQL Serverは、「ログイン」IDとは別に、データベース毎に「ユーザー」というアカウントがあり、データベースの操作はその「ユーザー」が行っています。

そして、「ログイン」と「ユーザー」を紐付けることにより、外部からSQL Serverにログインした時に、紐付かれた「ユーザー」でデータベースにアクセスするという仕組みになっています。

紐付け

「ログイン」は複数のデータベースの「ユーザー」と紐付けることができるので、1回のログインで複数のデータベースにアクセスすることができます。

ただし、「ログイン」は1つのデータベースに対して、1つの「ユーザー」の、1対1にしか紐付けることができません。

権限

データベースに対して何ができるかの権限は、「ログイン」に対してではなく、「ユーザー」に対して付与します。

「ユーザー」に権限を付与する際、いくつかの権限をセットにした「ロール」というものがあるので、「ロール」を「ユーザー」に割り当てるのが一番手っ取り早いです。

「ロール」はデータベース毎にあり、組み込みの「ロール」もあり、下記に一覧が記載されています。

構成

「ログイン」のID・パスワード情報は、SQL Serverのmasterデータベースに登録されています。

以上を踏まえて、新しく読み取り専用ユーザーを追加する手順は下記になります。

手順

ログイン作成

管理者アカウントで、masterデータベースに接続してクエリを実行します。

Azure SQL Databaseの場合、Azureポータルからmasterデータベースに接続してクエリを実行する方法がないため、 SQL Server Management Studioや、VSCodeのSQL Server 拡張機能などのクライアントから、masterデータベースに接続してクエリを実行します。

CREATE LOGIN <login_name> WITH PASSWORD='<password>'

ログインの削除は下記で行えます。

DROP LOGIN <login_name>

データベースのユーザー作成

次に、アクセスしたいデータベースに接続しなおして、そのデータベースに「ユーザー」を追加し、「ログイン」と紐付けます。

CREATE USER <user_name> FROM LOGIN <login_name>

ログイン名とユーザー名は異なって構いません。

ユーザーの削除は下記で行えます。

DROP USER <user_name>

ユーザーに権限(ロール)付与

作成した「ユーザー」に、権限(ロール)を割り当てます。

今回は読み取り専用ユーザーにしたかったので、「db_datareader」(データ読み取り)ロールを割り当てました。

ALTER ROLE <role_name> ADD MEMBER <user_name>

割り当てたロールの削除は下記で行えます。

ALTER ROLE <role_name> DROP MEMBER <user_name>

組み込みのロールは下記に記載されています。

以上で、読み取り専用の「ユーザー」(SQL Server認証)を作ることができます。

複数のデータベースにアクセスできるようにする

アクセスしたいデータベース毎に、「ユーザー作成&ログイン紐付け」と「ユーザーにロール割当」を行うことにより、複数のデータベースにアクセスできるようになります。

パスワードを変更する

パスワード変更は、管理者アカウントでmasterデータベースに接続して、下記で行います。

ALTER LOGIN <login_name> WITH PASSWORD='<password>'

確認(一覧表示)

確認用に、設定したデータの表示方法を記載しておきます。

ログイン一覧

ログイン一覧は、masterデータベースに接続して、下記のクエリで見ることができます。

SELECT * FROM sys.sql_logins

SQL Server(Azure SQL Database)に読み取り専用ユーザーを追加する方法

ユーザー・ロール一覧

ユーザー・ロール一覧は、一覧を見たいデータベースに接続して、下記のクエリで見ることができます。

SELECT * FROM sys.database_principals

SQL Server(Azure SQL Database)に読み取り専用ユーザーを追加する方法

ユーザー・ロールだけでなく、他の情報も表示されます。

  • カラム「type_desc」が「SQL_USER」がユーザー
  • カラム「type_desc」が「DATABASE_ROLE」がロール

のレコードになります。

ロールのユーザー一覧

ユーザーにどんなロールが割り当てられているかを見るには、一覧を見たいデータベースに接続して、下記のクエリで見ることができます。

SELECT * FROM sys.database_role_members

SQL Server(Azure SQL Database)に読み取り専用ユーザーを追加する方法

ロール・ユーザーは名前ではなく、「principal_id」で表示されているので、前述のユーザー・ロール一覧の「principal_id」と照らし合わせて名前を補完します。

ログインとユーザーの紐付け一覧

「ユーザー」の「sid」は、紐づく「ログイン」の「sid」を指しているので、それにより、どの「ログイン」と、どの「ユーザー」が紐付くかが分かります。

また、そのことから、「ログイン」と「ユーザー」が1対1対応だということもわかります。

紐付け一覧は、データベースをまたいだクエリになり、Azure SQL Databaseでは手間だったので、ここでは割愛いたしました。

新しいユーザー追加の方法

ここまで書いておいて何なんですが、以上が従来のユーザー追加の方法で、最近は新しい方法が推奨されています。

従来の方法の背景

元々、SQL Serverは、PCにインストールして使われ、1つのSQL Serverの中に、いくつものデータベースを作るような構成で運用されてきました。その場合、SQL Serverに対してユーザーが用意され、そのユーザーが、SQL Server内の複数のデータベースに対してアクセスする形が、理にかなっていました。

新しい方法の背景

しかし、下記記事にも書いたのですが、Azure SQL Databaseでは、データベースはそれぞれ別サーバーで独立して動いていて、従来のSQL Serverのように、データベース間でデータを結合するのが得意ではありません。

www.kwbtblog.com

なので、従来のユーザーのように、1人のユーザーが複数のデータベースにアクセスできるメリットはあまりなく、むしろ、データベースのユーザーが全て、1つのmasterデータベースに依存するリスクの方が大きいです。

そういったAzure SQL DatabaseとSQL Serverの構成の違いから、Azure SQL Databaseでは、従来のSQL Serverのユーザー追加方法ではなく、データベース毎にユーザーを作成し、そのユーザーで直接データベースにログインする方法が推奨されています。

手順(新しい方法)

新しい方法といっても、特に難しくはありません。

まず、「ログイン」を作る必要がなくなります。

そして、「ユーザー」を作る時の「ログイン」との紐付けも不要になります。

代わりに、「ユーザー」が従来の「ログイン」も兼ねるので、「ユーザー」作成の時に、パスワードの設定が必要になります。

以上を踏まえ、新しい方法による「ユーザー兼ログイン」の作成は下記になります。

CREATE USER <user_name> WITH PASSWORD='<password>'

ユーザー削除や、ロールの割り当て方法は従来と同じです。

パスワードの変更は下記になります。

ALTER USER <user_name> WITH PASSWORD='<password>'

Azure SQL Databaseへのログインは、作成した「ユーザー」と「パスワード」で行います。

ただし、「ユーザー」はデータベースで定義されているため、ログインする時は接続先データベースの指定が必須になります。

感想など

ロールはユーザーに対して、複数割り当てることができるので、読み書き可のユーザーを追加したいなら、「db_datareader」「db_datawriter」を付与するとできます。

SQL Serverを時代に沿ったマネージドサービスにしようと、マイクロソフトも色々試行錯誤してるんですねぇ。

参考記事