| Bug #16236 | show_view_priv set to No user is still able to run show create table or create | ||
|---|---|---|---|
| Submitted: | 5 Jan 2006 20:38 | Modified: | 9 Jan 2006 23:31 |
| Reporter: | Gordon Bruce | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.17-nt | OS: | Windows (Windows XP) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[5 Jan 2006 23:09]
MySQL Verification Team
I was unable to repeat the behavior reported. Could you please test with another database instead of called test and if you are able to repeat please provide the complete test script: C:\mysql\bin>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database db4; Query OK, 1 row affected (0.06 sec) mysql> use db4 Database changed mysql> create table tb1 (id int); Query OK, 0 rows affected (0.06 sec) mysql> create view vtb1 as select * from tb1 where id > 10; Query OK, 0 rows affected (0.01 sec) mysql> grant select on db4.* to "user1"@"localhost" identified by "user1"; Query OK, 0 rows affected (0.02 sec) mysql> select user,create_view_priv,show_view_priv from mysql.user; +--------+------------------+----------------+ | user | create_view_priv | show_view_priv | +--------+------------------+----------------+ | root | Y | Y | | miguel | N | N | | user1 | N | N | +--------+------------------+----------------+ 3 rows in set (0.02 sec) C:\mysql\bin>mysql -uuser1 -puser1 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.18-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use db4 Database changed mysql> show tables; +---------------+ | Tables_in_db4 | +---------------+ | tb1 | | vtb1 | +---------------+ 2 rows in set (0.00 sec) mysql> show create table vtb1; ERROR 1142 (42000): SHOW VIEW command denied to user 'user1'@'localhost' for table 'vtb1' mysql>
[9 Jan 2006 21:50]
Gordon Bruce
I tried it with a different table name than test and it worked as I expected i.e. a user without show access gets denied when they try to da a SHOW CREATE TABLE. You can close this report.
[9 Jan 2006 23:31]
MySQL Verification Team
Thank you for the feedback and bug report.
Description: mysql> use MySQL Database changed mysql> select user,create_view_priv,show_view_priv from user user; +---------+------------------+----------------+ | user | create_view_priv | show_view_priv | +---------+------------------+----------------+ | root | Y | Y | | gbruce1 | Y | Y | | test | N | N | +---------+------------------+----------------+ 3 rows in set (0.00 sec) mysql> exit Bye C:\Program Files\MySQL\mysql server 5.0\bin>mysql -u test -P 3306 -p test Enter password: **** mysql> show tables; +----------------------+ | Tables_in_test | +----------------------+ | accounts | +----------------------+ 1 rows in set (0.00 sec) mysql> show create table accounts; | View | Create View | accounts | CREATE ALGORITHM=UNDEFINED DEFINER=`gbruce1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`accounts` AS select `mailprint`.`accounts`.`cpny_ID` AS `cpny_ID`,`mailprint`.`accounts`.`acct_ID` AS `acct_ID`,`mailprint`.`accounts`.`offc_ID` AS `offc_ID`,`mailprint`.`accounts`.`acct_Reporting_Office` AS `acct_Reporting_Office`,`mailprint`.`accounts`.`acct_Type` AS `acct_Type`,`mailprint`.`accounts`.`acct_Name` AS `acct_Name`,`mailprint`.`accounts`.`acct_Photo` AS `acct_Photo`,`mailprint`. `accounts`.`acct_Billing_Type` AS `acct_Billing_Type`,`mailprint`.`accounts`.`acct_AgentID` AS `acct_AgentID`,`mailprint`.`accounts`.`acct_Multi` AS `acct_Multi`,`mailprint`.`accounts`.`acct_Active` AS `acct_Active`,`mailprint`.`accounts`.`acct_Timestamp` AS `acct_Timestamp`,`mailprint`.`accounts`.`acct_Create` AS `acct_Create`,`mailprint`.`accounts`.`acct_Notes` AS `acct_Notes` from `mailprint`.`accounts` | 1 row in set (0.02 sec) mysql> show create view accounts; | View | Create View | accounts | CREATE ALGORITHM=UNDEFINED DEFINER=`gbruce1`@`localhost` SQL SECURITY DEFINER VIEW `test`.`accounts` AS select `mailprint`.`accounts`.`cpny_ID` AS `cpny_ID`,`mailprint`.`accounts`.`acct_ID` AS `acct_ID`,`mailprint`.`accounts`.`offc_ID` AS `offc_ID`,`mailprint`.`accounts`.`acct_Reporting_Office` AS `acct_Reporting_Office`,`mailprint`.`accounts`.`acct_Type` AS `acct_Type`,`mailprint`.`accounts`.`acct_Name` AS `acct_Name`,`mailprint`.`accounts`.`acct_Photo` AS `acct_Photo`,`mailprint`. `accounts`.`acct_Billing_Type` AS `acct_Billing_Type`,`mailprint`.`accounts`.`acct_AgentID` AS `acct_AgentID`,`mailprint`.`accounts`.`acct_Multi` AS `acct_Multi`,`mailprint`.`accounts`.`acct_Active` AS `acct_Active`,`mailprint`.`accounts`.`acct_Timestamp` AS `acct_Timestamp`,`mailprint`.`accounts`.`acct_Create` AS `acct_Create`,`mailprint`.`accounts`.`acct_Notes` AS `acct_Notes` from `mailprint`.`accounts` | 1 row in set (0.00 sec) mysql>mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.17-nt | +-----------+ 1 row in set (0.01 sec) How to repeat: Create a user with view priveledges turned off. Log in as that user. Submit either "SHOW CREATE TABLE {existing view name}" "SHOW CREATE VIEW {existing view name}". Suggested fix: Enforce the user view privledges.