mysql用户管理权限分配详解
前言
如果想成为一个技术总监,技术经理。我觉得对于数据库用户的权限分配还是很重要的。比如,阿里巴巴,一个刚来的实习生,他可能连真实数据库都接触不到。等到终于熬成可以操作下数据库了,但是发现自己还是只有读取权限,并没有其他任何对数据库造成影响的权限。
这就是技术经理,技术总监。技术组长。所做的事情,维护好公司的利益,最为重要。
用户权限管理作用:
- 可以限制用户访问哪些库、哪些表
- 可以限制用户对哪些表执行CREATE、DELETE、(ALTER、UPDATE)、SELECT等操作
- 可以限制用户登录的IP或域名
- 可以限制用户自己的权限是否可以授权给别的用户
1、user表的组成
mysql中所有的用户都是存放在user表中的,user表有39个字段。这些字段可以分为4类:
• 用户列;
• 权限列;
• 安全列;
• 资源控制列;
1.1、用户列
用户列包括Host、User、Password,
User:要创建用户的名字。
Host:主机名:表示要这个新创建的用户允许从哪台机登陆
Password:新创建用户的登陆数据库密码,如果没密码可以不写。
1.2、权限列
user表的权限列是以priv结尾的字段。
这些字段的值只有Y和N。Y表示该权限可以用到所有数据库上;N表示该权限不能用到所有数据库上;
列 | 说明 |
Select_priv | 确定用户是否可以通过SELECT命令选择数据 |
Insert_priv | 确定用户是否可以通过INSERT命令插入数据 |
Update_priv | 确定用户是否可以通过UPDATE命令修改现有数据 |
Delete_priv | 确定用户是否可以通过DELETE命令删除现有数据 |
Create_priv | 确定用户是否可以创建新的数据库和表 |
Drop_priv | 确定用户是否可以删除现有数据库和表 |
Reload_priv | 确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令,包括日志、权限、主机、查询和表 |
Shutdown_priv | 确定用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时,都应当非常谨慎 |
Process_priv | 确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程 |
File_priv | 确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令 |
Grant_priv | 确定用户是否可以将已经授予给该用户自己的权限再授予其他用户 |
References_priv | 目前只是某些未来功能的占位符;现在没有作用 |
Index_priv | 确定用户是否可以创建和删除表索引 |
Alter_priv | 确定用户是否可以重命名和修改表结构 |
Show_db_priv | 确定用户是否可以查看服务器上所有数据库的名字,包括用户拥有足够访问权限的数据库 |
Super_priv |
确定用户是否可以执行某些强大的管理功能,例如通过KILL命令删除用户进程,使用SET GLOBAL修改全局MySQL变量,执行关于复制和日志的各种命令 |
Create_tmp_table_priv | 确定用户是否可以创建临时表 |
Lock_tables_priv | 确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改 |
Execute_priv | 确定用户是否可以执行存储过程 |
Repl_slave_priv | 确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中,有利于主机和客户机之间的通信 |
Repl_client_priv | 确定用户是否可以确定复制从服务器和主服务器的位置 |
Create_view_priv | 确定用户是否可以创建视图 |
Show_view_priv | 确定用户是否可以查看视图或了解视图如何执行 |
Create_routine_priv | 确定用户是否可以更改或放弃存储过程和函数 |
Alter_routine_priv | 确定用户是否可以修改或删除存储函数及函数 |
Create_user_priv | 确定用户是否可以执行CREATE USER命令,这个命令用于创建新的MySQL账户 |
Event_priv | 确定用户能否创建、修改和删除事件 |
Trigger_priv | 确定用户能否创建和删除触发器 |
1.3、安全列
user表的安全列有4个字段:
• ssl_type;
• ssl_cipher;
• x509_issuer;
• x509_subject;
ssl用于加密;x509标准可以用来标识用户。普通的发行版都没有加密功能。可以使用SHOW VARIABLES LIKE ‘have_openssl’语句来查看是否具有ssl功能。如果取值为DISABLED,那么则没有ssl加密功能。
SHOW VARIABLES LIKE 'have_openssl'
1.、资源控制列
user表的4个资源控制列是:
• max_questions:每小时可以允许执行多少次查询;
• max_updates:每小时可以允许执行多少次更新;
• max_connections:每小时可以建立多少连接;
• max_user_connections:单个用户可以同时具有的连接数。
默认值为0,表示无限制。
2、db表
db表存储了某个用户对一个数据库的权限。
主要分为2个,用户列和权限列
2.1、用户列
db表的用户列有3个字段:
• Host:主机名;
• Db:数据库名;
• User:用户名;
2.2、权限列
举例
Create_routine_priv:是否具有创建存储过程权限;
Alter_routine_priv:是否具有修改存储过程权限;
2.3、user表和db表的关联以及级别关系
user表中的权限是针对所有数据库的,如果user表中的Select_priv字段取值为Y,正常情况下该用户可以查询所有数据库中的表;
假如最终结果是为某个用户值设置了查询一个库中表的权限,但是user表的Select_priv字段的取值为N。那么db表中的Select_priv字段的取值将会是Y。
原因:用户先根据user表的内容获取权限,然后再根据db表的内容获取权限。
3、tables_priv表和columns_priv表
tables_priv:可以对单个表进行权限设置:
• tables_priv表包含8个字段:
• Host:主机名;
• DB:数据库名;
• User:用户名;
• Table_name:表名
• Table_priv:对表进行操作的权限
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')
• Timestamp:修改权限的事件
• Grantor:权限的设置者
columns_priv:可以对单个数据列进行权限设置,有7个列,作用同上:
Host、Db、User、Table_name、Column_name、Column_priv、Timestamp。
3.1、真正的权限分配顺序
MySQL权限分配是按照
user表(所有数据库)-> db表 (某个数据库)-> table_priv表(某个表) -> columns_priv表(某表的某列)的顺序进行分配的。在数据库系统中,先判断user表中的值是否为’Y’,如果user表中的值是’Y’,就不需要检查后面的表。如果user表为N,则一次检查后面的表。
4、开始真正的账户管理吧,朋友们
4.1、新建普通用户
正常来说一共有三种方式
• 使用create user语句来创建新的用户;
• 直接在mysql.user表中INSERT用户;
• 使用grant语句来新建用户;
4.1.1、create user (建议使用)
使用create user语句创建用户,必须要拥有create user权限。
示例:
CREATE USER 'admin'@'%' IDENTIFIED BY 'admin'
其中,user参数表示新建用户的账户,user由用户名(User)和主机名(Host)构成;IDENTIFIED BY关键字用来设置用户的密码;password参数表示用户的密码;
格式:”用户名”@”登录IP或域名”。%表示没有限制,在任何主机都可以登录。比如:”yangxin”@”192.168.0.%”,表示yangxin这个用户只能在192.168.0 IP段登录
执行之后user表会增加一行记录,但权限暂时全部为‘N’。
4.1.2、用INSERT语句新建普通用户(一般不用)
可以使用INSERT语句直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。
另外,ssl_cipher、x509_issuer、x509_subject没有值,必须要设置值,否则INSERT语句无法执行。
示例:
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES('%','newuser1',PASSWORD('123456'),'','','')
执行INSERT之后,要使用下面的命令:来使用户生效。
FLUSH PRIVILEGES
4.1.3、用GRANT语句来新建普通用户 (一般用不到)
个人认为一般也用不上。如果需要用的话,百度下呗,或者后期如果博主用到的话,会继续再这里讲解的哦
4.2 删除用户
1、DROP USER语句删除普通用户
需要拥有DROP USER权限。语法如下:,user是需要删除的用户,由用户名(User)和主机名(Host)构成。
DROP USER 'admin'@'%'
2、DELETE语句删除普通用户
可以使用DELETE语句直接将用户的信息从mysql.user表中删除。但必须拥有对mysql.user表的DELETE权限。
DELETE FROM mysql.user WHERE Host = '%' AND User = 'admin'
删除完成后,FLUSH PRIVILEGES
进行生效
5、真实场景
5.1 查看数据库中所有的用户
muysql> select u.host,u.user from user u;
5.2 创建用户并授权
正常情况下实习生,只给select权限即可。
mysql> grant all privileges on *.* to 'admin'@'%' identified by 'yangxin123456' with grant option;
GRANT SELECT ON *.* TO netUser@'%'
all privileges:表示将所有权限授予给用户。也可指定具体的权限,如:SELECT、CREATE、DROP等。
on:表示这些权限对哪些数据库和表生效,格式:数据库名.表名,这里写“*”表示所有数据库,所有表。如果我要指定将权限应用到test库的test表中,可以这么写:test.test。这里是针对的某个库的某个表。可以test.*
表示test库中的所有表
to:将权限授予哪个用户。格式:”用户名”@”登录IP或域名”。
identified by:指定用户的登录密码
with grant option:表示允许用户将自己的权限授权给其它用户
5.3 、刷新权限(flush)
对用户做了权限变更之后,一定记得重新加载一下权限,将权限信息从内存中写入数据库。
mysql> flush privileges;
5.4、回收权限(revoke)
删除yangxin这个用户的create权限,该用户将不能创建数据库和表。
mysql> revoke create on *.* from 'yangxin@localhost';
mysql> flush privileges;
5.5、删除用户
mysql> select host,user from user;
+---------------+---------+
| host | user |
+---------------+---------+
localhost HealerJean
localhost mysql.session
localhost mysql.sys
localhost root
8 rows in set (0.00 sec)
mysql> drop user 'HealerJean'@'localhost';
5.6、用户重命名
mysql> rename user 'test3'@'%' to 'test1'@'%';
5.7、修改密码
mysql> set password for 'root'@'localhost'=password('123456')
如果满意,请打赏博主任意金额,感兴趣的请下方留言吧。可与博主自由讨论哦
支付包 | 微信 | 微信公众号 |
---|---|---|
![]() |
![]() |
![]() |