In-depth explanation of MySQL user account management and permission management

In-depth explanation of MySQL user account management and permission management

Preface

The MySQL permission table is loaded into the memory when the database is started. After the user passes the identity authentication, the corresponding permissions are accessed in the memory. In this way, the user can perform various operations within the scope of the permissions in the database.

Let’s take a look at the detailed introduction.

The mysql permission system is roughly divided into five levels:

Global Level

Global privileges apply to all databases in a given server. These permissions are stored in the mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.

Database level

Database privileges apply to all objects in a given database. These permissions are stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke database privileges only.

Table Level

Table privileges apply to all columns in a given table. These privileges are stored in the mysql.talbes_priv table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke table privileges only.

Column level

Column permissions apply to a single column in a given table. These privileges are stored in the mysql.columns_priv table. When using REVOKE, you must specify the same columns as those being granted.

Subroutine level

CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT privileges apply to stored routines. These privileges can be granted at the global level or at the database level. Also, except CREATE ROUTINE, these privileges can be granted at the subprogram level and are stored in the mysql.procs_priv table.

These permissions are stored in the following system tables:

mysql.user
mysql.db
mysql.host
mysql.table_priv
mysql.column_priv

When a user connects, mysqld will verify the user's permissions through the above tables!

1. Access to the permission table

In the two processes of permission access, the system will use the three most important permission tables user, host and db in the "mysql" database (created when MySQL is installed, the database name is "mysql").

Among these three tables, the most important one is the user table, followed by the db table. The host table is not used in most cases.

The columns in user are mainly divided into four parts: user column, permission column, security column and resource control column.

The most commonly used columns are user column and permission column, among which the permission column is divided into general permission and management permission. Ordinary permissions are used for database operations, such as select_priv, super_priv, etc.

When a user connects, the access process of the permission table has the following two processes:

First, determine whether the connected IP, username, and password exist in the table from the three fields of host, user, and password in the user table. If so, the identity authentication is passed, otherwise the connection is rejected.

If the authentication is successful, the database permissions are obtained in the following permission table order: user -> db -> tables_priv -> columns_priv.

In these permission tables, the scope of permissions decreases in sequence, and global permissions cover local permissions. The first stage above is easy to understand. The following example will explain the second stage in detail.

In order to facilitate testing, you need to modify the variable sql_mode, otherwise an error will be reported, as follows

MySQL [(none)]> grant select on *.* to xxx@localhost;
ERROR 1133 (42000): Can't find any matching row in the user table
MySQL [(none)]> SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 2 warnings (0.07 sec)

MySQL [(none)]> grant select on *.* to xxx@localhost;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
// The default value of sql_mode is NO_AUTO_CREATE_USER (prevents GRANT from automatically creating a new user unless a password is also specified)
SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

1. Create a user

xxx@localhost, and grant select permissions on all tables in all databases

First check the permission status displayed in the user table

MySQL [mysql]> select * from user where user="xxx" and host='localhost' \G;
*************************** 1. row ***************************
Host: localhost
User: xxx
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type: 
ssl_cipher: 
x509_issuer: 
x509_subject: 
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:mysql_native_password
authentication_string: 
password_expired: N
password_last_changed: 2018-12-03 17:34:49
password_lifetime: NULL
account_locked: N

Check the permission status of the db table again

MySQL [mysql]> select * from db where user="xxx" and host='localhost' \G;
Empty set (0.03 sec)

It can be found that Select_priv: Y in the user table, and all others are N

There is no record in the DB table

That is to say, users who have the same permissions for all databases do not need to be recorded in the db table, but only need to change select_priv in the user table to "Y". In other words, each permission in the user table represents permission for all databases.

2. Change the permissions on xxx@localhost to select permissions on all tables in the db1 database only.

MySQL [mysql]> create database db1;
Query OK, 1 row affected (0.01 sec)

MySQL [mysql]> re^C
MySQL [mysql]> revoke select on *.* from xxx@localhost;
Query OK, 0 rows affected, 1 warning (0.06 sec)

MySQL [mysql]> grant select on db1.* to xxx@localhost;
Query OK, 0 rows affected, 1 warning (0.09 sec)

MySQL [mysql]> select * from user where user='xxx'\G;
*************************** 1. row ***************************
Host: localhost
User: xxx
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
MySQL [mysql]> select * from db where user='xxx'\G;
*************************** 1. row ***************************
Host: localhost
Db: db1
User: xxx
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N

At this time, it is found that select_priv in the user table becomes "N", and a record with db as xxx is added to the db table. That is, when certain permissions are granted only to some databases, the corresponding permission column in the user table remains "N", and the specific database permissions are written to the db table. The table and column permission mechanism is similar to that of db.

3. tables_priv records table permissions

MySQL [db1]> create table t1(id int(10),name char(10));
Query OK, 0 rows affected (0.83 sec)

MySQL [db1]> grant select on db1.t1 to mmm@localhost;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

MySQL [mysql]> select * from user where user='mmm'\G;
*************************** 1. row ***************************
Host: localhost
User: mmm
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
...

MySQL [mysql]> select * from db where user='mmm'\G;
Empty set (0.00 sec)


MySQL [mysql]> select * from tables_priv where user='mmm'\G;
*************************** 1. row ***************************
Host: localhost
Db: db1
User: mmm
Table_name: t1
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select
Column_priv: 
1 row in set (0.00 sec)

ERROR: 
No query specified

MySQL [mysql]> select * from columns_priv where user='mmm'\G;
Empty set (0.00 sec)

You can see that a record is added to the tables_priv table, but there is no record in the user db columns_priv table.

From the above example, we can see that when a user passes the permission authentication and performs permission assignment, the permission assignment will be performed in the order of ==user -> db -> tables_priv -> columns_priv==, that is, the global permission table user is checked first. If the corresponding permission in user is "Y", the permission of this user to all databases is "Y", and db, tables_priv and columns_priv will no longer be checked; if it is "N", the specific database corresponding to this user is checked in the db table, and the permission of "Y" in db is obtained; if the corresponding permission in db is "N", the permissions in tables_priv and columns_priv are checked in turn. If all are "N", it is judged that the permission is not possessed.

2. Account Management

Grant

Grant can not only be used to authorize, but also to create users.

Authorization syntax:

grant permission list on library name.table name to user@host identified by 'password';

Create user p1 with permissions to execute all permissions on all databases and can only connect locally

MySQL [mysql]> grant all privileges on *.* to p1@localhost;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

MySQL [mysql]> select * from user where user='p1'\G
*************************** 1. row ***************************
Host: localhost
User: p1
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type: 
ssl_cipher: 
x509_issuer: 
x509_subject: 
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:mysql_native_password
authentication_string: 
password_expired: N
password_last_changed: 2018-12-03 18:11:01
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)

Except for the grant_priv permission, all permissions are "Y" in the user table.

Add grant permissions to p1

MySQL [mysql]> grant all privileges on *.* to p1@localhost with grant option;
Query OK, 0 rows affected, 1 warning (0.03 sec)

MySQL [mysql]> select * from user where user='p1'\G
*************************** 1. row ***************************
Host: localhost
User: p1
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type: 
ssl_cipher: 
x509_issuer: 
x509_subject: 
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:mysql_native_password
authentication_string: 
password_expired: N
password_last_changed: 2018-12-03 18:11:01
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)

Set a password to grant permissions

MySQL [mysql]> grant all privileges on *.* to p1@localhost identified by '123' with grant option;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

MySQL [mysql]> select * from user where user='p1'\G
*************************** 1. row ***************************
Host: localhost
User: p1
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type: 
ssl_cipher: 
x509_issuer: 
x509_subject: 
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:mysql_native_password
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
password_expired: N
password_last_changed: 2018-12-03 18:14:40
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)

In databases after version 5.7, the password field is changed to authentication_string

Create a new user p2, which can connect from any IP address. The user has the rights to select, update, insert, and delete all tables in the db1 database. The initial password is "123"

MySQL [mysql]> grant select,insert,update,delete on db1.* to 'p2'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

MySQL [mysql]> select * from user where user='p2'\G;
*************************** 1. row ***************************
Host: %
User: p2
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
...
Create_tablespace_priv: N
ssl_type: 
ssl_cipher: 
x509_issuer: 
x509_subject: 
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:mysql_native_password
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
password_expired: N
password_last_changed: 2018-12-03 18:20:44
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)

ERROR: 
No query specified

MySQL [mysql]> select * from db where user='p2'\G;
*************************** 1. row ***************************
Host: %
Db: db1
User: p2
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)

The permissions in the user table are all "N", and the record permissions added in the db table are all "Y". In this way, users are only granted appropriate permissions without being granted excessive permissions.

The IP restriction in this example allows all IPs to connect, so it is set to "%". In the MySQL database, control is performed through the host field of the user table. The host can be assigned the following types of values.

Note: The value of host in the user table of mysql database is "%" or empty, which means that all external IPs can connect, but the local server localhost is not included. Therefore, if you want to include the local server, you must grant permissions to localhost separately.

Grant super, process, and file permissions to user p3@%

MySQL [mysql]> grant super,process,file on db1.* to 'p3'@'%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES


MySQL [mysql]> grant super,process,file on *.* to 'p3'@'%';
Query OK, 0 rows affected (0.03 sec)

These permissions are all management permissions, so you cannot specify a database. The "on" must be followed by *.*, otherwise an error message will be displayed, as shown above.

What are these permissions for?

processWith this privilege, the user can execute the SHOW PROCESSLIST and KILL commands. By default, every user can execute the SHOW PROCESSLIST command, but only the processes of this user can be queried.

Only those with the file permission can execute the select ..into outfile and load data infile... operations. However, do not grant the file, process, and super permissions to accounts other than administrators, as this poses a serious security risk.

The super privilege allows the user to terminate any query; modify the SET statement of global variables; use CHANGE MASTER, PURGE MASTER LOGS

Another special one

usage permissions

Connection (login) permissions, create a user, and the usage permission will be automatically granted to him (granted by default).

This permission can only be used to log in to the database and cannot perform any operations. The usage permission cannot be revoked, that is, REVOKE a user and cannot delete the user.

View account permissions

After the account is created, you can view the permissions by running the following command:

show grants for user@host;
MySQL [mysql]> show grants for p2@'%';
+-------------------------------------------------------------+
| Grants for p2@% |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'p2'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.* TO 'p2'@'%' |
+-------------------------------------------------------------+
2 rows in set (0.00 sec)

Changing Account Permissions

Create a user account p4 with select permissions on all tables in db1

MySQL [mysql]> grant select on db1.* to p4@'%';
Query OK, 0 rows affected, 1 warning (0.01 sec)

MySQL [mysql]> show grants for p4@'%';
+-------------------------------------+
| Grants for p4@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
| GRANT SELECT ON `db1`.* TO 'p4'@'%' |
+-------------------------------------+
2 rows in set (0.00 sec)

Add delete permission

MySQL [mysql]> grant delete on db1.* to p4@'%';
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]> show grants for p4@'%';
+---------------------------------------------+
| Grants for p4@% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
| GRANT SELECT, DELETE ON `db1`.* TO 'p4'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)

Merge with existing select permissions

Remove the delete permission

The revoke statement can revoke the granted permissions.

MySQL [mysql]> show grants for p4@'%';
+---------------------------------------------+
| Grants for p4@% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
| GRANT SELECT, DELETE ON `db1`.* TO 'p4'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)

MySQL [mysql]> revoke delete on db1.* from p4@'%';
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]> show grants for p4@'%';
+-------------------------------------+
| Grants for p4@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
| GRANT SELECT ON `db1`.* TO 'p4'@'%' |
+-------------------------------------+
2 rows in set (0.00 sec)

Can usage be revoke?

MySQL [mysql]> revoke select on db1.* from p4@'%';
Query OK, 0 rows affected (0.02 sec)

MySQL [mysql]> show grants for p4@'%';
+--------------------------------+
| Grants for p4@% |
+--------------------------------+
| GRANT USAGE ON *.* TO 'p4'@'%' |
+--------------------------------+
1 row in set (0.00 sec)

MySQL [mysql]> revoke usage on db1.* from p4@'%';
ERROR 1141 (42000): There is no such grant defined for user 'p4' on host '%'

The usage permission cannot be revoked, that is, revoke a user cannot delete the user.

To completely delete an account, you can use drop user

drop user p4@'%';

Account resource restrictions

When creating a MySQL account, there is another type of option called account resource limit. The purpose of this type of option is to limit the resource limits that each account actually has. The "resources" here mainly include:

max_queries_per_hour count : The number of queries executed per hour by a single account
max_upodates_per_hour count : The number of updates performed per hour for a single account
max_connections_per_hour count : The number of times a single account connects to the server per hour
max_user_connections count: The number of times a single account can concurrently connect to the server

Notice:

Add users or permissions, and use mysql> flush privileges; to refresh permissions.

For specific permissions, please refer to the official website documentation

https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in:
  • How to use DCL to manage users and control permissions in MySQL
  • How to create users and manage permissions in MySQL
  • Example analysis of mysql user rights management
  • Detailed explanation of MySQL user and permission management
  • Detailed explanation of MySQL user rights verification and management methods
  • Detailed explanation of MySQL user rights management
  • Summary of basic user and permission management methods in MySQL
  • Detailed explanation of MySQL user rights management

<<:  Detailed explanation of how to build phalcon environment under nginx server on centos7 system

>>:  25 advanced uses of JS array reduce that you must know

Recommend

Detailed explanation of the use and underlying principles of MySQL table partitions

Table of contents What is a partition table Parti...

MySQL 8.0.16 installation and configuration method graphic tutorial under Windows

This article records the installation graphic tut...

Detailed Analysis of Event Bubbling Mechanism in JavaScript

What is bubbling? There are three stages in DOM e...

Detailed explanation of nginx-naxsi whitelist rules

Whitelist rule syntax: BasicRule wl:ID [negative]...

Detailed explanation of JQuery selector

Table of contents Basic selectors: Level selector...

Introduction to JavaScript conditional access attributes and arrow functions

Table of contents 1. Conditional access attribute...

JavaScript implementation of classic snake game

This article shares the specific code of JavaScri...

Why is UTF-8 not recommended in MySQL?

I recently encountered a bug where I was trying t...

UDP connection object principle analysis and usage examples

I wrote a simple UDP server and client example be...

Ubuntu opens port 22

Scenario You need to use the xshell tool to conne...

Steps to package and deploy the Vue project to the Apache server

In the development environment, the vue project i...

Understanding MySQL Locking Based on Update SQL Statements

Preface MySQL database lock is an important means...

Analysis of the process of deploying pure HTML files in Tomcat and WebLogic

1. First, the pure HTML file must have an entry i...

MySQL NULL data conversion method (must read)

When using MySQL to query the database and execut...

Solve the problem that MySQL read-write separation causes data not to be selected after insert

MySQL sets up independent writing separation. If ...