SQL
mariadb
自分用メモ

MariaDB コマンドまとめ その3

More than 1 year has passed since last update.

よく使うかは謎だけど、押さえておきたい権限系について簡単に。

軽く動作確認した限りではgrant/revokeなど権限周りのSQLも
やはり<データベース名.テーブル名>とか<'ユーザー名'@'ホスト名'>は必要。
ここら辺の構文ルールは慣れない分だけ面倒に感じる。


権限の付与

GRANT 権限 ON データベース名.テーブル名 TO 'ユーザー名'@'ホスト名' IDENTIFIED BY 'パスワード' WITH GRANT OPTION;

MariaDBはGLOBALやらTABLEやらその権限をどこまで有効にするか、
範囲ごとのレベルが設定されているため、権限付与時にはそれを指定する必要がある。

単純には以下の理解で問題はなし。

 ・グローバル権限:全DBが対象
 ・データベース :指定したDB内の全テーブルが対象
 ・テーブル :指定したDBの指定したテーブルに適用
 ・カラム :指定したテーブル内の指定したカラムに適用

それぞれ構文は以下の様なイメージ

グローバル権限

グローバル権限を割り当てるには「~ON *.*」と指定。

グローバル権限
GRANT 権限 ON *.* TO 'ユーザー名'@'ホスト名';
GRANT SELECT, INSERT ON *.* TO 'you'@'localhost';

データベース権限

データベースレベルの権限を割り当てるには「~ON DB名.*」と指定。

データベース権限
GRANT 権限 ON データベース名.* TO 'ユーザー名'@'ホスト名';
GRANT SELECT, INSERT ON TEST_DB.* TO 'you'@'localhost';

テーブル権限

テーブルレベルの権限を割り当てるには「~ON DB名.テーブル名」と指定。

テーブル権限
GRANT 権限 ON データベース名.テーブル名 TO 'ユーザー名'@'ホスト名';
GRANT SELECT, INSERT ON TEST_DB.TEST_TBL TO 'you'@'localhost';

カラム権限

カラムレベルで付与する権限の後ろでカラムを括弧で囲んで指定する。
複数指定する時は「,」で区切って指定していく。

カラム権限
GRANT 権限 (カラム名) ON データベース名.テーブル名 TO 'ユーザー名'@'ホスト名';
GRANT SELECT (COL1), INSERT (COL1,COL2) ON TEST_DB.TEST_TBL TO 'you'@'localhost';

マニュアルを見るとその他にもストアドルーチン権限やらプロキシユーザー権限やら存在しするが、
おそらく使う機会は多くないと思われるので、その際に調べることにする。

権限に設定できるレベルの早見表

権限 権限内容 GLOBAL DB TABLE COLUMN
ALL [PRIVILEGES] GRANT OPTION以外の全権限 ×
ALTER ALTER TABLEの使用権限 ×
ALTER ROUTINE ストアドルーチンの変更、ドロップ権限 × ×
CREATE CREATE TABLEの使用権限 ×
CREATE ROUTINE ストアドルーチンの作成権限 × ×
CREATE TEMPORARY TABLES 一時テーブルの作成権限 × ×
CREATE USER ユーザの作成、変更、ドロップ権限、ユーザの権限変更の権限 × × ×
CREATE VIEW VIEWの作成権限 ×
DELETE 削除権限 ×
DROP テーブルのDROP権限 ×
EVENT イベントスケジューラのイベント作成権限 × ×
EXECUTE ストアドルーチンの実行権限 × × ×
FILE FILEの読み書き権限(入出力) × × ×
INDEX INDEXの作成、ドロップ権限 ×
INSERT データの登録権限
LOCK TABLES SELECT権限を持つテーブルのLOCK権限 × ×
PROCESS SHOW FULL PROCESSLISTの使用権限 × × ×
REFERENCES 未使用
RELOAD FLUSHの実行権限 × × ×
REPLICATION CLIENT SHOW MASTER STATUS / SHOW SLAVE STATUSの実行権限 × × ×
REPLICATION SLAVE レプリケーションのスレーブとしての動作権限 × × ×
SELECT SELECT権限
SHOW DATABASES SHOW DATABASESの実行権限 × × ×
SHOW VIEW SHOW CREATE VIEWの実行権限 ×
SHUTDOWN mysqladmin shutdownの実行権限 × × ×
SUPER CHANGE MASTER / KILL / PURGE MASTER LOGS / SET GLOBALの実行権限 × × ×
TRIGGER トリガの作成、ドロップの実行権限 ×
UPDATE UPDATEの実行権限 × × ×
USAGE 権限なし × × ×
GRANT OPTION 権限付与実行権限 ×

上記表については以下URLを参考に一部自分自分の見やすい様に変更。

MariaDB はじめてのMariaDB 【第4回 ユーザを作成してみよう】
http://www.sakc.jp/blog/archives/38222

権限の確認

特定ユーザーの権限を確認する

SHOW GRANTS FOR 'ユーザー名'@'ホスト名';
SHOW GRANTS FOR 'you'@'localhost';

権限の剥奪

OracleDBと同じでTOがFROMになるだけっぽい。
権限のレベルの部分が馴染みがない分面倒っちゃ面倒。

REVOKE 権限 ON 権限レベル FROM 'ユーザー名'@'ホスト名';
REVOKE select ON TEST_DB.TEST_TBL FROM 'you'@'localhost';

後、気になったのが権限をrevokeしても残っているUSAGE権限(そもそも権限なのか?)
こいつはいったい何者なのか気になったので調べてみたけど、
マニュアルの説明は「「権限なし」のシノニムです」で意味が分からない。

ユーザーを作成して触ってみた感じではMariaDBには接続できるけど
DBには接続できない。但しSHOWコマンドは一部使えるみたいなので、
OracleDBで言うところのCONNECT権限みたいなもんだと捉えればよさそう。

参考

MySQL 5.6 リファレンスマニュアル
13.7.1.4 GRANT 構文
https://dev.mysql.com/doc/refman/5.6/ja/grant.html

MySQL 5.6 リファレンスマニュアル
13.7.1.6 REVOKE 構文
https://dev.mysql.com/doc/refman/5.6/ja/revoke.html

MariaDB リファレンス
Account Management SQL Commands
https://mariadb.com/kb/en/mariadb/account-management-sql-commands/

MariaDB はじめてのMariaDB 【第4回 ユーザを作成してみよう】
http://www.sakc.jp/blog/archives/38222