MySQL 权限表测试遇到的问题

MySQL · liushuiwuqing · 于 2年前发布 · 1165 次阅读

前言

学习 binlog_format 参数的时候,遇到了两个疑问:MySQL 版本:5.5.20-v3i-log。

1)手动修改用户名以后导致权限不一样的问题。

2)GRANT USAGE ON .MySQL 手册上 有这么一句话

USAGE	   Synonym for “no privileges"

那表示只有这个权限的用户应该是没有访问权限才对,可是事实好像不太对。

测试过程1

mysql> create database test charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> grant select on test.* to test@192.168.194.115 identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test@192.168.194.115;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for test@192.168.194.115                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'192.168.194.115' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `test`.* TO 'test'@'192.168.194.115'                                                              |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> update mysql.user set user='test1' where user='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test1@192.168.194.115 ;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for test1@192.168.194.115                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'192.168.194.115' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

请问,为什么我只是修改了把 test 改成了 test1,怎么导致 show grants 的权限丢失了呢?

原因解释

后来在 @ruyi 的指点下,我重新按照上面的步骤继续模拟了下,然后我查询 USER_PRIVILEGES 表的信息,的确有 'test'@'192.168.194.115' 相关的记录。

mysql> select * from USER_PRIVILEGES; 
| GRANTEE                          | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
| 'test'@'192.168.194.115'        | def           | USAGE                   | NO           |
...

但是当我想手动修改 USER_PRIVILEGES 的时候 ,会发现没有权限来修改的。

mysql> update USER_PRIVILEGES set grantee=`'test1'@'192.168.194.115'` where grantee=`'test'@'192.168.194.115'`;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

show table status 看了下 information_schema 数据库下 表的存储引擎,发现大多数是 Memory 和 少量的 MyISAM 。后面继续挖掘,找到到 MySQL 手册上关于 INFORMATION_SCHEMA 的概述 (http://dev.mysql.com/doc/refman/5.5/en/information-schema.html )

主要内容如下:

Usage Notes for the INFORMATION_SCHEMA Database
INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information
about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database
contains several read-only tables. They are actually views, not base tables, so there are no files
associated with them, and you cannot set triggers on them. Also, there is no database directory with
that name.
Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you
can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

从这里可以看到,INFORMATION_SCHEMA 数据库下面的表数据是只能 select 不能修改的,那么上面的操作当然就不行了咯。所以有些童鞋在用低版本的 mysqldump 导出全表然后再导入数据的时候也会报类似 Access denied for user 'user'@'ip' to database 'information_schema' , MySQL Bug report 上有给出这个解释:

the information_schema is a virtual database, and you should not be importing or dumping information_schema in the first place. if this sql dumpfile was created by mysqldump, it was probably an old version from 4.1. in that case use a current version of mysqldump instead.

每次我遇到的时候,其实就是切换到一个高版本的 mysqldump 就好了。

言归正传,如果我仍然希望实现下面这样的效果改怎么办呢?

update USER_PRIVILEGES set grantee=`'test1'@'192.168.194.115'` where grantee=`'test'@'192.168.194.115';

其实是有方法的:

官网上有这么一句话:This is a nonstandard table. It takes its values from the mysql.user table. 所以需要做的就是修改 mysql.user 上的数据,然后 flush privileges 让 MySQL 重新的从 mysql 权限表上获取内容就可以了。

具体操作如下:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set user='test1' where user='test' and host='192.168.194.115';                      
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use information_schema ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from USER_PRIVILEGES where grantee like '%test%';
+---------------------------+---------------+----------------+--------------+
| GRANTEE                   | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+---------------------------+---------------+----------------+--------------+
| 'test1'@'192.168.194.115' | def           | USAGE          | NO           |
+---------------------------+---------------+----------------+--------------+
1 row in set (0.00 sec)

mysql> show grants for 'test1'@'192.168.194.115';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for test1@192.168.194.115                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'192.168.194.115' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT ON `test`.* TO 'test1'@'192.168.194.115'                                                              |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

其实秘诀就在于  information_schema.USER_PRIVILEGES  takes its values from the mysql.user table.  所以只要修改 mysql.user 了。

usage 权限测试

mysql> grant USAGE ON *.* TO 'usage'@'192.168.194.115' IDENTIFIED BY 'usage';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'usage'@'192.168.194.115';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for usage@192.168.194.115                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'usage'@'192.168.194.115' IDENTIFIED BY PASSWORD '*8820B09BABDA92D1B61A4598E5938EF97728E2E9' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[~]$ mysql -h192.168.194.115 -u usage -p -P 4308
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)
mysql> use information_schema;
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
...

本人执行了一些语句,发现有些能查询,有些是不能查询的。
select * from tables;
select * from COLUMNS ;

但是我执行下面的查询语句的时候,就会发现没有权限了。
mysql> select * from INNODB_TRX;
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

这个后面我才发现,我之所以能够执行 select * from tables; 这样的语句,是因为查询的结果都只有 TABLE_SCHEMA 是 information_schema 相关的信息,一旦我想查询其他 InnoDB 相关的信息,还是不行滴,这样做不知道会不会有安全漏洞。

最后说一下,我作为新手DBA 以前偶尔会操作类似 测试过程1 的那种情况来修改用户,忽略了只是修改用户名可能导致用户权限丢失的情况,可是让我不得其解的是,我修改了用户名怎么权限就没了呢?

现在一般的做法就是通过 grant/revoke drop user 方式来进行基本权限操作了。


共收到 3 条回复 MySQL DBA
ruyi#12年前 0 个赞

mysql的权限表是存放在information_schema下面的,你只修改了用户名,但是权限表里还是之前的用户,不匹配了,所以查不到权限。

你可以看下这个表:

mysql> use information_schema
Database changed
mysql> select * from SCHEMA_PRIVILEGES;

@ruyi #1楼 谢谢你的提醒,正好把测试过程1 的问题给解决掉了,已经更新了。每次来都能学习到东西,不错不错^_^。

ruyi#32年前 0 个赞
回复本帖 (需要登录)