时间：2021-07-01 10:21:17 帮助过：1人阅读
MySQL服务器通过权限来控制用户对数据库的访问,权限表存放在MySQL数据库中,由MySQL_install_db脚本初始化.存储账户权限信息表主要有：user,db,host，table_priv、columns_priv、procs_priv。
user表
user表是MySQL中最重要的一个权限表，记录允许连接到服务器的帐号信息，里面的权限是全局级的。
mysql> use mysql; Database changed mysql> DESC user; +------------------------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | 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(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | +------------------------+------------------------------+------+-----+---------+-------+
用户列
user表的用户列包括Host、User、Password。其中User和Host为User表的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配User表中的对应字段，只有3个值都匹配的时候，才允许连接建立。
权限列
权限列的字段决定了用户的权限，描述了全范围内允许对数据和数据库进行的操作。user表中对应的权限针对多有用户数据库的。这些字段值的类型为ENUM，可以取的值只有Y和N，Y表示该用户有对应权限，N表示用户没有对应的权限。
安全列
安全列只有6个字段，其中两个是ssl相关的，两个是x509相关的，另外两个是授权插件相关的。ssl用于加密；x509标准可用于标识用户；Plugin字段标识可用于验证用户身份的插件。
资源控制列
资源控制列的字段用来限制用户使用的资源，包含4各字段：
max_questions：用户每小时允许执行的查询操作次数
max_updates：用户每小时允许执行的更新操作次数
max_connections：用户每小时允许执行的连接操作数
max_user_connections：用户允许同时建立的连接次数
db表和host表
db表和host表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。host表中存储了某个主机对数据库的操作权限。
mysql> USE mysql; Database changed mysql> DESC db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | 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) mysql> DESC host; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | 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 | | | Trigger_priv | enum(‘N‘,‘Y‘) | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+ 20 rows in set (0.00 sec)
用户列
db表用户列有3个字段，分别是Host、User、Db，标识从某主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。
host表不存储用户名称，用户列只有两个字段，分别是Host和Db，表示从某个主机连接的用户对某个数据库的操作权限，其逐渐包括Host和Db两个字段。
权限列
表中create_routine_priv和alter_routine_priv这两个字段表名用户是否有创建和修改存储过程的权限。
user表中的权限是针对所有数据库的，当希望用户只对某个数据库有操作权限，需要将user表中对应的权限设置为N,然后在db表中设置对应数据库的操作权限。
table_priv表和columns_priv表
table_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限。
mysql> DESC tables_priv; +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Table_priv | set(‘Select‘,‘Insert‘,‘Update‘,‘Delete‘,‘Create‘,‘Drop‘,‘Grant‘,‘References‘,‘Index‘,‘Alter‘,‘Create View‘,‘Show view‘,‘Trigger‘) | NO | | | | | Column_priv | set(‘Select‘,‘Insert‘,‘Update‘,‘References‘) | NO | | | | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ 8 rows in set (0.00 sec) mysql> DESC columns_priv; +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Column_name | char(64) | NO | PRI | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | Column_priv | set(‘Select‘,‘Insert‘,‘Update‘,‘References‘) | NO | | | | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+ 7 rows in set (0.00 sec)
| Host、Db、User、Table_name：分别表示主机名、数据库名、用户名和表名 |
| Grantor：修改该记录的用户 |
| Timestamp：修改该记录的时间 |
| Table_priv：对表的操作权限 |
| Column_priv：对表中的列的操作权限 |
procs_priv表
procs_priv表可以对存储过程和存储函数设置操作权限。
mysql> DESC procs_priv; +--------------+----------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Routine_name | char(64) | NO | PRI | | | | Routine_type | enum(‘FUNCTION‘,‘PROCEDURE‘) | NO | PRI | NULL | | | Grantor | char(77) | NO | MUL | | | | Proc_priv | set(‘Execute‘,‘Alter Routine‘,‘Grant‘) | NO | | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------+ 8 rows in set (0.00 sec)
| Host、Db、User：分别表示主机名、数据库名、用户名 |
| Routine_name：存储过程或函数的名称 |
Routine_type：存储过程或函数的类型 |
| Grantor：插入或修改该记录的用户 |
| Prov_priv：拥有的权限 |
| Timestamp：记录更新时间 |
12.2、账户管理
MySQL提供许多语句用来管理用户账号，这些语句用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。
登录和退出MySQL服务器
使用root用户登录到本地mysql服务器的test库中
[root@mylinux ~]# mysql -uroot -p -hlocalhost test Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.5.56-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql>
使用root用户登录到本地mysql服务器的test库中，执行一条查询语句
[root@mylinux ~]# mysql -uroot -p -hlocalhost test -e "DESC person;" Enter password: +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(40) | NO | | | | | age | int(11) | NO | | 0 | | | info | char(50) | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+
新建普通用户
使用CREATE USER语句创建新用户
在执行CREATE USER或CRANT与举止，服务器会修改相应的用户权限表，添加或修改用户及权限。CREATE USER语法格式为：
CREATE USER auth_option [, auth_option] ... auth_option: user@host [ IDENTIFIED BY PASSWORD ‘hash_string‘ IDENTIFIED WITH auth_plugin AS ‘hash_string‘ ]
| user：创建的用户名称 |
| host：允许登录的用户主机名称 |
| IDENTIFIED BY：用来设置用户的密码 |
| PASSWORD:使用哈希值设置密码 |
| hash_string：用户登录时使用的普通明文密码 |
| IDENTIFIED WITH：为用户指定一个身份验证插件 |
| auth_plugin：插件的名称，可以是带单引号的字符串，或者带引号的字符串 |
使用CREATE USER创建一个用户，用户名是jeffrey，密码是mypass，主机名是localhost
mysql> CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘mypass‘; Query OK, 0 rows affected (0.00 sec)
使用CRANT创建新用户
GRANT语句是添加新用户并授权它们访问MySQL对象的首选方法，其语法格式为：
GRANT privileges ON db.table TO user@host [IDENTIFIED BY ‘password‘] [,user [IDENTIFIED BY ‘password‘]] [WITH GRANT OPTION]
使用GRANT语句创建一个新的用户testUser，密码为testpwd。 用户 testUser对所有的数据有查询和更新权限，并授于对所有数据表的SELECT和UPDATE权限 mysql> GRANT SELECT,UPDATE ON *.* TO ‘testUser‘@‘localhost‘ IDENTIFIED BY ‘testpwd‘; Query OK, 0 rows affected (0.00 sec) mysql> SELECT Host,User,Select_priv,Update_priv FROM mysql.user where user=‘testUser‘; +-----------+----------+-------------+-------------+ | Host | User | Select_priv | Update_priv | +-----------+----------+-------------+-------------+ | localhost | testUser | Y | Y | +-----------+----------+-------------+-------------+ 1 row in set (0.00 sec)
直接操作MySQL用户表
使用INSERT语句创建新用户的基本语法为：
INSERT INTO mysql.user(Host,User,Password,[privilegelist]) VALUES(‘localhost‘,‘username‘,PASSWORD(‘password‘),privilegevaluelist)
使用INSERT创建一个新账户，其用户名称为customer1，主机名称为localhost，密码为customer1：
mysql> INSERT INTO user (Host,User,Password) VALUES(‘localhost‘,‘customer1‘,PASSWORD(‘cust omer1‘)); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> SHOW WARNINGS; +---------+------+---------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------+ | Warning | 1364 | Field ‘ssl_cipher‘ doesn‘t have a default value | | Warning | 1364 | Field ‘x509_issuer‘ doesn‘t have a default value | | Warning | 1364 | Field ‘x509_subject‘ doesn‘t have a default value | +---------+------+---------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT host,user,password FROM user; +-----------+-----------+-------------------------------------------+ | host | user | password | +-----------+-----------+-------------------------------------------+ | localhost | root | | | mylinux | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | mylinux | | | | localhost | jeffrey | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | | localhost | testUser | *22CBF14EBDE8814586FF12332FA2B6023A7603BB | | localhost | customer1 | *73DA97747611396FD898E4A7E42B1097B0780646 | +-----------+-----------+-------------------------------------------+ 9 rows in set (0.00 sec)
由于ssl_cipher、x509_issuer、x509_subject这三个字段在user表中没有定义默认值，所以新用户创建失败。
删除普通用户
使用DROP USER删除用户‘jeffrey‘@‘localhost‘
mysql> DROP USER ‘jeffrey‘@‘localhost‘; Query OK, 0 rows affected (0.00 sec) mysql> SELECT host,user,password FROM user; +-----------+-----------+-------------------------------------------+ | host | user | password | +-----------+-----------+-------------------------------------------+ | localhost | root | &nb