mysql 权限分组管理,关于Mysql权限你真的都了解吗？

最新推荐文章于 2024-05-09 00:35:13 发布

晋六一

最新推荐文章于 2024-05-09 00:35:13 发布

阅读量221

点赞数

文章标签： mysql 权限分组管理

题图:《大话西游之月光宝盒》

MySql权限分类

NOTE:

为了后面描述权限变更及生效时机更清晰，我把权限按照可授权范围进行分类。有些权限能够在多个范围内进行授予，既同时属于多个类别。

查看Mysql提供的所有权限：

mysql> show privileges;+-------------------------+---------------------------------------+-------------------------------------------------------+| Privilege | Context | Comment |+-------------------------+---------------------------------------+-------------------------------------------------------+| Alter | Tables | To alter the table || Alter routine | Functions,Procedures | To alter or drop stored functions/procedures || Create | Databases,Tables,Indexes | To create new databases and tables || Create routine | Databases | To use CREATE FUNCTION/PROCEDURE || Create temporary tables | Databases | To use CREATE TEMPORARY TABLE || Create view | Tables | To create new views || Create user | Server Admin | To create new users || Delete | Tables | To delete existing rows || Drop | Databases,Tables | To drop databases, tables, and views || Event | Server Admin | To create, alter, drop and execute events || Execute | Functions,Procedures | To execute stored routines || File | File access on server | To read and write files on the server || Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess || Index | Tables | To create or drop indexes || Insert | Tables | To insert data into tables || Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) || Process | Server Admin | To view the plain text of currently executing queries || Proxy | Server Admin | To make proxy user possible || References | Databases,Tables | To have references on tables || Reload | Server Admin | To reload or refresh tables, logs and privileges || Replication client | Server Admin | To ask where the slave or master servers are || Replication slave | Server Admin | To read binary log events from the master || Select | Tables | To retrieve rows from table || Show databases | Server Admin | To see all databases with SHOW DATABASES || Show view | Tables | To see views with SHOW CREATE VIEW || Shutdown | Server Admin | To shut down the server || Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. || Trigger | Tables | To use triggers || Create tablespace | Server Admin | To create/alter/drop tablespaces || Update | Tables | To update existing rows || Usage | Server Admin | No privileges - allow connect only |+-------------------------+---------------------------------------+-------------------------------------------------------+NOTE1:Server Admin表示管理权限，都是只能进行全局授予的全局权限(Proxy权限例外，该权限对用户进行授予，mysql.proxies_priv表中记录proxy权限信息)，File权限也是只能进行全局授予的全局权限。NOTE2:除了proxy权限，其他所有权限都支持在全局级别授予(即都可以是全局权限)。NOTE3:Tables,Functions,Procedures 表示对象级别的权限。权限类型1--对象级别授予的权限

我们可以通过如下表查询对象级别权限：

mysql.tables_priv,mysql.columns_priv,mysql.procs_priv

可在表级别进行授予的权限：

'Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger'

可在列级别进行授予的权限：

'Select','Insert','Update','References'

可授予过程及函数的权限：

'Execute','Alter Routine','Grant'权限类型2--数据库级别授予的权限

我们可以通过mysql.db表查询数据级别权限，可以授予给数据库级别的权限有：

Select，Insert，Update，Delete，Create，Drop，Grant option，References，Index，Alter，Create temporary tables，Lock tables，Create view，Show view，Create routine，Alter routine，Execute，Event，Trigger权限类型3--全局级别授予的权限NOTE1:除了proxy权限，其他所有权限都支持在全局级别授予(即都可以是全局权限)NOTE2:有些权限只能进行全局授予(grant xxx on *.*)比如所有的管理权限和File权限(有一个管理权限例外，Proxy，该权限只能对用户授予 grant proxy on 'user1'@'host1' to 'user2'@'host2';)我们可以通过mysql.user表查询全局权限在mysql.user表中一共有29个列来标记权限情况，我们看到上面列出Mysql一共有31项权限，mysql.user中少了Proxy及Usage原因：1)Usage会在创建用户后自动授予用户;2)proxy权限授予对象是用户权限信息保存在mysql.proxies_priv表中;

MySql权限变更

我们可以通过如两种方法来变更用户权限.

1)通过grant,revoke语句来变更用户权限.

2)通过dml语句直接修改权限表来变更权限(不推荐这样做)。

NOTE:grant，revoke语句执行后会变更权限表中相关记录，同时会更新内存中记录用户权限的相关对象。dml语句直接修改权限表只是修改了表中权限信息，需要执行flush privileges;来更新内存中保存用户权限的相关对象。

全局权限变更

全局权限在Mysql中会保存在三个地方：

1)mysql.user 表中；

2)全局内存对象中(acl_users数组中)；

3)已经成功建立连接的线程中(Mysql连接成功后，会从acl_users数据组中copy用户全局权限到线程对象中，之后在这个连接中执行的语句，所有关于全局权限的判断，都直接使用线程对象内部保存的权限位)。

使用 grant,revoke 变更全局权限时，只会更新 mysql.user 表及 acl_users数组中的权限信息。所以对于已存在的会话来说，它的全局权在连接建立时就已经确定，不受后面权限变更的影响。

示例：

用户test_conn的全局delete权限收回后，已有会话不受影响依然可以执行delete操作，新建连接无法执行delete操作。

数据库级别权限变更

数据库级别的权限保存在两个地方：

1)mysql.db 表中；

2)全局内存对象中(acl_dbs数组中)

通常每次需要判断一个用户数据库级别权限时，都需要遍历一次 acl_dbs 数组，根据 user、host 和 db 找到匹配的对象，然后根据对象的权限位来判断，但是如果用户使用 use db;进入某个数据库，会把当前用户具有的该数据库级别权限保存在会话变量中，下次需要判断该数据库级别权限的时候则不需要访问acl_dbs数组，只需要访问该变量即可。

使用 grant，revoke 变更数据库级别权限时，会更新 mysql.db 表及 acl_dbs 数组中的权限信息。对于已存在的会话的影响分两种情况：

1)对于没有默认数据库的连接，因为此时数据库级别权限都是通过遍历全局的acl_dbs数组来判断，所以立即生效

2)对于有默认数据库的连接，因为当前默认数据库的数据库级别权限已经保存在会话变量中，所以当前默认数据库的数据库级别权限不受影响。当连接再次执行 use db;后变更生效。

示例：