preamble
The MySQL privilege table is loaded into memory when the database is started, and when a user is authenticated, the corresponding privileges are accessed in memory, so that this user can do all kinds of operations within the scope of privileges in the database.
Without further ado, let's take a look at the details together!
The mysql privilege system is roughly divided into five tiers.
global level
Global permissions apply to all databases in a given server. These permissions are stored in tables.GRANT ALL ON *. * and REVOKE ALL ON *. * grant and revoke global permissions only.
Database hierarchy
Database permissions apply to all targets in a given database. These permissions are stored in and table.GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke database permissions only.
superficial level
Table permissions apply to all columns in a given table. These permissions 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 permissions only.
hierarchy
Column permissions apply to a single column in a given table. These permissions are stored in the mysql.columns_priv table. When using REVOKE, you must specify the same column as the column being authorized.
Subroutine Hierarchy
The CREATE ROUTINE, ALTER ROUTINE, EXECUTE and GRANT permissions apply to stored subroutines. These permissions can be granted for both the global level and the database level. Also, with the exception of CREATE ROUTINE, these permissions can be granted at the subroutine level and stored in the mysql.procs_priv table.
This permission information is stored in the following system table.
mysql.table_priv
mysql.column_priv
When a user connects in, mysqld verifies the user's privileges with these tables above!
I. Access to the table of rights
In the two processes of accessing permissions, the system uses the three most important permission tables, user, host and db, in the "mysql" database (which was created during the installation of MySQL and is called "mysql").
Of these 3 tables, the most important is the user table, followed by the db table, and the host table is not used in most cases.
The columns in user are divided into four main sections: user, permission, security, and resource control.
The most commonly used columns are the user column and the privilege column, in which the privilege column is divided into general privileges and administrative privileges. Normal privileges are used for database operations, such as select_priv, super_priv, etc.
When a user makes a connection, the permission table is accessed by the following two processes:
First, from the host, user, and password fields in the user table, determine whether the IP, username, and password of the connection exist in the table, if they do, the authentication is passed, otherwise the connection is rejected.
If authenticated, the database privileges are obtained in the following order of the privilege table: user -> db -> tables_priv -> columns_priv.
In these permission tables, the scope of permissions decreases in order, and global permissions override local permissions. The first stage above is well understood, and the second stage is explained in detail below with an example.
For testing purposes, the variable sql_mode needs to be modified, otherwise it will report an error, 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)
// sql_mode has NO_AUTO_CREATE_USER in the default (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. Creation of users
xxx@localhost and give select permission to all tables on all databases
First, check the permission status of 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)
You can find Select_priv: Y in the user table, and N in all others.
No record in the DB table
That is, users who have the same privileges to all databases do not need to be recorded in the db table, but simply need to change select_priv in the user table to "Y". In other words, each privilege in the user table represents a privilege to all databases.
2. Change the permissions on xxx@localhost to select permissions on all tables on 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, we found that the select_priv in the user table is changed to "N", and a record with db xxx is added to the db table. In other words, when granting certain privileges to only a portion of the database, the corresponding privilege columns in the user table remain "N", while the specific database privileges are written to the db table. table and column privilege mechanisms are similar to those of db.
3. tables_priv record 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 row has been added to the tables_priv table, while there are no rows in the three user db columns_priv tables
From the above example, we can see that when a user passes the privilege authentication, when assigning privileges, it will assign privileges in the order of ==user -> db -> tables_priv -> columns_priv==, that is, we will check the global privilege table, user, first. If the corresponding privilege in user is "Y", then the privilege of this user is "Y" for all databases, and db, tables_priv and columns_priv will not be checked anymore; if it is "N", then check the specific databases corresponding to this user in the db table, and get a list of all the databases corresponding to this user. If it is "N", then go to the db table to check the specific database corresponding to this user, and get the privileges in db that are "Y"; if the corresponding privileges in db are "N", then check the privileges in tables_priv and columns_priv in turn, and if all are "N", then check the privileges in db, tables_priv and columns_priv in turn. If all of them are "N", then it is judged that it does not have the privilege.
II. Account management
Authorization grant
grant can be used not only for authorization, but also for creating users.
Authorized Syntax:
grant permission list on library name. Table name to user@host identified by 'password';
Create a user, p1, with permissions to execute all permissions on all databases and connect locally only.
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)
All permissions are "Y" inside the user table except the grant_priv permission.
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 grant privileges
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)
After version 5.7 of the database, the password field is changed to authentication_string
Create a new user, p2, who can connect from any IP address, with privileges to select, update, insert and delete all tables in the db1 database, and an initial password of "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 permissions for the records added to the db table are all "Y", so that only the appropriate permissions are granted to the user, and not too many.
The IP restriction in this example is that all IPs can connect, so it is set to "%". mysql database is controlled by the host field in the user table. host can be an assignment of the following type.
Note: A host value of "%" or null in the user table of the mysql database means that all external IPs can connect to it, but not the local server localhost, so if you want to include the local server, you have to give permissions to localhost separately.
Grant super, process, and file permissions to users 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 privileges belong to administrative privileges, so you can not specify a database, on must be followed by *. *, otherwise an error will be prompted, as above.
So what are these permissions for?
processWith this permission, users can execute the SHOW PROCESSLIST and KILL commands. By default, each user can execute the SHOW PROCESSLIST command, but can only query the user's processes.
You can only execute select . .into outfile and load data infile... operations, but do not grant file, process, super permissions to accounts other than the administrator, as this poses a serious security risk.
SUPER This privilege allows the user to terminate any query; modify SET statements for global variables; use CHANGE MASTER, PURGE MASTER LOGS
Another more specific
usage permissions
Connection (login) privileges, creating a user automatically grants it usage privileges (granted by default).
This privilege can only be used for database login, not to perform any operation; and usage privilege can not be reclaimed, i.e., the ``REVOKE user does not delete users.
View Account Privileges
Once the account has been created, you can view the permissions with 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 Privileges
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 privileges
Deleting the delete permission
The revoke statement reclaims privileges that have already been granted.
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 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 privilege cannot be reclaimed, i.e., the revoke user does not delete users.
To completely delete an account, you can use the drop user
drop user p4@'%';
Account Resource Limitations
When you create a MySQL account, there is also a category of options called Account Resource Limits, which serves to limit the amount of resources that each account can actually have, and the "resources" in this case mainly include:
max_queries_per_hour count : the number of queries executed per hour for 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 can connect to the server per hour
max_user_connections count : the number of concurrent connections to the server by a single account
Attention:
To add users or privileges, use mysql> flush privileges; flush privileges
Specific permissions can be found in the official documentation
/doc/refman/5.7/en/
summarize
Above is the entire content of this article, I hope that the content of this article on your learning or work has a certain reference learning value, if there are questions you can leave a message to exchange, thank you for my support.