MySQL授权认证+数据库管理

MySQL授权认证

MySQL权限系统介绍

权限系统的作用是授权来自某个主机的某个用户可以查询、插入、修改、删除等数据库操作的权限。
不能明确的指定拒绝某个用户的连接
权限控制(授权与回收)的执行语句包括create user,grant,revoke
授权后的权限都会存放在MySQL的内部数据库中（数据库名叫mysql），并在数据库启动之后把权限信息复制到内存中
MySQL用户的认证信息不光包括用户名，还要包含连接发起的主机（以下两个joe被认为不是同一个用户）
- SHOW GRANTS FOR 'joe'@'office.example.com';
- SHOW GRANTS FOR 'joe'@'home.example.com';

查看用户：

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

创建与删除用户：

#创建用户并授权
mysql> create user admin@'10.0.0.51' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

#删除用户
mysql> drop user admin@'10.0.0.51';
Query OK, 0 rows affected (0.00 sec)

查询与插入权限：

#admin@'10.0.0.1'用户对course有查询权限
mysql> grant select on course.* to admin@'10.0.0.1';
Query OK, 0 rows affected (0.00 sec)

#admin@'10.0.0.2'用户对course有插入权限
mysql> grant insert on course.* to admin@'10.0.0.2';

MySQL权限级别

全局性的管理权限，作用于整个MySQL实例级别
数据库级别的权限，作用域某个指定的数据库上或者所有的数据库上
数据库对象级别的权限，作用于指定的数据库对象上（表、视图等）或者所有的数据库对象上

系统权限表

权限存储在mysql库的 user，db，tables_priv，columns_priv，and proces_priv这几个系统表中，待MySQL实例启动后就加载到内存中

user表：存放用户信息以及全局信息（所有数据库）权限，决定了来自哪里主机的哪些用户可以访问数据库实例，如果有全局权限则意味这对所有数据库都有此权限
Db表：存放数据库级别的权限，决定了来自哪里主机的哪些用户可以访问此数据库
Tables_priv表：存放表级别的权限，决定了来自哪里主机的哪些用户可以访问数据库的这个表
Columns_priv表：存放列级别的权限，决定了来自哪些数据的哪些用户可以访问数据库表的这个字段
Procs_priv表：存放存储过程和函数级别的权限

MySQL修改权限的生效

执行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可以跳过所有系统权限表才允许所有用户登录，只在特殊情况下暂停使用

全局权限授予与撤销：

#全局查询权限和插入授予10.0.0.1
mysql> grant select,insert on *.* to admin@'10.0.0.51';
Query OK, 0 rows affected (0.00 sec)


#查看有哪些权限
mysql> show grants for admin@'10.0.0.51';
+----------------------------------------------------+
| Grants for admin@10.0.0.51                         |
+----------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'admin'@'10.0.0.51' |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from mysql.user where user='admin'\G;

#撤销权限
mysql> revoke select on *.* from admin@'10.0.0.51';
Query OK, 0 rows affected (0.01 sec)

#现在只能插入，不能查询
mysql> show grants for admin@'10.0.0.51';
+--------------------------------------------+
| Grants for admin@10.0.0.51                 |
+--------------------------------------------+
| GRANT INSERT ON *.* TO 'admin'@'10.0.0.51' |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from mysql.user\G;

数据库级别授权与撤销：

#10.0.0.51用户对test库有更新权限
mysql> grant update on test.* to admin@'10.0.0.51';
Query OK, 0 rows affected (0.00 sec)

#查看
mysql> show grants for admin@'10.0.0.51';
+----------------------------------------------------+
| Grants for admin@10.0.0.51                         |
+----------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'admin'@'10.0.0.51' |
| GRANT UPDATE ON `test`.* TO 'admin'@'10.0.0.51'    |
+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from mysql.db where user='admin'\G;

#撤销授权
mysql> revoke update on test.* from admin@'10.0.0.51';
Query OK, 0 rows affected (0.00 sec)

#查询权限表
mysql> select * from mysql.db where user='admin'\G;

数据库对象级别授权与撤销：

#10.0.0.51用户表级别授权
mysql> grant delete on world.city to admin@'10.0.0.51' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

#查询权限
mysql> show grants for admin@'10.0.0.51';
+-------------------------------------------------------+
| Grants for admin@10.0.0.51                            |
+-------------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'admin'@'10.0.0.51'    |
| GRANT UPDATE ON `test`.* TO 'admin'@'10.0.0.51'       |
| GRANT UPDATE ON `mysql`.* TO 'admin'@'10.0.0.51'      |
| GRANT DELETE ON `world`.`city` TO 'admin'@'10.0.0.51' | <----刚刚授权过的权限
+-------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from mysql.tables_priv\G;

#删除权限
mysql> delete from world.city where name='Wuhan';
Query OK, 1 row affected (0.00 sec)
#查询
mysql> select * from world.city where name='Wuhan';
Empty set (0.00 sec)

#更新到内存
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#查询权限表
mysql> select * from mysql.tables_priv where user='admin'\G;

授权列层级的权限：

#授权列级name和id的查询权限
mysql> grant select (name,id) on world.city to admin@'10.0.0.51';
Query OK, 0 rows affected (0.00 sec)

#查看用户权限
mysql> show grants for admin@'10.0.0.51';
+--------------------------------------------------------------------------+
| Grants for admin@10.0.0.51                                               |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'10.0.0.51'                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.* TO 'admin'@'10.0.0.51' |
| GRANT SELECT (name, id) ON `world`.`city` TO 'admin'@'10.0.0.51'         | <---添加的权限
+--------------------------------------------------------------------------+
3 rows in set (0.00 sec)

#添加到内存
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#查询表
mysql> select * from mysql.columns_priv;
+-----------+-------+-------+------------+-------------+---------------------+-------------+
| Host      | Db    | User  | Table_name | Column_name | Timestamp           | Column_priv |
+-----------+-------+-------+------------+-------------+---------------------+-------------+
| 10.0.0.51 | world | admin | city       | id          | 0000-00-00 00:00:00 | Select      |
| 10.0.0.51 | world | admin | city       | name        | 0000-00-00 00:00:00 | Select      |
+-----------+-------+-------+------------+-------------+---------------------+-------------+
2 rows in set (0.00 sec)

注：权限是向下继承的

数据库管理

MySQL用户连接

连接数据库创建密码：

#修改密码
mysqladmin -uroot -p '旧密码' '新密码'
#设置新密码
mysqladmin -u root -p password 密码

#内部设置密码
set password for 用户@localhost=password('新密码');

#通过user表修改密码
use mysql;
update user set password=password('新密码') where user='用户';

连接数据库方式

socket（本地登录使用）

#使用本地socket文件登录
[root@db01 ~]# mysql -uroot -p123456 -S /tmp/mysql.sock

TCP/IP登录

#root连接本地登录
[root@db01 ~]# mysql -uroot -p123456 -hlocalhost

直接连接到数据库

[root@db01 ~]# mysql -uroot -p mysql 
Enter password: 
#查看表
mysql> show tables;

MySQL常用参数介绍

参数	介绍
-u	用户名
-p	密码
-S	socket文件目录
-h	ip地址
-P	端口号（默认3306）
-e	免交互执行mysql里的命令，不用登录进去例 mysql -uroot -p123 -e "show databases;"
<	将sql文件导入数据库中例 mysql -uroot -p123

MySQL客户端内置功能

参数	介绍
help	打印帮助
/G	将行转成列显示
Ctrl+C	返回新的命令行（终止命令）
Ctrl+D，exit，quit	退出登录
source	导入脚本（source /root/word.sql）

创建MySQL用户

有两种方式创建MySQL授权用户

执行create user/grant命令（推荐方式）
通过insert语句直接操作MySQL系统权限表

#创建用户
mysql> create user 'root'@'localhost' identified by '123456';
#授权所有库和表的权限都有
mysql> grant all privileges on *.* to 'root'@'localhost' with grant option;


mysql> create user 'test'@'localhost' identified by '123456';
mysql> grant all on *.* to test@'%' identified by '123456';
mysql> grant all privileges on *.* to 'test'@'localhost' with grant option;
mysql> create user 'admin'@'localhost' identified by '123456';
mysql> create reload,process on *.* to 'admin'@'localhost';
mysql> create user 'html'@'localhost';

revoke回收权限

revoke根grant语法差不多，只需要把关键字 " to " 换成 "from" 即可，并且revoke语句不需要跟密码设置。

注：revoke可以回收所有权限，可以回收部分权限

#撤销对所有库的删除权限（其他权限中间加逗号连接）
mysql> revoke delete on *.* from admin@'10.0.0.51';
Query OK, 0 rows affected (0.00 sec)

#查看权限
mysql> show grants for admin@'10.0.0.51';
+--------------------------------------------------------------------------+
| Grants for admin@10.0.0.51                                               |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'10.0.0.51'                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.* TO 'admin'@'10.0.0.51' |
| GRANT SELECT (name, id) ON `world`.`city` TO 'admin'@'10.0.0.51'         |
+--------------------------------------------------------------------------+
3 rows in set (0.00 sec)

#删除所有权限
mysql> revoke all on *.* from admin@'10.0.0.51';
mysql> flush privileges

注意事项：

grant，revoke用户权限后，该用户只有重新连接MySQL数据库，权限才能生效。
如果想让授权的用户，也可以将这些权限给grant给其他用户，那么授权时需要添加选项"grant option"！

mysql> grant insert,select,update,alter on test.* to admin@'10.0.0.51' identified by '123456' with grant option;

删除用户

通过执行drop user命令删除MySQL用户

mysql> drop user 'admin'@'10.0.0.51';

设置MySQL用户资源限制

通过设置全局变量 max_user_connections 可以限制所有用户在同一时间连接MySQL实例的数量，但此参数无法对每个用户区别对待，所以MySQL提供了对每个用户的资源限制管理
MAX_QUERIES_PER_HOUR：一个用户在一个小时内可以查询的次数（基本包含所有语句）
MAX_UPDATE_PER_HOUR：一个用户在一个小时内可以执行修改的次数（仅包含修改数据库或表的语句）
MAX_CONNECTIONS_OER_HOUR：一个用户可以在同一时间连接MySQL实例的数量
从5.0.3版本开始，对用户'user'@'%.example.com'的资源限制是指所有通过example.com域名主机连接user用户的连接，而不是分别指从host1.example.com和host2.example.com主机过来的连接
创建用户并设置限制：

mysql> create user 'test1'@'localhost' identified by '123456'
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;

#查看用户信息
show create user test1@'localhost';
#修改限制
alter user 'test1'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
#取消某项资源限制是把原先的值修改为0；
alter user 'test1'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

当针对某个用户的max_user_connections非0时，则忽略全局系统参数max_user_connections，反之则全局系统参数生效。

设置MySQL用户密码过期策略

设置系统参数default_password_lifetime作用于所有的用户账户
- default_password_lifetime=180 设置180天过期
- default_password_lifetime=0 设置密码不过期
如果为每个用户设置了密码过期策略，则会覆盖上述系统参数
ALTER USER 'admin'@'10.0.0.51' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'admin'@'10.0.0.51' PASSWORD EXPIRE NEVER; 密码不过期
ALTER USER 'admin'@'10.0.0.51' PASSWORD EXPIRE DEFAULT; 默认过期策略
手动强制某个用户密码过期
ALTER USER 'admin'@'10.0.0.51' PASSWORD EXPIRE;

MySQL用户lock

通过执行create user /alter user 命令中带account lock / unlock 子句设置用户的lock状态

Create user 语句默认的用户是unlock状态

mysql> create user test2@'10.0.0.51' identified by '123456' account lock;
Query OK, 0 rows affected (0.00 sec)

Alter user 语句默认不会修改用户的 lock / unlock 状态

mysql> alter user 'test2'@'localhost' account lock;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user test1@'localhost' account unlock;
Query OK, 0 rows affected (0.00 sec)

当客户端只用lock状态的用户登录MySQL时，会收到如此报错

[root@db01 ~]# mysql -utest1 -p
Enter password: 
ERROR 3118 (HY000): Access denied for user 'test1'@'localhost'. Account is locked.

#查看当前用户锁的状态
mysql> select user,account_locked from mysql.user where user='test1';
+-------+----------------+
| user  | account_locked |
+-------+----------------+
| test1 | Y              |
+-------+----------------+
1 row in set (0.00 sec)

企业应用中的常规MySQL用户

企业生产系统中MySQL用户的创建通常由DBA统一协调创建，而且按需创建
** DBA通常直接使用root用户来管理数据库**
** 通常会创建指定业务数据库上的增删改查、临时表、执行存储过程的权限应用程序来连接数据库**

mysql> create user app_full identified by '123456';
mysql> grant select,update,insert,delete,create temporary tables,execute on esn.* to app_full@'10.0.0.*';

通常也会创建指定业务数据库上的只读权限给特定应用程序或某些高级别人员来查询数据，防止数据被修改

mysql> create user app_readonly identified by '123456';
#这里我们只给查询权限
mysql> grant select on esn.* to app_readonly identified by '123456';

注：通常我们设置密码手动设置容易重复，这里我们可以使用密码生成器来完成创建密码。