MariaDBで実装されるという噂だったROLE、まだだと思っていたらもうあったんですね。ということでさっくり試してみる。10.0.5から実装されたらしいけど、試したバージョンは10.0.11。
オリジナルのドキュメントはこちら。
https://mariadb.com/kb/en/roles-overview/
まずはROLEを作成してみる。mysqlスキーマに対してSELECTのみの権限を持つsys_selectロールを作成して、yoku0825ユーザーに割り当てる。
MariaDB [mysql]> CREATE ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> GRANT SELECT ON mysql.* TO sys_select;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> GRANT sys_select TO yoku0825;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> GRANT USAGE ON *.* TO yoku0825;
Query OK, 0 rows affected (0.00 sec)
…あれ、GRANT .. ON .. TO ..って、これ、sys_select@%ユーザーが出来ちゃうんじゃね？；
MariaDB [mysql]> SELECT user, host, password FROM user ORDER BY 1, 2;
+------------+-----------------+----------+
| user | host | password |
+------------+-----------------+----------+
| root | 127.0.0.1 | |
| root | ::1 | |
| root | ip-172-31-0-135 | |
| root | localhost | |
| sys_select | | |
| yoku0825 | % | |
+------------+-----------------+----------+
6 rows in set (0.00 sec)
出来てるっぽいけど、hostが空欄だ。
# bin/mysql -usys_select
ERROR 1045 (28000): Access denied for user 'sys_select'@'localhost' (using password: NO)
ログインはできない。
MariaDB [mysql]> SELECT user, host, password, is_role FROM user ORDER BY 1, 2;
+------------+-----------------+----------+---------+
| user | host | password | is_role |
+------------+-----------------+----------+---------+
| root | 127.0.0.1 | | N |
| root | ::1 | | N |
| root | ip-172-31-0-135 | | N |
| root | localhost | | N |
| sys_select | | | Y |
| yoku0825 | % | | N |
+------------+-----------------+----------+---------+
6 rows in set (0.01 sec)
よく見てみると、mysql.userテーブルにis_roleというカラムが追加されてて、これで制御されてるっぽい。
MariaDB [mysql]> SELECT * FROM roles_mapping;
+-----------+----------+------------+--------------+
| Host | User | Role | Admin_option |
+-----------+----------+------------+--------------+
| % | yoku0825 | sys_select | N |
| localhost | root | sys_select | Y |
+-----------+----------+------------+--------------+
2 rows in set (0.00 sec)
割り当てたロールはmysql.roles_mappingテーブルに格納されている。
じゃあ早速yoku0825ユーザーでログインしなおして、mysqlスキーマにアクセスを試す。
MariaDB [(none)]> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| yoku0825@% |
+----------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT user, host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'yoku0825'@'localhost' for table 'user'
Σ(ﾟдﾟlll) ダメじゃん。
と思ったら、ROLEはログインした後明示的に変更しないといけないぽい。
MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NULL |
+----------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| sys_select |
+----------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT user, host FROM mysql.user;
+------------+-----------------+
| user | host |
+------------+-----------------+
| sys_select | |
| yoku0825 | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | ip-172-31-0-135 |
| root | localhost |
+------------+-----------------+
6 rows in set (0.00 sec)
sudoっぽい感じ。でもどうやって戻るんだかよくわからない。
MariaDB [(none)]> CREATE ROLE sys_insert;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT INSERT ON mysql.* TO sys_insert;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT sys_insert TO yoku0825;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NULL |
+----------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| sys_select |
+----------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET ROLE sys_insert;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| sys_insert |
+----------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT user, host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'yoku0825'@'localhost' for table 'user'
SET ROLEで上書きすると、それまでのROLEの権限は使えなくなる。
MariaDB [(none)]> GRANT ALL ON d1.* TO yoku0825;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| yoku0825@% |
+----------------+
1 row in set (0.00 sec)
MariaDB [(none)]> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> DROP DATABASE d1;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SET ROLE sys_select;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> DROP DATABASE d1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GRANTS;
+--------------------------------------------------+
| Grants for yoku0825@% |
+--------------------------------------------------+
| GRANT sys_select TO 'yoku0825'@'%' |
| GRANT sys_insert TO 'yoku0825'@'%' |
| GRANT USAGE ON *.* TO 'yoku0825'@'%' |
| GRANT ALL PRIVILEGES ON `d1`.* TO 'yoku0825'@'%' |
| GRANT USAGE ON *.* TO 'sys_select' |
| GRANT SELECT ON `mysql`.* TO 'sys_select' |
+--------------------------------------------------+
6 rows in set (0.00 sec)
SET ROLESしても、予め与えられていた権限が上乗せされるわけではなくて、和になる。当然か。
SHOW GRANTSで見るとわかりやすげ。
もっとグループパーミッション的なものを想像していたけど、"sudoっぽい"ということで、取り敢えずそんなかんじ。
【2014/05/19 13:55】
DEFAULT ROLEが実装されればもう少し変わるんだろうけど、これは10.1での実装予定となっております。
https://mariadb.atlassian.net/browse/MDEV-5210