MySQLのuser作成 MySQL 8.0以降はROLE機能が追加 Grantでユーザ作成は不可に

MySQL 8.0以降でのuser作成についてまとめています。

以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。

MySQL 8でのユーザ作成

MySQL8では、ROLE機能が導入されました。これまで、create userとGrant文を実行してユーザ作成・権限付与を行っていた手順に加えて、ROLEというアクセス権限のコレクションを扱えるようになりました。

create userでユーザ作成

ユーザ作成は、従来通り以下の構文で作成可能です。

mysql> create user user0@localhost IDENTIFIED BY 'user0_password';
Query OK, 0 rows affected (0.04 sec)

MySQL5.7では可能だった「GRANT文でユーザ作成と権限付与を同時に実行」ができなくなりました。以下のとおりSyntaxエラーとなります。

mysql> GRANT ALL PRIVILEGES ON employees.* to user1@localhost IDENTIFIED BY 'user1_password';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'user1_password'' at line 1

create roleでROLE作成

ROLEによる権限付与をおこなうには、まず以下のようにROLEの作成をおこないます。

mysql> create role select_only@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> select User,Host from mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| select_only      | localhost |
+------------------+-----------+

ROLEは、userと同じくmysql.userテーブルに格納されます。上記の例では、インストール直後にデフォルトで作成されるユーザに加えて、select_onlyというROLEが格納されているのが確認できます。

grant文でROLEに権限付与、ROLEをユーザに付与

GRANT文にて、ROLE「select_only」に、employeesデータベースの全テーブルへのselect権限を付与します。

mysql> grant select on employees.* to select_only@localhost;
Query OK, 0 rows affected, 1 warning (0.04 sec)

ROLE「select_only@localhostname」を、user0@localhostに付与します。

mysql> grant select_only@localhost to user0@localhost;
Query OK, 0 rows affected (0.03 sec)

ROLEは、userと全く同じ扱いなので、以下のSQLで「user0@localhostと全く同じ権限をuser1@localhostに付与する」ことも可能です。

mysql> grant user0@localhost to user1@localhost;
Query OK, 0 rows affected (0.03 sec)

付与された権限は、show grantsで確認できます。

mysql> show grants for user0@localhost;
+--------------------------------------------------------+
| Grants for user0@localhost                             |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `user0`@`localhost`              |
| GRANT `select_only`@`localhost` TO `user0`@`localhost` |
+--------------------------------------------------------+

default roleの設定

実は、grantで権限を付与しただけではアクセスが可能になりません。default roleというログイン時に設定されるロールを設定する必要があります。

mysql> set default role select_only@localhost to user0@localhost;
Query OK, 0 rows affected (0.09 sec)

新しく作成されたユーザ「user0」でログインしなおしてみます。

ログインできたことと、current_role()がselect_only@localhostであることを確認できました。

# mysql -u user0 -puser0_password;
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_role();
+---------------------------+
| current_role()            |
+---------------------------+
| `select_only`@`localhost` |
+---------------------------+
1 row in set (0.00 sec)

実際にemployeesデータベースのemployeesテーブルにselect文でアクセスできました。

mysql> select * from employees.employees limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+

delete文を実行しようとすると、権限がない（command denied）エラーが起こります。

mysql> delete from employees.employees;
ERROR 1142 (42000): DELETE command denied to user 'user0'@'localhost' for table 'employees'

ROLEの剥奪は、revokeを使用

user0@localhostから、ROLE「select_only」を剥奪するには、以下のようにします。

mysql> revoke select_only@localhost from user0@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user0@localhost;
+-------------------------------------------+
| Grants for user0@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `user0`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)

show grantsで、ROLEがなくなっていることが確認できました。

まとめ

ポテパンダの一言メモ

• create roleでROLE作成
• grantでROLEを付与、revokeでROLEを剥奪可能
• MySQL 8.0以降では、grant文でユーザ作成と権限付与を自動に実行することはできなくなった