• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL用户和权限管理

MySQL 彭东稳 8年前 (2016-04-03) 25381次浏览 已收录 2个评论

一、MySQL权限体系

MySQL 的认证是“用户”加“主机”形式,而权限是访问资源对象,MySQL 服务器通过权限表来控制用户对数据库的访问,权限表存放在 mysql 数据库中,初始化数据库时会初始化这些权限表。存储账户权限信息表主要有下面 5 张:

  • user
  • db
  • tables_priv
  • columns_priv
  • procs_priv

MySQL 5.6 之前还有 host 表,现在已经把 host 内容整合进 user 表。

官方文档对权限有比较详细的描述,为了方便我把其中的表格列在下面。第一列表示所有的权限,可以在 Grant 语句中指定的,第二列是对应权限存储在系统数据库 mysql 几张表中的定义,第三列表示权限作用的范围,其中 Global(Server administration)对应 mysql.user 表,Database 对应 mysql.db 表,Tables 对应 mysql.tables_priv 表,Columns 对应 mysql.columns_priv 表,Stored routines 对应 mysql.procs_priv 表。

Privilege Column Context
ALL [PRIVILEGES] Synonym for “all privileges” Server administration
ALTER Alter_priv Tables
ALTER ROUTINE Alter_routine_priv Stored routines
CREATE Create_priv Databases, tables, or indexes
CREATE ROUTINE Create_routine_priv Stored routines
CREATE TABLESPACE Create_tablespace_priv Server administration
CREATE TEMPORARY TABLES Create_tmp_table_priv Tables
CREATE USER Create_user_priv Server administration
CREATE VIEW Create_view_priv Views
DELETE Delete_priv Tables
DROP Drop_priv Databases, tables, or views
EVENT Event_priv Databases
EXECUTE Execute_priv Stored routines
FILE File_priv File access on server host
GRANT OPTION Grant_priv Databases, tables, or stored routines
INDEX Index_priv Tables
INSERT Insert_priv Tables or columns
LOCK TABLES Lock_tables_priv Databases
PROCESS Process_priv Server administration
PROXY See proxies_priv table Server administration
REFERENCES References_priv Databases or tables
RELOAD Reload_priv Server administration
REPLICATION CLIENT Repl_client_priv Server administration
REPLICATION SLAVE Repl_slave_priv Server administration
SELECT Select_priv Tables or columns
SHOW DATABASES Show_db_priv Server administration
SHOW VIEW Show_view_priv Views
SHUTDOWN Shutdown_priv Server administration
SUPER Super_priv Server administration
TRIGGER Trigger_priv Tables
UPDATE Update_priv Tables or columns
USAGE Synonym for “no privileges” Server administration

权限描述

ALLALL PRIVILEGES

授予特定级别的所有权限(不包括 GEANT OPTIION),例如,在全局或表级别分别授予所有权限。

ALTER

允许使用 ALTER TABLE 语句来改变表的结构。 ALTER TABLE 也需要 CREATE 和 INSERT 权限。重命名表需要 ALTER 和 DROP 旧表,CREATE 和 INSERT 新表。

ALTER ROUTINE

支持更改或删除存储例程(存储过程和函数)的语句。

CREATE

允许创建新的数据库和表的语句。

CREATE ROUTINE

允许创建存储例程(存储过程和函数)的语句。

CREATE TEMPORARY TABLES

允许使用CREATE TEMPORARY TABLE语句临时表的创建。

CREATE USER

允许使用 ALTER USER、CREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGES 的语句。

CREATE VIEW

允许使用 CREATE VIEW 语句。

DELETE

允许从数据库中的表中删除的行。

DROP

允许移除(删除)现有数据库,表和视图。有 DROP 权限才能使用 ALTER TABLE … DROP PARTITION 语句。同样 TRUNCATE TABLE 也需要 DROP 权限。

EVENT

允许创建、修改、删除和显示事件。

EXECUTE

允许执行存储程序(存储过程和函数)的语句。

FILE

影响下面的操作和服务器行为:

  • 允许读取和写入文件,在服务器上使用 LOAD DATA 和 SELECT … INTO OUTFILE 语句,以及 LOAD_FILE() 函数。拥有 FILE 权限的用户可以读取 MySQL 服务器的任何文件。
  • 允许在 MySQL 服务器具有写权限的任何目录中创建新文件。这包括数据目录和授权表文件。
  • 在 MySQL 5.7.17 中,允许在 CREATE TABLE 语句中使用数据目录和索引目录选项。

作为一种安全措施,服务器不会覆盖现有文件。

要限制哪些文件可以读取和写入的位置由系统变量 secure_file_priv 来设置为特定的目录。

GRANT OPTION

可以授予或从其他用户撤消某些权限,但对其他用户授予或撤销的权限你自己本身得有,比如给其他用户添加 SELECT 权限,那么你自己除了有 GRANT OPTION 权限外,还需要有 SELECT 权限。

INDEX

允许创建或删除索引,INDEX 权限适用于现有的表。如果你拥有 CREATE 表的权限,你是可以在 CREATE TABLE 时定义索引。

INSERT

能够插入数据行到数据库中的表。INSERT 还需要为 ANALYZE TABLE,OPTIMIZE TABLE 和 REPAIR TABLE 表维护语句。

LOCK TABLES

允许使用显式的 LOCK TABLES 语句来锁定你拥有 SELECT 权限表。这包括使用写锁,从而防止其他会话读取锁定的表。

PROCESS

允许显示有关服务器内执行的线程信息显示(即,有关的语句信息由会话执行)。此权限允许使用 SHOW PROCESSLIST 或 mysqladmin 的 processlist 看到属于其他帐户的线程,你总能看到你自己的线程。 PROCESS 权限还允许使用 SHOW ENGINE 语句。

PROXY

使一个用户假冒或伪装为另一个用户,拥有另一个用户的所有权限。See Section 6.2.14, “Proxy Users”.

REFERENCES

外键约束的创建需要父表的 REFERENCES 特权。

REPLICATION CLIENT

允许使用 SHOW MASTER STATUS,SHOW SLAVE STATUS 和 SHOW BINARY LOGS 语句。

REPLICATION SLAVE

授予此权限的账户允许请求获取主服务器上的更新(请求二进制文件),允许使用 SHOW MASTER STATUS,SHOW SLAVE STATUS 和 SHOW BINARY LOGS 语句。使用 mysqlbinlog 选项 --read-from-remote-server(-r)--read-from-remote-master 也需要此权限。

SELECT

使得能够从数据库中的表中查询行数据。SELECT 语句需要 SELECT 权限,仅当它们访问表的时候。一些 SELECT 语句不需要访问表,可以在没有任何数据库权限的时候执行。例如,你可以使用 SELECT 作为一个简单的计算器计算表达式。

SHOW DATABASES

启用该帐户通过发出 SHOW DATABASE 语句来看看数据库名。没有此权限的账户只能看到他们有一些权限的数据库,如果服务器用--skip-show-database选项则不能使用 SHOW DATABASE。

SHOW VIEW

允许使用 SHOW CREATE VIEW 语句。

SHUTDOWN

允许使用 SHUTDOWN、mysqladmin shutdown 关闭命令,和 mysql_shutdown() C API 函数。

SUPER

影响下面的操作和服务器行为:

  • 允许修改全局系统变量来更改服务器配置。对于一些系统变量,设置会话级别也需要 SUPER 权限。如果一个系统变量设置会话级别也需要特殊权限,那么变量描述会进行表明。例如 binlog_format,SQL_LOG_BIN 和 sql_log_off。
  • 允许更改全局事务隔离级别和一些其他特性。
  • 允许启动和停止复制,包括组复制。
  • 允许使用 CHANGE MASTER 和 CHANGE REPLICATION FILTER 语句。
  • 允许 PURGE BINARY LOGS 和 BINLOG 语句。
  • 使执行视图或存储的程序时设定的有效授权ID。具有此权限的用户可以在视图或存储的程序的属性DEFINER指定任何帐户。
  • 允许使用 CREATE SERVER、ALTER SERVER 和 DROP SERVER 语句。
  • 允许使用 mysqladmin debug 命令。
  • 允许启用 InnoDB 的加密密钥轮询。
  • 允许启用由 DES_ENCRYPT() 函数读取 DES 密钥文件。
  • 允许在客户端连接控制不允许非超级帐户的行为:
    • 允许使用 KILL 语句或 mysqladmin kill 命令来杀死属于其他帐户线程。(一个帐户可以随时杀死自己的线程)
    • 当 SUPER 客户端连接服务器不执行 init_connect 系统变量的内容。
    • 服务器接受来自 SUPER 客户端的一个连接,即使在达到由 MAX_CONNECTIONS 系统变量配置的连接限制。
    • 可以在启用 READ_ONLY 系统变量的情况下依然可以进行更新操作,这适用于已经存在的表的更新,并可以使用账户管理语句,如 GRANT 和 REVOKE 隐式地更新表。

TRIGGER

允许 trigger 操作。你必须拥有这个权限一个表来创建,删除,执行,或该表显示触发器。

UPDATE

允许更新数据库中的表的行。

USAGE

此权限说明符的意思是“没有权限”。

二、GRANT语句

GRANT 语句赋予对应用户相应的权限,会根据不同的语法存储到不同的表中,以链接中官方文档中的语句为例:

Global Privileges

其中 *.* 表示所有数据的所有表,对应的权限会保存在 mysql.user 表中,和 user 相关联。

user表

user 表是 MySQL 中最重要的一个权限表,记录允许连接到服务器的账号信息,里面的权限是全局级的。例如:一个用户在 user 表中被授予了 DELETE 权限,则该用户可以删除 MySQL 服务器上所有数据库的任何记录。user 表中大概有 45 个字段,这些字段大概可以分为 4 类,分别是用户列、权限列、安全列和资源控制列,详细解释如下:

  • Host、User:表示主机和用户,是 user 表的主键。
  • *-priv:此类型的字段都是权限列,权限列的字段决定了用户的权限,描述了全局范围内允许对数据和数据库进行的操作。包括查询、修改、删除等普通权限,还有包括了关闭服务器、超级权限和加载用户等高级权限。user表对应的权限是针对所有用户数据库的,这些字段的类型为ENUM,可以取的值只能为Y或N,Y表示该用户有对应的权限;N表示没有。
  • ssl_type、ssl_cipher、x509_issuer、x509_subject:这几个字段是安全连接相关的,SSL和证书。
  • max_questions、max_updates、max_connections、max_user_connections:这几个字段是用户资源限制相关的,如此用户某个时间内最大查询、更新、连接等操作。
  • authentication_string、account_locked、password_lifetime、password_last_changed:这几个字段是跟用户密码相关的,部分都是MySQL 5.7新增字段,作用分别是标记账号锁定、密码存活时间和密码改变时间;

MySQL 5.7去掉了password字段,由authentication_string替换,因为5.7更换了密码插件,因此存储密码的字段更换了。

Database Privileges

其中 mydb.* 表示 mydb 数据库下的所有表,对应的权限会保存在 mysql.db 表中,和 db 相关联。

db表

db表存储了用户对某个数据库的操作权限,决定用户能从哪个主机哪个用户来操作哪个数据库。User表中存储了某个主机和用户对数据库的操作权限,配置和db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受GRANTREVOKE语句的影响,字段大致可以分为两类:用户列和权限列,详细解释如下:

  • Host、Db、User:表示主机、数据库和用户,是 db 表的主键。
  • *-priv:此类型的字段都是权限列,权限列的字段决定了用户的权限。user表的权限时针对所有数据库的,全局的;但如果希望某个用户只对某个数据库有相应的查询、修改、删除等普通权限,那么就需要在db表中设定,而user表只有对应的主机、用户和密码等信息。这些字段的类型为ENUM,可以取的值只能为Y或N,Y表示该用户有对应的权限;N表示没有。

Table Privileges

对应的权限保存在 mysql.tables_priv 中,和 db , user 关联。

tables_priv表

tables_priv表用来对表设置操作权限,有几个字段分别是HostDbUserTable_nameGrantorTimestampTable_privColumn_priv,各个字段说明如下:

  • HostDbUserTable_name这几个字段分表示主机名、数据库名、用户名和表名。
  • Grantor字段表示修改该记录的用户。
  • Timestamp字段表示修改该记录的时间。
  • Table_priv字段表示对表的操作权限,包括 set(‘Select’,’Insert’,’Update’,’Delete’,’Create’,’Drop’,’Grant’,’References’,’Index’,’Alter’,’Create View’,’Show view’,’Trigger’)
  • Column_priv字段表示对表中的列的操作权限,包括 set(‘Select’,’Insert’,’Update’,’References’)

Column Privileges

对应的权限保存在 mysql.tables_priv 中,和 db, table, user 关联。

columns_priv表

columns_priv表用来对表设置操作权限,有这么几个字段分别是HostDbUserTable_nameTimestamp、Column_name和Column_priv,各个字段说明如下:

  • HostDbUser和Column_name这几个字段分表示主机名、数据库名、用户名和列名。
  • Timestamp字段表示修改该记录的时间。
  • Column_priv字段表示对表中的列的操作权限,包括set(‘Select’,’Insert’,’Update’,’References’)

Stored Routine Privileges

对应的权限保存在 mysql.procs_priv 中,和 routine_name, db,user 关联。

procs_priv表

存储过程和存储函数相关的权限,分别是HostDbUserRoutine_nameRoutine_typeGrantorProc_privTimestamp,各个字段的说明如下:

  • HostDbUser字段分别表示主机名、数据库名和用户名;Routine_name表示存储过程或函数的名称。
  • Routine_type字段表示存储过程或函数的类型。
  • Routine_type字段有两个值,分别是FUNCTIONPROCEDUREFUNCTION表示这是一个函数;PROCEDURE表示这是一个存储过程。
  • Grantor字段记录是插入或修改该记录的用户。
  • Proc_priv字段表示拥有的权限,包括ExecuteAlter RoutineGrant这3种。
  • Timestamp字段表示记录更新时间。

在 GRANT 语句中的几个对象需要引用,尽管在许多情况下引用是可选的。帐户、角色、数据库、表、列和常规名称。例如,如果账户名中的 user_name 或 host_name 值作为一个未加引号的标识符是合法的,你不需要引用它。然而,在指定含有特殊字符(如-)的 user_name 字符串,或含有特殊字符或通配符(如%)的 host_name 字符串(例如,’test-user’@’%.com’)时,必须加引号,分别引用用户名和主机名。

当在 GRANT 语句中指定数据库名称时,允许使用 _ 和 % 通配符,该语句在数据库级别上授予权限(GRANT … ON db_name.*)。这意味着,例如,要使用 _ 字符作为数据库名称的一部分,在 GRANT 语句中使用\_转义字符指定它,以防止用户能够访问与通配符模式相匹配的其他数据库(例如,GRANT … ON `foo\_bar`.* TO …)。如果不转义,那么将会导致被授权用户拥有 db[a-z]name 相关数据库权限。

在权限分配中,MySQL 将数据库名称中出现的未转义的 _ 和 % SQL 通配符解释为文字字符:

  • 当数据库名称不是用于在数据库级别授予权限,而是作为授予其他对象(如表或例程)权限的限定词时(例如,GRANT … ON db_name.tbl_name)。
  • 启用 partial_revokes 导致 MySQL 将数据库名称中未转义的 _ 和 % 通配符解释为字面字符,就像它们被转义为 \_ 和 \%。因为这改变了 MySQL 解释权限的方式,所以对于可能启用 partial_revokes 的安装,最好在权限分配中避免使用未转义的通配符。更多信息,see Section 6.2.12, “Privilege Restriction Using Partial Revokes”

三、MySQL访问控制两阶段

阶段1:客户端连接核实阶段

客户端连接核实阶段,当连接 MySQL 服务器时,服务器基于用户的身份以及用户是否能通过正确的密码身份验证,来接受或拒绝连接。即客户端用户连接请求中会提供用户名称、主机地址和密码,MySQL 使用 user表中的三个字段(HostUserPassword)执行身份检查,服务器只有在 user 表记录的 Host User 字段匹配客户端主机名和用户名,并且提供正确的面貌时才接受连接。如果连接核实没有通过,服务器完全拒绝访问;否则,服务器接受连接,然后进入 阶段2 等待用户请求。

阶段2:客户端操作核实阶段

客户端操作核实阶段,当客户端的连接请求被 MySQL 服务器端通过其身份认证后。那么接下来就可以发送数据库的操作命令给服务器端处理,服务器检查用户要执行的操作,在确认权限时,MySQL 首先检查 user 表,如果指定的权限没有在 user 表中被授权;MySQL 将检查 db 表,db 表时下一安全层级,其中的权限限定于数据库层级,在该层级的 SELECT 权限允许用户查看指定数据库的所有表中的数据;如果在该层级没有找到限定的权限,则 MySQL 继续检查 tables_priv 表以及 columns_priv 表,如果所有权限表都检查完毕,但还是没有找到允许的权限操作,MySQL 将返回错误信息,用户请求的操作不能执行,操作失败。其过程大概如下图:

MySQL用户和权限管理

整体认证的思路比较简单,对于权限的判断自然是自上而下的,假如一个用户有对某个数据库的写权限,自然不必继续判断对该数据库下的某个表是否有写权限。考虑一下这种情况,假如一个用户对某个数据库只有 SELECT 权限,但是对该数据库其中的一张表只有 INSERT 权限,自上而下先判断数据库的权限,无法满足,接着再判断表的权限,依然无法满足。但是数据库的 SELECT 权限实际上表示对表也有 SELECT 权限,只是没有保存到 mysql.tables_priv 表中罢了。所以在自上而下认证的过程中需要把上级已经获得的权限传递给下级。权限的使用频率非常高,如果每次都从数据库中查找效率太低,MySQL 将其缓存起来,在早期月报中就讨论过权限的缓存,可以参考。

权限缓存

用户在连接数据库的过程中,为了加快权限的验证过程,系统表中的权限会缓存到内存中。例如:mysql.user 缓存在数组 acl_users 中,mysql.db 缓存在数组 acl_dbs 中,mysql.tables_priv 和 mysql.columns_priv 缓存在 hash 表 column_priv_hash 中,mysql.procs_priv 缓存在 hash 表 proc_priv_hash 和 func_priv_hash 中。

另外 acl_cache 缓存 db 级别的权限信息。例如执行 use db 时,会尝试从 acl_cache 中查找并更新当前数据库权限(thd->security_ctx->db_access)。

权限更新过程

以 grant select on test.t1 为例:

  1. 更新系统表 mysql.user,mysql.db,mysql.tables_priv;
  2. 更新缓存 acl_users,acl_dbs,column_priv_hash;
  3. 清空 acl_cache。

FLUSH PRIVILEGES

FLUSH PRIVILEGES 会重新从系统表中加载权限信息来构建缓存。

当我们通过 SQL 语句直接修改权限系统表来修改权限时,权限缓存是没有更新的,这样会导致权限缓存和系统表不一致。因此通过这种方式修改权限后,应执行 FLUSH PRIVILEGES 来刷新缓存,从而使更新的权限生效。

通过 GRANT/REVOKE/CREATE USER/DROP USER 来更新权限是不需要 FLUSH PRIVILEGES的。

Note

当前连接修改了权限信息时,现存的其他客户连接是不受影响的,权限在客户的下一次请求时生效。

四、MySQL用户及密码管理

MySQL提供许多语句用来管理用户账号,这些语句可以用来管理包括登陆和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性,需要通过账户管理来保证。下面介绍四种用来管理账号密码的方式:

1. 通过mysqladmin工具(只能改密码)

2. 通过CREATE USER语句

创建登录用户,MySQL的登录用户必须是’USERNAME’@’HOST’(用户名加主机名),如’mysql’@’172.16.16.1′,含义是只有在172.16.16.1这台主机上才可以使用mysql用户登录MySQL数据库(还可以指定只允许登录那个数据库)。

HOST的表现方式:

1. IP地址,如172.16.16.1;

2. 主机名,如localhost;

3. 网络地址,如172.16.0.0

4. 通配符,如

%:匹配任意字符

_:匹配任意单个字符如172.16.16._(允许172.16.16.1-172.16.16.9)

3. 通过直接修改mysql.user表的用户记录

改完记得刷新内存中现有的表,另外这种形式在MySQL 5.7中使用会被警告,会告诉你这是一个即将被移除的特性。MySQL 5.7提供了新的更改密码的方式ALTER USER语句。

4. 通过GRANT指令(只能用于添加新用户)

同样,通过grant创建用户在MySQL 5.7中使用会被警告,是一个即将被移除的特性。推荐使用CREATE USER语句。

虽然介绍了好几种方法创建用户,但真正在使用中,最好按照规范使用CREATE USER创建用户,GRANT设置权限,ALTER USER更改密码,而不要直接将用户信息插入user表中,因为user表中存储了全局级别的权限以及其他的账户信息,如果意外破坏了user表中的记录,则可能会对MySQL服务器造成很大的影响。

五、MySQL管理员密码找回

1. 关闭MySQL

2. 在配置文件中[mysqld]字段添加skip-grant-tables指令,跳过授权表

3. 给root用户登录mysql设置密码为redhat并以加密方式

MySQL5.7修改密码

六、MySQL权限管理

权限管理主要是对登录到MySQL的用户进行权限验证,所有用户的权限都存储在MySQL的权限表中,不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能时证实连接到一台给定主机的用户,并且赋予该用户在数据库上的SELECT/INSERT/UPDATEDELETE权限。

1. MySQL权限说明

账户权限信息被存储在MySQL数据库的几张权限表中,在MySQL启动时,服务器将这些数据库表中权限信息的内容读入内存。其中GRANTREVOKE语句所涉及的常用权限大致如下这些:CREATEDROPSELECTINSERTUPDATEDELETEINDEXALTERCREATE、ROUTINEFILE等,还有一个特殊的proxy权限,是用来赋予某个用户具有给他人赋予权限的权限。

2. MySQL用户授权

授权就是为某个用户授予权限,合理的授权可以保证数据库的安全,MySQL中可以使用GRANT语句为用户授予权限。授权可以分为多个层次:

全局层级:全局权限适用于一个给定服务器中的所有数据库,这些权限存储在mysql.user表中。

数据库层级:数据库权限适用于一个给定数据库中的所有目标,这些权限存储在mysql.db表中。

表层级:表权限适用于一个给定表中的所有列,这些权限存储在mysql.tables_priv表中。

列层级:列权限使用于一个给定表中的单一列,这些权限存储在mysql.columns_priv表中。

子程序层级:CREATE ROUTINEALTER ROUTINEEXECUTEGRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且,除了CREATE ROUTINE外,这些权限可以被授予子程序层级,并存储在mysql.procs_priv表中。

PSMySQL中必须拥有GRANT权限的用户才可以执行GRANT语句。

2.1 GRANT赋予用户权限

all表示赋予用户全部权限(包含存储过程、存储函数等创建和执行)。当数据库名称.表名称被*.*代替,表示赋予用户操作服务器上所有数据库所有表的权限。用户地址可以是localhost,也可以是ip地址、机器名字、域名。也可以用’%’表示从任何地址连接。而’连接口令’不能为空,否则创建失败。

2.2 REVOKE移除用户权限

2.3 SHOW查看用户的权限

使用REVOKE收回权限之后,用户帐户的记录将从dbhosttables_privcolumns_priv表中删除,但是用户帐号记录依然在user表中保存。

3. 数据库开发人员,创建表、索引、视图、存储过程、函数等权限授权

grant创建、修改、删除MySQL数据表结构权限

grant操作MySQL外键权限

grant操作MySQL临时表权限。

grant操作MySQL索引权限

grant操作MySQL视图、查看视图源代码 权限

grant操作MySQL存储过程、存储函数权限

<参考>

MySQL · 源码分析 · 权限浅析

https://dev.mysql.com/doc/refman/5.7/en/security.html


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!

(2)个小伙伴在吐槽