Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "mysql8 creation, deletion, authorization and cancellation of users". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Log in to mysqlmysql-uroot-p2 and query which users select host,user from mysql.user first.

The red arrow is the primary administrator and the yellow arrow is native to the mysql system. Don't touch it. The blue arrow is the sub-user, and this is what I used to match. Now delete it and let's start over.

3. Delete users: drop user 'username' @ 'hostname'; drop user 'wyy'@'192.168.0.105'

4. Create the user create user 'username' @'to allow that host to link to the 'identified by' password; create user 'wyy'@'192.168.0.105' identified by' wyy18222'; only allows the host to link to 192.168.0.105

Note:

Mysql8.0 uses caching-sha2-password encryption by default, which may not be supported by the old client, but can be changed to mysql_native_password

Create user 'test'@'%' identified with mysql_native_password BY' password

Percent sign%; indicates that any ip address can be linked

Create user 'wyy'@'192.168.0.105' identified by' wyy18222'; is a link that can only be used in 192.168.0.105.

5. Modify password Alter user 'username' @ 'hostname' identified by 'new password'; alter user 'wyy'@'192.168.0.105' identified by' 123 password 6. Authorization

Authorize all permissions to the user

Grant all privileges on *. * to 'user name' @ 'hostname' with grant option;grant all privileges on *. * to 'wyy'@'192.168.0.105' with grant option

Grant: authorization, Grant

Privileges: permissions, privileges

First asterisk: indicates all databases

Second asterisk: indicates all tables

With grant option: indicates that this user can grant permissions to other users, but cannot exceed the permissions of that user. You don't have to add this.

For example, if wyy has only select and update permissions, but no insert and delete permissions, when authorizing another user, it can only be granted select and update permissions, but not insert and delete permissions.

Authorize individual permissions to the user

All privileges can be replaced with select,update,insert,delete,drop,create and other operations.

Grant select,insert,update,delete on *. * to 'username' @ 'hostname'

Assign permissions to the user

Grant specified database permissions to the user

Grant all privileges on database. * to 'wyy'@'192.168.0.105';grant all privileges on xrs. * to 'wyy'@'192.168.0.105'; grants wyy all permissions of the database named xrs

Grant the user the specified table permissions

Grant all privileges on database. Specify the table name to 'wyy'@'192.168.0.105'; to grant permissions to wyy for a table under a database

Note:

Some online directly create and empower:

Grant all privileges *. * to 'user to create' @ 'localhost' identified by' custom password'

I tried it in mysql8 (I haven't tried it below version 8 yet). You have to create a user before you can empower it, not at the same time.

7. Refresh permissions

Flush privileges

Refresh the system permissions related table of MySQL with flush privileges after newly setting users or changing passwords

Otherwise, access will be denied.

Another way is to restart the mysql server for the new settings to take effect. ­

8. Check the user's authorization show grants for 'wyy'@'192.168.0.105'

9. Revoke the user's authorization (cancellation right) revoke all privileges on. * from 'wyy'@'192.168.0.105'

Revoke whatever permissions the user has.

Add: mysql8.0 creates pits encountered by users and authorized users

Create a user:

Create user userName@localhost identified with mysql_native_password by 'password'; (with mysql_native_password without this, Navicat will not be able to log in prompt: 2059-authentication plugin... Error, because Navicat does not support the default encryption of the latest database)

Authorized users:

GRANTALL PRIVILEGESON databaseName.*TO userName@'ip'; (note that this is different from previous databases, there is no need to be followed by IDENTIFIED BY 'password'; or it will prompt ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near' IDENTIFIED BY 'password'' at line 5). This is the end of the content of "mysql8 creation, deletion, authorization and cancellation operation". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.