I have run across a problem with differences in the mysql.tables_priv
table and the INFORMATION_SCHEMA.TABLE_PRIVILEGES tables. My
application is currently written to query the information schema for
privileges before allowing users to insert or update. The problem is
that for some users and some tables the INFORMATION_SCHEMA does not
reflect the true privileges in the mysql database. consider the
following 2 querys/results
mysql> select * from information_schema.table_privileges where grantee
like '%user1%' and table_name like '%table%';
+--------------------+---------------+--------------+--------------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME
| PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+--------------+--------------------+----------------+--------------+
| 'user1'@'localhost'| NULL | lu | table1 |
SELECT | NO |
| 'user1'@'localhost'| NULL | lu | table1 |
INSERT | NO |
| 'user1'@'localhost'| NULL | lu | table1 |
UPDATE | NO |
+--------------------+---------------+--------------+--------------------+----------------+--------------+
3 rows in set (0.11 sec)
mysql> select * from mysql.tables_priv where user like '%user1%' and
table_name like '%table%';
+-----------+----+------+----------------------------+----------------+---------------------+----------------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp
| Table_priv | Column_priv |
+-----------+----+------+----------------------------+----------------+---------------------+----------------------+-------------+
| localhost | lu | user1 | table1 | [EMAIL PROTECTED] |
2008-09-08 17:30:02 | Select,Insert,Update | |
| localhost | lu | user1 | table2 | [EMAIL PROTECTED] |
2008-09-08 17:30:02 | Select,Insert,Update | |
+-----------+----+------+----------------------------+----------------+---------------------+----------------------+-------------+
2 rows in set (1.11 sec)
as you can see privileges clearly reflected in the mysql database are
not reflected in the information schema. what makes the problem even
more fun is that not all users are affected for the same table.
another user could very well have the problem with table1 instead of
table2 and another user may have no problems at all.
Any thoughts or suggestions would be greatly appreciated.
Thanks
Christian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
