MySQL8.0 create user and authorization

When will it be needed

• When connecting to an external system, you need to grant access rights to other systems
• In this system, manage data by permissions to prevent root permission from deleting database and running 😂

Mysql version

MySql8.0 +

Specific steps

1. Run the MySql command

Mysql -u#UserName -p#PassWord

#UserName indicates your MySql UserName. #PassWord indicates your MySql PassWord

🐶 The user name and password of the dog are root

mysql -uroot -proot

Copy the code

2. Access the database

If no database is created, run the command first. If a database already exists, skip this step

create database #databaseName;

#databaseName represents the database you are working on

🐶 this dog to create a B2B database, remember to add a semicolon;

create database b2b;

Copy the code

use databaseName;

#databaseName represents the database you are working on

🐶 this dog to operate b2b database, remember to add semicolon;

use b2b;

Copy the code

3. Create a user

create user ‘#userName’@’#host’ identified by ‘#passWord’;

#userName represents the new user account you want to create for the database. #host represents the access permission

• % indicates all host address permissions (remote access)
• Localhost: local permission (not remotely accessible)
• Specify a special Ip access permission such as 10.138.106.102

#passWord represents the new passWord you want to create for this database

🐶 The user to be created is testUser, and the password is Haier… The password strength must be lowercase, lowercase, and alphanumerical. Otherwise, the password strength does not match ⚠️. ERROR 1396 (HY000) is displayed if the user name is the same: Operation CREATE USER failed for ‘testUser’@’%’

create user 'testUser'@'%' identified by 'Haier... 123 ';

Copy the code

4. View users

The mysql database is displayed

user mysql;

View user information

select host, user, authentication_string, plugin from user;

user mysql; 

select host, user, authentication_string, plugin from user;

Copy the code

🐶 If the displayed information contains the newly added user testUser, the user is successfully added. Remember to check to switch back to the operation of the database, the dog needs to operate b2b

use b2b; 

Copy the code

5. User authorization

grant #auth on #databaseName.#table to ‘#userName’@’#host’;

#auth stands for permission, as follows

• All PRIVILEGES All privileges
• Select query permission
• The select, insert, update, delete bowdlerize check permissions
• The select, […]. Increase… Such as permissions

#databaseName indicates the name of the database. #table indicates the specific table

• * represents all tables
• A and B are concrete A and B tables

#userName indicates the userName

#host stands for access, as follows

• % indicates all host address permissions (remote access)
• Localhost: local permission (not remotely accessible)
• Specify a special Ip access permission such as 10.138.106.102

🐶 This dog has the permission to add, delete, and change the difference in the B2B database areA_code table

grant select,insert,update,delete on b2b.area_code to 'testUser'@'%';

Copy the code

6. Refresh

🔥 be sure to refresh the authorization for it to take effect

flush privileges;

7. View user rights

show grants for ‘#userName’@’#host’;

#userName indicates the userName

#host stands for access, as follows

• % indicates all host address permissions (remote access)
• Localhost: local permission (not remotely accessible)
• Specify a special Ip access permission such as 10.138.106.102

🐶 This dog wants to view testUser

show grants for 'testUser'@'%';

Copy the code

Validation 8.

Verify using visual tools such as NavICat

9. Revoke the permission

revoke #auth on #databaseName.#table from ‘#userName’@’#host’;

#auth stands for permission, as follows

• All PRIVILEGES All privileges
• Select query permission
• The select, insert, update, delete bowdlerize check permissions
• The select, […]. Increase… Such as permissions

#databaseName indicates the name of the database. #table indicates the specific table

• * represents all tables
• A and B are concrete A and B tables

#userName indicates the userName

#host stands for access, as follows

• % indicates all host address permissions (remote access)
• Localhost: local permission (not remotely accessible)
• Specify a special Ip access permission such as 10.138.106.102

🐶 alter table areA_code alter table areA_code alter table area_code alter table area_code alter table area_code alter table area_code

revoke select,insert,update,delete on b2b.area_code from 'testUser'@'%';

Copy the code

🐶 this dog again check user permissions

show grants for 'testUser'@'%';

Copy the code

10. Delete the user

drop user ‘#userName’@’#host’;

#userName indicates the userName

#host stands for access, as follows

• % indicates all host address permissions (remote access)
• Localhost: local permission (not remotely accessible)
• Specify a special Ip access permission such as 10.138.106.102

🐶 The user to be deleted is testUser

drop user 'testUser'@'%';

Copy the code

😂 This article is by JavaDog original, please indicate the source.