• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL用户和权限管理(一)

MySQL SEian.G 6年前 (2019-02-16) 3390次浏览 已收录 1个评论
文章目录[隐藏]

针对MYSQL用户和权限管理,准备分两个部分来介绍

第一部分:主要是MYSQL数据库的权限体系以及MYSQL访问控制的两个阶段;我们都知道,MYSQL初始化完成之后,自带四个默认的数据库;下面的内容主要涉及到的是mysql库中相关的内容;MYSQL用户权限管理(一)

第二部分:主要是MYSQL账户以及密码管理,会涉及到账户的创建、删除、授权等问题;MYSQL用户权限管理(二)

一、MySQL权限体系

授予MySQL帐户的权限决定了帐户可以执行的操作。MySQL权限在它们适用的上下文和不同操作级别上有所不同:
管理权限使用户能够管理MySQL服务器的操作;这些权限是全局的,因为它们不是特定于特定数据库的。
数据库权限适用于数据库及其中的所有对象。可以为特定数据库或全局授予这些权限,以便它们适用于所有数据库。
可以为数据库中的特定对象,数据库中给定类型的所有对象(例如,数据库中的所有表)或全局的所有对象授予数据库对象(如表,索引,视图和存储例程)的权限。所有数据库中给定类型的对象

MySQL的认证方式是“用户”+“主机”形式,而权限是访问资源对象,MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库中,初始化数据库时会初始化这些权限表。存储账户权限信息表主要有:user,db,tables_priv,columns_priv,procs_priv这五张表(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 表。

MySQL用户和权限管理(一)

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

Global Privileges

GRANT ALL ON *.* TO ‘someuser’@’somehost’;GRANT SELECT, INSERT ON *.* TO ‘someuser’@’somehost’;
其中 *.* 表示所有数据的所有表,对应的权限会保存在 mysql.user 表中,和 user 相关联。

  • user表
    user表是MySQL中最重要的一个权限表,记录允许连接到服务器的账号信息,里面的权限是全局级的。例如:一个用户在user表中被授予了DELETE权限,则该用户可以删除MySQL服务器上所有数据库的任何记录。
  • user表中大概有45个字段,这些字段大概可以分为4类,分别是用户列、权限列、安全列和资源控制列(详细的表结构见下),详细解释如下:
    1)Host、User:表示主机和用户,是user表的主键。
    2)*-priv:此类型的字段都是权限列,权限列的字段决定了用户的权限,描述了全局范围内允许对数据和数据库进行的操作。包括查询、修改、删除等普通权限,还有包括了关闭服务器、超级权限和加载用户等高级权限。user表对应的权限是针对所有用户数据库的,这些字段的类型为ENUM,可以取的值只能为Y或N,Y表示该用户有对应的权限;N表示没有。
    3)ssl_type、ssl_cipher、x509_issuer、x509_subject:这几个字段是安全连接相关的,SSL和证书。
    4)max_questions、max_updates、max_connections、max_user_connections:这几个字段是用户资源限制相关的,如此用户某个时间内最大查询、更新、连接等操作。
    5)authentication_string、account_locked、password_lifetime、password_last_changed:这几个字段是跟用户密码相关的,部分都是MySQL 5.7新增字段,作用分别是标记账号锁定、密码存活时间和密码改变时间;
    MySQL 5.7去掉了password字段,由authentication_string替换,因为5.7更换了密码插件,因此存储密码的字段更换了。
MySQL [(none)]> show create table mysql.user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint(5) unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.01 sec)

 

Database Privileges

GRANT ALL ON mydb.* TO ‘someuser’@’somehost’;GRANT SELECT, INSERT ON mydb.* TO ‘someuser’@’somehost’;
其中 mydb.* 表示 mydb 数据库下的所有表,对应的权限会保存在 mysql.db 表中,和 db 相关联。

  • db表
    db表存储了用户对某个数据库的操作权限,决定用户能从哪个主机哪个用户来操作哪个数据库。
  • User表中存储了某个主机和用户对数据库的操作权限,配置和db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受GRANT和REVOKE语句的影响,字段大致可以分为两类:用户列和权限列,详细解释如下:
    1)Host、Db、User:表示主机、数据库和用户,是db表的主键。
    2)*-priv:此类型的字段都是权限列,权限列的字段决定了用户的权限。user表的权限时针对所有数据库的,全局的;但如果希望某个用户只对某个数据库有相应的查询、修改、删除等普通权限,那么就需要在db表中设定,而user表只有对应的主机、用户和密码等信息。这些字段的类型为ENUM,可以取的值只能为Y或N,Y表示该用户有对应的权限;N表示没有。
MySQL [(none)]> show create table mysql.db\G;
*************************** 1. row ***************************
Table: db
Create Table: CREATE TABLE `db` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
1 row in set (0.00 sec)

 

Table Privileges

GRANT ALL ON mydb.mytbl TO ‘someuser’@’somehost’;GRANT SELECT, INSERT ON mydb.mytbl TO ‘someuser’@’somehost’;
对应的权限保存在 mysql.tables_priv 中,和 db , user 关联。

  • tables_priv表
    tables_priv表用来对表设置操作权限,有几个字段分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和Column_priv,各个字段说明如下:
    1)Host、Db、User和Table_name这几个字段分表示主机名、数据库名、用户名和表名。
    2)Grantor字段表示修改该记录的用户。
    3)Timestamp字段表示修改该记录的时间。
    4)Table_priv字段表示对表的操作权限,包括set(‘Select’,’Insert’,’Update’,’Delete’,’Create’,’Drop’,’Grant’,’References’,’Index’,’Alter’,’Create View’,’Show view’,’Trigger’)。
    5)Column_priv字段表示对表中的列的操作权限,包括set(‘Select’,’Insert’,’Update’,’References’)。
MySQL [(none)]> show create table mysql.tables_priv\G;
*************************** 1. row ***************************
Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

 

 Column Privileges

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO ‘someuser’@’somehost’;
对应的权限保存在 mysql.columns_priv 中,和 db, table, user 关联。

  • columns_priv表
    columns_priv表用来对表设置操作权限,有这么几个字段分别是Host、Db、User、Table_name、Timestamp、Column_name和Column_priv,各个字段说明如下:
    1)Host、Db、User和Column_name这几个字段分表示主机名、数据库名、用户名和列名。
    2)Timestamp字段表示修改该记录的时间。
    3) Column_priv字段表示对表中的列的操作权限,包括set(‘Select’,’Insert’,’Update’,’References’)。
MySQL [(none)]> show create table mysql.columns_priv\G;
*************************** 1. row ***************************
Table: columns_priv
Create Table: CREATE TABLE `columns_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges'
1 row in set (0.00 sec)

 

 Stored Routine Privileges

GRANT CREATE ROUTINE ON mydb.* TO ‘someuser’@’somehost’;GRANT EXECUTE ON PROCEDURE mydb.myproc TO ‘someuser’@’somehost’;
对应的权限保存在 mysql.procs_priv 中,和 routine_name, db,user 关联。

  • procs_priv表
    存储过程和存储函数相关的权限,分别是Host、Db、User、Routine_name、Routine_type、Grantor、Proc_priv和Timestamp,各个字段的说明如下:
    1)Host、Db和User字段分别表示主机名、数据库名和用户名;Routine_name表示存储过程或函数的名称。
    2)Routine_type字段表示存储过程或函数的类型。
    3)Routine_type字段有两个值,分别是FUNCTION和PROCEDURE。FUNCTION表示这是一个函数;PROCEDURE表示这是一个存储过程。
    4)Grantor字段记录是插入或修改该记录的用户。
    5)Proc_priv字段表示拥有的权限,包括Execute、Alter Routine、Grant这3种。
    6)Timestamp字段表示记录更新时间。
MySQL [(none)]> show create table mysql.procs_priv\G;
*************************** 1. row ***************************
Table: procs_priv
Create Table: CREATE TABLE `procs_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,
`Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
`Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges'
1 row in set (0.00 sec)

 

二、MySQL访问控制两阶段

阶段1:客户端连接验证阶段
当尝试连接到MySQL服务器时,服务器会根据以下条件接受或拒绝连接:

您的身份以及是否可以通过提供正确的密码来验证您的身份
您的帐户是锁定还是解锁

当连接MySQL服务器时,服务器基于用户的身份以及用户是否能通过正确的密码身份验证,来接受或拒绝连接。使用三个执行凭证检查 user表范围列(Host,User,和 authentication_string)。锁定状态记录在user表格 account_locked列中。仅当某些 表行中的Host和 User列user与客户端主机名和用户名匹配时,服务器才接受连接,客户端提供该行中指定的密码, account_locked值为’N’。服务器首先检查凭据,然后检查帐户锁定状态。任一步骤失败都会导致服务器完全拒绝您的访问权限。否则,服务器接受连接,然后进入阶段2并等待请求。

如果User列值为非空,则传入连接中的用户名必须完全匹配。如果 User值为空,则它匹配任何用户名。如果user与传入连接匹配的表行具有空白用户名,则该用户被视为没有名称的匿名用户,而不是具有客户端实际指定名称的用户。这意味着空白用户名用于连接持续时间内的所有进一步访问检查(即,在阶段2期间)。

该authentication_string列可以为空白。这不是通配符,并不表示任何密码匹配。这意味着用户必须在不指定密码的情况下进行连接。如果服务器使用插件对客户端进行身份验证,则插件实现的身份验证方法可能会也可能不会使用authentication_string 列中的密码。在这种情况下,外部密码也可能用于向MySQL服务器进行身份验证。

下表显示了表中各种组合User和Host值。
MySQL用户和权限管理(一)
传入连接的客户端主机名和用户名可以匹配user表中的多个行 。前述组实例证明这一点:若干条目的匹配示出从连接h1.example.net的fred。
当可能存在多个匹配项时,服务器必须确定要使用哪些匹配项。它解决了这个问题如下:
只要服务器将user表读入内存,它就会对行进行排序。
当客户端尝试连接时,服务器按排序顺序查看行。
服务器使用与客户端主机名和用户名匹配的第一行。

关于连接验证阶段详细可参考官方文档说明:https://dev.mysql.com/doc/refman/5.7/en/connection-access.html

阶段2:客户端请求验证阶段

建立连接后,服务器进入访问控制的第2阶段。对于通过该连接发出的每个请求,服务器确定您要执行的操作,然后检查您是否具有足够的权限来执行此操作。这是授权表中的特权列发挥作用的地方。这些权限可以来自mysql库下的 user,db, tables_priv,columns_priv,或procs_priv表。

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

关于请求验证阶段详细可参考官方文档说明:https://dev.mysql.com/doc/refman/5.7/en/request-access.html

MYSQL账户与权限管理的第一部分就先介绍到这里,请关注第二部分的介绍与使用;MYSQL用户权限管理(二)

MySQL用户和权限管理(一)


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL用户和权限管理(一)
喜欢 (4)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(1)个小伙伴在吐槽
  1. 讲解非常清晰
    青空2019-02-16 23:05 回复 Windows 7 | Chrome 64.0.3282.186