MySQL权限与安全

by Posted on

MySQL 的权限系统主要用来对连接到数据库的用户进行权限的验证,以此来判断此用户是否属于合法的用户,如果是合法用户则赋予相应的数据库权限。
数据库的权限和数据库的安全是息息相关的,不当的权限设置可能会导致各种各样的安全隐患,操作系统的某些设置也会对 MySQL 的安全造成影响。

权限系统的工作原理

MySQL 权限系统通过下面两个阶段进行认证:
(1)对连接的用户进行身份认证,合法的用户通过认证,不合法的用户拒绝连接;
(2)对通过认证的合法用户赋予相应的权限,用户可以在这些权限范围内对数据库做
相应的操作。

对于身份的认证,MySQL 是通过 IP 地址和用户名联合进行确认的,也就是说,同样的一个用户名,如果来自不同的 IP 地址,则 MySQL 将其视为不同的用户
MySQL 的权限表在数据库启动的时候就载入内存,当用户通过身份认证后,就在内存中进行相应权限的存取

mysql权限表

  • User表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限；
  • DB表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库；
  • Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库的这个表；
  • Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库的这个表的这个字段；
  • Procs_priv表：存放存储过程和函数级别的权限。
权限 权限级别 权限说明
CREATE 数据库、表或索引 创建数据库、表或索引权限
DROP 数据库或表 删除数据库或表权限
GRANT OPTION 数据库、表或保存的程序 赋予权限选项
REFERENCES 数据库或表  
ALTER 表 更改表，比如添加字段、索引等
DELETE 表 删除数据权限
INDEX 表 索引权限
INSERT 表 插入权限
SELECT 表 查询权限
UPDATE 表 更新权限
CREATE VIEW 视图 创建视图权限
SHOW VIEW 视图 查看视图权限
ALTER ROUTINE 存储过程 更改存储过程权限
CREATE ROUTINE 存储过程 创建存储过程权限
EXECUTE 存储过程 执行存储过程权限
FILE 服务器主机上的文件访问 文件访问权限
CREATE TEMPORARY TABLES 服务器管理 创建临时表权限
LOCK TABLES 服务器管理 锁表权限
CREATE USER 服务器管理 创建用户权限
PROCESS 服务器管理 查看进程权限
RELOAD   服务器管理 执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT 服务器管理 复制权限
REPLICATION SLAVE 服务器管理 复制权限
SHOW DATABASES 服务器管理 查看数据库权限
SHUTDOWN 服务器管理 关闭数据库权限
SUPER 服务器管理 执行kill线程权限

图片

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);

图片

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';

图片

查看系统用户列表

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 命令修改密码
图片

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> SELECT user,host FROM mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| u1            | localhost |
| u2            | localhost |
| u3            | localhost |
| u4            | localhost |
| user1         | localhost |
+---------------+-----------+
8 rows in set (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;
-- 表级别授权

查看用户权限

图片

权限回收

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 |
+---------------------------------------------------------------+

Published by 1024@phper

热爱编程，热爱生活