Microsoft SQL Server 2016 のロール管理（サーバーロールとデータベースロール）について解説します。
ロールとは何か？
- role ← 役、役割、役柄、任務
SQL Server 上では「ロール（role）」は「権限の集合体」という意味になります。
グループに似ていますが、グループは単なる「枠」という概念が強いですが、ロールは「業務に都合の良い様々なレベル権限がまとまったもの」と考えられます。
SQL Server ではデフォルトで 2 種類の「ロール」が実装されています。
- サーバーロール（sysadmin、bulkadmin、dbcreator など）
- データベースロール（db_accessadmin、db_backupoperatorなど）
※デフォルトのロールだけでも十分ですが、自身で新規のロールを作成することもできます。（サーバーロールに関しては SQL Server 2012 以降可能となりました）
※アプリケーションロールもありますが、デフォルトで設定されていないため各ユーザーが作成＆設定する必要があります。
■サーバーロール
■データベースロール
サーバーロール
「サーバーロール」は「セキュリティ」－「サーバーロール」にあります。
デフォルトでは 9 種類のサーバーロールが実装されています。
「sysadmin」のプロパティを確認します。
プロパティはシンプルで、単純に所属している「ロールメンバー」の一覧があるだけです。
ロールメンバーには以下が所属していることが分かります。
- Administrator ← ドメインユーザー（ローカルユーザー）、OS のアカウント
- sa ← ログイン
- test01 ← ドメインユーザー（ローカルユーザー）、OS のアカウント
逆に「test01」が所属しているサーバーロールを確認します。
下図のように「test01」は「sysadmin」に所属していることが分かります。
サーバーロール一覧
- bulkadmin ← データの一括挿入ができます。
- dbcreator ← データベースの作成ができます。
- diskadmin ← ディスク管理ができます。
- processadmin ← プロセス管理ができます。
- public ← システム上のロールです。
- securityadmin ← ログイン管理ができます。
- serveradmin ← サーバーの基本的な管理ができます。
- setupadmin ← リンクサーバー管理ができます。
- sysadmin ← 最高権のサーバーロールです。
bulkadmin
bulkadmin ロールのメンバーは、BULK INSERT ステートメントを実行して、データの一括挿入ができます。
dbcreator
dbcreator ロールのメンバーは、任意のデータベースを「作成」「変更」「削除」「復元」といった操作ができます。
diskadmin
ディスク管理ができます。
processadmin
プロセス管理ができます。
例えば、SQL Server のインスタンス内で実行中のプロセスを終了できます。
public ロール（サーバーレベル）
public は、システム上のロールです。
public ロールは「サーバーレベル」と「データベースレベル」に存在しています。
- サーバーレベルの public ロール ← すべての SQL Server ログインは、public サーバーロールに所属しています。
- データベースレベルの public ロール ← システムデータベースを含め、すべてのデータベースに存在し、すべてのユーザーおよびロールはデフォルトで public ロールに所属しています。
securityadmin
ログインとログインのプロパティの管理ができます。
サーバーレベルの「GRANT」「DENY」「REVOKE」できます。
データベースへの権限があればデータベースのレベルも「GRANT」「DENY」「REVOKE」できます。
serveradmin
サーバーの基本的な管理ができます。
例えば、サーバー全体のオプションを変更したり、サーバーをシャットダウンしたりできます。
setupadmin
setupadmin ロールのメンバーは、Transact-SQL ステート面を使用して、リンクサーバーを追加したり削除したりといった管理ができます。
sysadmin
sysadmin は、最高権のサーバーロールです。
サーバーに対するすべての操作を実行できます。
例えば、sa アカウントのパスワードも変更できる権限を持っています。
セキュリティ上、複数のアカウントを sysadmin ロールに所属させないようにします。
データベースロール
データベースロールは、各データベースごとに格納されています。
下図のようにデフォルトでは 10 種類の「データベースロール」が実装されています。
この 10 種類で不都合は全くありませんが、独自の新規「データベースロール」を作成することもできます。
- db_accessadmin ← データベースユーザーの管理ができます。
- db_backupoperator ← バックアップ管理ができます。
- db_datareader ← テーブルのデータの参照ができます。
- db_datawriter ← テーブルのデータの変更ができます。
- db_ddladmin ← DDL を実行できます。
- db_denydatareader ← テーブルのデータの参照を禁止できます。
- db_denydatawriter ← テーブルのデータの変更を禁止できます。
- db_owner ← データベース内での最高権限を持っています。
- db_securityadmin ← ロールの管理ができます。
- public ← システム上のロールです。
※面白いことに、参照権限・変更権限だけではなく、参照禁止する権限・変更を禁止する権限のロールが存在することです。
db_accessadmin
db_accessadmin データベースロールのメンバーは、Windows ログイン（OS アカウント）、Windows グループ、SQL Server ログインのデータベースへのアクセスを追加・削除ができます。
db_backupoperator
db_backupoperator ロールのメンバーは、データベースのバックアップ管理ができます。
db_datareader
db_datareader ロールのメンバーは、テーブルのデータの参照ができます。
db_datawriter
db_datawriter ロールのメンバーは、テーブルのデータの追加、変更、削除ができます。
db_ddladmin
db_ddladmin ロールのメンバーは、すべての DDL（データ定義言語）を実行できます。
DDL は、Data Definition Language の略です。
具体的には、以下のコマンド（命令）などを発行することができます。
- CREATE
- DROP
- ALTER
- TRUNCATE
db_denydatareader
db_denydatareader ロールのメンバーは、対象データベース内のユーザーテーブルのデータを参照することができません。
db_denydatawriter
db_denydatawriter ロールのメンバーは、対象データベース内のユーザーテーブルのデータの追加・変更・削除ができません。
db_owner
db_owner ロールのメンバーは、対象データベース内での最高権限を持ち、すべての校正作業とメンテナンス作業を実施できます。
また、データベースを削除することもできます。
db_securityadmin
db_securityadmin ロールのメンバーは、ロールの変更や権限の管理ができます。
public
システム上のロールです。
参考図書・学習書
秀和システム社より出版の「SQL Server 2016データベース構築・管理ガイド Enterprise対応 Kindle版」です。
久しぶりに SQL Server の設計・構築・管理をやることになったので、ほぼゼロの状態から学習しています。
内容は非常に充実しており、インフラ側で必要な内容（SQL Server 2016のインストールから、管理ツールの使い方、データベース作成、テーブル作成、バックアップ＆リストア手順など）は一通り揃っています。
SQL Server 2016データベース構築・管理ガイド Enterprise対応
コメント