| Bug #9795 | MySQL forgets about SHOW VIEW / CREATE VIEW privileges granted on table level | ||
|---|---|---|---|
| Submitted: | 10 Apr 2005 18:12 | Modified: | 8 Jul 2005 17:50 |
| Reporter: | Alexander M. Turek | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.4-beta-nightly-20050402 | OS: | Linux (Linux 2.6) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[20 Apr 2005 16:03]
Tobias Asplund
Doesn't only affect VIEW privileges, seems to be a global thing. (fresh pull from bk-tree) mysql> \r Connection id: 1 Current database: mysql mysql> SHOW GRANTS FOR sr@localhost; +---------------------------------------------------+ | Grants for sr@localhost | +---------------------------------------------------+ | GRANT USAGE ON *.* TO 'sr'@'localhost' | | GRANT SELECT ON `skup0003_`.* TO 'sr'@'localhost' | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql> GRANT INSERT ON skup0003_.alts TO sr@localhost; Query OK, 0 rows affected (0.03 sec) mysql> SHOW GRANTS FOR sr@localhost; +--------------------------------------------------------+ | Grants for sr@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO 'sr'@'localhost' | | GRANT SELECT ON `skup0003_`.* TO 'sr'@'localhost' | | GRANT INSERT ON `skup0003_`.`alts` TO 'sr'@'localhost' | +--------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> \q Bye flupps@eeyore:~$ mysqladmin shutdown flupps@eeyore:~$ STOPPING server from pid file /var/mysql/eeyore.flupps.org.pid 050420 17:54:21 mysqld ended root@eeyore:/usr/local/mysql# ./bin/mysqld_safe & [1] 52710 root@eeyore:/usr/local/mysql# Starting mysqld daemon with databases from /var/mysql flupps@eeyore:~$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.5-beta-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW GRANTS FOR sr@localhost; +---------------------------------------------------+ | Grants for sr@localhost | +---------------------------------------------------+ | GRANT USAGE ON *.* TO 'sr'@'localhost' | | GRANT SELECT ON `skup0003_`.* TO 'sr'@'localhost' | +---------------------------------------------------+ 2 rows in set (0.00 sec)
[22 Jun 2005 19:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/26343
[3 Jul 2005 20:02]
Michael Widenius
Patch is ok, but before pushing add the following tests: - Add only SHOW VIEW privilege for a view - Check that it works and that 'select from ... TABLE_PRIVILEGES' shows the privilege' - flush privileges - Check that it works and that 'select from ... TABLE_PRIVILEGES' shows the privilege' Do the same for CREATE VIEW
[5 Jul 2005 10:36]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/26671
[5 Jul 2005 13:03]
Oleksandr Byelkin
pushed to 5.0.9
[8 Jul 2005 17:50]
Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Documented in 5.0.9 change history; closed.
Description: MySQL 5.0 allows me to grant the privileges SHOW VIEW and CREATE VIEW on table level. As you can see in the output below, those are also part of 'ALL PRIVILEGES'. The problem is that after having flushed the grant tables or restarted the server, MySQL forgets that these privileges have been set. Also included in the output is the structure of the mysql.tables_priv table that is supposed to store this information. But as you can see, those privileges are not part of the SET field definition, so MySQL is not able to store them at all. The table has freshly been created by the mysql_install_db script that was bundled with this MySQL version. How to repeat: mysql> CREATE USER dummy@localhost IDENTIFIED BY 'foobar'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE SCHEMA dummy; Query OK, 1 row affected (0.02 sec) mysql> CREATE TABLE dummy.dummytable (dummyfield INT); Query OK, 0 rows affected (0.02 sec) mysql> CREATE VIEW dummy.dummyview AS SELECT dummyfield FROM dummy.dummytable; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON dummy.dummytable TO dummy@localhost; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON dummy.dummyview TO dummy@localhost; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR dummy@localhost; +--------------------------------------------------------------------------------------------------------------+ | Grants for dummy@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dummy'@'localhost' IDENTIFIED BY PASSWORD '*9B500343BC52E2911172EB52AE5CF4847604C6E5' | | GRANT ALL PRIVILEGES ON `dummy`.`dummyview` TO 'dummy'@'localhost' | | GRANT ALL PRIVILEGES ON `dummy`.`dummytable` TO 'dummy'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; +--------------+------------+------------------------------------------------------------------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | PRIVILEGES | +--------------+------------+------------------------------------------------------------------------------------------------+ | dummy | dummytable | ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, UPDATE | | dummy | dummyview | ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, UPDATE | +--------------+------------+------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.02 sec) mysql> SHOW GRANTS FOR dummy@localhost; +-----------------------------------------------------------------------------------------------------------------------------+ | Grants for dummy@localhost | +-----------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dummy'@'localhost' IDENTIFIED BY PASSWORD '*9B500343BC52E2911172EB52AE5CF4847604C6E5' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `dummy`.`dummyview` TO 'dummy'@'localhost' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `dummy`.`dummytable` TO 'dummy'@'localhost' | +-----------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE = '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME; +--------------+------------+------------------------------------------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | PRIVILEGES | +--------------+------------+------------------------------------------------------------------------+ | dummy | dummytable | ALTER, CREATE, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, UPDATE | | dummy | dummyview | ALTER, CREATE, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, UPDATE | +--------------+------------+------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW FIELDS FROM mysql.tables_priv; +-------------+-----------------------------------------------------------------------------------------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------------------------------------------------------------------------------+------+-----+-------------------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Grantor | char(77) | NO | MUL | | | | Timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') | NO | | | | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+-----------------------------------------------------------------------------------------------+------+-----+-------------------+-------+ 8 rows in set (0.04 sec)