Solo  当前访客：13 开始使用
极客说
记录精彩的程序人生

MySQL用户管理（十二）

作者：wangdj | 更新日期: 2024-11-16 21:17 | 浏览数： 0 | 评论数： 0

标签： , , ,

MySQL用户管理

  • MySQL是一个多用户数据库，可以为不同用户指定允许的权限。

权限表

  • MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由MySQL_install_db脚本初始化。存储账户权限信息表主要有user、db、host、tables_priv、columns_priv和procs_priv。

user表

  • user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账户信息，里面的权限是全局的。

  1. 用户列

    user表的用户列包括Host、User、authentication_string，分别表示主机名、用户名和密码。其中，User和Host为User表的联合主键。

  2. 权限列

    权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库；高级权限用于数据库管理。如果要修改权限，可以使用GRANT语句或UPDATE语句更改user表的这些字段来修改用户对应的权限。

  3. 安全列

    安全列只有6个字段，其中两个是ssl相关的，两个是x509相关的，另外两个是授权插件相关的。Ssl用于加密；x509标准可用于标识用户；Plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。另外，可以通过SHOW VARIABLES LIKE 'have_openssl' 语句来查询服务器是否支持ssl功能。

  4. 资源控制列

    资源控制列的字段用来限制用户使用的资源，包括4个字段，分别为：

    （1）max_questions——用户每小时允许执行的查询操作次数。

    （2）max_updates——用户每小时允许执行的更新操作次数。

    （3）max_connections——用户每小时允许执行的连接操作次数。

    （4）max_user_connections——用户允许同时建立的连接次数。

    一个小时内用户查询或者连接数量超过资源控制权限，用户将被锁定，直到下一个小时才可以在此执行对应的操作。

db表和host表

  • Db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。Host表中存储了某个主机对数据库的操作权限。这些权限表不受GRANT和REVOKE语句的影响。

  1. 用户列

    db表用户列有3个字段，分别是Host、User、Db，标识从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。host表不存储用户名称，用户列只有2个字段，分别是Host和Db，表示从某个主机连接的用户对某个数据库的操作权限，其主键包括Host和Db两个字段。一般情况下db表就可以满足权限控制需求了。

  2. 权限列

    权限列决定用户操作的权限。当有用户连接到MySQL服务器时，db表中没有用户登录的主机名称，则MySQL会从host表中查找相匹配的值，并根据查询的结果决定用户的操作是否被允许。

tables_priv表和columns_priv表

  • tables_priv表用来对表设置权限，columns_priv表用来对表的某一列设置权限。

procs_priv表

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

账户管理

  • MySQL提供许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性需要通过账户管理来保证。

登录和退出MySQL服务器

  • 通过MySQL -help 命令可以查看MySQL命令帮助信息，MySQL命令的常用参数如下：

    （1）-h 主机名，可以使用该参数指定主机名或ip，如果不指定，默认是localhost。

    （2）-u 用户名，可以使用该参数指定用户名。

    （3）-p 密码，可以使用该参数指定登录密码。如果该参数后面有一段字段，则该段字符串将作为用户的密码直接登录。如果后面没有内容，在登录的时候就会提示输入密码。注意：该参数后面的字符串和-p之间不能有空格。

    （4）-P 端口号，该参数后面接MySQL服务器的端口号，默认为3306。

    （5）数据库名，可以在命令的最后指定数据库名。

    （6）-e 执行SQL语句。如果指定了该参数，就将在登录后执行-e后面的命令或SQL语句并退出。

新建普通用户

  • 在MySQL数据库中，有两种方式创建新用户：一种是使用CREATE USER或GRANT语句；另一种是直接操作MySQL授权表。

  1. 使用CREATE USER语句创建新用户

    • 执行CREATE USER 或GRANT 语句时，服务器会修改相应的用户授权表，添加或者修改用户及其权限。CREATE USER 语句的基本语法格式如下：

      CREATE USER user_specification
	[,user_specification] ...

user_specification:
	user@host
	[
        IDENTIFIED BY [PASSWORD] 'password'
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
    ]

      IDENTIFIED BY表示用来设置用户的密码；

      [PASSWORD] 表示使用哈希值设置密码，该参数可选；

      'password'表示用户登录时使用的普通明文密码；

      IDENTIFIED WITH语句为用户指定一个身份验证插件；

      auth_plugin是插件的名称，可以是一个带单引号的字符串，或者带引号的字符串，或者带引号的字符串；

      auth_string是可选的字符串参数，传递给身份验证插件，由插件解释该参数的意义。

      注意：user和host都为字符串形式，需要加引号。

    • 使用CREATE USER语句的用户必须有全局的CREATE USER权限或MySQL数据库的INSERT权限。新添加的用户没有任何权限。

    • 如果在创建用户时未指明主机名，则主机名部分默认为 '%' （对所有的主机开放权限）。

    • 如果用户登录不需要密码，可以省略IDENTIFIED BY部分。

    • 对于使用插件认证连接的用户，服务器调用指定名称的插件。如果创建用户时或者连接服务器时，服务器找不到对应的插件，将返回一个错误。

    • IDENTIFIED BY和IDENTIFIED WITH是互斥的，所以对于一个账户来说只能使用一个验证方法。

  2. 使用GRANT语句创建用户

    • CREATE USER语句创建的新用户没有任何权限，还需要使用GRANT语句赋予用户权限。而GRANT语句不仅可以创建新用户，还可以在创建的同时对用户授权。

    • GRANT还可以指定用户的其他特点，如使用安全连接、限制使用服务器资源等。

    • 使用GRANT语句创建新用户时必须有GRANT权限，GRANT语句的基本语法格式如下：

      GRANT privileges ON db.table
TO user@host [IDENTIFIED BY 'password'] [,user [IDENTIFIED BY 'password'] ]
[WITH GRANT OPTION]

      privileges表示赋予用户的权限类型；

      WITH GRANT OPTION为可选参数，表示对新建立的用户赋予GRANT权限，即该用户可以对其他用户赋予权限。

    • 注意：User表中的user和host字段区分大小写，在查询的时候要指定正确的用户名称或者主机名。

  3. 直接操作MySQL用户表

    • 可以使用INSERT语句向user表直接插入一条记录来创建一个新的用户。使用INSERT语句，必须拥有对MySQL.user表的INSERT权限。使用INSERT语句创建新用户的基本语法格式如下：

      INSERT INTO MySQL.user(Host,User,authentication_string,[privilegelist])
VALUES('host','username',PASSWORD('passwod'),privilegevaluelist);

      privilegevaluelist为对应的权限的值，只能取 'Y' 或者 'N' 。

删除普通用户

  • 在MySQL数据库中，可以使用DROP USER语句删除用户，也可以直接通过DELETE从MySQL.user表中删除对应的记录来删除用户。

  1. 使用DROP USER语句删除用户

    • DROP USER 语句语法如下：

      DROP USER user [,user];

    • 要使用DROP USER，必须拥有MySQL数据库的全局CREATE USER权限或DELETE权限。删除用户时可以指定主机名以防出错，例如 'user'@'localhost'。

    • DROP USER不能自动关闭任何打开的用户对话。如果用户有打开的对话，此时取消用户，命令则不会生效，直到用户对话被关闭后才能生效。一旦对话被关闭，用户也被取消，此用户再次试图登录时将会失败。

  2. 使用DELETE语句删除用户

    DELETE 语句基本语法格式如下：

    DELETE FROM MySQL.user WHERE host = 'hostname' AND user = 'username';

修改用户的密码

  1. 使用mysqladmin命令在命令行指定新密码

    mysqladmin命令的基本语法格式如下：

    mysqladmin -u username -h hostname -p password "newpwd"

    注意：新密码要用双引号。

  2. 修改MySQL数据库的user表

    用户登录到MySQL服务器后，使用UPDATE语句修改MySQL数据库的user表的authentication_string字段，从而修改用户的密码。使用UPDATE语句修改用户密码的语句如下：

    UPDATE MySQL.user SET authentication_string = PASSWORD("rootpwd") WHERE user = "username" AND host = "hostname";

    密码修改成功之后，使用FLUSH PRIVILEGES语句重新加载权限。

  3. 使用SET语句修改用户的密码

    SET PASSWORD语句可以用来重新设置其他用户登录密码或自己使用的账户的密码。使用SET语句修改用户密码的语法结构如下：

    SET PASSWORD [FOR 'username'@'hostname'] = PASSWORD("newpwd");

  4. 使用GRANT语句修改用户密码

    可以在全局级别使用GRANT USAGE语句（* . *）指定某个账户的密码而不影响账户当前的权限，使用GRANT语句修改密码，必须拥有GRANT权限。GRANT USAGE的基本语句格式如下：

    GRANT USAGE ON *.* TO 'username@hostname'
IDENTIFIED BY 'passwd';

    使用GRANT...IDENTIFIED BY语句或mysqladmin password命令设置密码，它们均会加密密码。在这种情况下，不需要使用PASSWORD()。

root用户密码丢失的解决方法

  • 对于root用户密码丢失这种特殊情况，MySQL实现了对应的处理机制。可以通过特殊方法登录到MySQL服务器，然后在root用户下重新设置密码。

  1. 使用--skip--grant-tables选项启动MySQL服务

    以skip-grant-tables选项启动时，MySQL服务器将不加载权限判断，任何用户都能访问数据库。在Windows操作系统中，可以使用mysqld或mysqld-nt来启动MySQL服务进程。若MySQL目录为添加到环境变量中，则需要先在命令行下切换到MySQL的bin目录。

    mysqld命令如下：

    mysqld --skip-grant-tables

    mysqld-nt命令如下：

    mysqld-nt --skip-grant-tables

    在Linux操作系统中，使用mysqld_safe来启动MySQL服务。也可以使用 /etc/init.d/mysql 命令来启动MySQL服务。

    mysqld_safe命令如下：

    mysqld_safe --skip-grant-tables user = mysql

    /etc/init.d/mysql命令如下：

    /etc/init.d/mysql start-mysqld --skip-grant-tables

    启动mysqld服务前需先停止当前MySQL服务进程，命令如下：

    NET STOP MySQL

    注意：命令执行之后，用户无法输入指令，需要打开另外一个命令行窗口，输入不加密码的登录指令

    mysql -u root

权限管理

  • 权限管理主要是对登录到MySQL的用户进行权限验证。MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限。

授权

  • MySQL中可以使用GRANT语句为用户授予权限。授予的权限可以分为多个层级。

  1. 全局层级

    全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON * . * 和 REVOKE ALL * . * 只授予和撤销全局权限。

  2. 数据库层级

    数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.* 和 REVOKE ALL ON db_name.* 只授予和撤销数据库权限。

  3. 表层级

    表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。GRANT ALL ON db.name.tb1_name 和 REVOKE ALL ON db_name.tb1.name 只授予和撤销表的权限。

  4. 列层级

    列权限适用于一个给定表中的单个列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，必须指定宇被授权列相同的列。

  5. 子程序层级

    CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在mysql.procs_priv表中。

  • 要使用GRANT或REVOKE，必须拥有GRANT OPTION权限，并且必须用于正在授予或撤销的权限。GRANT的语法如下：

    GRANT priv_type [(columns)] [,priv_type [(columns)] ] ....
ON [object_type] table1,table2,...tablen
TO user [IDENTIFIED BY [password] 'password']
[,user [IDENTIFIED BY [password] 'password'] ]...
	[WITH GRANT OPTION]

object_type = TABLE | FUNCTION | PROCEDURE

    object_type指定授权作用的对象类型包括TABLE（表）、FUNCTION（函数）和PROCEDURE（存储过程）；

    user参数表示用户账户，由用户名和主机名构成。

    WITH关键字后可以跟一个或多个with_option参数。这个参数有5个权限，意义如下：

    1. GRANT OPTION：被授权的用户可以将这些权限授予别的用户。
    2. MAX_QUERIES_PER_HOUR count：设置每小时可以执行count次查询。
    3. MAX_UPDATES_PER_HOUR count：设置每小时可以执行count次更新。
    4. MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立count个连接。
    5. MAX_USER_CONNECTIONS count：设置单个用户可以同时建立count个连接。

收回权限

  • MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账号记录仍然在user表中保存。

  1. 收回所有用户的所有权限，用于取消对于已命名的用户的所有全局层级、数据库层级、表层级和列层级的权限，其语法如下：

    REVOKE ALL PRIVILEGES,GRANT OPTION
FROM 'user'@'host' [,'user'@'host'...];

    REVOKE语句必须和FROM语句一起使用，FROM语句指明需要收回权限的用户。

  2. 长格式的REVOKE语句，基本语法如下：

    REVOKE priv_type [(columns)] [,priv_type [(column)] ]...
ON table1,table2,...tablen
FROM 'user'@'host' [,'user'@'host'...]

    要使用REVOKE语句，必须拥有MySQL数据库的全局CREATE USER权限或UPDATE权限。

  • 当从旧版本的MySQL升级时，如果要使用EXECUTE、CREATE VIEW、SHOW VIEW、CREATE USER、CERATE ROUTINE和ALTER ROUTINE权限，必须首先升级授权表。

查看权限

  • SHOW GRANTS语句可以显示指定用户的权限信息。基本语法格式如下：

    SHOW GRANTS FOR 'user'@'host';

  • 通过user表查看指定用户的权限信息，基本语法格式如下：

    SELECT privileges_list FROM MySQL.user WHERE user = 'username' AND host = 'hostname';

访问控制

  • MySQL的访问控制分为两个阶段：连接核实阶段和请求核实阶段。

连接核实阶段

  • 当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证来接受或拒绝连接。客户端连接请求中会提供用户名称、主机地址名和密码，MySQL使用user表中的3个字段（Host、User和Password）执行身份检查。

请求核实阶段

  • MySQL通过向下层级的顺序检查权限表（从user表到columns_priv表），检查用户是否有足够的权限执行指定的操作，这些权限可以来自user、db、host、tables_priv或columns_priv表。

注意事项

  • 数据库安全需要注意user数据表中是否存在匿名用户。在user表中匿名用户的User字段值为空字符串，这会允许任何人连接到数据库，检测是否存在匿名登录用户的方法的SQL语句如下：

    SELECT * FROM MySQL.user WHERE User = '';

  • 创建用户的方法有：GRANT语句、CREATE USER语句和直接操作user表。一般情况下，最好使用GRANT或者CREATE USER语句，而不要直接将用户信息插入user表，因为user表存储了全局级别的权限以及其他的账户信息，如果意外破坏了user表中的记录，则会对MySQL服务器造成很大的影响。

标题：MySQL用户管理（十二）
作者：wangdj
地址：https://qntop.com/articles/2024/11/16/1731763003079.html

新一篇：MySQL数据备份与恢复（十三）
旧一篇：MySQL触发器（十一）