MySQL Permissions and user management

The MySQL privilege system (controlled by MySQL permissions table user and DB) is certified in the following two ways:

1) for the connected users to authenticate, legitimate through authentication, illegal to deny the connection.

2) for the user who is authenticated by the connection, the database can be manipulated within the legal scope.

MySQL's permissions table is loaded into memory when the database is started, and when the user is authenticated, it can access the corresponding permissions in memory and operate the database accordingly. During access, the MySQL database uses the user, DB, and host permissions tables of its internal "MySQL" database. The most important of these is the user permission table, whose contents are mainly divided into: The Users column, the permission column, the security column and the Resource control column.

When the user connects, the MySQL database makes the following two procedures:

1) First from the user table in the host, user, password three fields to determine whether the connected IP, users and passwords exist in the table, if there is verified, otherwise the validation fails.

2) For authenticated users, the user's permissions to the database are obtained through the following permissions table:

User-->db-->tables_priv-->columns_priv. In these permissions tables, the permission ranges are decremented, and the global permissions override the local permissions. First,MySQL user account management

1. Create an account:

Two methods, the grant syntax to create user accounts or directly modify the authorization table, production is more inclined to use the first method of account creation, here is only the grant syntax to create account creation method.

Example 1:

Mysql> Grant all privileges on * * to [e-mail protected] identified by ' MySQL ' with GRANT option;

Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT * from user where user= ' u1 ' and host= ' localhost ' \g;

1. Row ***************************

Host:localhost

User:u1

Password: *e74858db86eba20bc33d0aecae8a8108c56b17fa

Select_priv:y

Insert_priv:y

Update_priv:y

Delete_priv:y

Create_priv:y

Drop_priv:y

Reload_priv:y

Shutdown_priv:y

Process_priv:y

File_priv:y

Grant_priv:y

References_priv:y

Index_priv:y

Alter_priv:y

Show_db_priv:y

Super_priv:y

Create_tmp_table_priv:y

Lock_tables_priv:y

Execute_priv:y

Repl_slave_priv:y

Repl_client_priv:y

Create_view_priv:y

Show_view_priv:y

Create_routine_priv:y

Alter_routine_priv:y

Create_user_priv:y

Event_priv:y

Trigger_priv:y

Create_tablespace_priv:y

Example 2:

Mysql> Grant Select,insert,delete,update on test1.* to [email protected] '% ' identified by ' MySQL ' with GRANT option;

Query OK, 0 rows Affected (0.00 sec)

Example 3:

Mysql> Grant Usage,super,process,file on * * to ' U3 ' @ '% ';

Query OK, 0 rows Affected (0.00 sec)

2. View account privileges

MySQL View account permissions use: Show grants for ' user ' @ ' host ';

Mysql> Show grants for [email protected] '% ';

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

| Grants for [email protected]% |

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

| GRANT USAGE on *. U2 ' @ '% ' identified by PASSWORD ' *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA ' |

| GRANT SELECT, INSERT, UPDATE, DELETE on ' test1 '. * to ' U2 ' @ '% ' with GRANT OPTION |

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

2 rows in Set (0.00 sec)

For later versions of MySQL5.0, you can also use the INFORMATION_SCHEMA database for viewing:

mysql> use INFORMATION_SCHEMA;

Database changed

Mysql> SELECT * from schema_privileges where grantee= "' U2 ' @ '% '";

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

| GRANTEE | Table_catalog | Table_schema | Privilege_type | is_grantable |

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

| ' U2 ' @ '% ' | def | Test1 | SELECT | YES |

| ' U2 ' @ '% ' | def | Test1 | INSERT | YES |

| ' U2 ' @ '% ' | def | Test1 | UPDATE | YES |

| ' U2 ' @ '% ' | def | Test1 | DELETE | YES |

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

4 rows in Set (0.00 sec)

3. Change account permissions

There are two ways to add and Reclaim account permissions, GRANT and REVOKE statements, or directly modify permission tables. Where grant statements increase permissions are consistent with creating user methods. The revoke syntax is described here:

To increase user permissions:

Mysql> Grant Usage on * * to ' u5 ' @ '% ';

Query OK, 0 rows Affected (0.00 sec)

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

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

| Grants for [email protected]% |

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

| GRANT USAGE on * * to ' u5 ' @ '% ' |

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

1 row in Set (0.00 sec)

Mysql> Grant SELECT On * * to ' u5 ' @ '% ';

Query OK, 0 rows Affected (0.00 sec)

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

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

| Grants for [email protected]% |

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

| GRANT SELECT on * * to ' u5 ' @ '% ' |

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

1 row in Set (0.00 sec)

Mysql> Grant Insert,delete on * * to ' u5 ' @ '% ';

Query OK, 0 rows affected (0.01 sec)

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

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

| Grants for [email protected]% |

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

| GRANT SELECT, INSERT, DELETE on *. * to ' U5 ' @ '% ' |

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

1 row in Set (0.00 sec)

Reclaim User rights:

mysql> REVOKE Delete on * * from ' u5 ' @ '% ';

Query OK, 0 rows Affected (0.00 sec)

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

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

| Grants for [email protected]% |

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

| GRANT SELECT, INSERT on *. * to ' U5 ' @ '% ' |

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

1 row in Set (0.00 sec)

Mysql> Revoke Select,insert on * * from ' u5 ' @ '% ';

Query OK, 0 rows Affected (0.00 sec)

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

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

| Grants for [email protected]% |

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

| GRANT USAGE on * * to ' u5 ' @ '% ' |

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

1 row in Set (0.00 sec)

Mysql> REVOKE usage on * * from ' u5 ' @ '% ';

Query OK, 0 rows Affected (0.00 sec)

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

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

| Grants for [email protected]% |

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

| GRANT USAGE on * * to ' u5 ' @ '% ' |

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

1 row in Set (0.00 sec)

NOTE: Revoke cannot reclaim the usage logon rights, which means that revoke cannot delete the MySQL user.

4. Change your account password

1) Method One: mysqladmin execute password on command line: The user needs to have super privilege.

[[email protected] bin]#./mysqladmin-uroot-hlocalhost-p3306 password ' mysql '

Warning:using a password on the command line interface can is insecure.

[Email protected] bin]#./mysql-uroot

ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)

[Email protected] bin]#./mysql-uroot-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 28

Server version:5.6.31 Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

2) Method Two: Set Password statement change user password:

mysql> set password for ' u1 ' @ ' localhost ' =password (' Oracle ');

Query OK, 0 rows Affected (0.00 sec)

[Email protected] bin]#./mysql-uu1-hlocalhost-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 42

Server version:5.6.31 Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

Modify your password to omit for:

Mysql> set Password=password (' MySQL ');

Query OK, 0 rows Affected (0.00 sec)

3) Method Three: Grant's identified by clause specifies the user password directly:

Mysql> Grant Usage on *. * to ' u1 ' @ ' localhost ' identified by ' Oracle ';

Query OK, 0 rows Affected (0.00 sec)

4) Directly change the user table of the MySQL database, you can also use MD5 encrypted ciphertext when changing the password: Pay attention to the use time of password function.

Slightly

5. Delete MySQL account method

There are two ways to delete a MySQL user, drop user and directly modify user table:

mysql> drop user ' t1 ' @ ' localhost ';

Query OK, 0 rows Affected (0.00 sec)

6. Account Resource limitation

The resource limitations of MySQL include the following:

1) The number of queries per hour for a single account;

2) The number of updates per hour for a single account;

3) The number of times a single account connects to the database per hour;

4) Number of concurrent connections to the database per hour for a single account.

The syntax for setting resource limits is as follows:

Grant ... with option;

Where option can be the following:

1) max_queries_per_hour count; Maximum number of queries per hour;

2) max_updates_per_hour count; The maximum number of updates per hour;

3) max_connections_per_hour count; Maximum number of connections per hour;

4) max_user_connections count; Maximum user concurrent connections (MySQL system global max_user_connections parameter).

Cases:

Mysql> Grant Select on test.* to [email protected]

With Max_queries_per_hour 5

-Max_user_connections 5;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Select User,max_questions,max_updates,max_connections,max_user_connections from Mysql.user where user= ' Chavin ';

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

| user | max_questions | Max_updates | max_connections | max_user_connections |

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

|             Chavin |           5 |               0 |                    0 | 5 |

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

1 row in Set (0.00 sec)

[Email Protected]spdev bin]#./mysql-uchavin-hlocalhost

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 66

Server version:5.6.31 Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> use test;

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

Mysql> select * from T1;

+------+

| ID |

+------+

| 1 |

| 2 |

+------+

2 rows in Set (0.00 sec)

Mysql> select * from T1;

ERROR 1226 (42000): User ' Chavin ' have exceeded the ' max_questions ' resource (current value:5)

Mysql>

Clear Account Resource Limit method: Root user executes flush User_resources/flush privileges/mysqladmin Reload One of these three commands is cleared.

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

Modify or delete a user's resource limit to set the corresponding resource limit to 0.

Mysql> Grant Usage on *. * to [email protected]

With max_queries_per_hour 0;

Query OK, 0 rows Affected (0.00 sec)

MySQL Permissions and user management