mysql--Rights Management

When you install MySQL, a database named MySQL is automatically installed. The permissions table is stored under this database.
MySQL assigns each user the appropriate permissions based on the contents of these permission tables
So we're also using these tables to manage users and permissions.

Use mysql;show tables;+---------------------------+| Tables_in_mysql |+---------------------------+|Columns_priv||DB|| Event | | Func | | General_log | | Help_category | | Help_keyword | | help_relation | | Help_topic | | Innodb_index_stats | | Innodb_table_stats | | Ndb_binlog_index | | Plugin | | Proc | | Procs_priv | | Proxies_priv | | Servers | | Slave_master_info | | Slave_relay_log_info | | Slave_worker_info | | Slow_log | |Tables_priv|| Time_zone | | Time_zone_leap_second | | Time_zone_name | | time_zone_transition | | Time_zone_transition_type | |User|+---------------------------+

User table
1. The user table functions fairly well with the "Users Management module" in the MySQL workflow, which determines whether we allow or deny a connection to a
2. The user table is used to record the users account information that is allowed to connect to the server, and the permissions are global level

3. Depending on the user table structure, the table's fields can be divided into 4 categories
(1) User list
HostHost name,UserUser name,PasswordPassword
(2) Permission column (ending with priv)
Include normal permissions: Query permissions, modify permissions, and so on
and advanced administrative permissions: Turn off service permissions, super permissions, load users
These fields have only N and y two options, and the default values are set to n for the security period
Grant_privDo you have grant permissions,Shutdown_privWhether there is permission to stop the MySQL service
Super_privDo you have super privileges,Execute_privWhether there is permission to execute stored procedures and functions
(3) Safety column
(4) Resource control columns
The default value for these fields is 0, which means there is no limit
max_questions How many queries are allowed per hour
max_updates How many updates are allowed per hour
max_connections How many connections are allowed per hour
max_user_connections Number of connections that a single user can have at the same time

desc user;+------------------------+-----------------------------------+------+-----+----| Field | Type | Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+---|Host|char (60) | NO |                       PRI |       | || User | char (16) | NO |                       PRI |       | || Password | Char ( in a)|     NO |                       |       | ||Select_priv|Enum (' N ', ' Y ') |     NO | |       N | ||Insert_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Update_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Delete_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Create_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Drop_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Reload_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Shutdown_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Process_priv| Enum (' N ', ' Y ') |     NO | |       N | ||File_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Grant_priv| Enum (' N ', ' Y ') |     NO | |       N | ||References_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Index_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Alter_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Show_db_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Super_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Create_tmp_table_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Lock_tables_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Execute_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Repl_slave_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Repl_client_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Create_view_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Show_view_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Create_routine_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Alter_routine_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Create_user_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Event_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Trigger_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Create_tablespace_priv| Enum (' N ', ' Y ') |     NO | |       N | ||Ssl_type| Enum ("', ' any ', ' X509 ', ' SPECIFIED ') |NO |          | |Ssl_cipher| Blob |     NO | |       NULL | ||X509_issuer| Blob |     NO | |       NULL | ||X509_subject| Blob |     NO | |       NULL | ||max_questions|Int (one) unsigned |     NO | |       0 | || Max_updates | int (one) unsigned |     NO | |       0 | || max_connections | int (one) unsigned |     NO | |       0 | || max_user_connections | int (one) unsigned |     NO | |       0 | || Plugin | CHAR (64) |     YES | |       Mysql_native_password | || authentication_string | Text |     YES | |       NULL | || password_expired | Enum (' N ', ' Y ') |     NO | | N| |+------------------------+-----------------------------------+------+-----+----------------

db Table
1. The user is allowed to connect, but is it possible to operate all the databases?
2. So we need db table/host table used to specify a user's permissions on a database
3. DB table are divided into two categories:
   (1) User column
        Host hostname, db database name, user user name
Span style= "FONT-SIZE:16PX;" >   (2) The Permissions column
       The user obtains permissions based on the contents of the user table, and then gets the permissions      based on the contents of the DB table;
        For example, the Select_priv field for a user in the Users table is ' N ', which means that all tables in the database do not have permission to query
       However, this user in the DB table is set to ' Y ' for the Select_priv field of the student table, indicating that it has only the permission to query the student table

desc DB;+-----------------------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------------+---------------+------+-----+---------+-------+|Host|char (60) | NO |         PRI |       | || Db | CHAR (64) | NO |         PRI |       | || User | char (+)| NO |         PRI |       | || Select_priv |Enum (' N ', ' Y ') |     NO | |       N | || Insert_priv | Enum (' N ', ' Y ') |     NO | |       N | || Update_priv | Enum (' N ', ' Y ') |     NO | |       N | || Delete_priv | Enum (' N ', ' Y ') |     NO | |       N | || Create_priv | Enum (' N ', ' Y ') |     NO | |       N | || Drop_priv | Enum (' N ', ' Y ') |     NO | |       N | || Grant_priv | Enum (' N ', ' Y ') |     NO | |       N | || References_priv | Enum (' N ', ' Y ') |     NO | |       N | || Index_priv | Enum (' N ', ' Y ') |     NO | |       N | || Alter_priv | Enum (' N ', ' Y ') |     NO | |       N | || Create_tmp_table_priv | Enum (' N ', ' Y ') |     NO | |       N | || Lock_tables_priv | Enum (' N ', ' Y ') |     NO | |       N | || Create_view_priv | Enum (' N ', ' Y ') |     NO | |       N | || Show_view_priv | Enum (' N ', ' Y ') |     NO | |       N | || Create_routine_pRiv | Enum (' N ', ' Y ') |     NO | |       N | || Alter_routine_priv | Enum (' N ', ' Y ') |     NO | |       N | || Execute_priv | Enum (' N ', ' Y ') |     NO | |       N | || Event_priv | Enum (' N ', ' Y ') |     NO | |       N | || Trigger_priv | Enum (' N ', ' Y ') |  NO| |       N | |+-----------------------+---------------+------+-----+---------+-------+

tables_priv table and Columns_priv table
1. Similarly, even if the user has permission to operate on a database, is it possible to manipulate all the tables? Can I manipulate all the records in a table?
2. The tables_priv table is used to set permissions on a single table
3. The Columns_priv table is used to set permissions on a single record (column)
4. The main fields include:
host hostname
db database name
User username
table_name table name
table_priv permission to operate on a table (Select,insert,update,delete,create,drop,grant,references,index,alter)
column_priv permission to operate on a record (select,insert,update,references)
Timestamp time to modify permissions
Grantor The set of permissions

Permission assignment rules for users
(1) the rights assignment for MySQL is assigned according to the order of the user table--->db table--->tables_priv table--->columns_priv table.
(2) If the value of a permission in the user table is Y, you are not allowed to check the future table.
(3) If the value of one of the permissions in the user table is n, then check each table one at a time

mysql--Rights Management