SQL Serverのデータを、BIツールで読み込んでグラフを作ったりしています。
その場合、データは読み取りしかしないし、色んな人がグラフを作るので、書き込み権限を持ったユーザーは使いたくなく、共有用に読み取り専用ユーザーを作成しました。
今回、SQL Serverでユーザー関連を触るのは初めてだったので、備忘録として、SQL Serverのユーザーについてのザックリとしたまとめと、SQL Server（Azure SQL Database）に読み取り専用ユーザーを追加する方法を記載しました。
仕組み
基本
SQL Serverには、「ログイン」と「ユーザー」という、２種類のアカウントがあります。
「ログイン」は、外部からSQL Serverに接続する時に使うIDのことで、「SQL Server認証」とも呼ばれています。
SQL Serverは、「ログイン」IDとは別に、データベース毎に「ユーザー」というアカウントがあり、データベースの操作はその「ユーザー」が行っています。
そして、「ログイン」と「ユーザー」を紐付けることにより、外部からSQL Serverにログインした時に、紐付かれた「ユーザー」でデータベースにアクセスするという仕組みになっています。
紐付け
「ログイン」は複数のデータベースの「ユーザー」と紐付けることができるので、１回のログインで複数のデータベースにアクセスすることができます。
ただし、「ログイン」は１つのデータベースに対して、１つの「ユーザー」の、１対１にしか紐付けることができません。
権限
データベースに対して何ができるかの権限は、「ログイン」に対してではなく、「ユーザー」に対して付与します。
「ユーザー」に権限を付与する際、いくつかの権限をセットにした「ロール」というものがあるので、「ロール」を「ユーザー」に割り当てるのが一番手っ取り早いです。
「ロール」はデータベース毎にあり、組み込みの「ロール」もあり、下記に一覧が記載されています。
構成
「ログイン」の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
ユーザー・ロール一覧
ユーザー・ロール一覧は、一覧を見たいデータベースに接続して、下記のクエリで見ることができます。
SELECT * FROM sys.database_principals
ユーザー・ロールだけでなく、他の情報も表示されます。
- カラム「type_desc」が「SQL_USER」がユーザー
- カラム「type_desc」が「DATABASE_ROLE」がロール
のレコードになります。
ロールのユーザー一覧
ユーザーにどんなロールが割り当てられているかを見るには、一覧を見たいデータベースに接続して、下記のクエリで見ることができます。
SELECT * FROM sys.database_role_members
ロール・ユーザーは名前ではなく、「principal_id」で表示されているので、前述のユーザー・ロール一覧の「principal_id」と照らし合わせて名前を補完します。
ログインとユーザーの紐付け一覧
「ユーザー」の「sid」は、紐づく「ログイン」の「sid」を指しているので、それにより、どの「ログイン」と、どの「ユーザー」が紐付くかが分かります。
また、そのことから、「ログイン」と「ユーザー」が１対１対応だということもわかります。
紐付け一覧は、データベースをまたいだクエリになり、Azure SQL Databaseでは手間だったので、ここでは割愛いたしました。
新しいユーザー追加の方法
ここまで書いておいて何なんですが、以上が従来のユーザー追加の方法で、最近は新しい方法が推奨されています。
従来の方法の背景
元々、SQL Serverは、PCにインストールして使われ、１つのSQL Serverの中に、いくつものデータベースを作るような構成で運用されてきました。その場合、SQL Serverに対してユーザーが用意され、そのユーザーが、SQL Server内の複数のデータベースに対してアクセスする形が、理にかなっていました。
新しい方法の背景
しかし、下記記事にも書いたのですが、Azure SQL Databaseでは、データベースはそれぞれ別サーバーで独立して動いていて、従来のSQL Serverのように、データベース間でデータを結合するのが得意ではありません。
なので、従来のユーザーのように、１人のユーザーが複数のデータベースにアクセスできるメリットはあまりなく、むしろ、データベースのユーザーが全て、１つの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を時代に沿ったマネージドサービスにしようと、マイクロソフトも色々試行錯誤してるんですねぇ。
参考記事
- https://blog.engineer-memo.com/2019/04/23/sql-server-%E3%81%AE%E3%83%AD%E3%82%B0%E3%82%A4%E3%83%B3-%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E3%81%AE%E6%A8%A9%E9%99%90%E3%82%92-ssms-%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%AA%E3%81%8C%E3%82%89/
- https://docs.microsoft.com/ja-jp/sql/relational-databases/security/contained-database-users-making-your-database-portable?redirectedfrom=MSDN&view=sql-server-2017
- https://docs.microsoft.com/ja-jp/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-2017
