Bug #63722 information_schema.user_privileges not updated after GRANT

• Submitted: 12 Dec 2011 11:21
• Modified: 12 Dec 2011 12:07
• Reporter: Sergey Naumov
• Status: Not a Bug
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.1.49
• OS: Linux (Debian squeeze)
• CPU Architecture: Any
• Tags: information_schema

[12 Dec 2011 11:21] Sergey Naumov

Description:
I have debian 6 squeeze with latest updates.

> mysql -V
mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1

I want to have a way to check user privileges (by user himself). Parsing Show Grants output is not a good way because of escapes, % ..., so I have tried to use information_schema.user_privileges. But after granting some privileges and flushing them there was no changes in information_schema.user_privileges (neither if I select * by root, nor by user to whom I granted privileges).

mysql> SHOW GRANTS FOR 'sknaumov'@'localhost';
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sknaumov'@'localhost' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT CREATE ON `squidtest`.* TO 'sknaumov'@'localhost'                                                         |
| GRANT INSERT ON `squid%`.* TO 'sknaumov'@'localhost'                                                            |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from information_schema.user_privileges;
+------------------------+---------------+----------------+--------------+
| GRANTEE                | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+------------------------+---------------+----------------+--------------+
| 'sknaumov'@'localhost' | NULL          | USAGE          | NO           |
+------------------------+---------------+----------------+--------------+
1 row in set (0.00 sec)

How to repeat:
Let's suppose we have a database. I have one with a table for squid logging.
Name of the database is squidtest.
> mysql -u root -p squidtest
GRANT CREATE ON `squidtest`.* TO 'sknaumov'@'localhost';
FLUSH PRIVILEGES;
quit

> mysql -u sknaumov -p squidtest
SELECT * FROM information_schema.user_privileges;

[12 Dec 2011 11:53] Peter Laursen

You granted a schema-specific privilege - and not a global one - so you should instead:

SELECT * FROM information_schema.schema_privileges;

Peter
(not a MySQL person)

[12 Dec 2011 12:07] Sergey Naumov

Indeed, SELECT * from information_schema.schema_privileges works.