MySQL Bugs: #16681: information_schema shows forbidden VIEW details

Bug #16681	information_schema shows forbidden VIEW details
Submitted:	20 Jan 2006 18:11	Modified:	24 May 2006 17:27
Reporter:	Bernd Wurst	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0.18/5.0.19 bk	OS:	Linux (gentoo linux (kernel 2.6))
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
If I create a VIEW that produces a virtual table as a combination of several other tables, then it may be desired that the user that accesses the view should only be able to use the given VIEW but should not be able to see how it works internally (so he should not be able to see a wide range of the database layout).

Therefore, a privillege exists, called 'Show_view_priv'. I am able to forbid a user to do so and SHOW CREATE VIEW results in an error message.
But (that's the bug, imho) the user is still able to view the complete command used to create the view by issuing
 SELECT * FROM information_schema.views;

I think that this information should not be given as long as the user does not have the SHOW CREATE (Show_view_priv) permission.

This issue was also posted in the mysql-forums where I was adviced to open this bug report. The thread is here: http://forums.mysql.com/read.php?101,65114,65412

How to repeat:
* Create (as some kind of administrator) a VIEW let's say
  CREATE VIEW test AS SELECT customers.status AS st, users.username AS user, passwords.md5 AS password FROM customers INNER JOIN users ON customers.id=users.cid INNER JOIN passwords ON passwords.uid=users.id;
* Create a new user
* Grant the new user only the SELECT privillege on the VIEW `test`
* Log in as new user
* SHOW CREATE VIEW mydb.test; => results in an error
* SELECT * FROM information_schema.views WHERE View='test'; => shows what SHOW CREATE hides.

Suggested fix:
MySQL should only show information_schema.views (or at least the contents) to users that have the needed privilleges.

Actually, as far as I can see, ANSI IEC 9075:2003 allows the user to see the view, but blanks out the VIEW_DEFINITION unless the current user is (either directly or indirectly) owner of the view. 

Just look at the definition of the VIEWS information_shema view (ISO/IEC 9075-11:2003 (E) 5.76 VIEWS view, page 98):

CREATE VIEW VIEWS AS
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
CASE
WHEN EXISTS
( SELECT *
FROM DEFINITION_SCHEMA.SCHEMATA AS S
WHERE ( TABLE_CATALOG, TABLE_SCHEMA )
= ( S.CATALOG_NAME, S.SCHEMA_NAME )
AND
( SCHEMA_OWNER = CURRENT_USER
OR
SCHEMA_OWNER IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) ) )
THEN VIEW_DEFINITION
ELSE NULL
END AS VIEW_DEFINITION,
CHECK_OPTION, IS_UPDATABLE,
( SELECT IS_INSERTABLE_INTO
FROM DEFINITION_SCHEMA.TABLES AS T
WHERE ( V.TABLE_CATALOG, V.TABLE_SCHEMA, V.TABLE_NAME )
= ( T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME )
) AS INSERTABLE_INTO
FROM DEFINITION_SCHEMA.VIEWS AS V
WHERE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) IN
( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM TABLES )
AND
TABLE_CATALOG
= ( SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA_CATALOG_NAME );
GRANT SELECT ON TABLE VIEWS
TO PUBLIC WITH GRANT OPTION;

It seems to me that thee expression for the VIEW_DEFINITION column: 

CASE
WHEN EXISTS
( SELECT *
FROM DEFINITION_SCHEMA.SCHEMATA AS S
WHERE ( TABLE_CATALOG, TABLE_SCHEMA )
= ( S.CATALOG_NAME, S.SCHEMA_NAME )
AND
( SCHEMA_OWNER = CURRENT_USER
OR
SCHEMA_OWNER IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) ) )
THEN VIEW_DEFINITION
ELSE NULL
END AS VIEW_DEFINITION

is intended to hide the definition for unauthorized users. Any user can still see the existence and other properties of the view

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db1
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb1 (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create view vtb1 as select * from tb1;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on db1.* to user@localhost identified by 'user';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/mysql -uuser -p db1
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create view vtb1;
ERROR 1142 (42000): SHOW VIEW command denied to user 'user'@'localhost' for table 'vtb1'

mysql> SELECT * FROM information_schema.views WHERE TABLE_NAME='vtb1'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: db1
     TABLE_NAME: vtb1
VIEW_DEFINITION: select `db1`.`tb1`.`id` AS `id` from `db1`.`tb1`
   CHECK_OPTION: NONE
   IS_UPDATABLE: YES
        DEFINER: root@localhost
  SECURITY_TYPE: DEFINER
1 row in set (0.01 sec)

mysql>

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/commits/5576

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/commits/6750

Fixed in 5.0.22

Noted in 5.0.22 changelog.

rivilege checking on the contents of the
<literal>INFORMATION_SCHEMA.VIEWS</literal> table was
insufficiently restrictive.

Corrrection: entry is in 5.0.23 changelog.