MySQL 账户

没有评论

MySQL

帐户名称

MySQL 支持多种身份验证插件 (内部/外部)。

MySQL 帐户名包含一个用户名和一个主机名，这可以为从不同主机连接的具有相同用户名的用户创建不同的帐户。

MySQL 和操作系统用户名/密码的区别

  • MySQL 用于身份验证目的的用户名/密码与操作系统用户名/密码无关
  • MySQL 用户名最长为32个字符。操作系统用户名的最大长度可能不同
  • 存储在 mysql.user 表中的密码使用了特定的插件算法进行加密
  • 如果用户名和密码仅包含 ASCII 字符，则无论字符集设置如何，都可以连接到服务器；如果用户名或密码包含非 ASCII 字符，则可能需要指定的字符集才能验证

账户名格式

  • ‘user_name’@’host_name’
    • ‘user_name’
      • user_name 为空时，表示匿名用户，如 ”@’localhost’
    • @’host_name’
      • ‘user_name’@’%’ 的主机名部分是可选的
        • 和 ‘user_name’ 等效，后面这个 ‘%’ 可省略
      • host_name 可以是主机名或 IP 地址 (IPv4/IPv6)
        • 127.0.0.1，表示 IPv4 环回接口
        • ::1，表示 IPv6 环回接口
      • 主机名或 IP 地址中允许使用 ％ 和 _ 通配符
        • MySQL 不会对以数字和点开头的主机名执行匹配

          由于主机值中允许使用 IP 通配符值 (例如，” 198.51.100.％” 匹配子网中的每个主机) ，因此有人可以通过命名主机198.51.100.somewhere.com 来尝试利用此功能。

      • IPv4 地址，可以使用网络掩码 (host_ip/netmask)

        'david'@'198.51.100.0/255.255.255.0'
'david'@'198.51.100.44/24'  # 也支持 CIDR 表示法
        • 网络掩码符号不能用于 IPv6 地址
      • 当有多个匹配项时，会按照一定规则进行排序，然后选择匹配到第一行

  • 如果用户名和主机名是合法的，则不需要用引号引起来

    • 可以使用反引号 (`) ，单引号 (‘) 或双引号 (“) 将用户名和主机名作为标识符或字符串引号
    • 如果使用引号，user_name 和 host_name 要分别引起来
  • 使用函数查询账户名
    • user()
    • current_user()
    • current_user

预留账户

  • ‘root’@’localhost’
    • 管理员账户
  • ‘mysql.sys’@’localhost’
    • 相当于一个 root 的备用账户，当 root 账户被删除时，可以使用这个账户
    • 账户已被锁定，不能用于客户端连接
  • ‘mysql.session’@’localhost’
    • 插件在内部用于访问服务器
    • 账户已被锁定，不能用于客户端连接
  • ‘mysql.infoschema’@’localhost’
    • 用作 INFORMATION_SCHEMA 视图的 DEFINER
    • 相当于一个 root 的备用账户，当 root 账户被删除时，可以使用这个账户
    • 账户已被锁定，不能用于客户端连接

账户类别

系统账户和普通账户

  • 具有 SYSTEM_USER 权限的是系统账户
    • 系统账户可以修改系统/普通账户
    • SYSTEM_USER 权限的操作影响
      • 账户操作
      • 杀死当前会话和在其中执行的语句
      • 为存储的对象设置 DEFINER 属性
      • 指定强制角色
        • 具有 SYSTEM_USER 权限的角色不能列在系统变量 mandatory_roles 中
  • 无 SYSTEM_USER 权限的是普通账户
    • 普通账户只能修改普通账户
    • 可以为普通账户赋予 SYSTEM_USER 权限，但是这个账户可能还需要其他权限

系统会话和普通会话

  • 具有 SYSTEM_USER 权限的是系统会话
  • 无 SYSTEM_USER 权限的是普通会话

保护系统帐户不被常规帐户操纵

普通账户/角色，不要授予 SYSTEM_USER 权限和 mysql 库的修改权限。

  • 回收 SYSTEM_USER 权限和对 mysql 库的权限 
    CREATE USER u1 IDENTIFIED BY 'password';

GRANT ALL ON *.* TO u1 WITH GRANT OPTION;
-- GRANT ALL includes SYSTEM_USER, so at this point
-- u1 can manipulate system or regular accounts

REVOKE SYSTEM_USER ON *.* FROM u1;
-- Revoking SYSTEM_USER makes u1 a regular user;
-- now u1 can use account-management statements
-- to manipulate only regular accounts

REVOKE ALL ON mysql.* FROM u1;
-- This partial revoke prevents u1 from directly
-- modifying grant tables to manipulate accounts
  • 使对 mysql 库只有只读权限，回收其他权限 
    CREATE USER u2 IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u2;
REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u2;
  • 使对 mysql 库的指定表和列具有只读权限，回收其他权限 
    CREATE USER u3 IDENTIFIED BY 'password';
GRANT ALL ON *.* TO u3;
REVOKE ALL ON mysql.* FROM u3;
GRANT SELECT ON mysql.db TO u3;
GRANT SELECT(Host,User) ON mysql.user TO u3;

账户和权限

为账户分配权限

  • 创建账户和授予权限 
    CREATE USER 'finley'@'localhost'
  IDENTIFIED BY 'password';
GRANT ALL
  ON *.*
  TO 'finley'@'localhost'
  WITH GRANT OPTION;

CREATE USER 'finley'@'%.example.com'
  IDENTIFIED BY 'password';
GRANT ALL
  ON *.*
  TO 'finley'@'%.example.com'
  WITH GRANT OPTION;

CREATE USER 'admin'@'localhost'
  IDENTIFIED BY 'password';
GRANT RELOAD,PROCESS
  ON *.*
  TO 'admin'@'localhost';

CREATE USER 'dummy'@'localhost';

CREATE USER 'custom'@'localhost'
  IDENTIFIED BY 'password';
GRANT ALL
  ON bankaccount.*
  TO 'custom'@'localhost';

CREATE USER 'custom'@'host47.example.com'
  IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
  ON expenses.*
  TO 'custom'@'host47.example.com';

CREATE USER 'custom'@'%.example.com'
  IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
  ON customer.addresses
  TO 'custom'@'%.example.com';
  • 检查账户权限和属性 
    mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost                          |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |
+-----------------------------------------------------+

mysql> SET print_identified_with_as_hex = ON;
mysql> SHOW CREATE USER 'admin'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for admin@localhost: CREATE USER 'admin'@'localhost'
IDENTIFIED WITH 'caching_sha2_password'
AS 0x24412430303524301D0E17054E2241362B1419313C3E44326F294133734B30792F436E77764270373039612E32445250786D43594F45354532324B6169794F47457852796E32
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE CURRENT DEFAULT
  • 回收账户权限 
    REVOKE ALL ON *.* FROM 'finley'@'%.example.com';

REVOKE RELOAD ON *.* FROM 'admin'@'localhost';

REVOKE INSERT,UPDATE,DELETE ON customer.addresses FROM 'custom'@'%.example.com';

mysql> SHOW GRANTS FOR 'admin'@'localhost';  -- 验证
+---------------------------------------------+
| Grants for admin@localhost                  |
+---------------------------------------------+
| GRANT PROCESS ON *.* TO 'admin'@'localhost' |
+---------------------------------------------+
  • 删除账户 
    DROP USER 'finley'@'localhost';
DROP USER 'finley'@'%.example.com';
DROP USER 'admin'@'localhost';
DROP USER 'dummy'@'localhost';

回收部分权限

  • 使用部分回收
    • 系统变量
      • partial_revokes
        • 控制是否可以对账户设置权限限制

        • 默认是禁用的，此时如果回收部分权限会报错

          -- 未开启
mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT ON *.* TO u1;
mysql> REVOKE INSERT ON world.* FROM u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'

-- 开启
SET PERSIST partial_revokes = ON;

-- 验证
mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+
        • 一旦启用，如果有账户使用了权限限制，则禁用失败

    • 权限限制的记录保存在 mysql.user 系统表中

      • User_attributes 列 
        mysql> SELECT User, Host, User_attributes->>'.Restrictions'
       FROM mysql.user WHERE User_attributes->>'.Restrictions'  '';
+------+------+------------------------------------------------------+
| User | Host | User_attributes->>'$.Restrictions'                   |
+------+------+------------------------------------------------------+
| u1   | %    | [{"Database": "world", "Privileges": ["INSERT"]}] |
+------+------+------------------------------------------------------+
    • 限制
      • schema 部分支持 SQL 通配符 % 和 _
      • 可以对不存在的 schema 使用部分回收，但是前提是已拥有全局权限
        • 如果本身就无权限，回收权限也就没有意义
      • 权限限制可以被继承
        • 如果一个账户有权限限制，则这个账户给其他账户授权时，其他账户可能继承权限限制
      • 权限限制有时会被移除
        • 通过一个无限制权限的账户向此账户全局授权
        • 在 schema 级别授予权限
        • 回收全局权限
      • 如果一个账户在全局和 schema 级别都有权限，则必须回收两次才能完成权限限制。

  • 部分回收和显式的 schema 授权

    • 可以授予 schema 级别权限，而不是全局权限
    • 也可以授予全局权限，然后通过部分回收做权限限制
  • 禁用部分回收
    • 必须先移除权限限制
      • 查看哪些账户有权限限制 
        SELECT User, Host, User_attributes->>'.Restrictions' FROM mysql.user WHERE User_attributes->>'.Restrictions'  '';
        • mysql.user
      • 移除账户的权限限制
        • 通过一个无限制权限的账户向此账户全局授权 
          GRANT INSERT, DELETE ON *.* TO u1;
        • 在 schema 级别授予权限 
          GRANT INSERT, DELETE ON world.* TO u1;
        • 回收全局权限 
          REVOKE INSERT, DELETE ON *.* FROM u1;
        • 移除账户 
          DROP USER u1;
    • 然后禁用系统变量 partial_revokes 
      SET PERSIST partial_revokes = OFF;
  • 部分回收和复制
    • 复制结构中，如果存在部分回收的权限限制，则所有主机的权限设置应该一致

权限变更的影响

  • 如果使用账户变更的语句修改账户，服务器会自动重新加载授权表
  • 如果使用 insert、update、delete 语句修改授权表，需要手动重新加载，否则是不起作用的
    • flush privileges
    • mysqladmin flush-privilegs
    • mysqladmin reload
  • 授权表的重新加载影响所有会话

  • 全局的权限和密码对已连接的客户端无影响，只对接下来的连接影响

账户和密码

创建用户同时分配密码

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

为已有账户分配密码

ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

使用非匿名用户分配密码

ALTER USER USER() IDENTIFIED BY 'password';

从命令行分配密码 (不安全)

mysqladmin -u user_name -h host_name password "password"

锁定账户

客户端无法登录锁定账户。锁定帐户不会影响使用代理帐户或存储的程序或视图的能力。

语法

在 create user/alter user 后面加上 account lock/account unlock 来创建或修改锁定账户。

create user test2@localhost identified by 'test2' account lock;
alter user test2@localhost account unlock;

查看锁定状态

  • mysql.user
    • account_locked 列
  • 状态变量
    • Locked_connects
      • 尝试登录锁定账户的次数

帐户资源限制

系统变量

  • max_user_connections
    • 最大客户端连接数。
    • 只限制了连接数量，对连接后的操作无限制

对单个账户进行限制

  • 语法

    create user/alter user 加 with 子句。

    • 限定项
      • MAX_QUERIES_PER_HOUR
        • 每小时的查询数量
      • MAX_UPDATES_PER_HOUR
        • 每小时的更新数量
      • MAX_CONNECTIONS_PER_HOUR
        • 每小时连接次数
      • MAX_USER_CONNECTIONS
        • 每个账户的并发连接数
        • 如果既设置了全局系统变量 max_user_connections，又设置了账户限制 MAX_USER_CONNECTIONS
          • 如果账户限制有值，以账户限制为准
          • 如果账户限制为0，以全局变量为准
    • 设置限制
      • create user 
        CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
 WITH MAX_QUERIES_PER_HOUR 20
      MAX_UPDATES_PER_HOUR 10
      MAX_CONNECTIONS_PER_HOUR 5
      MAX_USER_CONNECTIONS 2;
      • alter user 
        mysql> ALTER USER 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
    • 解除限制

      设置成0，表示无限制。

      mysql> ALTER USER 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
  • 判断账户
    • mysql.user 表中的一行就是一个账户
  • 存储位置
    • 这些限制存储在 mysql.user 的列中
      • max_questions
      • max_updates
      • max_connections
      • max_user_connections
  • 资源计数
    • 是按账户计数的，建立多个连接无法增加限制数
    • 资源计数重置
      • 重置所有账户的资源计数为0
        • flush user_resources
        • 或重新加载授权表
          • flush privileges;
          • mysqladmin reload
      • 重置单个账户的资源计数
        • alter user 再次设置一遍

      • 服务器启动后，从0开始重新计数

发表于：MySQL
2021-05-18
# MySQL# 安全# 访问控制和账户管理# 账户
复制链接
英仔
版权声明：本站原创文章，由 英仔 2021-05-18发表，共计7552字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
mysqladmin-服务器管理程序
MySQL 账户
mysqldumpslow-慢查询日志分析工具
MySQL 版本
评论（没有评论）