Mysql:grant Grammar (mysql5.x)

　　This example, run in MySQL5.0 and above version.

The simple format that MySQL gives user permission commands can be summarized as:

Grant permission on database object to user

　　One, grant ordinary data user, query, insert, UPDATE, delete all table data in the database right.

Grant SELECT on testdb.* to common_user@ '% '

Grant insert on testdb.* to common_user@ '% '

Grant update on testdb.* to common_user@ '% '

Grant Delete on testdb.* to common_user@ '% '

Alternatively, replace it with a MySQL command:

Grant SELECT, INSERT, UPDATE, delete on testdb.* to common_user@ '% '

　　Second, grant database developers, create tables, indexes, views, stored procedures, functions ... and other permissions.

Grant creates, modifies, and deletes MySQL data table structure permissions.

Grant create on testdb.* to developer@ ' 192.168.0.% ';

Grant alter on testdb.* to developer@ ' 192.168.0.% ';

Grant drop on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL foreign key permissions.

Grant references on testdb.* to developer@ ' 192.168.0.% ';

Grant operates the MySQL temporary table permission.

Grant create temporary tables on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL indexing permissions.

Grant index on testdb.* to developer@ ' 192.168.0.% ';

Grant operates the MySQL view and views the view source code permissions.

Grant CREATE view on testdb.* to developer@ ' 192.168.0.% ';

Grant Show view on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL stored procedures, function permissions.

Grant create routine on testdb.* to developer@ ' 192.168.0.% '; --now, can show procedure status

Grant alter routine on testdb.* to developer@ ' 192.168.0.% '; --now, can drop a procedure

Grant execute on testdb.* to developer@ ' 192.168.0.% ';

　　Third, Grant ordinary DBA management of a MySQL database permissions.

Grant all privileges on TestDB to dba@ ' localhost '

Among them, the keyword "privileges" can be omitted.

　　The grant Advanced DBA manages permissions for all databases in MySQL.

Grant all on *.* to dba@ ' localhost '

　　Five, mysqlgrant authority, can function at many levels respectively.

1. Grant functions on the entire MySQL server:

Grant SELECT on *.* to Dba@localhost; --The DBA can query the tables in all databases in MySQL.

Grant all on *.* to Dba@localhost; --DBA can manage all databases in MySQL

2. Grant functions on a single database:

Grant SELECT on testdb.* to Dba@localhost; --DBAs can query the tables in TestDB.

3. Grant functions on a single datasheet:

Grant SELECT, INSERT, UPDATE, delete on testdb.orders to Dba@localhost;

4. Grant acts on the columns in the table:

Grant Select (ID, SE, rank) on Testdb.apache_log to Dba@localhost;

5. Grant functions on stored procedures, functions:

Grant execute on procedure testdb.pr_add to ' dba ' @ ' localhost '

Grant execute on function testdb.fn_add to ' dba ' @ ' localhost '

　　Vi. View MySQL user rights

View current user (Own) permissions:

Show grants;

To view additional MySQL user rights:

Show grants for Dba@localhost;

　　Vii. revoke permissions that have been given to the MySQL user.

Revoke is similar to Grant's syntax, simply replace the keyword "to" with "from":

Grant all on *.* to Dba@localhost;

Revoke all in *.* from Dba@localhost;

　　Eight, mysqlgrant, revoke user rights considerations

1. Grant, revoke user rights, the user can only reconnect to the MySQL database, permissions to take effect.

2. If you want to allow authorized users to grant these permissions to other users, you need option "grant option"

Grant SELECT on testdb.* to dba@localhost with GRANT option;

This feature is generally not used. In practice, database permissions are best managed by DBAs.