Implementation of Mysql User Rights Management

1. Introduction to MySQL permissions

There are 4 tables that control permissions in MySQL,分別為user表，db表，tables_priv表，columns_priv表. My current version is MySQL 5.7.22.

The verification process of the mysql permission table is:

1. First, determine whether the connected IP, username, and password exist from the three fields Host, User, and Password in the user table. If they exist, the verification is passed.
2. After identity authentication, permissions are assigned and verified in the order of user, db, tables_priv, and columns_priv. That is, first check the global permission table user. If the corresponding permission in user is Y, then the permission of this user for all databases is Y, and db, tables_priv, and columns_priv will no longer be checked. If it is N, check the specific database corresponding to this user in the db table and obtain the permission Y in db. If db is N, check the specific table corresponding to this database in tables_priv and obtain the permission Y in the table, and so on.

1.1 MySQL Privilege Levels

Divided into:
Global management permissions: Applies to the entire MySQL instance level Database-level permissions: Applies to a specific database or all databases Database object-level permissions: Applies to a specific database object (table, view, etc.) or all database objects

The permissions are stored in the mysql database in the user, db, tables_priv, columns_priv, and procs_priv system tables, which are loaded into memory after the MySQL instance is started.

Check which users mysql has:

mysql> select user,host from mysql.user;

Let's look at root 用戶在權限系統表中的數據:

mysql> use mysql;
mysql> select * from user where user='root' and host='localhost'\G; #All permissions are Y, which means all permissions are availablemysql> select * from db where user='root' and host='localhost'\G; # No such recordmysql> select * from tables_priv where user='root' and host='localhost'; # No such recordmysql> select * from columns_priv where user='root' and host='localhost'; # No such recordmysql> select * from procs_priv where user='root' and host='localhost'; # No such record

As mentioned above: the verification process of permissions

Check the permissions of the root@'localhost' user

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

2. Detailed explanation of MySQL permissions

All/All Privileges權限代表全局或者全數據庫對象級別的所有權限

The Alter permission allows you to modify the table structure, but it must be accompanied by the Create and Insert permissions. If you want to rename a table, you must have the permissions to alter and drop the original table, and create and insert the new table.

The Alter routine permission allows you to modify or delete stored procedures and functions.

The Create permission allows you to create new databases and tables.

The Create routine permission allows you to create stored procedures and functions.

The Create tablespace permission allows you to create, modify, and delete tablespaces and log groups.

The Create temporary tables permission allows you to create temporary tables.

The Create user permission allows you to create, modify, delete, and rename users.

The Create view permission represents the permission to create a view.

The Delete permission allows you to delete row data.

The Drop permission allows you to delete databases, tables, and views, including the truncate table command.

Event permissions allow you to query, create, modify, and delete MySQL events.

The Execute permission allows you to execute stored procedures and functions.

File permissions allow you to read and write disk files in directories accessible to MySQL. Available commands include load data infile, select ... into outfile, and load file() functions.

The Grant option permission indicates whether to allow this user to grant or revoke the permissions you granted to other users. This permission is required when re-granting permissions to the administrator.

Index permissions indicate whether to allow creation and deletion of indexes

The Insert permission indicates whether data can be inserted into the table. The Insert permission is also required when executing the Analyze Table, Optimize Table, and Repair Table statements.

The Lock permission allows you to lock a table with select permission to prevent other links from reading or writing to this table.

Process permissions allow you to view process information in MySQL, such as executing commands such as show processlist, mysqladmin processlist, and show engine.

The Reference permission was introduced after version 5.7.6 and indicates whether to allow the creation of foreign keys.

The Reload permission allows the flush command to be executed, indicating that the permission table is reloaded into the system memory. The refresh command means closing and reopening the log file and refreshing all tables.

Replication client permissions allow you to execute show master status, show slave status, show binary logs commands

Replication slave permission means that the slave host is allowed to connect to the master through this user in order to establish a master-slave replication relationship

The Select permission allows you to view data from a table. Some select statements that do not query table data do not require this permission, such as Select 1+1 and Select PI()+2. The Select permission is also required when executing update/delete statements that contain a where condition.

The Show databases permission means that you can view all database names by executing the show databases command.

The Show view permission allows you to view the statements that create a view by executing the show create view command.

The Shutdown permission allows you to shut down the database instance. The executed statements include mysqladmin shutdown

Super permissions allow you to execute a series of database management commands, including the kill command to force a connection to close, the change master to create a replication relationship command, and the create/alter/drop server command.

The Trigger permission allows you to create, delete, execute, and display triggers.

The Update permission allows you to modify data in the table.

Usage permissions are the default permissions after creating a user, which itself represents the connection login permission

2.1 System Permission Table

User表: stores user account information and global level (all database) permissions, which determines which users from which hosts can access the database instance. If有全局權限則意味著對所有數據庫都有此權限
Db table: stores數據庫級別permissions, which determines which users from which hosts can access this database
Tables_priv table:存放表級別的權限, which determines which users from which hosts can access this table in the database
Columns_priv table:存放列級別的權限, which determines which users from which hosts can access this field in the database table
Procs_priv table:存放存儲過程和函數level permissions

The most important thing is the user table

2.1.1 User and db permission table structure

Special fields in the User permission table structure:

• Plugin, authentication_string field stores user authentication information
• Password_expired is set to 'Y', which means that the DBA is allowed to set the password of this user to expire and require the user to reset the password after expiration (alter user/set password to reset the password)
• Password_last_changed is a timestamp field that represents the last time the password was modified.此數值自動更新when you create a user or modify a user's password by executing commands such as create user/alter user/set password/grant.
• Password_lifetime represents the number of days from the password_last_changed time until the password expires.
• Account_locked代表此用戶被鎖住，無法使用

Before MySQL 5.7, there was a password field in the user table.

2.1.2 Tables_priv and columns_priv permission table structure

Tables_priv and columns_priv privilege values

2.1.3 procs_priv permission table structure

• Routine_type is an enumeration type, representing whether it is a stored procedure or a function
• The Timestamp and Grantor fields are not used for the time being.

System permission table field length limit table

Case sensitivity in authorization authentication

• Fields user, password, authencation_string, db, table_name are case sensitive
• Fields host, column_name, routine_name are case insensitive

2.2 User Rights Information Management

2.2.1 View user permission information

Check which users MYSQL has

mysql> select user,host from mysql.user;

View the permissions that have been granted to the user, such as root

mysql> show grants for root@'localhost';

View other unauthorized information of the user

mysql> show create user root@'localhost'; 

2.2.2 User Composition

MySQL authorized users consist of two parts:用戶名和登錄主機名

• The syntax for expressing a user is 'user_name'@'host_name'
• Single quotes are optional, but包含特殊字符則是必須的
• "@'localhost' represents anonymous login users
• Host_name can be a host name or an IPv4/IPv6 address. Localhost represents the local machine, 127.0.0.1 represents the local ipv4 address, and ::1 represents the local ipv6 address
• The Host_name field allows the use of %和_ matching characters. For example, '%' represents all hosts, '%.mysql.com' represents

All hosts from the mysql.com domain. '192.168.1.%' represents all hosts from the 192.168.1 network segment.

2.2.3 Modify User Permissions

After executing Grant, revoke, set password, or rename user to modify permissions, MySQL will automatically load the modified permission information into the system memory.

If you insert/update/delete the system privilege table, you must execute the refresh privilege command to synchronize it to the system memory. The refresh privilege commands include: flush privileges /mysqladmin flush-privileges / mysqladmin reload

If you modify the permissions at the tables and columns level, the new permissions will take effect the next time the client operates.

If you modify database-level permissions, the new permissions take effect after the client executes the use database command.

If you modify the global level permissions, you need to re-create the connection for the new permissions to take effect.

If you modify the global level permissions, you need to re-create the connection for the new permissions to take effect (for example, changing the password)

2.2.4 Create mysql user

There are two ways to create a MySQL authorized user

• 執行create user/grant命令(recommended method)
• Directly operate the MySQL system permission table through the insert statement

# Create finley This just creates a user and does not have permissions mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
# Make finley the administrator usermysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH
GRANT OPTION;
#Create a user and grant RELOAD,PROCESS permissions on all libraries and tablesmysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identified by '123456';
 
# Create user keme, who has only select permission on the id column in the test database and temp table.mysql> grant select(id) on test.temp to keme@'localhost' identified by '123456';

2.2.4 Reclaim mysql permissions

Revoke the user's permissions through the revoke command. When revoking, check what permissions the user has and then revoke them. I will test the admin user.

mysql> show grants for admin@'localhost';
mysql> select user,host from mysql.user;
mysql> revoke PROCESS ON *.* FROM admin@'localhost'; 

2.2.5 Deleting mysql users

Delete MySQL users by executing the drop user command. You can also delete them through the system privilege table (not recommended).

mysql> drop user admin@'localhost';

2.2.6 Setting MySQL User Resource Limits

By setting the global variable max_user_connections, you can limit the number of connections that all users can make to a MySQL instance at the same time. However, this parameter cannot be used to treat each user differently, so MySQL provides resource limit management for each user.

MAX_QUERIES_PER_HOUR: The number of queries a user can execute in an hour (basically including all statements)

MAX_UPDATES_PER_HOUR: The number of times a user can perform modifications in an hour (only statements that modify databases or tables are included)

MAX_CONNECTIONS_PER_HOUR: The number of times a user can connect to MySQL in an hour

MAX_USER_CONNECTIONS:同一時間連接MySQL實例的數量

Starting from version 5.0.3, the resource limit for user 'user'@'%.example.com' refers to all connections from the example.com domain host to user user, rather than connections from host1.example.com and host2.example.com respectively.

2.2.7 Modify mysql user password

The ways to change the user password include:

mysql> ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
shell> mysqladmin -u user_name -h host_name password "new_password"

Specify a password when creating a user

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

The ways to modify the user password of the current session include:

mysql> ALTER USER USER() IDENTIFIED BY 'mypass';
mysql> SET PASSWORD = PASSWORD('mypass');

2.2.8 Set MySQL user password expiration policy

Set the system parameter default_password_lifetime to apply to all user accounts

• default_password_lifetime=180 Set the expiration date to 180 days
• default_password_lifetime=0 Set password not to expire

If a password expiration policy is set for each user, it will override the above system parameters

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; Password does not expire ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT; Default expiration policy

Manually force a user's password to expire

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

2.2.9 mysql user lock

Set the user's lock status by executing the create user/alter user command with the account lock/unlock clause

The default user status of the Create user statement is unlocked.

# Lock the user mysql when creating it> create user abc2@localhost identified by 'mysql' account lock;

By default, the Alter user statement does not modify the user's lock/unlock status

# Change the user to unlock
mysql> alter user abc2@'localhost' account unlock;

When the client logs in to MySQL using a user in the locked state, it will receive the following error message:
Access denied for user 'user_name'@'host_name'.
Account is locked.

Official documentation: https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html

This is the end of this article about the implementation of MySQL user rights management. For more relevant MySQL user rights management content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!