It seems, that this is a question that regularly shows up in forums or stackoverflow.
To start, let’s highlight that with 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 explecitely 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 do 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 . TOuser1@%|
+-----------------------------------+
1 row in set (0.00 sec)
It’s now time to grant more privileges to our user… but what are privileges 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 or user to create tables in the database users1 that we created for him and also allow him to
- 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 session we have still open for user1, we can check the granted privileges:
mysql> show grants\G
******************** 1. row ********************
Grants for user1@%: GRANT USAGE ON . TOuser1@%
******************** 2. row ********************
Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE, ALTER
ROUTINE, TRIGGER ONuser1.* TOuser1@%
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> 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 . TOuser1@%
******************** 2. row ********************
Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,
DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE,
CREATE ROUTINE, ALTER ROUTINE, TRIGGER ONmydatabase.* TOuser1@%
******************** 3. row ********************
Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,
DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE,
CREATE ROUTINE, ALTER ROUTINE, TRIGGER ONuser1.* TOuser1@%
******************** 4. row ********************
Grants for user1@%: GRANTdeveloper_user@%TOuser1@%
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 . TOuser2@%
******************** 2. row ********************
Grants for user2@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,
DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE,
CREATE ROUTINE, ALTER ROUTINE, TRIGGER ONmydatabase.* TOuser2@%
******************** 3. row ********************
Grants for user2@%: GRANTdeveloper_user@%TOuser2@%
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 FLUS 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.
via Planet MySQL
How to grant privileges to users in MySQL 8.0