MySQL permission management

MySQL permission management This article uses theory and practice to sort out MySQL permission-related knowledge points and gradually improve them in practical applications. 1. What permissions does a MySQL user have to copy from the Internet? What permissions does MYSQL have when I click to browse the blog? Copy a table from the official website to see: Permission level permission description

MySQL permission management This article uses theory and practice to sort out MySQL permission-related knowledge points and gradually improve them in practical applications. 1. What permissions does a MySQL user have to copy from the Internet? What permissions does MYSQL have when I click to browse the blog? Copy a table from the official website to see: Permission level permission description

MySQL permission management

This article makes a summary of MySQL permission-related knowledge points based on the theory and practice, and gradually makes improvements in practical applications.

I. What permissions does a MySQL user have?

Copy the following part from the Internet and click to view this blog

What permissions does MYSQL have? Copy a table from the official website:

Permission	Permission level	Permission description
CREATE	Databases, tables, or Indexes	Create Database, table, or index Permissions
DROP	Database or table	Delete database or table Permissions
GRANT OPTION	Database, table, or stored program	Grant permission options
REFERENCES	Database or table
ALTER	Table	Change tables, such as adding fields and indexes.
DELETE	Table	Delete data permission
INDEX	Table	Index permission
INSERT	Table	Insert permission
SELECT	Table	Query permission
UPDATE	Table	Update Permissions
CREATE VIEW	View	Create view Permissions
SHOW VIEW	View	View view Permissions
ALTER ROUTINE	Stored Procedure	Change Stored Procedure Permissions
CREATE ROUTINE	Stored Procedure	Create Stored Procedure permission
EXECUTE	Stored Procedure	Stored Procedure execution permission
FILE	File access on the server host	File Access permission
CREATE TEMPORARY TABLES	Server Management	Create temporary table Permissions
LOCK TABLES	Server Management	Lock table Permissions
CREATE USER	Server Management	Create User Permissions
PROCESS	Server Management	View process Permissions
RELOAD	Server Management	Execute commands such as flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload.
REPLICATION CLIENT	Server Management	Copy permission
REPLICATION SLAVE	Server Management	Copy permission
SHOW DATABASES	Server Management	View database Permissions
SHUTDOWN	Server Management	Disable database Permissions
SUPER	Server Management	Run the kill thread permission

The distribution of MySQL permissions refers to the permissions that can be set for tables and columns. This can be explained from a table in the official document:

Permission Distribution	Possible Permissions
Table Permissions	'Select', 'insert', 'update', 'delete', 'create', 'drop', 'Grant ', 'references', 'index', 'alter'
Column permission	'Select', 'insert', 'update', 'references'
Process permission	'Execute ', 'alter Routine', 'Grant'

Ii. Principles of MySQL permission experience:

Permission control is mainly for security reasons. Therefore, we need to follow the following principles:

1. Grant only the minimum permissions required to prevent users from doing bad things. For example, if you only need to query, you only need to grant the select permission. do not grant update, insert, or delete permissions to the user.

2. When creating a user, restrict the user's login host to a specified IP address or Intranet IP address segment.

3. delete a user without a password when initializing the database. Some users are automatically created after the database is installed. By default, these users do not have a password.

4. Set a password that meets the password complexity for each user.

5. Clear unnecessary users on a regular basis. Revoke permissions or delete users.

3. Permission addition, deletion, query, and modification

Add:

You can use the grant (document) command to add permissions in the following format:

GRANT    priv_type [(column_list)]      [, priv_type [(column_list)]] ...    ON [object_type] priv_level    TO user_specification [, user_specification] ...    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]    [WITH with_option ...]object_type:    TABLE  | FUNCTION  | PROCEDUREpriv_level:    *  | *.*  | db_name.*  | db_name.tbl_name  | tbl_name  | db_name.routine_nameuser_specification:    user [IDENTIFIED BY [PASSWORD] 'password']ssl_option:    SSL  | X509  | CIPHER 'cipher'  | ISSUER 'issuer'  | SUBJECT 'subject'with_option:    GRANT OPTION  | MAX_QUERIES_PER_HOUR count  | MAX_UPDATES_PER_HOUR count  | MAX_CONNECTIONS_PER_HOUR count  | MAX_USER_CONNECTIONS count

PS:

With_option is a restriction or management of the granted permissions. For example, with grant option indicates that the authorized user has the ability to grant the same permissions to other users.

Delete:

To delete a permission, use the revoke (official documentation) command. The specific format is as follows:

REVOKE    priv_type [(column_list)]      [, priv_type [(column_list)]] ...    ON [object_type] priv_level    FROM user [, user] ...REVOKE ALL PRIVILEGES, GRANT OPTION    FROM user [, user] ...

Example:

If you want to delete the permission of user jeffrey @ localhost to insert all databases, use the following command

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

If you want to delete the authorized permissions, you can:

REVOKE grant option ON *. * FROM 'Jeffrey '@ 'localhost ';

Of course, you can also use all privilege to delete all permissions (ps: all privilege does not include grant permissions) As you grant permissions)

REVOKE all privileges ON *. * FROM 'jeffre' @ 'localhost ';

View:

The show grants (official documentation) command displays user permissions.

SHOW GRANTS [FOR user]

When the for user is missing, the system displays the permissions of all users visible to the query:

mysql> show grants;+----------------------------------------------------------------------------------------------------------------------------------------+| Grants for root@localhost                                                         |+----------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                         |+----------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

View root User Permissions:

SHOW GRANTS FOR 'ROOT'@'LOCALHOST'

Iv. Practical Application and some pitfalls