DBA-mysql-授权
权限系统介绍
- 权限系统的作用是授予来自某个主机的某个用户可以查询、插入、修改、删除等数据库操作的权限。
- 不能明确的指定拒绝某个用户的连接。
- 权限控制（授权与回收）的执行语句包括create user, grant, revoke
- 授权后的权限都会存放在Mysql的内部数据库中（数据库名叫mysql），并在数据库启动之后把权限信息复制到内存中，这就是为什么授权完以后需要运行
flush Privileges - mysql用户认证信息不光包括用户，还要包含连接发起的主机，比如以下两个就是不同的用户
5.1. 'ljf'@'example1.node.com'
5.2. 'ljf'@'example2.node.com'
权限级别介绍
- 全局性的管理权限，作用于整个Mysql实例级别。
- 数据库级别的权限，作用于某个指定的数据库上或者所有的数据库上。
- 数据库对象级别的权限，作用于指定的数据库对象上（表、视图等） 或者所有的数据库对象上。
权限存储在mysql库的user,db,tables_priv,columns_priv,procs_priv这几个系统表中，待mysql实例启动后就加载到内存中。
Mysql权限级别介绍
查看mysql实例默认root用户的权限（来自localhost）
mysql> show grants for root@localhost\G;
too many records ............
mysql> select * from db where user='root' and host='localhost';
Empty set (0.00 sec)
mysql> select * from tables_priv where user='root' and host='localhost';
Empty set (0.00 sec)
mysql> select * from columns_priv where user='root' and host='localhost';
Empty set (0.00 sec)
mysql> select * from procs_pr
Mysql权限详解
- All/All Privileges 权限代表全局或者全数据库对象级别的所有权限。
- Alter权限代表允许修改表结构的权限，但必须要求有create和insert权限配合。如果是rename表名，则要求有alter和drop原表，create和insert新表的权限。
- Alter routine权限代表允许修改或者删除存储过程、函数的权限。
- Create权限代表允许创建新的数据库和表权限。
- Create routine权限代表允许创建存储过程、函数的权限。
- Create table 权限代表允许创建、修改、删除表空间和日志组的权限。
- Create temporary table权限代表允许创建临时表的权限。
- Create user权限代表允许创建、修改、删除、重命名user的权限。
- Create view权限代表允许创建视图的权限。
- Delete权限代表允许删除行数据的权限。
- Drop权限代表允许阐述数据库、表、视图的权限，包括truncate table命令。
- Event权限代表允许查询，创建，修改删除Mysql事件。
- Execute 权限代表允许执行存储过程和函数的权限。
- File权限代表允许在Mysql可以访问的目录进行读写磁盘文件操作，可以使用的命令包括load data infile,select .... into outfile, load file() 函数。
- Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权限。
- Index权限代表是否允许创建和删除索引。
- Insert权限代表是否允许在表里插入数据，同时在执行analyze table,optimize table , repair table语句的时候也需要insert权限。
- Lock权限代表允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或者写。
- Process权限代表允许查看Mysql进程信息，比如执行 show processlist,mysqladmin processlist, show engine 等命令
- Reference权限是在5.7.6版本之后引入的，代表是否允许创建外键。
- Reload权限代表允许执行flush命令，指明重新加载权限表到系统内存中，refresh命令代表关闭和重新开启日志文件并刷新所有的表。
- Replication client权限代表允许执行show master status,show slave status,show binary logs命令。
- Replication slave 权限代表允许slave主机通过此用户连接master以便建立主从复制关系。
- Select权限代表允许重表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select1+1，select PI()+2;而且select权限在执行update、delete语句中含有where条件的情况下也是需要的。
- show databases权限代表通过执行show databases命令查看所有的数据库名。
- show view权限代表通过执行show create view 命令查看视图创建的语句。
- shutdown 权限代表允许关闭数据库实例，执行语句包括mysqladmin shutdown
- super权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令，change master to 创建赋值关系的命令，以及create/alter/drop server等命令。
- Trigger权限表允许创建，删除，执行，显示触发器的权限。
- Update权限表允许修改表中的数据权限。
- Usage权限是创建一个用户之后的默认权限，其本身代表连接登录的权限。
mysql> create user abc@localhost identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> show grants for abc@localhost;
+-----------------------------------------+
| Grants for abc@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `abc`@`localhost` |
+-----------------------------------------+
1 row in set (0.00 sec)
系统权限表
权限存储在mysql库的user,db,tables_priv,columns_priv, procs_priv这几个系统表中，待mysql启动后加载到内存中。
- user表，存放用户账户信息以及全局级别（所有数据）权限，决定了来自哪些主机的那些用户可以访问数据库实例，如果有全局权限则意味着对所有的数据都有此权限。
- DB表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库。
- Tables_priv表，存放表级别的权限，决定了来自哪些主机的哪些用户可以访问这数据库的表。
- Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个字段。
- procs_priv表：存放存储过程和函数级别的权限。
User表重要字段解释
- Plugin,password,authentication_string三个字段存放用户认证信息
- password_expired设置为Y则表明允许DBA将此用户的密码设置为过期而且过期后要求用户的使用者重置密码(alter user/set password 来重置)
- password_last_changed作为一个时间戳字段代表密码上次修改时间，执行create user/alter user /set password / grant等命令创建用户或者修改用户密码时此数值更新。
- password_lifetime代表从password_last_changed时间开始此密码过期的天数。
- account_locked代表此用户被锁定，无法使用。
procs_priv权限表结构
routine_type是代表存储过程还是函数的类型
系统权限表
- user,password,authencation_string,db,table_name大小写敏感
- host,column_name,routine_name大小写不敏感。
举个例子：
create user ABC@localhost identified by '123456';
create user abc@localhost identified by '123456';
上面是创建了2个用户。
create user abc@localhost identified by '123456';
create user abc@Localhost identified by '123456';
上面两条命令还是同一个用户同一个主机。
授权用户与查看用户权限
查看权限
mysql> show grants for abc@localhost;
+-----------------------------------------+
| Grants for abc@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `abc`@`localhost` |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> show create user root@localhost;
'''''省略1W字
授权用户。
- mysql的授权用户由两部分组成，用户名和登录主机。
- 表达用户的语法为'username'@'hostname'
- 单引号不是必须的，但是如果其中包含特殊字符则是必须的
- ''@'localhost'代表匿名登录的用户
- hostname可以使用ipv4和ipv6的主机，::1代表ipv6的主机。
- hostname字段运行使用%匹配字符，比如'%s'代表所有的主机，'%.node.com'代表来自node.com这个域名下的所有主机。''192.168.1.%'表示这个192.168.1的所有网段，也可以写成'192.168.10%'
具体含义参考下表：
修改权限后生效
- 执行grant,revoke,set password,rename user命令修改权限之后，mysql会自动将修改后的权限信息同步加载到系统内存中。
- 如果将执行insert/update/delete操作上述的系统权限表之后，则必须再执行刷新权限命令才能同步到系统内存中，刷新权限命令包括:flush privileges/mysqladmin flush-privileges / mysqladmin reload
- 如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生效。
- 如果是修改database级别的权限，则新权限会在客户端执行use database命令后生效，如果是修改global级别的权限，则需要重新创建连接（也就是重连），新的权限才会生效。
- --skip-grant-tables可以跳过所有的系统权限表而允许所有的用户登录，旨在特殊情况下临时使用。
使用mysql
连接mysql
- mysql -uroot -p mysql
- mysql --user=root --password=xxx mysql
创建mysql用户并授权
创建mysql用户由两种方式，
- 通过create user/grant命令
- 通过insert语句直接操作Mysql系统权限表。
例一：
mysql> create user 'abc1'@localhost identified by '123456' ;
mysql> grant all privileges on *.* to 'abc1'@'localhost' with grant option;
例二：
mysql> create user 'abc2'@'%' identified by '123456';
mysql> grant all privileges on *.* to 'abc2'@'%' with grant option;
例三：
mysql> create user 'abc3'@'localhost' identified by '123456';
mysql> grant reload,process on *.* to 'abc3'@'localhost';
mysql> grant select(id) on test.tmp to abc3@localhost;
例四：
mysql> create user 'custom'@'example1.node.com' identified by '123456';
Query OK, 0 rows affected (0.12 sec)
mysql> grant select,insert,update,delete,create,drop on test.tmp to custom@'example1.node.com' ;
回收用户权限
通过revoke来回收用户权限，语法如下：
revoke 权限名 on 库名.表名 from 用户名@主机名
例子一：
mysql> show grants for custom@'example1.node.com';
+----------------------------------------------------------------------------------------------------+
| Grants for custom@example1.node.com |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `custom`@`example1.node.com` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test`.`tmp` TO `custom`@`example1.node.com` |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke delete on test.tmp from custom@'example1.node.com'; #剔除delete权限
Query OK, 0 rows affected (0.10 sec)
mysql> show grants for custom@'example1.node.com';
+--------------------------------------------------------------------------------------------+
| Grants for custom@example1.node.com |
+--------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `custom`@`example1.node.com` |
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `test`.`tmp` TO `custom`@`example1.node.com` | # delete权限消除啦！
+--------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
删除用户
通过drop来删除用户。语法如下
drop user 用户名@主机名
例子1
mysql> drop user 'custom'@'example1.node.com';
Query OK, 0 rows affected (0.02 sec)
设置mysql用户资源限制
- max_user_connections限制所有用户在同一实际连接mysql实例的数量。通过设置max_user_connections可以限制所有用户在同一实际连接Mysql的实例的数量，但是此参数无法对每个用户区别对待，所以Mysql提供了每个用户的资源限制管理。
- max_queries_per_hour,一个用户在一个小时内可以执行查询的次数（基本包含了所有的语句）。
- max_update_per_hour,一个用户在一个小时内可以执行修改的次数（仅仅包含修改数据库或者表的语句）
- max_connections_per_hour,一个用户在一个小时内可以连接mysql的时间。
- max_user_connections ,一个用户在同一个时间连接mysql实例的数量。
如果不想对某个用户进行限制了，直接把对应的值改为0即可。
ps:
- 从5.0.3版本开始，对用户'user'@'%.example.com'的资源限制是指所有通过example.com域名主机连接user用户的连接，而不是分别从host1.example.com和host2.example.com的主机过来的连接。
- 当针对某个用户的max_user_connections非0时，则忽略全局系统参数max_user_connections，反之则全局系统参数生效。
开始设置用户资源限制。
- 创建用户时进行限制。
mysql> create user 'abc4'@'localhost' identified by '123456' with max_queries_per_hour 20
-> max_updates_per_hour 10
-> max_connections_per_hour 3
-> max_user_connections 2;
- 更改用户的资源限制。
更改后可以通过show create user来查看详细信息。
mysql> alter user 'abc4'@'localhost' with MAX_QUERIES_PER_HOUR 3;
设置mysql用户的密码
alter user 'abc4'@'localhost' identified by 'abc4';- 使用grant和set password在mysql8.0新版不好使了。
设置mysql用户密码过期策略
设置系统参数default_password_lifetime作用于所有的用户账户
- default_password_lifetime=180 设置180天过期
- default_password_lifetime=0 设置0天过期
如果为每个用户设置了密码过期策略，则会覆盖上述系统参数。
alter user 'abc1'@'localhost' password expire interval 90 day;90天过期时间alter user 'abc3'@'localhost' password expire default;默认过期策略alter user 'abc1'@'localhost' password expire never;密码不过期
手动强制某个用户密码过期
alter user 'abc1'@'localhost' password expire;
设置完成后，我们切换用户登录试试看效果
[root@linux-node2 ~]# mysqladmin -uabc3 -hlocalhost password '123456' -p
mysql> select 1+2;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
修改密码后看看效果
mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.21 sec)
# 切换到另一个终端，使用刚才abc3用户的连接
mysql> select 1+2
-> ;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
1 row in set (0.08 sec)
mysql 用户锁定
通过执行create user/alter user 命令中带account locak/unlock子句设置用户的lock状态。
- create user的时候锁定
create user abc5@localhost identified by '123456' account lock;
- alter user的时候锁定
mysql> alter user 'abc2'@localhost account lock;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
请注意，如果报上面的错误（错误代码为1819），请检查这个用户是否存在。
mysql> alter user 'abc3'@localhost account lock; # 存在就会更改成功
Query OK, 0 rows affected (0.09 sec)
