User authorization and deletion authorization methods in MySQL,

User authorization and deletion authorization methods in MySQL,

User authorization method

You can add new users by issuing the GRANT statement:

　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:

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:

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');

For the current Login User

SET PASSWORD = PASSWORD("newpassword");

Example:

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:

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:

% 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 .)

Articles you may be interested in:

• MySql adds user, authorization, password modification, and other statements
• MySQL user creation and authorization Methods
• How to create and authorize MySQL users and revoke User Permissions