How to create new permissions and set password in mysql

Mysql method to create new permissions and set passwords: First create a user through the CREATE USER command; then use the GRANT command to authorize; finally, set and change the user password through the SET PASSWORD command.

Recommended: "mysql video tutorial"

MySQL user creation and authorization

1. Create user

Command:

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

Instructions:

username: the user name you will create

host: Specify the host on which the user can log in , if it is a local user, you can use localhost. If you want the user to log in from any remote host, you can use the wildcard %

password: the user's login password. The password can be empty. If it is empty, the user can No password is required to log in to the server

Example:

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

2. Authorization:

Command:

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

Description:

privileges: User Operation permissions, such as SELECT, INSERT, UPDATE, etc. If you want to grant all permissions, use ALL

databasename: database name

tablename: table name. If you want to grant the user access to all databases The corresponding operation permissions of the table can be represented by *, such as *.*

Example:

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

Note:

Users authorized with the above command cannot authorize other users. If you want the user to be authorized, use the following command:

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

3. Set and change user password

Command:

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

If you are the currently logged in user, use:

SET PASSWORD = PASSWORD("newpassword");

Example:

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

4. Revoke user permissions

Command:

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

Instructions:

privilege, databasename, tablename: Same as authorization Part of

Example:

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

Note:

If you authorize user 'pig'@'%' like this (or similar): GRANT SELECT ON test.user TO 'pig'@'%', then using the REVOKE SELECT ON *.* FROM 'pig'@'%'; command cannot undo the user's SELECT operation on the user table in the test database. On the contrary, if the authorization is using GRANT SELECT ON *.* TO 'pig'@'%';, then the REVOKE SELECT ON test.user FROM 'pig'@'%'; command cannot revoke the user's access to the user table in the test database. Select permission.

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

5. Delete user

Command:

DROP USER 'username'@'host';

After installing MySQL5.6 version in Centos7 during development today, I created a new Weicheng account in the table and set a password. However, when I logged in to mysql with the Weicheng account, I found that if I used "mysql -uweicheng -p" to log in, an error would be reported. Even if the password was correct, I could not log in. Finally, I found that I could directly use " mysql -uweicheng" You can log in without entering a password.

Later, the reason for querying the data was: there should be empty users in the database. If so, use

select * from mysql.user where user='';

to check if there are any, and then use

use mysql;
delete from user where user = '';

to delete the redundant blank accounts. Then, use

flush privileges;­

to reload the permission table, and finally use

service mysqld restart

to restart the mysql service. The problem is solved. Mark it now!

Tip:

1. Be sure to remember to restart the mysql service, otherwise it will not take effect. I have not been able to solve the problem because I did not restart msyql!

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

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

There is another way, which is to restart the mysql server

The above is the detailed content of How to create new permissions and set password in mysql. For more information, please follow other related articles on the PHP Chinese website!