MySQLでの権限管理
はじめに
MySQLのユーザーと権限管理について学習したことを簡単にまとめます。
検証に使用した環境
DB：MySQL8.0
MySQLでの権限設定について
MySQLでユーザーの権限を管理するには、grant文やrevoke文を使います。
また、MySQL8.0からはロールという機能が追加されており、複数の権限をまとめたロールをユーザーに割り当てることができます。
ユーザー
MySQLではユーザー名と接続元のホストも指定し、ユーザーIDを特定します。
これは、ユーザー名が同じでもホストが別だと、同じユーザーとは限らないためです。
ユーザーには、権限とロールを設定できます。
また、rootユーザーは特権ユーザーと呼ばれ、あらゆる操作が出来てしまいます。
そのため、実運用では使用してはいけません。
ユーザーの作成
mysql> create user 'ユーザー名'@'接続元' identified by 'パスワード';
ユーザーに権限付与
mysql> grant 操作内容 on 操作対象 to 'ユーザー名'@'接続元';
複数の操作内容は、「select,insert,update」のようにカンマ区切りで指定できます。
ユーザーに権限剥奪
mysql> revoke 操作内容 on 操作対象 to 'ユーザー名'@'接続元';
複数の操作内容は、「select,insert,update」のようにカンマ区切りで指定できます。
権限を一覧
全てのユーザーの権限を一覧
mysql> show grants;
指定したユーザーの権限を表示
mysql> show grants for 'ユーザー名'@'ホスト名';
指定したユーザーに割り当てられているロールの権限も表示
mysql> show grants for 'ユーザー名'@'ホスト名' using 'ロール名';
権限
権限には操作対象のレベルを指定できます。
指定出来るレベルの種類は以下の種類があります。
権限の種類
管理権限(global)
すべてのデータベースに権限が適用されます。
グローバル権限を割り当てるには、各SQL文の操作対象に「*.*」を指定します。
データベース権限(database)
指定したデータベース内のすべてのオブジェクトに適用されます。
データベースレベルの権限を割り当てるには、各SQL文の操作対象に「データベース名.*」を指定します。
テーブル権限(table)
指定したテーブル内のすべてのカラムに適用されます。
テーブルレベルの権限を割り当てるには、各SQL文の操作対象に「データベース名.テーブル名」を指定します。
【注意】
データベース名.テーブル名ではなく、テーブル名を指定した場合
デフォルトデータベース内のテーブル名と認識されます。
もし、デフォルトデータベースが存在しなければ、エラーとなります。
カラム権限(column)
指定したテーブル内の単一カラムに適用されます。
カラムレベルで付与される各権限のあとに、括弧で囲まれた1つまたは複数のカラムを指定する必要があります。
例) カラム1の参照と、カラム1,カラム2への登録を許可する場合
mysql> grant select (カラム1), insert (カラム1, カラム2) on データベース名.テーブル名 to 'ユーザー名'@'接続元';
その他
上記の他、ストアドルーチン権限やプロキシユーザー権限があります。
詳細はこちら
権限に指定出来る操作
権限に指定出来る操作は様々あり、操作内容の詳細なパターンはこちらになります。
よく使用される操作内容
よく使用される操作内容は以下のものがあります。
ALL
grant optionとproxy以外の全ての操作
ALTER
alter tableの実行
CREATE
データベースやテーブルの作成操作
CREATE USER
以下の操作
create user
drop user
rename user
revoke all
privileges
DELETE
delete文の実行
DROP
データベースやテーブルなどの削除操作
INDEX
インデックスの作成・削除操作
INSERT
insert文の実行
LOCK TABLES
lock tables文の実行
PROSESS
show processlistの実行
SELECT
select文の実行
SHOW DATABASES
show databasesの実行
SUPER
SUPER権限をよる操作
UPDATE
update文の実行
ロール
MySQL8.0より、導入された機能です。
ロールには複数の権限を設定でき、ロールをユーザーに割り当てます。
ロールにはアクティブ、非アクティブがあり、アクティブにするには明示的に設定する必要があります。
ロールを作成
mysql> create role 'ロール名';
ロールの削除
mysql> drop role 'ロール名';
ロールに権限付与
mysql> grant 操作内容 on 操作対象 to 'ロール名';
ユーザーからロールの割り当てを解除
mysql> revoke 'ロール名' from 'ユーザー名';
注意
割り当てを解除できないロール
mandatory_rolesシステム変数に指定しているロールは解除できません。
mandatory_rolesシステム変数とは
サーバーが必須として扱う必要があるロールを指定するシステム変数。
このシステム変数に指定されたロールは全ユーザーに自動的に付与されますが、
実際にはユーザーの設定を変更していないため、付与されたロールはmysql.role_edgesシステムテーブルに表示されません。
ロールをユーザーに割り当てる
mysql> grant 'ロール名' to 'ユーザー名';
ロールをデフォルトでアクティブにする
サーバーがユーザーを認証するたびにアクティブにするロールを指定します。
カンマ区切りで複数のユーザーを指定できます。
mysql> set default role 'ロール名' to
'ユーザー名1'@'ホスト名1',
'ユーザー名2'@'ホスト名2',
'ユーザー名3'@'ホスト名3';
※全てのロールを対象にしたい場合、ロール名をallにします
現在のセッションでアクティブなロールを変更
mysql> set role 'ロール名';
アクティブなロールを表示する
現在のセッション内でアクティブなロール
mysql> select current_role();
参考文献