mysql数据库基础管理--数据库权限表

article/2025/10/25 8:26:54

mysql数据库通过权限表实现对mysql数据库的访问，权限表存放在mysql数据库中，有脚本

mysql_install_db初始化。

存储账户权限表主要 USER,DB,HOST,TABLES_PRIV,COLUMNS_PRIV和PROCS_PRIV.

mysql.USER

记录允许连接到数据库的用户信息，里面的权限是全局级的。例如一个用户有update权限，则该用户可以update所有数据库中的任何记录。

mysql> show tables like '%user%';
+--------------------------+
| Tables_in_mysql (%user%) |
+--------------------------+
| user |
+--------------------------+
1 row in set (0.00 sec)

mysql> show full tables like '%user%';
+--------------------------+------------+
| Tables_in_mysql (%user%) | Table_type |
+--------------------------+------------+
| user | BASE TABLE |
+--------------------------+------------+
1 row in set (0.00 sec)

mysql> desc mysql.user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-
------+
| Field | Type | Null | Key | Default |
Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-
------+
| Host | char(255) | NO | PRI | |
|
| User | char(32) | 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 |
|
| 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 unsigned | NO | | 0 |
|
| max_updates | int unsigned | NO | | 0 |
|
| max_connections | int unsigned | NO | | 0 |
|
| max_user_connections | int unsigned | NO | | 0 |
|
| plugin | char(64) | NO | | caching_sha2_password |
|
| authentication_string | text | YES | | NULL |
|
| password_expired | enum('N','Y') | NO | | N |
|
| password_last_changed | timestamp | YES | | NULL |
|
| password_lifetime | smallint unsigned | YES | | NULL |
|
| account_locked | enum('N','Y') | NO | | N |
|
| Create_role_priv | enum('N','Y') | NO | | N |
|
| Drop_role_priv | enum('N','Y') | NO | | N |
|
| Password_reuse_history | smallint unsigned | YES | | NULL |
|
| Password_reuse_time | smallint unsigned | YES | | NULL |
|
| Password_require_current | enum('N','Y') | YES | | NULL |
|
| User_attributes | json | YES | | NULL |
|
+--------------------------+-----------------------------------+------+-----+-----------------------+-
------+
51 rows in set (0.00 sec)

用户列

用户列包括host，user，password字段，分别表示主机，用户名，密码。修改用户密码实际就是修改password字段。

权限列

权限列字段决定了用户的权限，描述了在全局范围内允许对数据库进行的操作，包括查询、修改、关闭数据库、超级权限、和加载用户等高级权限，普通权限用于数据库操作数据库，高级权限用于数据库管理。（复制权限）

安全列

SSL用于加密，X509标准用于标识用户，PLUGIN字段用于验证用户身份的插件

mysql> show variables like '%openssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

查询是否支持SSL

资源控制列

max_questions用户每小时允许执行查询次数。

max_updates用户每小时允许执行的更新次数

max_connections用户每小时允许执行连接的操作次数

max_user_connections用户允许同时建立连接次数

可以使用grant语句更新这些字段值。

grant usage on *.* TO USER1@LOCAHOST MAX_USER_CONNECTIONS 200;

DB表和host表

db表和host表示mysql数据中重要的权限表，db表存储了用户对某个数据库的操作权限。

mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+-----------------------+---------------+------+-----+---------+-------
| Host | char(255) | NO | PRI | |
| Db | char(64) | NO | PRI | |
| User | char(32) | 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 |
+-----------------------+---------------+------+-----+---------+-------
22 rows in set (0.00 sec)