MySQL用户权限总结【用户授权必会】

x33g5p2x  于2021-11-27 转载在 Mysql  
字(2.6k)|赞(0)|评价(0)|浏览(307)

一、MySQL用户权限

最近有一个项目,在开发过程中直接把数据库安装到阿里云服务器,本地连接阿里云服务器中的MySQL就不能直接root用户连接,而每次数据库操作都要使用新建的用户与用户进行交互操作。

在使用非root用户的时,执行本地的sql文件,就需要一些权限,比如 SELECT,INSERT,UPDATE,DELETE,CREATE 等等权限,下面就以此做一份笔记,之后在开发的时候,就可以查看此文章的记录,直接进行应用即可。

添加MySQL用户并设置权限的好处:新的SQL用户不允许访问访问属于其他SQL用户的库或表,甚至不能使用SELECT语句。新的SQL用户必须显式的被授予权限,才能执行对应的操作。

二、用户权限介绍

1.权限级别

  • 全局:可以管理整个MySQL
  • 数据库:可以管理指定的数据库
  • 数据表:可以管理指定数据库的指定表
  • 字段:可以管理指定数据库的指定表的指定字段

权限存储在mysql库的user,db,tables_priv,columns_priv,procs_priv这几个系统表中,待MySQL实例启动后就加载到内存中,实现用户的权限控制。

2.权限实现

MySQL权限实现分为两段验证:

第一阶段:服务器首先会检查此用户是否允许连接。先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在,存在则通过验证。

第二阶段:通过身份验证后,用户发起的每个请求都需要进行权限判断,按照 user,db,tables_priv,columns_priv,procs_priv 的顺序进行验证。即先检查全局权限表user,如果user中对应的权限为Y,则此用户对所有数据库的权限都为Y,将不再检查db, tables_priv,columns_priv;如果为N,则到db表中检查此用户对应的具体数据库,并得到db中为Y的权限;如果db中为N,则检查tables_priv中此数据库对应的具体表。以此类推。

3.权限分布

MYSQL的权限如何分布,就是针对表可以设置什么权限,针对列可以设置什么权限等等,这个可以从官方文档中的一个表来说明:

权限分布可能的设置的权限
表权限‘Select’, ‘Insert’, ‘Update’, ‘Delete’, ‘Create’, ‘Drop’, ‘Grant’, ‘References’, ‘Index’, ‘Alter’
列权限‘Select’, ‘Insert’, ‘Update’, ‘References’
过程权限‘Execute’, ‘Alter Routine’, ‘Grant’

以上也是我们最常用的一些权限。更多的权限设置,直接查看官方文档中对权限的描述即可。

4.查询权限表

查看用户MySQL用户

select user,host from mysql.user;

查看root用户在权限表中的权限

# Y表示有权限 ,N表示无权限
# 1.mysql.user表 (all)
select * from mysql.user where user='root';

# 2.mysql.db表 (empty)
select * from mysql.db where user='root';

# 3.mysql.tables_priv (empty)
select * from mysql.tables_priv where user='root';

# 4.mysql.colums_priv表 (empty)
select * from mysql.columns_priv where user='root';

# 5.mysql.procs_priv (empty)
select * from mysql.procs_priv where user='root';

三、用户权限实战

1.查看用户权限信息

查看当前用户

select user();

查看MYSQL有哪些用户

select user,host from mysql.user;

查看已经授权给用户的权限信息

show grants for 'pdh'@'%';

2.用户创建和授权

简单说一下MySQL的授权用户组成: 'user_name'@'host_name'(中间使用@符号连接)。其中user_name表示用户名,host_name表示主机,可以是ipv4和ipv6格式的,%表示所有主机均可访问。下面列举一下不同的格式表示不同的主机:

user_namehost_name说明
‘pdh’‘198.51.100.177’pdh,只能从此ip连接
‘pdh’‘198.51.100.%’pdh,从198.51.100 子网中的任何主机
‘pdh’‘%’pdh,任何主机可连

创建MySQL用户和权限*

# 1.使用CREATE创建用户,后再授权
# 1.1 创建 pdh 用户,设置密码为123456,并没有权限
CREATE USER 'pdh'@'%' IDENTIFIED BY '123456';
# 1.2 授予pdh查询和添加test库的权限
grant select,insert,update,delete,create,alter on test.* to 'pdh';

# 2.使用GRANT创建用户并授权test库的所有操作
grant all privileges on test.* to 'pdh'@'%' identified by "123456" with grant option;

以上指令说明

1. ALL PRIVILEGES 是表示所有权限,你也可以使用select、update等权限。
2. ON 用来指定权限针对哪些库和表
3. test.*  表示test库的所有表
4. TO 表示将权限赋予某个用户。
5. 'pdh'@'%' 表示pdh用户,主机为%。主机可以是IP、IP段、域名以及%
6. IDENTIFIED BY 指定用户的登录密码
7. WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人

刷新权限

使用这个命令使权限生效,对权限表user、db、host等做了update或者delete更新的时候务必执行权限刷新。

flush privileges;

查看和修改权限

查看当前用户权限

show grants;

查看某个用户权限

show grants for 'pdh'@'%';

回收权限

revoke alter on test.* from 'pdh'@'%';

相关文章