Detailed explanation of MySQL user rights management

Preface:

I am not sure whether you understand database user rights management. As a DBA, user rights management is an unavoidable part of your job. Especially for production databases, database user permissions should be managed in a standardized manner. This article will introduce the relevant content of MySQL user rights management.

1. Introduction to User Rights

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

A simple understanding of MySQL user permissions is that the database only allows users to do things within your rights and cannot cross the boundaries. For example, if you are only allowed to perform select operations, then you cannot perform update operations. You are only allowed to connect to MySQL from a certain IP, then you cannot connect to MySQL from any other machine except that IP.

In MySQL, user permissions are also divided into levels. The permissions that can be granted are as follows:

• Column level, related to a specific column in the table. For example, you can use the UPDATE statement to update the value of the student_name column in the students table.
• Table level, 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 students table.
• Database level, related to all tables in a specific database. For example, you can grant permission to create a new table 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 the user, db, tables_priv, columns_priv, and 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, which determines which users from which hosts can access this database.
• tables_priv table: stores table-level permissions, which determines which users from which hosts can access this table in the database.
• columns_priv table: stores column-level permissions, which determines which users from which hosts can access this field in the database table.
• procs_priv table: stores stored procedure and function-level permissions.

Refer to the official documentation, the permissions that can be granted are shown in the following table:

It seems that there are many different permissions that can be granted, but they can actually be roughly divided into three categories: data, structure, and management, which can be roughly classified as follows:

2. Actual Permission Management

We usually use the grant statement to grant permissions to database users. We recommend that you first create the user with the create user statement and then grant permissions separately. Let's take a look at this in detail through an example:

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

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

# Database 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 apply to.
In *.*, the * at the beginning is used to specify the database name, and the * at the end is used to specify the table name.
TO means granting permissions to a user.
'test_user'@'%' means test_user user. @ is followed by the restricted host, which can be IP, IP segment, domain name and %. % means anywhere.

#Flush privileges;

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

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

Permission management is something that cannot be ignored. We cannot give database users too much permission just for the sake of convenience. Especially for production databases, permission control should be carried out. It is recommended that program users are only granted basic permissions such as add, delete, modify and query, and individual users are only granted 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 select permission is required.
• When creating a user, limit the user's login host, usually to a specified IP or intranet IP segment.
• Create a separate database user for each service. It is best if a single user can only operate a single database.
• Record the information such as user permissions of each database in a timely manner to avoid forgetting.
• If there are external system calls, a read-only user should be configured, and the permissions should be accurate to the table or view.
• Regularly clean up unnecessary users, revoke 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 related articles on 123WORDPRESS.COM!