MySQL权限与安全管理

1.权限表

MySQL是一个多用户的数据库，MySQL服务器通过权限表来控制用户对数据库的访问。权限表存放在名为mysql的数据库中，存储账户权限信息的表主要有user、db、host、tables_priv、columns_priv和procs_priv。

1.1 user表

user表记录了允许连接到MySQL服务器的账号信息，里面的权限是全局的。我们先来看看user表的ddl语句：

CREATE TABLE user
(
    # 主机名
    Host                     char(255) CHARSET ascii                            DEFAULT ''                      NOT NULL,
    # 用户名
    User                     char(32)                                           DEFAULT ''                      NOT NULL,
    Select_priv              enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Insert_priv              enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Update_priv              enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Delete_priv              enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Create_priv              enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Drop_priv                enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Reload_priv              enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Shutdown_priv            enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Process_priv             enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    File_priv                enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Grant_priv               enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    References_priv          enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Index_priv               enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Alter_priv               enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Show_db_priv             enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Super_priv               enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Create_tmp_table_priv    enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Lock_tables_priv         enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Execute_priv             enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Repl_slave_priv          enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Repl_client_priv         enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Create_view_priv         enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Show_view_priv           enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Create_routine_priv      enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Alter_routine_priv       enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Create_user_priv         enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Event_priv               enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Trigger_priv             enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Create_tablespace_priv   enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    ssl_type                 enum ('', 'ANY', 'X509', 'SPECIFIED') CHARSET utf8 DEFAULT ''                      NOT NULL,
    ssl_cipher               blob                                                                               NOT NULL,
    x509_issuer              blob                                                                               NOT NULL,
    x509_subject             blob                                                                               NOT NULL,
    max_questions            int UNSIGNED                                       DEFAULT '0'                     NOT NULL,
    max_updates              int UNSIGNED                                       DEFAULT '0'                     NOT NULL,
    max_connections          int UNSIGNED                                       DEFAULT '0'                     NOT NULL,
    max_user_connections     int UNSIGNED                                       DEFAULT '0'                     NOT NULL,
    plugin                   char(64)                                           DEFAULT 'caching_sha2_password' NOT NULL,
    # 用户密码
    authentication_string    text                                                                               NULL,
    password_expired         enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    password_last_changed    timestamp                                                                          NULL,
    password_lifetime        smallint UNSIGNED                                                                  NULL,
    account_locked           enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Create_role_priv         enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Drop_role_priv           enum ('N', 'Y') CHARSET utf8                       DEFAULT 'N'                     NOT NULL,
    Password_reuse_history   smallint UNSIGNED                                                                  NULL,
    Password_reuse_time      smallint UNSIGNED                                                                  NULL,
    Password_require_current enum ('N', 'Y') CHARSET utf8                                                       NULL,
    User_attributes          json                                                                               NULL,
    PRIMARY KEY (Host, User)
)
    COMMENT 'Users and global privileges' COLLATE = utf8_bin;

GRANT SELECT ON TABLE user TO 'mysql.session'@localhost;

• 其中Host、User、authentication_string这3列分别表示主机名、用户名和密码。以Host和User列作为user表的联合主键。这几列也是user表的用户列。
• 类似于Select_priv列，这些列是user表的权限列，描述了用户拥有的权限，这些权限是全局范围的，允许对数据库和数据进行操作。这些列的数据类型为enum类型，默认值为N，可以使用GRANT或UPDATE语句更改这些字段来修改用户对应的权限。

1.2 db表

db表存储了用户对某个数据库的操作权限。

CREATE TABLE db
(
    Host                  char(255) CHARSET ascii      DEFAULT ''  NOT NULL,
    Db                    char(64)                     DEFAULT ''  NOT NULL,
    User                  char(32)                     DEFAULT ''  NOT NULL,
    Select_priv           enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Insert_priv           enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Update_priv           enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Delete_priv           enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Create_priv           enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Drop_priv             enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Grant_priv            enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    References_priv       enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Index_priv            enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Alter_priv            enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Create_tmp_table_priv enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Lock_tables_priv      enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Create_view_priv      enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Show_view_priv        enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Create_routine_priv   enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Alter_routine_priv    enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Execute_priv          enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Event_priv            enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    Trigger_priv          enum ('N', 'Y') CHARSET utf8 DEFAULT 'N' NOT NULL,
    PRIMARY KEY (Host, Db, User)
)
    COMMENT 'Database privileges' COLLATE = utf8_bin;

CREATE INDEX User
    ON db (User);

1.3 tables_priv

tables_priv存储用户对表的操作权限

CREATE TABLE tables_priv
(
    Host        char(255) CHARSET ascii   DEFAULT ''                NOT NULL,
    Db          char(64)                  DEFAULT ''                NOT NULL,
    User        char(32)                  DEFAULT ''                NOT NULL,
    Table_name  char(64)                  DEFAULT ''                NOT NULL,
    Grantor     varchar(288)              DEFAULT ''                NOT NULL,
    Timestamp   timestamp                 DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
    Table_priv  set ('Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter', 'Create View', 'Show view', 'Trigger') CHARSET utf8 DEFAULT ''                NOT NULL,
    Column_priv set ('Select', 'Insert', 'Update', 'References') CHARSET utf8 DEFAULT ''                NOT NULL,
    PRIMARY KEY (Host, Db, User, Table_name)
)
    COMMENT 'Table privileges' COLLATE = utf8_bin;

CREATE INDEX Grantor
    ON tables_priv (Grantor);

1.4 columns_priv

columns_priv表存储了用户对列的操作权限

CREATE TABLE columns_priv
(
    Host        char(255) CHARSET ascii                                       DEFAULT ''                NOT NULL,
    Db          char(64)                                                      DEFAULT ''                NOT NULL,
    User        char(32)                                                      DEFAULT ''                NOT NULL,
    Table_name  char(64)                                                      DEFAULT ''                NOT NULL,
    Column_name char(64)                                                      DEFAULT ''                NOT NULL,
    Timestamp   timestamp                                                     DEFAULT CURRENT_TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
    Column_priv set ('Select', 'Insert', 'Update', 'References') CHARSET utf8 DEFAULT ''                NOT NULL,
    PRIMARY KEY (Host, Db, User, Table_name, Column_name)
)
    COMMENT 'Column privileges' COLLATE = utf8_bin;

2.用户管理

2.1 创建用户

这里使用CREATE USER语句来创建用户，在创建用户的时候，我们也可以指定身份验证插件，MySQL8.0默认的身份验证插件为：caching_sha2_password，具有更好的安全性，MySQL8.0之前默认的身份验证插件为：mysql_native_password。如果我们升级了MySQL服务器版本，出现客户端连接不上服务器的现象，这个时候最好是去升级客户端版本，不建议将身份验证插件从caching_sha2_password修改为mysql_native_password。

• 不指定身份验证插件，就使用默认身份验证插件

  CREATE USER 'test1'@'%' IDENTIFIED BY '123456';
  

• 指定身份验证插件

  CREATE USER 'test1'@'%' IDENTIFIED WITH 'mysql_native_password' BY '123456';
  

使用CREATE USER语句的用户，必须拥有全局的CREATE USER权限或mysql数据库的insert权限。每添加一个用户，会在mysql数据库的user表中添加一条新记录，但是这个用户没有任何权限

2.2 删除用户

# 如果不指定hostname，将默认使用'%'
DROP USER 'test1'@'%';

执行DROP USER语句不会自动关闭任何用户已经打开的会话。如果已经打开会话的用户被删除，则该语句在该用户的会话关闭之前不会生效，因此用户在该会话中还是可以进行正常操作。一旦会话关闭，用户将被删除，该用户将不能再登录。

3.权限管理

权限管理主要是对登录到MySQL服务器的用户进行权限管理。

3.1 授权

授权就是为某个用户授予权限。MySQL中使用GRANT语句为用户授权。

• 全局权限

  # 将所有数据库的所有表的所有权限赋予给用户，第一个*表示所有数据库，第二个*表示所有表
  GRANT ALL PRIVILEGES ON *.* TO 'test3'@'localhost';
  

• 数据库权限

  # 将数据库test中所有表的查询、更新权限赋予给用户
  GRANT SELECT, UPDATE ON test.* TO 'test3'@'localhost';
  

• 表权限

  # 将数据库test中表coop_spec的查询权限赋予给用户
  GRANT SELECT ON test.coop_spec TO 'test3'@'localhost';
  

• 列权限

  # 将数据库test中表coop_spec的id列的查询权限赋予给用户
  GRANT SELECT(id) ON test.coop_spec TO 'test3'@'localhost';
  

在授权的同时还可以在末尾跟上WITH GRANT OPTION，表示被授权的用户，可以将授权这些权限给其他用户

3.2 取消授权

• 全局权限

  # 将赋予给用户所有数据库的所有表的所有权限取消，第一个*表示所有数据库，第二个*表示所有表
  REVOKE ALL PRIVILEGES ON *.* FROM 'test3'@'localhost';
  

• 数据库权限

  # 将赋予给用户的数据库test中所有表的查询权限取消
  REVOKE SELECT ON test.* FROM 'test3'@'localhost';
  

• 表权限

  # 将赋予给用户数据库test中表coop_spec的查询权限取消
  REVOKE SELECT ON test.coop_spec FROM 'test3'@'localhost';
  

• 列权限

  # 将赋予给用户数据库test中表coop_spec的id列的查询权限取消
  REVOKE SELECT(id) ON test.coop_spec FROM 'test3'@'localhost';
  

3.3 查看权限

SHOW GRANTS FOR 'test3'@'localhost';

4.角色

MySQL8.0版本中，可以创建角色，角色可以看成是一些权限的集合，可以为用户赋予角色

# 创建角色
CREATE ROLE 'role_update';
# 赋予表的更新权限给角色
GRANT UPDATE ON test.coop_spec TO role_update;
# 查看角色的权限
SHOW GRANTS FOR 'role_update';
# 撤销角色的权限
REVOKE UPDATE ON test.coop_spec FROM role_update;
# 将角色赋予给用户
GRANT role_update TO 'test3'@'localhost';
# 删除角色
DROP ROLE role_update;