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

Editor to share with you about the mysql new permissions to set the password, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql New permission password setting method: first create a user through the CREATE USER command, then use the GRANT command to authorize, and finally change the user password through the SET PASSWORD command setting.

MySQL creates users and authorizations

one。 Create a user

Command:

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

Description:

Username: the user name you will create

Host: specify the host on which the user can log in. If localhost is available for local users, if you want the user to log in from any remote host, you can use the wildcard%.

Password: the login password of the user. The password can be empty. If it is empty, the user can log in to the server without a password.

Example:

CREATE USER 'dog'@'localhost' IDENTIFIED BY' 123456 creation USER 'pig'@'192.168.1.101_' IDENDIFIED BY' 123456 creation USER 'pig'@'%' IDENTIFIED BY' 123456 creation USER 'pig'@'%' IDENTIFIED BY'; CREATE USER 'pig'@'%'

two。 Authorization:

Command:

GRANT privileges ON databasename.tablename TO 'username'@'host'

Description:

Privileges: the user's permission to operate, such as SELECT,INSERT,UPDATE, etc. If you want to grant the permission, use ALL

Databasename: database name

Tablename: table name, which can be indicated by * if you want to grant the user the appropriate operation permissions on all databases and tables, such as *. *

Example:

GRANT SELECT, INSERT ON test.user TO 'pig'@'%';GRANT ALL ON *. * TO' pig'@'%';GRANT ALL ON maindataplus.* TO 'pig'@'%'

Note:

A user authorized with the above command cannot authorize another user. If you want that user to be authorized, use the following command:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION

three。 Set and change user password

Command:

SET PASSWORD FOR 'username'@'host' = PASSWORD (' newpassword')

If it is used by the current login user:

SET PASSWORD = PASSWORD ("newpassword")

Example:

SET PASSWORD FOR 'pig'@'%' = PASSWORD ("123456")

four。 Revoke user rights

Command:

REVOKE privilege ON databasename.tablename FROM 'username'@'host'

Description:

Privilege, databasename, tablename: same authorization part

Example:

REVOKE SELECT ON *. * FROM 'pig'@'%'

Note:

If you authorize the user 'pig'@'%'' like this (or something similar): GRANT SELECT ON test.user TO 'pig'@'%', does not undo the user's SELECT operation on the user table in the test database by using the REVOKE SELECT ON *. * FROM' pig'@'%'; command. Conversely, if the authorization uses GRANT SELECT ON *. * TO 'pig'@'%';, the REVOKE SELECT ON test.user FROM' pig'@'%'; command does not revoke the user's Select permissions on the user table in the test database.

The specific information can be viewed with the command SHOW GRANTS FOR 'pig'@'%';.

five。 Delete user

Command:

DROP USER 'username'@'host'

Today, after installing the MySQL5.6 version in Centos7, we created a new weicheng account in the table and set the password, but after logging in with the weicheng account, we found that if you log in using the "mysql-uweicheng-p" login will report an error, even if the password is correct, you can not log in. Finally, you can log in directly with the "mysql-uweicheng" without entering the password.

Later, the reason for querying the information is that there should be users available in the database, through the

Select * from mysql.user where user=''

Query if any, and then pass the

Use mysql;delete from user where user =''

Delete the extra blank account, and then, through the

Flush privileges; ­

Reload the permission table once, and finally use the

Service mysqld restart

Restart the mysql service, and the problem is solved, so mark!

Tip:

1. Be sure to remember to restart the mysql service, otherwise it will not take effect. It is because there is no restart of msyql that you have not been able to solve it!

2. The user table of msyql is in the user table in the mysql database, the main fields are host,user,password, etc., as the main table for the management of mysql.

Mysql refresh permission command: FLUSH PRIVILEGES; (usually used after database user information is updated)

Another way is to restart the mysql server.

The above are all the contents of mysql's new permission to set password. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.