目录
MySQL是一个多用户管理的数据库,可以为不同用户分配不同的权限,分为root用户和普通用户,root用户为超级管理员,拥有所有权限,而普通用户拥有指定的权限。
在 MySQL 数据库中,权限表除了 user 表外,还有 db 表、tables_priv 表、columns_priv 表和 procs_priv 表。
1、user表
联合主键:Host、user.
通常用户信息、修改用户的密码、删除用户及分配权限等就是在mysql数据库的user表中。
-
用户列
Host:主机名,值为%时表示匹配所有主机。
User:用户名。
authentication_string:密码。5.7 版本password 字段改成authentication_string
Host | User | authentication_string |
% | domain_check | *55B565DA3839E5955A68EA96EB735 |
::1 | test | *26C378D308851D5C717C13623EFD6 |
localhost | root | *26C378D308851D5C717C13623EFD6 |
127.0.0.1 | root | *26C378D308851D5C717C13623EFD6 |
(root,%),表示可以远程登录,并且是除服务器外的其他任何终端,%表示任意IP都可登录。
(root,localhost), 表示可以本地登录,即可以在服务器上登陆,localhost则只允许本地登录。
(root,127.0.0.1 ),表示可以本机登陆,即可以在服务器上登陆
(root,sv01),表示主机名为sv1可以登陆,sv01具体指的哪台机器,可以在cat /etc/hostname查看
(root,::1) , 表示本机可以登陆, 看密码都是相同嘛,具体::1代表意义,待查
一个localhost,一个127.0.0.1,一个::1 (是IPv6格式的 127.0.0.1 ),其实这三个都是一个意思,都是表示本地机。
-
权限列
权限列决定了用户的权限,描述了用户在全局范围内允许对数据库和数据库表进行的操作,字段类型都是枚举Enum,值只能是Y或N,Y表示有权限,N表示没有权限。
字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
---|---|---|---|---|
Select_priv | enum('N','Y') | NO | N | 是否可以通过SELECT 命令查询数据 |
Insert_priv | enum('N','Y') | NO | N | 是否可以通过 INSERT 命令插入数据 |
Update_priv | enum('N','Y') | NO | N | 是否可以通过UPDATE 命令修改现有数据 |
Delete_priv | enum('N','Y') | NO | N | 是否可以通过DELETE 命令删除现有数据 |
Create_priv | enum('N','Y') | NO | N | 是否可以创建新的数据库和表 |
Drop_priv | enum('N','Y') | NO | N | 是否可以删除现有数据库和表 |
Reload_priv | enum('N','Y') | NO | N | 是否可以执行刷新和重新加载MySQL所用的各种内部缓存的特定命令,包括日志、权限、主机、查询和表 |
Shutdown_priv | enum('N','Y') | NO | N | 是否可以关闭MySQL服务器。将此权限提供给root账户之外的任何用户时,都应当非常谨慎 |
Process_priv | enum('N','Y') | NO | N | 是否可以通过SHOW PROCESSLIST命令查看其他用户的进程 |
File_priv | enum('N','Y') | NO | N | 是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令 |
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 | 是否可以执行某些强大的管理功能,例如通过KILL命令删除用户进程;使用SET GLOBAL命令修改全局MySQL变量,执行关于复制和日志的各种命令。(超级权限) |
Create_tmp_table_priv | enum('N','Y') | NO | N | 是否可以创建临时表 |
Lock_tables_priv | enum('N','Y') | NO | N | 是否可以使用LOCK TABLES命令阻止对表的访问/修改 |
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 | 是否可以执行CREATE USER命令,这个命令用于创建新的MySQL账户 |
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标准加密安全字段 | |
ssl_cipher | blob | NO | 支持ssl标准加密安全字段 | |
x509_issuer | blob | NO | 支持x509标准字段 | |
x509_subject | blob | NO | 支持x509标准字段 | |
plugin | char(64) | NO | mysql_native_password | 引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户 |
password_expired | enum('N','Y') | NO | N | 密码是否过期 (N 未过期,y 已过期) |
password_last_changed | timestamp | YES | 记录密码最近修改的时间 | |
password_lifetime | smallint(5) unsigned | YES | 设置密码的有效时间,单位为天数 | |
account_locked | enum('N','Y') | NO | N | 用户是否被锁定(Y 锁定,N 未锁定) |
注意:即使 password_expired 为“Y”,用户也可以使用密码登录 MySQL,但是不允许做任何操作。
通常标准的发行版不支持 ssl,读者可以使用 SHOW VARIABLES LIKE "have_openssl" 语句来查看是否具有 ssl 功能。如果 have_openssl 的值为 DISABLED,那么则不支持 ssl 加密功能。
-
资源控制列
资源控制列的字段用来限制用户使用的资源。
字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
---|---|---|---|---|
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 | 规定允许同时建立的连接次数 |
以上字段的默认值为 0,表示没有限制。一个小时内用户查询或者连接数量超过资源控制限制,用户将被锁定,直到下一个小时才可以在此执行对应的操作。可以使用 GRANT 语句更新这些字段的值。
-
常用命令
查询所有用户 mysql> select Host,User,authentication_stringfrom user;查询用户权限:all表示所有权限,select表示只查权限,update表示只改权限,delete表示只删权限等。 mysql> show grants for "user"@"host"; mysql> show grants for "root"@"localhost"; 添加授权用户(新创建的用户,默认情况下是没有任何权限的):使用root用户连接到服务器 mysql> create user "用户名"@"IP地址" identified by "密码"; mysql> create user "haidon"@"%" identified by "123456"; mysql> create user "haidon"@"localhost" identified by "123456"; IP地址的表示方式: 1. % 表示用户可以从任何地址连接到服务器 2. localhost 表示用户只能从本地连接到服务器 3. 指定一个ip表示用户只能从此ip连接到服务器 分配用户权限(给用户授权) mysql> grant 权限列表 on 库.表 to "用户名"@"ip地址" with grant option; mysql> grant all privileges on *.* to "haidon"@"%" with grant option; mysql> grant all privileges on *.* to "haidon"@"%" identified by 'test' with grant option; mysql> grant all privileges on domain_check.tb_user to "haidon"@"localhost" with grant option; mysql> grant select on domain_check.tb_user to "haidon"@"localhost" with grant option; mysql> grant select,insert on domain_check.tb_user to "haidon"@"132.24.98.25" with grant option; 1.权限列表:select、update、delete、insert、alter、drop、create、...(show) 2.库.表:*.*表示所有库的所有表。with grant option表示它具有grant权限。密码是test。 3.如果带了 with grant option,那么用户haidon可以将select ,update权限传递给其他用户( 如xiaodon)。 4.如果没带 with grant option,那么用户haidon不能给用户xiaodon授权。 5.all后面加上privileges,具体到哪些权限时得看MySQL版本,5.7版本不加privileges,8.0版本加privileges。 6.mysql> insert into user values("%","haidon","test","Y","Y","Y","Y","Y","Y","Y","Y","Y","Y"); mysql> flush privileges; 这两句和上面第3句grant的效果是一样的。 7.mysql> insert into user (host,user) values("132.24.98.25","haidon"); mysql> insert into db values("132.24.98.25","haidon","Y","Y","Y","Y","Y","Y","N","N","N","N") mysql> flush privileges; 这三句和上面第6句grant的效果是一样的。 收回用户权限 mysql> revoke all on *.* from "haidon"@"localhost"; mysql> revoke all on domain_check.tb_user from "haidon"@"localhost"; mysql> revoke select on *.* from "haidon"@"localhost"; 删除授权用户 mysql> drop user "用户名"@"ip地址" mysql> drop user "haidon"@"%" mysql> delete from user where user='haidon'; mysql> flush privileges;
2、db表
联合主键:Host、User、Db
db 表比较常用,是 MySQL 数据库中非常重要的权限表,表中存储了用户对某个数据库的操作权限。表中的字段大致可以分为两类,分别是用户列和权限列。
-
用户列
db 表用户列有 3 个字段,分别是 Host、User、Db,标识从某个主机连接某个用户对某个数据库的操作权限,这 3 个字段的组合构成了 db 表的主键。
db 表的用户列如下表所示:
字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
---|---|---|---|---|
Host | char(60) | NO | 无 | 主机名 |
Db | char(64) | NO | 无 | 数据库名 |
User | char(32) | NO | 无 | 用户名 |
-
权限列
db 表中的权限列和 user 表中的权限列大致相同,只是user 表中的权限是针对所有数据库的,而 db 表中的权限只针对指定的数据库。如果希望用户只对某个数据库有操作权限,可以先将 user 表中对应的权限设置为 N,然后在 db 表中设置对应数据库的操作权限。
3、tables_priv表和columns_priv表
tables_priv 表用来对单个表进行权限设置,columns_priv 表用来对单个数据列进行权限设置。
tables_priv 表结构如下表所示:
联合主键:Host、Db、User、Table_name
字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
---|---|---|---|---|
Host | char(60) | NO | 无 | 主机 |
Db | char(64) | NO | 无 | 数据库名 |
User | char(32) | NO | 无 | 用户名 |
Table_name | char(64) | NO | 无 | 表名 |
Grantor | char(93) | NO | 无 | 修改该记录的用户 |
Timestamp | timestamp | NO | CURRENT_TIMESTAMP | 修改该记录的时间 |
Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | 无 | 表示对表的操作权限,包括 Select、Insert、Update、Delete、Create、Drop、Grant、References、Index 和 Alter 等 |
Column_priv | set('Select','Insert','Update','References') | NO | 无 | 表示对表中的列的操作权限,包括 Select、Insert、Update 和 References |
注意:
MySQL的SET字段类型。SET是一个字符串对象,可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。https://blog.csdn.net/kxjrzyk/article/details/51731899
columns_priv 表结构如下表所示:
联合主键:Host、Db、User、Table_name、Column_name
字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
---|---|---|---|---|
Host | char(60) | NO | 无 | 主机 |
Db | char(64) | NO | 无 | 数据库名 |
User | char(32) | NO | 无 | 用户名 |
Table_name | char(64) | NO | 无 | 表名 |
Column_name | char(64) | NO | 无 | 数据列名称,用来指定对哪些数据列具有操作权限 |
Timestamp | timestamp | NO | CURRENT_TIMESTAMP | 修改该记录的时间 |
Column_priv | set('Select','Insert','Update','References') | NO | 无 | 表示对表中的列的操作权限,包括 Select、Insert、Update 和 References |
4、procs_priv表
procs_priv 表可以对存储过程和存储函数进行权限设置,procs_priv 的表结构如表所示:
字段名 | 字段类型 | 是否为空 | 默认值 | 说明 |
---|---|---|---|---|
Host | char(60) | NO | 无 | 主机名 |
Db | char(64) | NO | 无 | 数据库名 |
User | char(32) | NO | 无 | 用户名 |
Routine_name | char(64) | NO | 无 | 表示存储过程或函数的名称 |
Routine_type | enum('FUNCTION','PROCEDURE') | NO | 无 | 表示存储过程或函数的类型,Routine_type 字段有两个值,分别是 FUNCTION 和 PROCEDURE。FUNCTION 表示这是一个函数;PROCEDURE 表示这是一个 存储过程。 |
Grantor | char(93) | NO | 无 | 插入或修改该记录的用户 |
Proc_priv | set('Execute','Alter Routine','Grant') | NO | 无 | 表示拥有的权限,包括 Execute、Alter Routine、Grant 3种 |
Timestamp | timestamp | NO | CURRENT_TIMESTAMP | 表示记录更新时间 |