JC-MySQL permission details

 

MySQL databases have been applied to many Internet companies. mysql permission management is very important. Properly planning mysql database permissions is very beneficial to the security and use of databases. A good standard and habit will not cause major problems when doing anything.

 

The following is the mysql permission planning authorization statement:

 

To authorize jc users to query, insert, update, and delete all table data in the local testdb database, the authorization method is as follows:

Grant select on testdb. * to jc @ 'localhost'

Grant insert on testdb. * to jc @ 'localhost'

Grant update on testdb. * to jc @ 'localhost'

Grant delete on testdb. * to jc @ 'localhost'

Authorizes jc users to create, modify, and delete MySQL Data Table structures.

Grant create on testdb. * to jc @ 'localhost ';

Grant alter on testdb. * to jc @ 'localhost ';

Grant drop on testdb. * to jc @ 'localhost ';

Authorize jc users to operate MySQL foreign Key Permissions

Grant references on testdb. * to jc @ 'localhost ';

Authorize jc users to operate on MySQL temporary tables

Grant create temporary tables on testdb. * to jc @ 'localhost'

Authorize jc users to operate MySQL Indexes

Grant index on testdb. * to jc @ 'localhost ';

Authorize jc users to operate on MySQL view and view source code Permissions

Grant create view on testdb. * to jc @ 'localhost ';

Grant show view on testdb. * to jc @ 'localhost ';

Authorize jc users to manage a MySQL database:

Grant all privileges on testdb to jc @ 'localhost ';

Authorize jc users to manage all databases in MySQL:

Grant all on *. * to jc @ 'localhost ';

The permission granted to the jc user can be applied to multiple levels.

1. grant applies to the entire MySQL Server:

Grant select on *. * to jc @ localhost;-jc can query tables in all databases in MySQL.

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

2. grant applies to a single database:

Grant select on testdb. * to jc @ localhost;-jc can query tables in testdb.

3. grant applies to a single data table:

Grant select, insert, update, delete on testdb. orders to jc @ localhost;

4. grant applies to columns in the table:

Grant select (id, se, rank) on testdb. apache_log to jc @ localhost;

5. grant applies to stored procedures and functions:

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

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

 

 

View MySQL user permissions

View the current user) permission:

Show grants;

View other MySQL user permissions:

Show grants for dba @ localhost;

 

 

Revoke permissions granted to MySQL users.

The syntax of revoke is similar to that of grant. You only need to replace the keyword "to" with "from:

Grant all on *. * to jc @ localhost;
Revoke all on *. * from jc @ localhost;

 

 

Permission authorization syntax and list:

GRANT Syntax:

GRANT privileges (columns)

ON what

TO user identified by "password"

WITH GRANT OPTION

 

Permission list:

• ALTER: Modify tables and indexes.
• CREATE: CREATE a database and a table.
• DELETE: DELETE existing records in the table.
• DROP: discard (delete) databases and tables.
• INDEX: Create or discard an INDEX.
• INSERT: INSERT a new row into the table.
• REFERENCE: unused.
• SELECT: Retrieves records from a table.
• UPDATE: Modify existing table records.
• FILE: read or write files on the server.
• PROCESS: View information about the thread executed on the server or kill the thread.
• RELOAD: RELOAD the authorization table or clear logs, host caches, or table caches.
• SHUTDOWN: Shut down the server.
• ALL: ALL permissions, synonym for all privileges.
• USAGE: Special "no permission" permission.

 

 

 

 

This article is from the IMySQL blog, please be sure to keep this source http://jiechao2012.blog.51cto.com/3251753/1144429