Explain MySQL user permission management in detail

Time:2021-9-26

preface:

I’m not sure whether you understand database user rights management. As a DBA, user rights management is an inseparable work. In particular, the production database and database user permissions should be managed in a standardized manner. This article will introduce MySQL user rights management.

1. Introduction to user permissions

After we have created a database user, we cannot perform any operations. We need to assign appropriate access permissions to the user.

The simple understanding of MySQL user permissions is that the database only allows users to do things within your rights and cannot cross the boundary. For example, if you are only allowed to perform the select operation, you cannot perform the update operation. If you are only allowed to connect to MySQL from an IP, you cannot connect to MySQL from a machine other than that IP.

In mysql, user permissions are also hierarchical. The following groups of permissions can be granted:

  • Column level, which is related to a specific column in the table. For example, you can use the update statement to update students in the table students_ Permission for the value of the name column.
  • Table level, which is related to all data in a specific table. For example, you can use the select statement to query the permissions of all data in the table students.
  • Database level, which is related to all tables in a specific database. For example, you can create permissions for new tables in the existing database mytest.
  • Global, related to all databases in MySQL. For example, you can delete an existing database or create a new database.

Permission information is stored in user, DB and tables of MySQL system library_ priv、columns_ priv、procs_ Priv is in these system tables.

  • User table: stores user account information and global level (all databases) permissions.
  • DB table: the permissions at the database level determine which users from which hosts can access the database.
  • tables_ Priv table: the permissions at the table level determine which hosts and users can access the table in the database.
  • columns_ Priv table: the permission to store the column level determines which users from which hosts can access this field of the database table.
  • procs_ Priv table: stores stored procedure and function level permissions.

Referring to the official documents, the permissions that can be granted are shown in the following table:

It seems that there are many kinds of permissions that can be granted. In fact, they can be roughly divided into three categories: data, structure and management. They can be roughly classified as follows:

2. Authority management practice

We generally use grant statement to empower database users. It is recommended that you first create users with create user statement, and then authorize them separately. Here is an example:

#   Create user
create user 'test_user'@'%' identified by 'xxxxxxxx';

#   Global permissions
GRANT super,select on *.* to 'test_user'@'%';

#   Library permissions
GRANT select,insert,update,delete,create,alter,execute on `testdb`.* to 'test_user'@'%';

#   Table permissions
GRANT select,insert on `testdb`.tb to 'test_user'@'%';

#   Column permissions
GRANT select (col1), insert (col1, col2) ON `testdb`.mytbl to 'test_user'@'%';

#Grant command description:
super,select   Indicates the specific permission to grant.
ON   Used to specify which libraries and tables permissions apply to.
*.*   The front * sign in the is used to specify the database name, and the rear * sign is used to specify the table name.
TO   Indicates that permission is given to a user.
'test_ user'@'%'   Indicates test_ User user, @ followed by restricted host, which can be IP, IP segment, domain name and%,% indicates anywhere.

#   Refresh permissions
flush privileges;

#   View the permissions of a user
show grants for 'test_user'@'%';

#   Recycle permissions
revoke delete on `testdb`.* from 'test_user'@'%';

Permission management is a matter that can not be ignored. We can’t give database users great permission for convenience. Especially for the production library, permission control should be carried out. It is recommended that program users only give basic permissions such as adding, deleting, modifying and querying, and individual users only give query permissions.

For safety reasons, the following empirical principles are recommended:

  • Only the minimum permissions that can meet the needs are granted to prevent users from doing bad things. For example, if the user only needs to query, just give the select permission.
  • When creating a user, restrict the user’s login host, which is generally limited to the specified IP or intranet IP segment.
  • Create database users for each service separately. It is better for a single user to operate only a single database.
  • Timely record the user permissions and other information of each database to avoid forgetting.
  • If there are external system calls, read-only users shall be configured, and the permissions shall be accurate to the table or view.
  • Clean up unnecessary users regularly, reclaim permissions or delete users.

The above is a detailed explanation of MySQL user rights management. For more information about MySQL user rights management, please pay attention to other relevant articles of developeppaer!

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]