MySQL authorization command

MySQL clears the database operation: TRUNCATE TABLE tablename;

The simple format that MySQL gives to user rights commands can be summarized as: Grant permissions on database objects to users
A grant general data user, the right to query, insert, UPDATE, delete all table data in the database.

1 Grant Select on testdb.* to [email protected] '% '  grant insert on testdb.* to [email protected] '% '  Grant update o n testdb.* to [e-mail protected] '% '  grant Delete on testdb.* to [email protected] '% '  2 or, replace with a MySQL command: 3 Grant Select, insert, UPDATE, delete on testdb.* to [email protected] '% '  

II. Grant Database Developer, creating tables, indexes, views, stored procedures, functions ... and other permissions.
Grant Create, modify, and delete MySQL data table structure permissions

1 Grant create on testdb.* to [email protected] ' 192.168.0.% '; 2 Grant Alter on  testdb.* to [email protected] ' 192.168.0.% '; 3 Grant drop   

Grant Operation MySQL FOREIGN key permissions

Grant references on testdb.* to [email protected] ' 192.168.0.% ';  

Grant Operation MySQL Temp table permissions

Grant create temporary tables on testdb.* to [email protected] ' 192.168.0.% ';  

Grant Operation MySQL Index permissions

Grant Index on  testdb.* to [email protected] ' 192.168.0.% ';  

Grant Operation MySQL View, view source code permissions

Grant CREATE view on testdb.* to [email protected] ' 192.168.0.% '; Grant Show   view on testdb.* to [email protected] ' 192.168.0.% ';  

Grant operations MySQL stored procedures, function permissions

1 Grant create routine on testdb.* to [email protected] ' 192.168.0.% ';  -Now, can show procedure status 2 grant alter  routine on testdb.* to [email protected] ' 192.168.0.% ';  --Now, you can drop a procedure 3 grant execute        

Iii. Grant General DBA manages permissions for a MySQL database

Grant all privileges the TestDB to [email protected] ' localhost '  where the keyword ' privileges ' can be omitted.

Iv. Grant Advanced DBA manages permissions for all databases in MySQL

Grant all on * * to [email protected] ' localhost '  

MySQL grant permissions can be used on multiple levels, respectively

1. Grant acts on the entire MySQL server:

Grant SELECT On *. * to [email protected]; --DBAs can query tables in all databases in MySQL. Grant All    

2. Grant acts on a single database:

Grant Select on testdb.* to [email protected]; --DBAs can query the tables in TestDB.

3. Grant acts on a single data table:

Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected];  

4. Grant acts on the columns in the table:

Grant Select (ID, SE, rank) on testdb.apache_log to [email protected];  

5. Grant acts on stored procedures, functions:

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

Vi. View MySQL User Rights view current user (Own) permissions: show grants;
View other MySQL user rights: Show grants for [email protected];
Vii. revoke permissions that have been granted to MySQL users

Revoke is similar to Grant's syntax, just replace the keyword "to" with "from": Grant all on  * * to   

Viii. MySQL Grant, REVOKE user rights considerations

1. Grant, after revoke user rights, the user has to reconnect to the MySQL database for the permission to take effect.
2. If you want to allow authorized users, you can also grant these permissions to other users, you need the option "grant option"
Grant SELECT on testdb.* to [e-mail protected] with GRANT option; This feature is generally not available. In practice, database permissions are best managed centrally by DBAs.

Nine.  FLUSH privileges; This will not take effect until you reconnect the database or restart the database.

MySQL authorization command