Mysql's permission tables include: 1. User table, used to record account information that is allowed to connect to the server. All permissions enabled in this table are global level and apply to all databases; 2. db table , stores the user's operating permissions for a certain database; 3. tables_priv table, used to set permissions for a single table; 4. columns_priv table, used to set permissions for a single data column; 5. procs_priv table, used for storage Procedures and stored functions set permissions.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
In the MySQL database, the permission tables include the user table, db table, tables_priv table, columns_priv table and procs_priv table.
user permission table
The user table is the most important permission table in MySQL, used to record account information allowed to connect to the server. It should be noted that all permissions enabled in the user table are global and apply to all databases.
The fields in the user table can be roughly divided into 4 categories, namely user columns, permission columns, security columns and resource control columns.
User column
The user column stores the information that users need to enter when connecting to the MySQL database. It should be noted that MySQL 5.7 version no longer uses Password as the password field, but changed it to authentication_string.
Permission column
The fields in the permission column determine the user's permissions and are used to describe the operations allowed on data and databases in the global scope.
Permissions are roughly divided into two categories, namely advanced management permissions and ordinary permissions:
Advanced management permissions mainly manage the database, such as the permission to close services, Super permissions and loading users, etc.;
Normal permissions mainly operate the database, such as query permissions, modification permissions, etc.
The permission columns of the user table include Select_priv, Insert_ priv and other fields ending with priv. The data type of these field values is ENUM. The only possible values are Y and N: Y represents the user There are corresponding permissions, N means that the user does not have the corresponding permissions. For security reasons, the default value for these fields is N.
If you want to modify the permissions, you can use the GRANT statement to grant some permissions to the user, or you can set the permissions by updating the user table with the UPDATE statement.
Security column
The security column is mainly used to determine whether the user can log in successfully. The security column in the user table is as shown in the following table:
Security columns of the user table
| Field name |
Field type |
Is it empty |
Default value |
Description |
| ssl_type |
enum('','ANY','X509','SPECIFIED') |
NO |
|
Support ssl standard encryption security field |
| ssl_cipher |
blob |
NO |
| Support ssl standard encryption security field |
##x509_issuer | blob | NO | | Support x509 Standard fields |
x509_subject | blob | NO | | Support x509 standard fields |
plugin | #char(64) | NO | mysql_native_password | Introducing plugins for password verification when users connect, plugin Create external/proxy user |
password_expired | enum('N','Y') | NO | N | Whether the password has expired (N has not expired, y has expired) |
password_last_changed | timestamp | YES | | Record the time when the password was last modified |
password_lifetime | smallint(5) unsigned | YES | | Set the password validity time in days |
account_locked | enum('N','Y') | NO | N | Whether the user is locked (Y is locked, N is not locked) |
Note: Even if password_expired is "Y", the user can still use the password to log in to MySQL, but no operations are allowed.
Usually standard distributions do not support ssl. Readers can use the SHOW VARIABLES LIKE "have_openssl" statement to check whether it has ssl functionality. If the value of have_openssl is DISABLED, the ssl encryption feature is not supported.
Resource control column
The fields of the resource control column are used to limit the resources used by users. The resource control columns in the user table are shown in Table 4.
Resource control column of the user table
| Field name |
Field type |
Whether it is empty |
Default value |
Description |
| max_questions |
int(11) unsigned |
NO |
0 |
Specifies the number of query operations allowed per hour |
| max_updates |
int(11) unsigned |
NO |
0 |
Specifies the number of update operations allowed per hour |
| max_connections |
int(11) unsigned |
NO |
0 |
Specifies the number of connection operations allowed per hour |
| max_user_connections |
int(11) unsigned |
NO |
0 |
Specifies the number of connections allowed to be established simultaneously |
The default value of the above fields is 0, indicating no limit. If the number of user queries or connections exceeds the resource control limit within an hour, the user will be locked and cannot perform corresponding operations here until the next hour. The values of these fields can be updated using the GRANT statement.
db table
The db table is commonly used and is a very important permission table in the MySQL database. The table stores the user's permissions for a certain database. operating authority. The fields in the table can be roughly divided into two categories, namely user columns and permission columns.
User column
The db table user column has 3 fields, namely Host, User, and Db, which identify a user connecting to a database from a certain host. The combination of these three fields constitutes the primary key of the db table.
The user list of the db table is as shown in the following table:
| Field name |
Field type |
Is it empty? |
Default value |
Description |
| Host |
char(60) |
NO |
None |
Host name |
##Db | char(64) | NO | None | Database name |
User | char(32) | NO | None | User name |
Permission columns
The permission columns in the db table are roughly the same as those in the user table, except that the Permissions are for all databases, while the permissions in the db table are only for the specified database. If you want the user to have operation permissions only for a certain database, you can first set the corresponding permissions in the user table to N, and then set the operation permissions for the corresponding database in the db table.
tables_priv table and columns_priv table
The tables_priv table is used to set permissions on a single table, and the columns_priv table is used to set permissions on a single data column. set up. The tables_priv table structure is as shown in the following table:
Field name | Field type | Is it empty | Default value | Description |
Host | char(60) | NO | None | Host |
Db | char(64) | NO | None | Database name |
User | char(32) | NO | None | Username |
Table_name | char(64) | NO | None | Table name |
Grantor | char(93) | NO | None | User who modified this record |
Timestamp| | timestamp | NO | CURRENT_TIMESTAMP | The time to modify the record |
Table_priv | set( 'Select','Insert','Update','Delete','Create','Drop','Grant','References',
'Index','Alter','Create View' ,'Show view','Trigger')
| NO | None | indicates the operation permissions on the table, including Select, Insert, Update, Delete, Create, Drop, Grant, References, Index and Alter, etc. |
Column_priv | set('Select','Insert','Update','References') | NO | None | Indicates the operation permissions on the columns in the table, including Select, Insert, Update and References |
##columns_priv The table structure is as shown in the following table:
| Field name |
Field type |
Is it empty |
Default value |
Description |
##Host | char(60) | NO | None | Host |
Db | char(64) | NO | None | Database name |
User | char(32) | NO | None | Username |
Table_name | char(64) | NO | None | Table name |
Column_name | char(64) | NO | None | Data column name, used to specify which data columns have operation permissions |
Timestamp | timestamp | NO | CURRENT_TIMESTAMP | Modify the time of this record |
Column_priv | set('Select','Insert','Update','References') | NO | None | means yes Operation permissions for columns in the table, including Select, Insert, Update and References |
##procs_priv table## The #procs_priv table can set permissions on stored procedures and stored functions. The table structure of procs_priv is as shown in the table:
Field name
Field type | Whether it is empty | Default value | Description |
| Host
char(60) | NO | None | Host Name |
| Db
char(64) | NO | None | Database name |
| User
char(32) | NO | None | Username |
| Routine_name
char(64) | NO | None | Represents the name of a stored procedure or function |
| Routine_type
enum('FUNCTION','PROCEDURE') | NO | None | Indicates the type of stored procedure or function. The Routine_type field has two values, namely FUNCTION and PROCEDURE. FUNCTION indicates that this is a function; PROCEDURE indicates that this is a stored procedure. |
| Grantor
char(93) | NO | None | User who inserted or modified this record |
| Proc_priv
set('Execute','Alter Routine','Grant') | NO | None | indicates the permissions you have, including Execute, Alter Routine, and Grant. |
| Timestamp
timestamp | NO | CURRENT_TIMESTAMP | indicates the record update time |
|
[Related recommendations: mysql video tutorial
]
The above is the detailed content of What are the permission tables of mysql?. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
