How to grant privileges to users in MySQL 8.0

It seems, that this is a question that regularly shows up in forums or 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, 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 347
 Server version: 8.0.13
 Copyright (c) 2000, 2018, 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>

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()               |
 +---------------------+
 | 2019-01-10 14:36:05 |
 +---------------------+
 1 row in set (0.00 sec)
 mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 +--------------------+
 1 row in set (0.20 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 . TO 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.13, they are currently 46 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                          |                                                       |
 | SET_USER_ID                | Server Admin                          |                                                       |
 | ROLE_ADMIN                 | Server Admin                          |                                                       |
 | RESOURCE_GROUP_USER        | Server Admin                          |                                                       |
 | RESOURCE_GROUP_ADMIN       | Server Admin                          |                                                       |
 | BINLOG_ADMIN               | Server Admin                          |                                                       |
 | SYSTEM_VARIABLES_ADMIN     | Server Admin                          |                                                       |
 | GROUP_REPLICATION_ADMIN    | Server Admin                          |                                                       |
 | CONNECTION_ADMIN           | Server Admin                          |                                                       |
 | REPLICATION_SLAVE_ADMIN    | Server Admin                          |                                                       |
 | ENCRYPTION_KEY_ADMIN       | Server Admin                          |                                                       |
 | BACKUP_ADMIN               | Server Admin                          |                                                       |
 | PERSIST_RO_VARIABLES_ADMIN | Server Admin                          |                                                       |
 +----------------------------+---------------------------------------+-------------------------------------------------------+
 46 rows in set (0.00 sec)

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 
 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 . TO user1@%
******************** 2. row ********************
Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE, ALTER
ROUTINE, TRIGGER ON user1.* TO user1@%
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_users;
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@% |
+----------------------+
1 row in set (0.00 sec)

mysql> show grants\G
******************** 1. row ********************
Grants for user1@%: GRANT USAGE ON . TO user1@%
******************** 2. row ********************
Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, 
CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON mydatabase.* TO user1@%
******************** 3. row ********************
Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, 
CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON user1.* TO user1@%
******************** 4. row ********************
Grants for user1@%: GRANT developer_user@% TO user1@%
4 rows in set (0.00 sec)

Now we would like that every time user1 logs into MySQL, his new role will be 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.13 MySQL Community Server - GPL
 Copyright (c) 2000, 2018, 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 . TO user2@%
******************** 2. row ********************
Grants for user2@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, 
CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON mydatabase.* TO user2@%
******************** 3. row ********************
Grants for user2@%: GRANT developer_user@% TO user2@%
3 rows in set (0.18 sec)

Summary

In summary, now in MySQL 8.0 you cannot create a user from GRANT, 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 and you have more password management options.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

11 Comments

  2. You need to “CREATE ROLE ‘developer_user’;” before you grant the permissions to the role – otherwise you get “ERROR 1410 (42000): You are not allowed to create a user with GRANT”

  6. Hi Lefred,

               Good tutorial, very easy to understand and follow steps is plain.

    You know wich privileges need get user for configurate innodbcluster, for example create a cluster_role and after set to the user it.

    I had this issue because try to connect with root localhost:

    Dba.createCluster: User ‘root’ can only connect from ‘localhost’. (RuntimeError)
    Dba.createCluster: User ‘root’ can only connect from ‘localhost’. (RuntimeError)

  7. Hi Hector,

    It’s possible to let the shell do all this for you. Just connect using root@localhost before configuring the instance. The shell will propose you to extend root to ‘%’ or add a new user that you could use to manage the cluster (this is my preferred option).

     MySQL  JS > dba.configureInstance('root@127.0.0.1')
Please provide the password for 'root@127.0.0.1': 
Save password for 'root@127.0.0.1'? [Y]es/[N]o/Ne[v]er (default No): y
Configuring local MySQL instance listening at port 3306...

This instance reports its own address as mysql2:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.

1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel

Leave a Reply to El meu blogCancel Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue