オリジナルのドキュメントはこちら。 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
0 件のコメント :
コメントを投稿