MySQL用户及权限
- 一、MySQL用户认证
- 二、MySQL的权限分类及存储
- 三、 MySQL访问控制
- 1、用户连接时的检查
- 2、执行SQL语句时的检查
- 四、权限表字段详解
- 五、用户管理实践
- 六、MySQL管理员密码找回
- 七、 MySQL 权限管理实践
- 1. grant 所有权限
- 2. grant super权限在*.*上(super权限可以对全局变量更改);
- 3. grant某个库下所有表的所有权限
- 4. grant某个库下所有表的select权限
- 5. grant某个库下某个表的insert权限
- 6. grant某个库下某个表的update权限
- 7. grant某个库下某个表的某个字段update权限
- 8.通过GRANT语句中的USAGE权限,可以创建账户而不授予任何权限
- 9. grant创建、修改、删除MySQL数据表结构权限
- 10. grant操作MySQL外键权限
- 11. grant操作MySQL临时表权限
- 12. grant操作MySQL索引权限
- 13.grant操作MySQL视图、查看视图源代码权限
- 14. grant操作MySQL存储过程、存储函数权限
- 15.PROXY特殊权限
- 16. 查看用户的权限
- 17. 移除用户权限
- 八、MySQL 用户和权限管理经验
一、MySQL用户认证
MySQL的用户认证形式是: 用户名+主机。比如test@127.0.0.1和test@192.168.10.10是不一样的用户。就好比现实中的牛家村的张三和马家村的张三是分别两个人一样。MySQL中的权限分配都是分配到用户+主机的实体上。MySQL的主机信息可以是本地(localhost),某个IP,某个IP段,以及任何地方等,即用户的地址可以限制到某个具体的IP,或者某个IP范围,或者任意地方。MySQL用户分为普通用户和root用户。root用户是超级管理员,拥有所有权限,普通用户只拥有被授予的各种权限。
二、MySQL的权限分类及存储
1.MySQL用户权限层级
- 全局层级:全局权限适用于一个给定MySQL Server中的所有数据库,这些权限存储在mysql.user表中。
GRANT ALL ON *.* TO 'user'@'host'; # *.* 表示数据库库的所有库和表,对应权限存储在mysql.user表中
- 数据库层级:数据库权限适用于一个给定数据库中的所有目标,这些权限存储在mysql.db表中。
GRANT ALL ON mydb.* TO 'user'@'host'; #mydb.* 表示mysql数据库下的所有表,对应权限存储在mysql.db表中
- 表层级:表权限适用于一个给定表中的所有列,这些权限存储在mysql.tables_priv表中。
GRANT ALL ON mydb.mytable TO 'user'@'host'; #mydb.mytable 表示mysql数据库下的mytable表,对应权限存储在mysql.tables_priv表
- 列层级:列权限使用于一个给定表中的单一列,这些权限存储在mysql.columns_priv表中。
GRANT ALL (col1, col2, col3) ON mydb.mytable TO 'user'@'host'; #mydb.mytable 表示mysql数据库下的mytable表, col1, col2, col3表示mytable表中的列名
- 子程序层级:CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且,除了CREATE ROUTINE外,这些权限可以被授予子程序层级,并存储在mysql.procs_priv表中。
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'user'@'host'; #mydb.mytable 表示mysql数据库下的mytable表,PROCEDUR表示存储过程
2. MySQL权限简单分类
- 数据权限分为:库、表和字段三种级别
- 管理权限主要是管理员要使用到的权限,包括:数据库创建,临时表创建、主从部署、进程管理等
- 程序权限主要是触发器、存储过程、函数等权限。
3. MySQL权限详情
第一列表示可以在grant命令中制定的权限第二列对应着几张权限表(user,db,tables_priv, columns_priv, procs_priv)中的列第三列表示权限的作用范围,其中Global(Server administration)对应 mysql.user 表,Database 对应 mysql.db 表,Tables 对应 mysql.tables_priv 表,Columns 对应 mysql.columns_priv 表,Stored routines 对应 mysql.procs_priv 表。
MYSQL的权限如何分布,就是针对表可以设置什么权限,针对列可以设置什么权限等等,这个可以从官方文档中的一个表来说明:
权限分布可能设置的权限表权限Select, Insert, Update, Delete, Create, Drop, Grant, References, Index, Alter列权限Select, Insert, Update, References程序权限Execute, Alter Routine, Grant
1、具体权限列表
三、 MySQL访问控制
MySQL访问控制分为两个阶段:
- 用户连接检查阶段
- 执行SQL语句时检查阶段
1、用户连接时的检查
1)当用户连接时,MySQL服务器首先从user表里匹配host, user, password,匹配不到则拒绝该连接2)接着检查user表的max_connections和max_user_connections,如果超过上限则拒绝连接3)检查user表的SSL安全连接,如果有配置SSL,则需确认用户提供的证书是否合法只有上面3个检查都通过后,服务器才建立连接,连接建立后,当用户执行SQL语句时,需要做SQL语句执行检查。
2、执行SQL语句时的检查
1)从user表里检查max_questions和max_updates,如果超过上限则拒绝执行SQL下面几步是进行权限检查:2)首先检查user表,看是否具有相应的全局性权限,如果有,则执行,没有则继续下一步检查3)接着到db表,看是否具有数据库级别的权限,如果有,则执行,没有则继续下一步检查4)最后到tables_priv, columns_priv, procs_priv表里查看是否具有相应对象的权限从以上的过程我们可以知道,MySQL检查权限是一个比较复杂的过程,所以为了提高性能,MySQL的启动时就会把这5张权限表加载到内存。
四、权限表字段详解
1.user表
user表的权限是基于服务器范围的所有权限,比如用户拥有服务器中所有数据库的select权限那么在user表中的Select_priv列为Y,如果用户单单只拥有某个一数据库的select权限那么user表中的Select_priv为N,会在DB表中记录一条信息在DB表中的select_priv为Y。
desc mysql.user;
2.db表
如果授予一个用户单独某个数据库的权限,就会在db表中记录一条相关信息。
desc mysql.db;
3.tables_priv表
desc mysql.tables_priv;
上面的Column_priv比较奇怪,因为照理说tables_priv只显示表级别的权限,列级别的权限应该在columns_priv里显示才对。后来查了资料才知道,原来这是为了提高权限检查时的性能,试想一下,权限检查时,如果发现tables_priv.Column_priv为空,就不需要再检查columns_priv表了,这种情况在现实中往往占大多数。
4. columns_priv表
desc mysql.columns_priv;
5. procs_priv表
desc mysql.procs_priv;
五、用户管理实践
1.用户创建
- 通过create user语句创建用户
在执行CREATE USER或CRANT语句后,MySQL服务器会修改相应的用户权限表,添加或修改用户及权限。
create user 'USERNAME'@'HOST' identified by 'PASSWORD';
HOST的形式:1. IP地址,如172.16.16.1;2. 主机名,如localhost;3. 网络地址,如172.16.0.04. 通配符,如%:匹配任意字符_:匹配任意单个字符如172.16.16._(允许172.16.16.1-172.16.16.9)
eg:
mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.00 sec)
- 通过grant语句创建新用户
GRANT语句是添加新用户并授权它们访问MySQL对象的首选方法,其语法格式为:
grant all on DB_NAME.TABLE_NAME to 'USERNAME'@'HOST' identified by 'PASSWORD';
HOST的表现形式和create user一样
eg:
# 用户 testUser对所有的数据有查询和更新权限,并授于对所有数据表的SELECT和UPDATE权限
mysql> GRANT SELECT,UPDATE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd';
Query OK, 0 rows affected (0.00 sec)
1) 创建root用户
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION;
mysql> flush privileges;
2). 创建一个基本的增删改查用户
mysql> GRANT UPDATE, DELETE, INSERT, SELECT ON *.* TO 'test'@'%' identified by 'test' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
mysql> flush privileges;
# MAX_QUERIES_PER_HOUR,MAX_CONNECTIONS_PER_HOUR,MAX_UPDATES_PER_HOUR设置为0表示不限制
3). 创建数据库基本的增删改查用户
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW, EXECUTE ON `db_name`.* TO 'test'@'%' identified by 'test';
mysql> flush privileges;
4). 授予数据库名以db开头的数据库的权限
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW, EXECUTE ON `db%`.* TO 'perform'@'%';
mysql> flush privileges;
5). 创建备份用户权限
mysql> GRANT SELECT,EVENT,SHOW DATABASES,LOCK TABLES,SUPER,REPLICATION CLIENT ON *.* TO 'backup'@'localhost' identified by 'backup';
mysql> flush privileges;
6). 备份恢复用户权限
mysql> GRANT INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'restore'@'localhost' identified by '123456';
mysql> flush privileges;
7). 复制用户权限
mysql> GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';
mysql> flush privileges;
2.用户删除
mysql> drop user 'USERNAME'@'HOST';
# 删除MySQL默认的无用账户;
mysql> drop user 'root'@'localhost.localdomain';
# 删除MySQL默认的无用账户;
mysql> drop user 'root'@'127.0.0.1';
3. 更改用户名
mysql> rename user OLD_NAME to NEW_NAME;
4. 修改用户密码
- 通过mysqladmin工具
# 给root@localhost用户登录mysql设置密码为"redhat";
$ mysqladmin -u root -h localhost password "redhat"
# 修改root@localhost用户登录mysql数据库的密码;
$ mysqladmin -u root -h localhost password "new passwd" -p "old passwd"
- 通过直接修改mysql.user表的用户记录
# MySQL 5.6
mysql> update mysql.user set password=PASSWORD('redhat') where user='root';
mysql> flush privileges;
# MySQL 5.7
mysql> update mysql.user set authentication_string=PASSWORD('redhat') where user='root';
mysql> flush privileges;
- set password语句
mysql> set password for 'root'@'localhost'=PASSWORD('redhat');
mysql> flush privileges;
- ALTER USER语句(MYSQL5.7版本)
mysql> use mysql
mysql> alter user root@'localhost' identified by '123456';
mysql> flush privileges;
六、MySQL管理员密码找回
1.修改配置文件,跳过授权表在配置文件中[mysqld]字段添加skip-grant-tables指令
$ cat /etc/my.cnf
[mysqld]
skip-grant-tables
2. 重启MySQL Server
service mysqld restart
3. 给root用户登录mysql设置密码为helloWORD并以加密方式
mysql> use mysql;
# MySQL5.6
mysql> update user set password=PASSWORD('helloWORD') where user='root';
# MySQL5.7
mysql> update mysql.user set authentication_string=PASSWORD('helloWORD') where user='root';
mysql> flush privileges;
4.修改配置文件,注释刚才添加的配置项
$ cat /etc/my.cnf
[mysqld]
#skip-grant-tables
5.重启MySQL Server
service mysqld restart
七、 MySQL 权限管理实践
账户权限信息被存储在MySQL数据库的几张权限表中,在MySQL启动时,服务器将这些数据库表中权限信息的内容读入内存。其中GRANT和REVOKE语句所涉及的常用权限大致如下这些:CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、INDEX、ALTER、CREATE、ROUTINE、FILE等,还有一个特殊的proxy权限,是用来赋予某个用户具有给他人赋予权限的权限。
1. grant 所有权限
mysql> grant all privileges on *.* to 'USERNAME'@'HOST';
mysql> flush privileges;
2. grant super权限在*.*上(super权限可以对全局变量更改);
mysql> grant super on *.* to 'USERNAME'@'HOST';
mysql> flush privileges;
3. grant某个库下所有表的所有权限
mysql> grant all privileges on DB_NAME.* to 'USERNAME'@'HOST';
mysql> flush privileges;
4. grant某个库下所有表的select权限
mysql>grant select on DB_NAME.* to 'USERNAME'@'HOST';
mysql> flush privileges;
5. grant某个库下某个表的insert权限
mysql> grant insert on DB_NAME.TABLE_NAME to 'USERNAME'@'HOST';
mysql> flush privileges;
6. grant某个库下某个表的update权限
mysql>grant update on DB_NAME.TABLE_NAME to 'USERNAME'@'HOST';
mysql> flush privileges;
7. grant某个库下某个表的某个字段update权限
mysql> grant update(COLUMN_NAME) on DB_NAME.TABLE_NAME to 'USERNAME'@'HOST';
mysql> flush privileges;
8.通过GRANT语句中的USAGE权限,可以创建账户而不授予任何权限
mysql> grant usage on *.* to 'USERNAME'@'HOST';
mysql> flush privileges;
9. grant创建、修改、删除MySQL数据表结构权限
mysql> grant create on testdb.* to developer@'192.168.0.%';
mysql> grant alter on testdb.* to developer@'192.168.0.%';
mysql> grant drop on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;
10. grant操作MySQL外键权限
mysql> grant references on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;
11. grant操作MySQL临时表权限
mysql> grant create temporary tables on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;
12. grant操作MySQL索引权限
mysql> grant index on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;
13.grant操作MySQL视图、查看视图源代码权限
mysql> grant create view on testdb.* to developer@'192.168.0.%';
mysql> grant show view on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;
14. grant操作MySQL存储过程、存储函数权限
mysql> grant create routine on testdb.* to developer@'192.168.0.%';
mysql> grant alter routine on testdb.* to developer@'192.168.0.%';
mysql> grant execute on testdb.* to developer@'192.168.0.%';
mysql> flush privileges;
15.PROXY特殊权限
如果想让某个用户具有给他人赋予权限的能力,那么就需要proxy权限了。当你给一个用户赋予all权限之后,你查看mysql.user表会发现Grant_priv字段还是为N,表示其没有给他人赋予权限的权限。
我们可以查看一下系统默认的超级管理员权限:
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
可以看到其本身有PROXY权限,并且这个语句跟一般授权语句还不太一样。所以如果想让一个远程用户有给他人赋予权限的能力,就需要给此用户PROXY权限,如下:
mysql> grant all on *.* to 'test'@'%' identified by 'helloWORD';
mysql> GRANT PROXY ON ''@'' TO 'test'@'%' WITH GRANT OPTION;
mysql> flush privileges;
16. 查看用户的权限
Mysql> show grants for 'USERNAME'@'HOST';
17. 移除用户权限
# 移除tom用户对于db.xsb的权限;
Mysql> revoke all on db.xsb from 'tom'@'localhost';
# 刷新授权表;
Mysql> flush privileges;
使用REVOKE收回权限之后,用户帐户的记录将从db、host、tables_priv、columns_priv表中删除,但是用户帐号依然在user表中保存。
八、MySQL 用户和权限管理经验
1. 用户管理经验
- 1)、尽量使用create user, grant等语句,而不要直接修改权限表。
虽然create user, grant等语句底层也是修改权限表,和直接修改权限表的效果是一样的,但是,对于非高手来说,采用封装好的语句肯定不会出错,而如果直接修改权限表,难免会漏掉某些表。而且,修改完权限表之后,还需要执行flush privileges重新加载到内存,否则不会生效。
- 2). 把匿名用户删除掉。
匿名用户没有密码,不但不安全,还会产生一些莫名其妙的问题,强烈建议删除。
2. 权限管理经验
- 1)、只授予能满足需要的最小权限,防止用户干坏事。比如用户只是需要查询,那就只给select权限就可以了,不要给用户赋予update、insert或者delete权限。
- 2)、创建用户的时候限制用户的登录主机,一般是限制成指定IP或者内网IP段。
- 3)、初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户,这些用户默认没有密码。
- 4)、为每个用户设置满足密码复杂度的密码。
- 5)、定期清理不需要的用户,回收权限或者删除用户。