User authorization/deletion authorization methods in MySQL

This article introduces how to use GRANT in mysql to GRANT permissions to users. It also introduces commands for revoking permissions and Deleting Users. I hope some methods can help you.

User authorization method

You can add new users by issuing the GRANT statement:

　

The Code is as follows:	Copy code
Shell> mysql -- user = root mysql Mysql> grant all privileges on *. * TO monty @ localhost Identified by 'something' with grant option; Mysql> grant all privileges on *. * TO monty @ "%" Identified by 'something' with grant option; Mysql> grant reload, process on *. * TO admin @ localhost; Mysql> grant usage on *. * TO dummy @ localhost;

These GRANT statements install three new users

Authorization:

Command:

The Code is as follows:	Copy code
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 represented by *, as shown in *. *.

Example:

The Code is as follows:	Copy code
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:

The Code is as follows:	Copy code
GRANT privileges ON databasename. tablename TO 'username' @ 'host' with grant option;

3. Set and change user passwords

Command:

The Code is as follows:	Copy code
Set password for 'username' @ 'host' = PASSWORD ('newpassword ');

For the current Login User

The Code is as follows:	Copy code
Set password = PASSWORD ("newpassword ");

Example:

The Code is as follows:	Copy code
Set password for 'pig' @ '%' = PASSWORD ("123456 ");

Revoke permissions and delete users

To cancel a user's permissions, use the REVOKE statement. The syntax of REVOKE is very similar TO the GRANT statement, except that it is replaced by from without the indetifed by and with grant option clauses:

 

The Code is as follows:	Copy code
REVOKE privileges (columns) ON what FROM user

The user part must match the user part of the user you want to revoke permission from the original GRANT statement. Privileges does not need to be matched. You can use the GRANT statement to GRANT permissions, and then use the REVOKE statement to REVOKE only some permissions.

The REVOKE statement only deletes permissions, but does not delete users. Even if you revoke all permissions, the user records in the user table are retained, which means that the user can still connect to the server. To completely Delete a user, you must use a Delete statement to explicitly Delete user records from the user table:

The Code is as follows:	Copy code
% Mysql-u root mysqlmysql> Delete FROM user-> Where User = "user_name" and Host = "host_name"; mysql> flush privileges;

The Delete statement deletes user records, while the FLUSH statement tells the server to overload the authorization table. (When you use the GRANT and REVOKE statements, the table is automatically reloaded, but you do not modify the authorization table directly .)