MySQL creates users, authorizes users, revokes user permissions, changes user passwords, and deletes users (Practical Tips). mysql user permissions

MySQL creates users, authorizes users, revokes user permissions, changes user passwords, and deletes users (Practical Tips). mysql user permissions

Create a user in MySQL and grant and revoke User Permissions

Running Environment: MySQL5.0

1. Create a user

Command:

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

Description: username-the username you will create, host-specifies the host on which the user can log on. If a local user can use localhost, if you want to allow the user to log on from any remote host, you can use the wildcard %. password-the user's login password. The password can be blank. If it is blank, the user can log on to the mysql server without the 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'@'%';

Ii. Authorization

Command:

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

Pri: privileges-operation permissions of users, such as SELECT, INSERT, and UPDATE (for detailed list, see the end of this Article ). use ALL .; databasename-Database Name, tablename-table name. If you want to grant the user the corresponding operation permissions on all databases and tables, it can be expressed ..

Example:

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

Note: The user authorized with the preceding command cannot authorize other users. to authorize the user, run the following command:

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

3. Set and change user passwords

Command:

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

If the current login user uses set password = PASSWORD ("newpassword ");

Example:

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

Iv. Revoke User Permissions

Command:

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

Note: privilege, databasename, tablename-same as the authorization section.

Example: revoke select on. FROM 'pig' @ '% ';

Note: If you authorize the user 'pig' @ '%' like this (or similar): grant select on test. user TO 'pig' @ '%', use revoke select on. FROM 'pig' @ '%'; the command does not cancel the SELECT Operation on the user table in the test database. conversely, grant select on. TO 'pig' @ '%'; then
The revoke select on test. user FROM 'pig' @ '%' command cannot REVOKE the user's Select permission ON the user table in the test database.

FOR more information, run the show grants for 'pig' @ '%' command.

5. delete a user

Command:

DROP USER 'username'@'host';

The above section describes how to create, authorize, revoke, change, and delete a user in MySQL, if you have any questions, please leave a message and the editor will reply to you in time. Thank you very much for your support for the help House website!