Detailed tutorial on how to create a user in mysql and grant user permissions

User Management

Create a new user

grammar

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'

Example

mysql> create user 'lisi'@'localhost' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

Username: The username to be created

localhost: specifies the hosts that users can log in from, the IP addresses, network segments, and host names that can be logged in. If it is the local machine, you can use localhost. If you want users to log in from any remote location, you can use the wildcard %.

mysql> select password(123123);
+-------------------------------------------+
| password(123123) |
+-------------------------------------------+
| *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec
//The above code is the encrypted 123123

Password: Because the password enhancement plug-in is enabled in MySQL 5.7, the password cannot be empty and must meet the password complexity requirements and be encrypted before being written to the database.

View Current Users

select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

The created user is saved in the user table of the MySQL database

mysql> use mysql;

mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| lisi | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
//lisi is what we added

Try to log in to lisi

[root@web3 ~]# mysql -ulisi -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.20-log Source distribution
//success

Rename Username

grammar

RENAME USER 'old_user'@'localhost' TO 'new_user'@'host'

old_user is the old user name, new_user is the new user name. After reloading user lisi, change it to zhangsan.

Example

mysql> RENAME USER 'lisi'@'localhost' TO 'zhangsan'@'192.168.200.4';
Query OK, 0 rows affected (0.00 sec)

View the effect

use mysql

mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+---------------+
| user | authentication_string | host |
+---------------+-------------------------------------------+---------------+
| root | | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| zhangsan | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | 192.168.200.4 |
+---------------+-------------------------------------------+---------------+
4 rows in set (0.00 sec)

The renaming is successful, and the host is changed from host to IP address.

Set a password for the user

There are two ways to modify user passwords: one is to modify the current user, the other is to modify other users.

Method 1 Syntax

SET PASSWORD = PASSWORD('password')

Method 2 Syntax

SET PASSWORD='username'@'host'=PASSWORD('password');

Note: You need to use a new password after logging out

Solution to forget root password

If you forget the password of another user, you can use the root user to reset it, but if you forget the root user, you must use a special method.

Method 1: Stop the mysql service process.

Using mysqld_safe with skip-grant-tables to start the database

Its function is to not authorize the table when the user logs in.

mysql_safe --skip-grant-tables&
//At this time, MySQL has been started. Enter MySQL directly without using a password, and use update to change the password. Note: After changing the password, refresh the database flush privileges;
Then try to log in with the new password root

Authorization Control

Permissions are very important, and assigning permission settings is also very important. Assigning permission libraries to clearly divide responsibilities is the most important thing to ensure the security of the system database.

Grant permissions

grammar

GRANT permission list ON library name.table name TO user name@host address IDENTIFIED BY 'password';

Common permissions: all, create, drop, insert, delete, update, select

Example

Add a new user

grant [permission 1,permission 2,permission 3..] on *.* to user@'host' identified by 'pasword';

Assign permissions

grant all on *.* to lisi@'192.168.1.%' identified by '111111';

Note: When the username and host name do not exist in the database, the username and host name will be created, which is equivalent to adding a user data. The login password is also the password specified later. If your original password is 1212, and the password following grant is different and is 123123, it will be equivalent to changing the password.

View permissions

SHOW GRANTS FOR 'username'@'host address';

Revoking permissions

Statements

revoke permission list on database.table from user@'host address';

Revoke permissions all

 revoke all on *.* from lisi@'192.168.1.%';

Summarize

This is the end of this article about the detailed operations of creating users in MySQL and granting user permissions. For more relevant content about creating users in MySQL and granting permissions, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!