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开始重新计数

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注