Introduction to MySQL Users and permissions

A few common sense about MySQL privilege:

1, MySQL's authority system is mainly used to verify the user's operation rights.

2, Inside MySQL, the permission information is stored in the MySQL database granttable. When MySQL starts, the information in the granttable is written to memory.

3. MySQL uses the user name plus host name as the identifier.

This identifier can be used to differentiate the same user name on different hosts.

4, MySQL rights control has 2 kinds of strategies:

1) Control the client connection based on whether the password is correct.

2) Assuming normal connect,server can also check whether each satement has permission to execute. A drop operation cannot be performed if there is only select permission for a table.

5, if the user's permissions change, the current connected session users will not be affected, the next logon will take effect.

Ii. the meaning of several rights tables about MySQL:

User: Account number, global permissions

DB: library-level permissions

Host: Obsolete

Tables_priv: Table-level permissions

Colums_priv: Column-level permissions

Procs_priv: Stored procedure and storage function-related permissions

Proxies_priv: Delegate User rights

Third, the MySQL user account creation rule

User name @ Host

User name: within 16 characters

Host:

Host Name: Www.test.com,mysql

ip:192.168.2.1

Network address: 192.168.0.0/255.255.0.0

Wildcard character:%,192.168.%.%,%.test.com

Iv. user privilege level for MySQL

Service Management class: Super

Library: CREATE

Table: DELETE, ALTER

Columns: INSERT, SELECT, UPDATE

More levels refer to MySQL official documentation

V. Several commands related to permissions

GRANT Permission,... On [Object type] db. {Table|routine} To ' username ' @ ' host ' [indentified by ' Password '];

REVOKE Permissions,... On [Object type] db. {Table|routine} From ' username ' @ ' host ';

SHOW GRANTS for ' username ' @ ' host ';

CREATE USER ' username ' @ ' host ' [identified by ' Password '];

DROP USER ' username ' @ ' host ';

RENAME USER old_name to New_name;

Vi. Examples of Operations commands for permissions

To view all users of the current database:

Select User,host,password from Mysql.user;

Grant the user super privileges (super and all privileges):

GRANT Super On * * to ' mysql ' @ ' localhost ';

GRANT all privileges on *. * to ' mysql ' @ ' localhost ';

Remove the user's super privileges (super and all privileges):

REVOKE Super On * * from ' mysql ' @ ' localhost ';

REVOKE all privileges on * * from ' mysql ' @ ' localhost ';

To view the permissions granted to a user

SHOW GRANTS for ' mysql ' @ ' localhost ';

Vii. problem handling for MySQL

1, the MySQL login password forgotten when the recovery operation

Two parameters are passed when starting Mysql_safe:

--skip-grant-tables Skip Authorization Form

--skip-networking for security, prevent network logons

Login Method One:

Modify/etc/init.d/mysql

Login Method Two:

Directly in the MY.CNF configuration

[Mysqld]

Skip-grant-tables

Skip-networking

Then change the password:

Modify the password directly by updating the authorization table, and then remove the two options to restart the server

UPDATE user SET Password=password (' 123456 ') WHERE user= ' root '

2, the client connection MySQL database slow problem

Directly in MY.CNF configuration, turn off DNS inverse parsing parameters

[Mysqld]

Skip-name-resolve

Introduction to MySQL Users and permissions