Introduction to MYSQL Grant,revoke User Rights Management usage

MySQL can assign strict, complex permissions to different users. Most of these operations can be implemented with SQL Directive grant (assigning permissions) and revoke (Reclaim permissions). Grant can assign the specified permissions to a specific user, and if the user does not exist, a user is created.

Grant Common format:

Grant permission 1, permission 2,... Permission n on database name. Table name to User name @ user address identified by ' Connect password ';

Permission 1, permission 2,... Permission n represents 14 permissions, such as Select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file.
When permission 1, permission 2,... Permission n is replaced by all privileges or all, giving the user full permissions.
When the database name. The table name is replaced by *.*, which indicates that the user is given permission to manipulate all tables on the server for all databases.
The user's address can be localhost, or it can be an IP address, machine name, domain name. You can also use '% ' to indicate a connection from any address.
' Connection password ' cannot be empty or the creation failed.

It is more important to priveleges (permissions).
The normal user's permission permissions apply to the description
Select table, column allows the user to select rows (records) from the table
Insert table, column allows user to insert new row in table
Update tables, columns allow users to modify values in existing table rows
Delete Table allows users to delete rows of existing tables
The index table allows users to create and drag specific table indexes
The ALTER TABLE allows the user to change the structure of an existing table. For example, you can add columns, rename columns or tables, modify the data type of a column
Create database, which allows users to create new databases or tables. If a particular database or table is specified in grant, they can only create the database or table, that is, they must first delete (drop) it
Drop database, table allows users to drag (delete) a database or table

Description of administrator permission rights
Create temporary tables allow administrators to use the TEMPORARY keyword in the CREATE TABLE statement
File allows data to be read from a file into a table, or read from a table
Lock tables allows you to use the Lock tables statement
Process allows administrators to view server processes belonging to all users
Reload allows administrators to reload authorization tables, empty authorizations, hosts, logs, and tables
REPLICATION client allows show STATUS to be used on replication hosts (master) and from machines (Slave)
REPLICATION slave allows replication to connect to the primary server from the server
Show databases allows you to view all the list of databases by using the databases statement. Without this permission, users can only see the database they can see
Shutdown allows administrators to shut down MySQL server
Super allows administrators to shut down threads belonging to any user

Special permission Permission Description
All (or all previleges) grant all permissions
Usage does not grant permissions. This creates a user and allows him to log in, but does not allow other operations, such as Update/select, etc.

Instance:

For example:

The code is as follows	Copy Code
Mysql>grant select,insert,update,delete on Test.user to Mql@localhost identified by ' 123456′;

Assign the local user mql the right to Select,insert,update,delete operations on the user table for database test, and set the password to 123456. If the MQL user does not exist, the user is created automatically. The specific permissions control can be viewed in the Mysql.db table. The table can also be updated directly to modify the permissions.

The code is as follows	Copy Code
Mysql>grant all privileges in test.* to mql@localhost identified by ' 123456′;

Assign the local user mql the right to perform all operations on the database test all tables, and set the password to 123456.

The code is as follows	Copy Code
Mysql>grant all privileges in *.* to mql@localhost identified by ' 123456′;

Assign the local user MQL permissions to all operations on all tables of all databases, and set the password to 123456.

The code is as follows	Copy Code
Mysql>grant all privileges in *.* to mql2@61.127.46.128 identified by ' 123456′;

Assign the user mql2 from 61.127.46.128 the right to perform all operations on all tables of all databases, and set the password to 123456.

REVOKE
Revoke and function are opposite to grant, the syntax format is:
REVOKE privileges on database name [. Table name] from user_name

For example:
Create user Bob with the password "Bob" but not give him any permissions:

The code is as follows	Copy Code
GRANT usage on * to Bob identified by ' Bob ';

To grant Bob Query and insert permissions in the books database:

The code is as follows	Copy Code
GRANT Select, insert on books.* to Bob;

To cancel all of Bob's permissions in the books database:

The code is as follows	Copy Code
REVOKE all in books.* from Bob;

Note: It should be noted that REVOKE all ... Simply recycle the user's permissions and do not delete the user. In MySQL, user information is stored in MySQL. In user. MySQL can delete a user completely by drop user, and its usage is:

The code is as follows	Copy Code
DROP USER user_name;

For example, to delete a user, Bob, you can use:

The code is as follows	Copy Code
DROP USER Bob;