DBA-mysql-授权

权限系统介绍

1. 权限系统的作用是授予来自某个主机的某个用户可以查询、插入、修改、删除等数据库操作的权限。
2. 不能明确的指定拒绝某个用户的连接。
3. 权限控制（授权与回收）的执行语句包括create user, grant, revoke
4. 授权后的权限都会存放在Mysql的内部数据库中（数据库名叫mysql），并在数据库启动之后把权限信息复制到内存中，这就是为什么授权完以后需要运行 flush Privileges
5. 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表重要字段解释

1. Plugin,password,authentication_string三个字段存放用户认证信息
2. password_expired设置为Y则表明允许DBA将此用户的密码设置为过期而且过期后要求用户的使用者重置密码(alter user/set password 来重置)
3. password_last_changed作为一个时间戳字段代表密码上次修改时间，执行create user/alter user /set password / grant等命令创建用户或者修改用户密码时此数值更新。
4. password_lifetime代表从password_last_changed时间开始此密码过期的天数。
5. account_locked代表此用户被锁定，无法使用。

procs_priv权限表结构

routine_type是代表存储过程还是函数的类型

系统权限表

1. user,password,authencation_string,db,table_name大小写敏感
2. 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字

授权用户。

1. 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

1. mysql -uroot -p mysql
2. mysql --user=root --password=xxx mysql

创建mysql用户并授权

创建mysql用户由两种方式，

1. 通过create user/grant命令
2. 通过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:

1. 从5.0.3版本开始，对用户'user'@'%.example.com'的资源限制是指所有通过example.com域名主机连接user用户的连接，而不是分别从host1.example.com和host2.example.com的主机过来的连接。
2. 当针对某个用户的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)