MySQL用户与权限管理

用户管理

登录MySQL服务器

mysql -h hostname | hostIP -P port -u usrename -p DatabaseName -e ''
  • 参数介绍
    • -h 参数：后面接主机名或者主机IP，hostname为主机，hosts为主机IP
    • -P 参数：后面接MySQL服务器的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号
    • -u 参数：后面接用户名，username为用户名
    • -p 参数 会提示输入密码
    • DatabaseName参数 指明登陆到哪一个数据库中。如果没有该参数，就会直接登录到MSQL数据库中，然后可以使用USE命令来选择数据库
    • -e 参数：后面可以直接加SQL语句。登录MySQL服务器以后可执行这个SQL语句，然后退出MySQL服务器

创建用户

  • 基本语法
CREATE USER 用户名 [@'localhost'] [IDENTIFIED BY '密码'] [, 用户名 [IDENTIFIED BY '密码']];
  • 用户参数表示新建用户的账户，由 用户 (User) 和 主机名(Host) 构成
  • [ ]表示可选，也就是说，可以指定用户登录时需要密码验证，也可不不指定密码验证，这样用户可以直接登录。
  • CREATE USER 语句可以同时创建多个用户
  • 创建的用户host 默认是 %,可以在创建时指定

修改用户

UPDATE mysql.user SET USER = '114' WHERE USER = 'WANG5';

## 一定要做，刷新权限
FLUSH PRIVILEGES;

删除用户

  • 方式1：使用DROP方式删除（推荐）
DROP USER user[, user]...;
  • 方式2：使用 DELETE 方式删除,会在 mysql.db 残留信息，不推荐使用
DELETE FROM mysql.user WHERE Host = 'hostname' AND User = 'username';
FLUSH PRIVILEGES;

设置用户密码

当前用户密码

  • 使用 ALTER USER 命令来修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'new_password';
  • 使用 SET 语句来修改当前用户密码
SET PASSWORD='new_password'

修改其他用户密码

  • 使用 ALTER 语句来修改普通用户密码
ALTER USER user [IDENTIFIED BY '新密码'] [,user [IDENTIFIED BY '新密码']...];
  • 使用 SET 命令修改普通用户的密码
SET PASSWORD FOR 'username'@'hostname'='new_password'

MySQL8 密码管理

  • MySQL中记录使用过的历史密码，目前包含如下密码管理功能

    1. 密码过期：要求定期修改密码
    2. 密码重用限制：不允许使用旧密码
    3. 密码强度评估：要求使用高强度的密码

  • 密码过期策略

    • 在MySQL中，数据库管理员可以手动设置账号密码过期，也可以建立一个自动密码过期策略
    • 过期策略可以是全局的，也可以为每个账号设置单独的过期策略

  • 手动设置立马过期

    • 该语法将用户的密码设置为过期，但是仍然可以登录进入数据库，但无法进行查询。
    ALTER USER user PASSWORD EXPIRE

  • 手动设置指定时间过期方式1：全局

    • MySQL默认使用 default_password_lifetime 系统变量建立全局密码过期策略
      • 默认值是0，表示禁用自动密码过期
      • 允许的值是正整数N，表示允许的密码生存期。密码必须每隔N天 进行修改
    • 方式1：使用SQL语句更改该变量的值并持久化
    SET PERSIST default_password_lifetime = 180; ## 建立全局策略，设置密码每隔180天过期
    • 方式2：配置文件 my.cnf 中进行维护
    [mysqld]
default_password_lifetime=180; # 建立全局策略，设置密码每隔180天过期

  • 手动设置指定时间过期方式2：单独设置

    • 每个账号既可延用全局密码过期策略，也可单独设置策略。在 CREATE USER 和 ALTER USER 语句上加入 PASSWORD EXPIRE 选项可实现单独设置策略。下面是一些语句示例
    #设置kangshifu账号密码每 90 天 过期
CREATE USER 'kangshifu'@'loaclhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

#设置密码永不过期
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;

#延用全局密码过期策略
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'kangshiuf'@'localhost' PASSWORD EXPIRE DEFAULT;

  • 密码重用策略

    • 手动设置密码重用方式1：全局

      • 方式1：使用SQL
      SET PERSIST password_history = 6; #设置不能选择最近使用过的6个密码

SET PERSISIT password_reuse_interval = 365; #设置不能选择最近一年内的密码
      • 方式2：my.cnf 配置文件
      [mysqld]
password_history=6
password_reuse_interval=365

    • 手动设置密码重用方式：单独设置

    #不能使用最近5个密码
CREATE USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;

#不能使用最近365内的密码
CREATE USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

权限管理

权限列表

show privileges;
  1. CREATE 和 DROP 权限：可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将MySQL数据库中的 DROP 权限授予某用户，用户就可以删除 MySQL 访问权限保存的数据库
  2. SELECT、INSERT、UPDATE 和 DELETE 权限 允许在一个数据库现有的表上实施操作
  3. SELECT 权限只有在它们真正从一个表中检索行时才被用到
  4. INDEX 权限 允许创建或删除索引，INDEX 适用于已有的表。
  5. ALTER 权限可以使用 ALTER TABLE 来更改表的结构和重新命名表
  6. CREATE ROUTINE 权限 用来创建保存的程序（函数和程序）；ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE 权限用来执行保存的程序
  7. GRANT 权限 允许授权给其他用户，可用于数据库、表和保存的程序
  8. FILE 权限 使用户可以使用LOAD DATA INFILE 和 SELECT ... INTO OUTFILE 语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件
  • MySQL的权限如何分布：
权限分布可能的设置的权限
表权限Select，Insert，Update，Delete，Create,Drop,Grant,References,Index,Alter
列权限Select,Insert,Update,REferences
过程权限Execute,Alter Routine,Create Routine,Grant

授予权限的原则

  1. 只授予能满足需要的最小权限
  2. 创建用户的时候 限制用户的登陆主机
  3. 为每个用户 设置满足密码复杂度的密码
  4. 定期清理不需要的用户，回收权限或者删除用户

授予权限

  • 有两种方式授予权限
    1. 角色赋予用户给用户授权
    2. 直接给用户授权
  • 授权命令
    • 该权限如果发现没有该用户，则会直接创建一个新用户
GRANT 权限1,权限2,...权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY '密码口令'];

  • 比如：

    GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost; ##给li4用户本地命令行方式，授予atguigudb这个库底下的所有表的增删改查的权限

GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123'
    • ALL PRIVILEGES 是表示所有权限，你也可以使用SELECT、UPDATE等权限
    • ON 用来指定权限针对哪些库和表

  • 如果需要赋予包括 GRANT 的权限，添加参数 "WITH GRANT OPTION" 这个选项即可，表示该用户可以将自己拥有的权限授予给别人。

  • GRANT重复给用户添加权限

查看权限

  • 查看当前用户权限
SHOW GRANTS;

SHOW GRANTS FOR CURRENT_USER;

SHOW GRANTS FOR CURRENT_USER();

SHOW GRANTS FOR 'user'@'主机地址';

收回权限

  • 取消已经赋予用户的某些权限

  • 注意：再将用户账户从user表删除之前，应该收回相应用户的所有权限

  • 收回权限命令

REVOKE 权限1,权限2,..,权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
  • 举例
#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';

#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@'localhost'; 
## 需要用户重新登陆后才能生效

权限表

  • MySQL服务器通过 权限表 来控制用户对数据库的访问，权限表存放在 mysql 数据库中。MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是 user 表、db表。除此之外，还有 table_priv 表、column_priv 表 和 proc_priv 表等。

USER表

  • 范围列

    • host：表示连接类型

      • % 表示所有远程通过 TCP 方式的连接
      • IP地址 通过制定 ip地址 进行的TCP方式的连接
      • 机器名 通过指定网络中的机器名进行的TCP方式的连接
      • ::1 IPv6的本地ip地址，等同于IPv4的127.0.0.1
      • localhost 本地方式通过命令行方式的连接

    • user：表示用户名

    • authentication_string：密码

  • 权限列

    • Grant_priv字段
      • 表示是否拥有GRANT权限
    • Shutdown_priv字段
      • 表示是否拥有停止MySQL服务的权限
    • Super_priv字段
      • 表示是否拥有超级权限
    • Execute_priv字段
      • 表示是否拥有EXECUTE权限
    • Select_priv,Insert_priv等
      • 为该用户所拥有的权限

  • 安全列

    • 有6个字段，ssl相关的(ssl_type,ssl_cipher),用于加密
    • x509_issuer,x509_subject,用于标识用户
    • 两个Plugin字段用于 验证用户身份 的插件

  • 资源控制列

    1. max_questions：用户每小时允许执行的查询操作次数
    2. max_update：用户每小时允许执行的更新操作次数
    3. max_connections：用户每小时允许执行的连接操作次数
    4. max_user_connections：用户允许同时建立的连接次数
#查看字段
DESC mysql.user;

#查看用户，以列的方式显示数据
SELECT * FROM mysql.user;

#查看特定字段
SELECT host,user FROM mysql.user;

db表

  • 查看表结构
DESC mysql.db
  1. 用户列：用户列拥有3个字段，分别是Host、User、Db。这3个字段分别表示主机名、用户名和数据库名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键
  2. 权限列：Create_routine_priv 和 Alter_routine_priv 这两个字段决定用户是否具有创建和修改存储过程的权限

tables_priv表和columns_priv表

  • tables_priv表用来对表设置操作权限，colums_priv表用来对表的某一列设置权限

procs_priv表

  • procs_priv表可以对存储过程和存储过函数设置操作权限

访问控制

  1. 连接核实阶段：
    1. 会使用user表中的host,user和authentication_string这3个字段匹配客户端提供信息
    2. 如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接收连接，然后进入阶段2等待用户请求
  2. 请求核实阶段
    1. 权限确认时，首先是user表，然后是db表，最后是tables_priv表和columns_priv表

角色管理

  • 角色：方便管理拥有相同权限的用户

创建角色

CREATE ROLE 'role_name'[@'@host_name'][,'role_name'[@'host_name']]...

给角色赋予权限

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

SHOW  privileges;

查看角色的权限

SHOW GRANTS FOR 'manager'

回收角色的权限

REVOKE privileges ON tablenames FROM 'rolename';

删除角色

DROP ROLE role[,role2]...

给用户赋予角色

GRANT role[,role2...] TO user [,user2,...];#role代表角色，user代表用户

#查询当前用户的角色，如果角色未被激活，结果将显示NONE
SELECT CURRENT_ROLE();

激活角色

  • 方式1：使用 set default role 命令激活角色
SET DEFAULT ROLE ALL TO user
  • 方式2：将activate_all_roles_on_login 设置为ON
SHOW VARIABLES LIKE 'activate_all_roles_on_login';

SET GLOBAL activate_all_roles_on_login=ON;

撤销用户的角色

REVOKE role FROM user;

设置强制角色 (mandatory role)

  • 强制角色：是给每个创建账户的默认角色，不需要手动设置。强制角色无法被REVOKE 或者 DROP

  • 方式1：服务启动前设置

[mysqld]
mandatory_roles='role1,role2@locaalhost,r3@%.atguigu.com'
  • 方式2：运行时设置
SET PERSIST mandatory_roles = 'role1,role2@locaalhost,r3@%.atguigu.com'; #系统重启后仍然有效

SET GLOBAL mandatory_roles = 'role1,role2@locaalhost,r3@%.atguigu.com'; #系统重启后失效

配置文件的使用

  • 与在命令行中指定启动选项不同的是，配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号[ ]括起来

启动命令与选项组

  • 配置文件中不同的选项组是给不同的启动命令使用的
    • [server] 组下边的启动选项将作用于所有的服务器程序
    • [cilent] 组下边的启动选项将作用于所有的客户端程序
启动命令类别能读取的组
mysqld启动服务器[mysqld]、[server]
mysql_safe启动服务器[mysqld]、[server]、[mysqld_safe]
mysql.server启动服务器[mysqld]、[server]、[mysqld_sserver]
mysql启动客户端[mysql]、[client]
mysqladmin启动客户端[mysqladmin]、[client]
mysqldump启动客户端[mysqldump]、[client]