For this last technical post of the MySQL Community Advent Calendar 2022, I will explain how to grant privileges to users in MySQL 8.0. This is an important task for anyone who is responsible for managing a MySQL database, as it allows you to control which users have access to which parts of your database. By granting the appropriate privileges to each user, you can ensure that your database remains secure while still allowing users to perform the tasks that they need to.
Moreover, this is a question that comes up regularly in the forums, on Slack or on StackOverflow.
To start, let’s highlight the fact that in MySQL 8.0 it’s not any more possible to create a user directly from the GRANT command:
(ERROR 1410 (42000): You are not allowed to create a user with GRANT).
This means that to grant some privileges to a user, the user must be created first.
Let’s create a user ‘user1‘ with ‘ChangeMe‘ as password that the user will have to change:
mysql> create user 'user1' identified by 'ChangeMe' password expire;
Query OK, 0 rows affected (1.35 sec)Let’s try to connect to MySQL using that new created user:
$ mysql -u user1 -pChangeMe -h localhost
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 19
Server version: 8.0.31
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> We can also use MySQL Shell, which is recommended, the behavior is the same:
Nothing special, we are connected as expected… but didn’t I explicitly expired the password ?
Yes I did, let’s try any statement:
mysql> select now();
ERROR 1820 (HY000): You must reset your password using ALTER USER
statement before executing this statement.We must change the password as expected. Let’s change it to ‘MySQL8isGreat‘:
mysql> set password='MySQL8isGreat';
Query OK, 0 rows affected (0.34 sec)And now we can use MySQL and run any statement we are allowed to do (that we have the privileges for).
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-12-25 10:00:05 |
+---------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 row in set (0.0006 sec)It seems I don’t have access to many databases…
The default privilege is very limited:
mysql> show grants; +-----------------------------------+ | Grants for user1@% | +-----------------------------------+ | GRANT USAGE ON . TOuser1%@user1%| +-----------------------------------+ 1 row in set (0.00 sec)
It’s now time to grant more privileges to our user… but which privileges are available ?
In 8.0.31, they are currently 68 privileges !
To list them all, just run:
mysql> show privileges;
+------------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+------------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create role | Server Admin | To create new roles |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Drop role | Server Admin | To drop roles |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
| XA_RECOVER_ADMIN | Server Admin | |
| TABLE_ENCRYPTION_ADMIN | Server Admin | |
| SYSTEM_VARIABLES_ADMIN | Server Admin | |
| SHOW_ROUTINE | Server Admin | |
| SET_USER_ID | Server Admin | |
| CONNECTION_ADMIN | Server Admin | |
| ENCRYPTION_KEY_ADMIN | Server Admin | |
| INNODB_REDO_LOG_ARCHIVE | Server Admin | |
| CLONE_ADMIN | Server Admin | |
| BINLOG_ENCRYPTION_ADMIN | Server Admin | |
| AUDIT_ABORT_EXEMPT | Server Admin | |
| BACKUP_ADMIN | Server Admin | |
| AUTHENTICATION_POLICY_ADMIN | Server Admin | |
| REPLICATION_APPLIER | Server Admin | |
| GROUP_REPLICATION_STREAM | Server Admin | |
| REPLICATION_SLAVE_ADMIN | Server Admin | |
| FIREWALL_EXEMPT | Server Admin | |
| FLUSH_OPTIMIZER_COSTS | Server Admin | |
| SESSION_VARIABLES_ADMIN | Server Admin | |
| APPLICATION_PASSWORD_ADMIN | Server Admin | |
| SYSTEM_USER | Server Admin | |
| RESOURCE_GROUP_ADMIN | Server Admin | |
| FLUSH_STATUS | Server Admin | |
| FLUSH_TABLES | Server Admin | |
| AUDIT_ADMIN | Server Admin | |
| FLUSH_USER_RESOURCES | Server Admin | |
| GROUP_REPLICATION_ADMIN | Server Admin | |
| INNODB_REDO_LOG_ENABLE | Server Admin | |
| PASSWORDLESS_USER_ADMIN | Server Admin | |
| ROLE_ADMIN | Server Admin | |
| BINLOG_ADMIN | Server Admin | |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin | |
| RESOURCE_GROUP_USER | Server Admin | |
| SENSITIVE_VARIABLES_OBSERVER | Server Admin | |
| SERVICE_CONNECTION_ADMIN | Server Admin | |
+------------------------------+---------------------------------------+-------------------------------------------------------+
68 rows in set (0.0004 sec) In MySQL HeatWave Database Service, there is one extra privilege (as in MySQL Enterprise Edition): TP_CONNECTION_ADMIN
You can see that a new user doesn’t have access to the test database anymore:
mysql> use test;
ERROR 1044 (42000): Access denied for user ‘user1’@’%’ to database ‘test’
Let’s allow our user to create tables in the database users1 that we created for him and also allow him to perform the following actions:
- Alter
- Create
- Delete
- Drop
- Index
- Insert
- Select
- Update
- Trigger
- Alter routine
- Create routine
- Execute
- Create temporary tables
As an admin account, root is the default one after an new on premise installation, in OCI MySQL HeatWave Service, the admin account is the one you specified during the creation of the DB instance:
mysql> grant alter,create,delete,drop,index,insert,select,update,trigger,alter routine,
create routine, execute, create temporary tables on user1.* to 'user1';
Query OK, 0 rows affected (0.23 sec)NO NEED TO RUN FLUSH PRIVILEGES !
And in the open session for user1, we can check the granted privileges:
mysql> show grants\G ******************** 1. row ******************** Grants for user1@%: GRANT USAGE ON . TOuser1%user1user1%@user1%user1user1%******************** 2. row ******************** Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ONuser1%user1user1%.* TOuser1%user1user1%@user1%user1user1%2 rows in set (0.00 sec)
Now let’s imagine we want to have multiple users that will have access to the same database (mydatabase), instead of specifying all the grants for each users, let’s use a common role for all of them. We will call it ‘developer_user‘:
mysql> create ROLE developer_user;
mysql> grant alter,create,delete,drop,index,insert,select,update,trigger,alter
routine,create routine, execute, create temporary tables
on mydatabase.* to 'developer_user';
Query OK, 0 rows affected (0.12 sec)Let’s grant the role to user1:
mysql> grant 'developer_user' to 'user1';
Query OK, 0 rows affected (0.16 sec)Now back again in user1‘s session and let’s verify:
mysql> SELECT CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | NONE | +----------------+ 1 row in set (0.00 sec) mysql> SET ROLE 'developer_user'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT CURRENT_ROLE(); +----------------------+ | CURRENT_ROLE() | +----------------------+ |developer_user%user1%mydatabaseuser1%user1user1%developer_user%user1%@developer_user%user1%mydatabaseuser1%user1user1%developer_user%user1%| +----------------------+ 1 row in set (0.00 sec) mysql> show grants\G ******************** 1. row ******************** Grants for user1@%: GRANT USAGE ON . TOdeveloper_user%user1%mydatabaseuser1%user1user1%developer_user%user1%@developer_user%user1%mydatabaseuser1%user1user1%developer_user%user1%******************** 2. row ******************** Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ONdeveloper_user%user1%mydatabaseuser1%user1user1%developer_user%user1%.* TOdeveloper_user%user1%mydatabaseuser1%user1user1%developer_user%user1%@developer_user%user1%mydatabaseuser1%user1user1%developer_user%user1%******************** 3. row ******************** Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ONdeveloper_user%user1%mydatabaseuser1%user1user1%developer_user%user1%.* TOdeveloper_user%user1%mydatabaseuser1%user1user1%developer_user%user1%@developer_user%user1%mydatabaseuser1%user1user1%developer_user%user1%******************** 4. row ******************** Grants for user1@%: GRANTdeveloper_user%user1%mydatabaseuser1%user1user1%developer_user%user1%@developer_user%user1%mydatabaseuser1%user1user1%developer_user%user1%TOdeveloper_user%user1%mydatabaseuser1%user1user1%developer_user%user1%@developer_user%user1%mydatabaseuser1%user1user1%developer_user%user1%4 rows in set (0.00 sec)
Now we would like that every time user1 logs into MySQL, his new role will be already set:
mysql> SET DEFAULT ROLE 'developer_user' to 'user1';
Query OK, 0 rows affected (0.22 sec)Let’s also create a user2 having the default role:
mysql> create user 'user2' identified by 'DontChangeMe' default role 'developer_user';
Query OK, 0 rows affected (0.18 sec)And we can immediately test it:
$ mysql -u user2 -pDontChangeMe -h localhost 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 352 Server version: 8.0.31 MySQL Community Server - GPL Copyright (c) 2000, 2022, 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> show grants\G ******************** 1. row ******************** Grants for user2@%: GRANT USAGE ON . TOuser2%mydatabaseuser2%developer_user%user2%@user2%mydatabaseuser2%developer_user%user2%******************** 2. row ******************** Grants for user2@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ONuser2%mydatabaseuser2%developer_user%user2%.* TOuser2%mydatabaseuser2%developer_user%user2%@user2%mydatabaseuser2%developer_user%user2%******************** 3. row ******************** Grants for user2@%: GRANTuser2%mydatabaseuser2%developer_user%user2%@user2%mydatabaseuser2%developer_user%user2%TOuser2%mydatabaseuser2%developer_user%user2%@user2%mydatabaseuser2%developer_user%user2%3 rows in set (0.18 sec)
Summary
In summary, now in MySQL 8.0:
- you cannot create a user from GRANT anymore,
- you don’t need to run FLUSH PRIVILEGES command (this is effective for a long time already, please forget about it !),
- you can use ROLES
- you have more password management options.
In conclusion, granting privileges to users in MySQL 8.0 is an essential task for anyone who is responsible for managing a MySQL database. By following the steps outlined in this blog post, you can easily grant the appropriate privileges to each user, ensuring that your database remains secure and that users are able to perform the tasks that they need to. Whether you are a database administrator or just someone who is responsible for managing a MySQL database, this information is essential for you to know when using MySQL 8.0 and MySQL HeatWave Service on OCI.