Shulou(Shulou.com)05/31 Report--

How to achieve rights management in MySQL, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. Introduction to user permissions

After we have created a database user, we cannot do anything yet, and we need to assign the appropriate access rights to that user.

The simple understanding of MySQL user rights is that the database only allows users to do things within your rights, not out of bounds. For example, if you are only allowed to perform select operations, then you cannot perform update operations. If you are only allowed to connect to MySQL from a certain IP, then you cannot connect to MySQL from a machine other than that IP.

In MySQL, user permissions are also graded, and the following groups of permissions can be granted:

Column level, related to a specific column in the table. For example, you can use the UPDATE statement to update the permissions for the values of the student_name column in the table students.

Table level, which is related to all the data in a specific table. For example, you can use the SELECT statement to query permissions for all data in the table students.

Database level, which is related to all tables in a specific database. For example, you can have permission to create a new table 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, procs_priv system tables of the mysql system library.

User table: stores user account information and global level (all databases) permissions.

Db table: stores database-level permissions that determine which users from which hosts can access the database.

Tables_priv table: stores table-level permissions that determine which hosts and which users can access this table in the database.

Columns_priv table: holds column-level permissions that determine which hosts and which users can access this field of the database table.

Procs_priv table: stores permissions at the stored procedure and function levels.

Referring to the official documentation, 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, but they can be roughly divided into three categories: data, structure, and management, which can be classified as follows:

two。 Rights management practice

We generally use grant statements to empower database users. It is recommended that you first use create user statements to create users and then authorize them separately. Let's take a look at it through 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 permissions to be granted. ON is used to specify which libraries and tables the permissions are for. The preceding * sign in *. * is used to specify the database name, and the subsequent * sign is used to specify the table name. TO means to grant permissions to a user. 'test_user'@'%' stands for test_user user, @ followed by restricted host, can be IP, IP segment, domain name and%,% means anywhere. # Refresh permission flush privileges; # View a user's permission show grants for 'test_user'@'%'; # Recycle permission revoke delete on `testdb`. * from' test_user'@'%'

Rights management is a thing that can not be ignored, we can not give database users a lot of permissions for convenience. Especially for the production library, authority control should be carried out. It is suggested that program users should only be given basic permissions such as additions, deletions, changes and queries, and individual users should only be given query rights.

For security 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, users only need to query, then only give select permission on it.

When creating a user, the login host of the user is limited, usually to a specified IP or private network IP segment.

Create a separate database user for each service, and it is best for a single user to operate only a single library.

Timely record the database user rights and other information, so as not to forget.

If there is an external system call, the read-only user should be configured and the permissions should be accurate to the table or view.

Regularly clean up unwanted users, reclaim permissions or delete users.

This is the answer to the question about how to achieve rights management in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.