用户管理

用户信息在哪里

默认数据库有四个：

  • information_schema
  • mysql
  • performance_schema
  • sys

其中 “mysql” 数据库有一个表名为user，存储着用户信息。

通常默认有四个用户：

root ,mysql.infoschema,mysql.session,mysql.sys 。

ubuntu 或 debian 用户还会有个maint用户。

debian-sys-maint中Debian系统对MySQL维护用的，可以理解为通过系统的某个“非常规”程序对Mysql进行备份恢复等行为时，改程序所使用的用来登录Mysql的账户。

具体作用是重启及运行mysql服务，不过有一个安全问题：该用户的权限和ROOT是一样的，并且密码可以在/etc某个文件中找到，这需要对账户进行一些限制。

root 的权限最高，顾名思义为“根”，拥有最高权限，创建其他用户，它不能没有。

user 的联合主键

用户信息里两个列很关键：host 和 user。

  • host : 主机，用来区分用户的连接段。
  • user : 用户名，区分用户

user 表的主键有两个：host和user，即联合主键。

所以不同连接段允许有重名用户出现，就像不同作用域能够有重名变量一样。

创建、修改、删除

创建用户指令：

1
CREATE USER 用户名 [@主机名称] [IDENTIFIED BY '密码']  #[]为可选

tips：

  1. 该语句可以同时创建多个用户。
  2. 用户名参数由用户和主机名构成。
  3. 需要有CREATE权限，新账户什么权限都没有。

修改

1
2
UPDATE mysql.user  set user = "wang5" where user =  "li4" [and host = 'xxx']
flush privileges;# 刷新权限，否则登陆时仍然是原本的用户信息

修改用户就是直接操作 user 表。

修改完需要``fs;

删除

两个方法：DROP 和 DELETE (user 表里删)。

  1. DROP

    1
    2
    3
    4
    DROP USER user,[user].... #user即用户名参数，可包含 host

    DROP USER 'li4';    # 默认删除host为%的用户
    DROP USER 'li4'@'localhost';

  2. DELETE

    1
    2
    3
    4
    DELETE FROM mysql.user WHERE Host='hostname' AND User = 'username';
    #必须有 host ，联合主键的两个字段，才能唯一确定一条记录。
    FLUSH PRIVILEGES; 
    #立即生效。

    DELETE 删除会有系统信息残留。DROP 指令能同时删去 mysql.user 表和 mysql.db 表内容。

设置当前用户密码

1. 使用ALTER USER命令来修改当前用户密码

1
ALTER USER USER() IDENTIFIED BY 'new_password';

2. 使用SET语句来修改当前用户密码

1
SET PASSWORD='new_password';

修改其他用户密码

1. 使用ALTER语句来修改普通用户的密码

1
2
ALTER USER user [IDENTIFIED BY '新密码'] 
[,user[IDENTIFIED BY '新密码']]…;

2. 使用SET命令来修改普通用户的密码

1
SET PASSWORD FOR 'username'@'hostname'='new_password';

密码过期关键词 EXPIRE

  • 你可以通过ALTER和CREATE 时 手动设置用户密码过期，这样用户可以登入数据库，但无法查询。除非重新设置密码。

  • 你也可以修改系统全局变量 Default password lifetime 。改变量默认为0，即不过期。设置为N代表密码生存期，N天后密码必须修改，否则将自动过期。

密码重用关键词 INTERVAL 、REUSE

分两类：时间和更改数量

  • 时间（周期）：修改后密码不能用最近N天时间使用过的密码相同。
  • 更改数量 ：修改后密码与最近N个密码不能相同。

系统变量：password_history , password_reuse_interval

权限管理

1
show privileges; #查看权限
  • CREATE和DROP权限，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
  • SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。
  • SELECT权限只有在它们真正从一个表中检索行时才被用到。
  • INDEX权限允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
  • ALTER权限可以使用ALTER TABLE来更改表的结构和重新命名表。
  • CREATE ROUTINE权限用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。
  • GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。
  • FILE权限使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

授予权限

1
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
  • 该权限如果发现没有该用户，则会直接新建一个用户。
  • 给li4用户用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
1
GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost;
  • 授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123。注意这里唯独不包括grant的权限
1
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';

授予权限的原则

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：

1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。

2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。

3、为每个用户设置满足密码复杂度的密码。

4、定期清理不需要的用户，回收权限或者删除用户。

查看权限

  • 查看当前用户权限
1
2
3
4
5
SHOW GRANTS; 
# 或 
SHOW GRANTS FOR CURRENT_USER; 
# 或 
SHOW GRANTS FOR CURRENT_USER();
  • 查看某用户的全局权限
1
SHOW GRANTS FOR 'user'@'主机地址';

收回权限

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

  • 收回权限命令
1
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
  • 举例
1
2
3
4
#收回全库全表的所有权限 
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%'; 
#收回mysql库下的所有表的插删改查权限 
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;
  • 注意：须用户重新登录后才能生效

权限表

user表

user表是MySQL中最重要的一个权限表， 记录用户账号和权限信息 ，有49个字段。

这些字段可分为四类：

  1. 范围列：

    • ==host== : 表示连接类型

      • % 表示所有远程通过 TCP方式的连接

      • IP 地址 如 (192.168.1.2、127.0.0.1) 通过制定ip地址进行的TCP方式的连接

      • 机器名 通过制定网络中的机器名进行的TCP方式的连接

      • ::1 IPv6的本地ip地址，等同于IPv4的 127.0.0.1

      • localhost 本地方式通过命令行方式的连接 ，比如mysql -u xxx -p xxx 方式的连接。

    • ==user== : 表示用户名，同一用户通过不同方式链接的权限是不一样的。

    • ==password== ：mysql 5.7 及之后版本的密码保存到 authentication_string 字段中不再使用password 字

      段。

  2. 权限列：是否拥有一些特定权限

  3. 安全列 ：加密，表示用户，验证用户身份。

  4. 资源控制列：限制用户使用的资源。共4个字段。

    ①max_questions，用户每小时允许执行的查询操作次数；

    ②max_updates，用户每小时允许执行的更新操作次数；

    ③max_connections，用户每小时允许执行的连接操作次数；

    ④max_user_connections，用户允许同时建立的连接次数。

查看字段： 

1
DESC mysql.user;

查看用户, 以列的方式显示数据： 

1
SELECT * FROM mysql.user \G;

查询特定字段： 

1
2
SELECT host,user,authentication_string,select_priv,insert_priv,drop_priv 
FROM mysql.user;

db表

  • 用户列

db表用户列有3个字段，分别是Host、User、Db。这3个字段分别表示主机名、用户名和数据库名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。

  • 权限列

Create_routine_priv和Alter_routine_priv这两个字段决定用户是否具有创建和修改存储过程的权限。

结构查看：

1
DESCRIBE mysql.db;

tables_priv 和 column_priv 表

  • tables_priv 表：对表设置操作权限
  • columns_priv 表 ：对表的某一列设置权限 。

procs_priv 表

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

上面几个表都在 database → mysql 里，想看自己 DESC 去。

角色

角色的创建、删除和权限操作

创建角色

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

角色名称的命名规则和用户名类似。如果host_name省略，默认为%，role_name不可省略，不可为空。

给角色赋予权限

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

上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称

1
SHOW PRIVILEGES\G

查看角色的权限

1
SHOW GRANTS FOR 'role_name';

只要你创建了一个角色，系统就会自动给你一个“USAGE”权限，意思是连接登录数据库的权限。

回收角色的权限

1
REVOKE privileges ON tablename FROM 'rolename';

删除角色

1
DROP ROLE role [,role2]...

注意，如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。

角色和用户

给用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。

1
GRANT role [,role2,...] TO user [,user2,...];

查询当前已激活的角色

1
SELECT CURRENT_ROLE();

激活角色

方式1：使用set default role 命令激活角色

1
SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

方式2：将activate_all_roles_on_login设置为ON

1
SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对所有角色永久激活。

撤销用户的角色

1
REVOKE role FROM user;

设置强制角色(mandatory role)

方式1：服务启动前设置

1
2
[mysqld] 
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'

方式2：运行时设置

1
2
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后失效

配置文件

**文件里会定义许多组(用 [ ] 括起来)，组下边可以定义启动选项。**通常称组为标签。

1
2
3
4
5
6
7
8
9
10
[server]
#具体启动选项
option1          #不需要选项值：True / False 类型
option2 = value2 #需要选项值 ： 键值对 类型
[mysqld]
...
[mysqld_safe]
...
[client]
...

配置文件中不同选项组是给不同的启动命令使用的。

有两个选项组比较特别：

  • [server]组的启动选项作用于所有服务器程序
  • [client]组的启动选项作用于所有客户端程序。

像mysql这个启动命令，就会调用 [mysql] 和 [client] 两个组

tips：

  1. [mysql-‘版本号’]：只适用于特定版本的专用选项组

  2. 两个组里出现重复配置项，以最后一个为准。

  3. 一个选项既出现在命令行，又出现在配置文件里，以命令行为准。

    如 defaults-extra-file 和 defaults-file 本身就是为了指定配置文件路径的。

快来参与讨论吧~