Rights Management for MySQL

The concept of Rights management:

MySQL Rights management is mainly to login MySQL users to verify, reasonable authority planning will improve the security of MySQL server.

1, verify the host, username, password (from where)

2, verify the operation permissions, such as: Select,update,delete (what can be done)

MySQL's various permissions:

Account permissions information is stored in MySQL's User,db,host,table_priv,columns_priv and Procs_priv tables, starting the database and reading into memory

The validation process for the MySQL permissions table is:

1. Verify that the IP, username, and password for the connection are present from the 3 fields in the user table Host,user,password.

2. After the identity authentication, the authority assigns, according to User,db,tables_priv,columns_priv the order to verify.

That is, first check the Global Permissions table user, if the corresponding permissions in user is Y, then this user permissions to all databases are Y, will no longer check the DB, Tables_priv,columns_priv;

If n, the specific database corresponding to this user is checked into the DB table, and the permission of Y in DB is obtained;

If n is in db, check the specific table for this database in Tables_priv, get permission y in the table, and so on.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7D/35/wKioL1bidGrhP8eiAAHDd5qLz1I096.png "style=" float: none; "title=" 1.png "alt=" Wkiol1bidgrhp8eiaahdd5qlz1i096.png "/>

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/7D/37/wKiom1bic-bz-SqzAAMg6fh2OBk773.png "style=" float: none; "title=" 2.png "alt=" Wkiom1bic-bz-sqzaamg6fh2obk773.png "/>

1,create and Drop permissions, you can create new databases and tables, or delete existing databases and tables.

If the drop permission in the MySQL database is granted to a user, the user can delete the database saved by MySQL access

2. SELECT, INSERT, update, and delete permissions allow operation on an existing table on a database

3. Select permissions are only used when they are actually retrieving rows from a table

4,index permissions allow you to create or delete an index, which applies to an existing table.

If you have create permission for a table, you can include the index definition in the CREATE TABLE statement

5,alter permissions, you can use ALTER TABLE to change the structure of the table and rename the table

6,create routine permissions to create saved programs (functions and programs), alter routine permissions are used to change and delete saved programs, execute permissions are used to execute saved programs

Mysqladmin command

The 1,reload command tells the server to re-read the authorization table into memory

2,flush-privileges is synonymous with reload.

3,refresh command clears all tables and closes/opens the log file

Shutdown command to shut down the server

The processlist command displays information about threads that are executing within the server.

The KILL command kills the server thread. Users can always display or kill their own threads, but require process permissions to display or kill other users and super-privileged threads to start

Authorized:

1, global level

Permissions are stored in the Mysql.user table, and grant all on *. * and revoke all on * * ONLY GRANT and REVOKE global permissions

2, data layer level

Permissions are stored in the mysql.db and Mysql.host tables. Grant all on db_name and revoke all on db_name.* only grant and REVOKE database permissions

3, Surface level

Permissions are stored in the Mysql.tables_priv table. Grant all on db_name.tbl_name and revoke all on DB_NAME.TBL_NAME granting and revoking table permissions only

4, Column level

stored in the Mysql.columns_priv table. When using revoke, you must specify the same columns as the authorized column

5, sub-program level

The syntax of Grant:

GRANT Priv_type [(columns)] [, Priv_type [(columns]] ...

On [object_type] Table1,table2,..., Tablen

to user [identified by [PASSWORD] ' PASSWORD ']

[, USER [identified by [PASSWORD] ' PASSWORD '] ...

[With GRANT OPTION]

object_type = TABLE |  FUNCTION | PROCEDURE

GRANT OPTION Value:

| Max_queries_per_hour Count

| Max_updates_per_hour Count

| Max_connections_per_hour Count

| Max_user_connections Count

Example:

Use the GRANT statement to create a new user grantuser with a password of "grantpwd". The user grantuser all data with query, insert permissions, and grant grant permissions.

Mysql> Grant Select,insert on *. * to ' grantuser ' @ ' localhost ' identified by ' grantpwd ' with GRANT option;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Select Host,user,select_priv,insert_priv,grant_priv from Mysql.user where user= ' grantuser ';

+-----------+-----------+-------------+-------------+------------+

| Host | user | Select_priv | Insert_priv | Grant_priv |

+-----------+-----------+-------------+-------------+------------+

| %         | Grantuser | Y | Y | N |

| localhost | Grantuser | Y | Y | Y |

+-----------+-----------+-------------+-------------+------------+

Revoke permissions

The first syntax is to reclaim all permissions for all users, to revoke all global levels for named users, database level, surface level, and column level permissions

REVOKE all privileges,grant OPTION from ' user ' @ ' host ' [, ' User ' @ ' host ' ...]

Another long-form REVOKE statement

REVOKE Priv_type [(columns)] [, Priv_type [(columns)]] ...

On Table1,table2,..., Tablen

From ' user ' @ ' host ' [, ' User ' @ ' host ' ...]

Example:

Use the REVOKE statement to cancel the update permission for user testuser.

Mysql> Grant Update on * * to ' testUser ' @ ' percent ' identified by ' wpf926,. ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Select Update_priv,user,host from Mysql.user where user= ' testUser ';

+-------------+----------+-----------+

| Update_priv | user | Host |

+-------------+----------+-----------+

| Y | TestUser | localhost |

| Y | TestUser | %         |

+-------------+----------+-----------+

2 rows in Set (0.00 sec)

mysql> REVOKE Update on * * from ' testUser ' @ '% ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Select Update_priv,user,host from Mysql.user where user= ' testUser ';

+-------------+----------+-----------+

| Update_priv | user | Host |

+-------------+----------+-----------+

| Y | TestUser | localhost |

| N | TestUser | %         |

+-------------+----------+-----------+

2 rows in Set (0.00 sec)

mysql> REVOKE Update on * * from ' testUser ' @ ' localhost ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Select Update_priv,user,host from Mysql.user where user= ' testUser ';

+-------------+----------+-----------+

| Update_priv | user | Host |

+-------------+----------+-----------+

| N | TestUser | localhost |

| N | TestUser | %         |

+-------------+----------+-----------+

2 rows in Set (0.00 sec)

View Permissions

Show Grant view the syntax for account information:

Show grant for ' user ' @ ' host ';

Example:

Querying user testuser permissions using the Show grants statement

Mysql> Show grants for ' testUser ' @ '% ';

+--------------------------------------------------------------------------------+

| Grants for [email protected]% |

+--------------------------------------------------------------------------------+

| GRANT USAGE on *. testUser ' @ '% ' identified by PASSWORD ' 64c4cd3a456ed50e ' |

+--------------------------------------------------------------------------------+

1 row in Set (0.00 sec)

Mysql> Show grants for ' testUser ' @ ' localhost ';

+-----------------------------------------------------------------------------------------+

| Grants for [email protected] |

+-----------------------------------------------------------------------------------------+

| GRANT SELECT on *. testUser ' @ ' localhost ' identified by PASSWORD ' 565491d704013245 ' |

+-----------------------------------------------------------------------------------------+

1 row in Set (0.00 sec)

Rights Management for MySQL