MySQL creates users, authorizes users, revokes user permissions, changes user passwords, and deletes users (practical tips)

MySQL creates users and authorizes and revokes user permissions

Operating environment: MySQL 5.0

1. Create a User

Order:

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

Description: username - the username you will create, host - specifies 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 a wildcard. Password - the login password of the user. The password can be empty. If it is empty, the user can log in to the MySQL server without a password.

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

Order:

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

Note: privileges - user's operation privileges, such as SELECT, INSERT, UPDATE, etc. (see the end of this article for a detailed list). If you want to grant all privileges, use ALL.; databasename - database name, tablename- table name. If you want to grant the user corresponding operation privileges for all databases and tables, you can use ..

example:

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

Note: The user authorized by the above command cannot authorize other users. If you want to allow the user to authorize, use the following command:

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

3. Setting and changing user password

Order:

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

If it is the current logged in user, use SET PASSWORD = PASSWORD("newpassword");

example:

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

4. Revoke User Permissions

Order:

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

Note: privilege, databasename, tablename - Same as the authorization part.

Example: REVOKE SELECT ON . FROM 'pig'@'%';

Note: If you grant authorization to user 'pig'@'%' like this (or something similar): GRANT SELECT ON test.user TO 'pig'@'%', then using the REVOKE SELECT ON . FROM 'pig'@'%'; command will not revoke the user's SELECT operation on the user table in the test database. On the contrary, if the authorization is GRANT SELECT ON . TO 'pig'@'%'; then
The REVOKE SELECT ON test.user FROM 'pig'@'%'; command also cannot revoke the user's Select privilege on the user table in the test database.

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

5. Deleting Users

Order:

DROP USER 'username'@'host';

The above is what I introduced to you about MySQL: creating users, authorizing users, revoking user permissions, changing user passwords, and deleting users (practical skills). I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!