MySQL users and permissions, mysql user permissions

MySQL users and permissions, mysql user permissions

I. Knowledge about MySQL permissions:

1. the MySQL permission system is mainly used to verify the user's operation permissions.

2. in MySQL, the permission information is stored in the granttable of MySQL database. When mysql is started, the information in granttable is written into the memory.

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

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

4. There are two policies for MySQL permission control:

1) control the client connection based on the correct password.

2) If you can connect normally, the server can also check whether each satement has the permission to execute. If you only have the select permission for a table, you cannot perform the drop operation.

5. If the user's permissions change, the connected session users will not be affected and will only take effect the next login.

 

 

Ii. Definitions of several permission tables related to MySQL:

User: user Account and global Permissions

Db: database-level Permissions

Host: abandon

Tables_priv: Table-level Permissions

Colums_priv: column-level permission

Procs_priv: permission related to stored procedures and stored Functions

Proxies_priv: proxy user permission

 

Iii. MySQL user account creation rules

Username @ host

User name: less than 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: %, 192.168. %. %, % .test.com

 

 

Iv. MySQL user permission level

Service Management: super

Database: CREATE

Table: DELETE, ALTER

Columns: INSERT, SELECT, UPDATE

 

For more information, see the official MySQL documentation.

 

 

5. Permission-related commands

GRANT permission,... ON [object type] db. {table | routine} TO 'username' @ 'host' [indentified by 'Password'];

REVOKE permission,... 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. Example of permission operation commands

View all users in the current database:

Select user, host, password from mysql. user;

 

Grant super permissions to users (both super and all privileges are supported ):

GRANT super ON *. * TO 'mysql' @ 'localhost ';

Grant all privileges on *. * TO 'mysql' @ 'localhost ';

 

Delete the super permission of a user (both super and all privileges are supported ):

REVOKE super ON *. * FROM 'mysql' @ 'localhost ';

Revoke all privileges on *. * FROM 'mysql' @ 'localhost ';

 

View permissions granted to users

Show grants for 'mysql' @ 'localhost ';

 

VII. MySQL troubleshooting

 

1. Restore the MySQL logon password when it is forgotten

 

When mysql_safe is started, two parameters are passed:

-- Skip-grant-tables skip authorization table

-- Skip-networking prevents network logon for security purposes

Logon Method 1:

Modify/etc/init. d/mysql

Logon Method 2:

Directly configure in my. cnf

[Mysqld]

Skip-grant-tables

Skip-networking

Then change the password:

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

UPDATE user set password = PASSWORD ('20140901') WHERE User = 'root'

 

2. Slow client connection to MySQL database

 

Directly configure in my. cnf to disable reverse DNS resolution Parameters

[Mysqld]

Skip-name-resolve