大圣Blog

MySQL

数据库操作

数据表操作

Mysql列类型及列属性

MySQL常用函数

MySQL增删改

MySQL查询

JOSN的支持

MySQL流程控制语句

视图

存储过程及存储函数

变量与游标

事务

触发器

权限及安全管理

MySQL日志

二进制日志

Mysql5.7复制功能

数据备份与恢复

性能优化

课程导航

触发器

MySQL日志

MySQL 的权限系统主要用来对连接到数据库的用户进行权限的验证,以此来判断此用户是否属于合法的用户,如果是合法用户则赋予相应的数据库权限。
数据库的权限和数据库的安全是息息相关的,不当的权限设置可能会导致各种各样的安全隐患,操作系统的某些设置也会对 MySQL 的安全造成影响。
## 权限系统的工作原理
MySQL 权限系统通过下面两个阶段进行认证:
(1)对连接的用户进行身份认证,合法的用户通过认证,不合法的用户拒绝连接;
(2)对通过认证的合法用户赋予相应的权限,用户可以在这些权限范围内对数据库做
相应的操作。

对于身份的认证,MySQL 是通过 IP 地址和用户名联合进行确认的,也就是说,同样的一个用户名,如果来自不同的 IP 地址,则 MySQL 将其视为不同的用户
MySQL 的权限表在数据库启动的时候就载入内存,当用户通过身份认证后,就在内存中进行相应权限的存取
## mysql权限表
* User表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限；
* DB表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库；
* Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库的这个表；
* Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库的这个表的这个字段；
* Procs_priv表：存放存储过程和函数级别的权限。

![图片](http://blog.1024phper.com/static/qiniu/blog19090409335477174)
## MySQL权限经验原则
权限控制主要是出于安全因素，因此需要遵循一下几个经验原则：  
    1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。  
    2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。  
    3、为每个用户设置满足密码复杂度的密码。  
    4、定期清理不需要的用户。回收权限或者删除用户。

## 帐号管理
### 创建普通用户
```
mysql> help CREATE USER
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see )

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}
```
**使用create user语句创建**
```
CREATE USER '用户1'@'localhost' IDENTIFIED BY '123456';
-- 创建用户
-- 若主机部分为\\\%则表示所有主机开发
-- 若为192.168.1.\\\%则表示对192.168.1这个网段所有ip开发
-- 若为*.baidu.com则表示对baidu.com所有子域名均开放
--
CREATE USER '用户2'@'192.168.1.1';
-- 创建无需密码即可登录的用户
```

```
SELECT PASSWORD(123456);
```
![图片](http://blog.1024phper.com/static/qiniu/blog19090409455098231)
```
CREATE USER '用户3'@'\\\%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
-- 为了避免使用明文密码
```

**用GRANT语句创建用户**  
不建议使用该方式创建用户，建议先创建用户，然后使用grant为其授权；而不是先创建用户及授权后，然后修改用户密码。
```
GRANT <privileges> ON <what> TO <user> [WITH GRANT OPTION];
```
### 删除普通用户
```
DROP USER '用户3'@'\\\%';
```
注意：若该用户正在连接中，则即使删除了该帐号，该用户也不会断开。但等他断开再连就连接不上了。另外就是用户操作，及授权操作后，使用 FLUSH  PRIVILEGES 刷新权限
### 修改用户账号
    mysql> rename user 'user1'@'\\\%' to 'user'@'127.0.0.1';
 ![图片](http://blog.1024phper.com/static/qiniu/blog19090409455025429)
### 查看系统用户列表
```
mysql> SELECT host,user FROM mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
| localhost | user1         |
+-----------+---------------+
4 rows in set (0.00 sec)
```
### 修改自己密码
```
C:\wamp\bin\mysql\mysql5.7.24\bin>mysqladmin -u root -h localhost -p password 111111
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be inse
cure.
Warning: Since password will be sent to server in plain text, use ssl connection
 to ensure password safety.
修改成功！
```
### root修改普通用户密码
```
 ALTER USER USER() IDENTIFIED BY '123456';  修改当前用户密码
 ALTER USER '用户1'@'localhost' IDENTIFIED BY '123456';  修改用户1的密码
```
### root密码丢失解决办法
修改配置文件my.cnf，在配置文件[mysqld]下添加skip-grant-tables，重启MySQL服务即可免密码登录
--skip-grant-tables 选项前面曾经介绍过，意思是启动 MySQL 服务的时候跳过权限表认证。 启动后，连接到 MySQL 的 root 将不需要口令。
登录上后然后使用ALTER USER 命令修改密码
![图片](http://blog.1024phper.com/static/qiniu/blog19090409455090734)
```
C:\wamp\bin\mysql\mysql5.7.24\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24 MySQL Community Server (GPL)
-- 此时不用密码就已经登录上了
mysql> flush privileges;    -- 注意，这个不能少
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)
```
最后记得把配置文件--skip-grant-tables那行删除，然后重启mysql，重新用新设的密码登录试试。
### 设置MySQL用户密码过期策略
设置系统参数default_password_lifetime作用于所有的用户账户
• default_password_lifetime=180 设置180天过期
• default_password_lifetime=0 设置密码不过期
```
• 如果为每个用户设置了密码过期策略，则会覆盖上述系统参数
mysql> ALTER USER '用户1'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.00 sec)
ALTER USER testuser IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;永不过期
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;默认过期策略
• 手动强制某个用户密码过期
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
```
### LOCK 用户
锁定用户语法：CREATE USER 'user1'@'localhost' ACCOUNT LOCK
解锁用户语法：CREATE USER 'user1'@'localhost' ACCOUNT UNLOCK
```
-- 新建一个用户
mysql> CREATE USER 'user1'@'localhost' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.01 sec) 
-- 开启另一个mysql黑窗口，用user1来登录，没有问题

-- 现在将user1用户账号锁定
mysql> ALTER USER 'user1'@'localhost' ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)
-- 再开启一个mysql黑窗口，用user1来登录，看能不能登录上
发现登录不上

```

## 用户授权
### 语法
```
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

-- 官网例子
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
```
### 实践
```
mysql> CREATE USER 'u1'@'localhost' , 'u2'@'localhost' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'u3'@'localhost' , 'u4'@'localhost' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON *.* TO 'u1'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)
-- 全局级别授权

mysql> GRANT ALL ON test.* TO 'u2'@'localhost';
Query OK, 0 rows affected (0.00 sec)
-- 数据库级别授权

mysql> GRANT ALL ON test.student TO 'u3'@'localhost' WITH GRANT OPTION;
-- 表级别授权
```
### 查看用户权限
![图片](http://blog.1024phper.com/static/qiniu/blog19090409455076662)
### 权限回收
```
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...
```
实践
```
mysql> REVOKE SELECT ON *.* FROM 'u1'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> REVOKE ALL PRIVILEGES ON test.* FROM 'u1'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'u1'@'localhost'
+---------------------------------------------------------------+
| Grants for u1@localhost                                       |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'u1'@'localhost'                        |
| GRANT USAGE ON `test`.* TO 'u1'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------+
```

触发器

MySQL日志

MySQL

课程导航

权限及安全管理

文章目录