Preface:

I don’t know if you know about database user authority management. As a DBA, user authority management is an unavoidable task. In particular, the production database, database user rights should be standardized management. This article will introduce MySQL user permission management.

1. User rights Overview

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

A simple understanding of MySQL user permissions is that the database only allows users to do things within their rights, not beyond them. For example, if you are only allowed to perform select, you cannot perform update. If you are only allowed to connect to MySQL from one IP, you cannot connect to MySQL from any 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 the permissions for the values in the student_NAME column of the table STUDENTS.
  • Table level, which relates to all data in a specific table. For example, you can use a SELECT statement to query permissions for all data from the table STUDENTS.
  • Database level, which relates to all tables in a specific database. For example, you can create permissions for new tables in an 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 the user, DB, tables_priv, columns_priv, and procs_priv tables of the mysql system library.

  • User table: Stores user account information and permissions at the global level (for all databases).
  • Db tables: Hold database-level permissions that determine which users from which hosts can access the database.
  • Tables_priv table: Stores table-level permissions that determine which users from which hosts can access the table in the database.
  • Columns_priv table: Holds column-level permissions that determine which users from which hosts can access this field in a database table.
  • Procs_priv table: Stores permissions at the stored procedure and function levels.

Refer to the official documentation for the permissions that can be granted as follows: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 classified as follows:

2. Permission management practice

The grant statement is used to grant privileges to database users. It is recommended that you first create a user with the CREATE User statement and then grant privileges separately. Take a look at the following examples:

# create usercreate user 'test_user'@The '%' identified by 'xxxxxxxx'; # global permissionsGRANT super,select on *.* to 'test_user'@The '%'; # library privilegesGRANT select.insert,update,delete.create.alter.execute on `testdb`.* to 'test_user'@The '%'; # table permissionsGRANT select.insert on `testdb`.tb to 'test_user'@The '%'; # column permissionGRANT select (col1), insert (col1, col2) ON `testdb`.mytbl to 'test_user'@The '%';

# GRANTCommand description: super,selectRepresents the specific permission to be granted.ONUsed to specify which libraries and tables the permissions are for.*.*In front of the*Is used to specify the database name, followed by*Number specifies the table name.TOIndicates that the permission is granted to a user.'test_user'@The '%'Indicates the test_user user, followed by @ restricted host, which can be IP, IP segment, domain name, and%.%Any place. Flush PRIVILEGES; Check the permissions of a usershow grants for 'test_user'@The '%'; Reclaim permissionrevoke delete on `testdb`.* from 'test_user'@The '%';
Copy the code

Authority management is a thing that can not be ignored, we can not give database users a lot of authority for convenience. Especially for production libraries, permissions should be controlled. It is recommended that program users only have basic permissions to add, delete, modify, and check, while individual users only have query permissions.

For safety reasons, it is recommended to follow the following rules of thumb:

  • Grant only the minimum permissions required to prevent users from doing bad things. For example, if the user only needs to query, then only give select permission.
  • When creating a user, restrict the user to a specified IP address or Intranet IP address segment.
  • Create separate database users for each service; ideally, a single user can only work with a single library.
  • Record the database user permissions and other information in time to avoid forgetting.
  • If there are external system calls, you should configure read-only users with permissions down to tables or views.
  • Periodically delete unnecessary users, reclaim permissions, or delete users.

Reference:

  • Dev.mysql.com/doc/refman/…
  • www.cnblogs.com/richardzhu/…