MySQL Bugs: #17254: Error for DEFINER security on VIEW provides too much info

Bug #17254	Error for DEFINER security on VIEW provides too much info
Submitted:	9 Feb 2006 0:52	Modified:	21 Dec 2006 20:31
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.18	OS:	Any (any)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
If you create a view with DEFINER security, DROP this user, then use the view, the error provides too much info:
ERROR 1449 (HY000): There is no 'arjen'@'localhost' registered

The user should just be told that they don't have access, not about which user was the definer! That's too much info.

Similar to accessing a db that doesn't exist; if you don't have access to that db, you get access denied; if you're root, you are told the db doesn't exist. That's the correct way.

How to repeat:
# mysql -u root
CREATE TABLE test.base (id INT);
INSERT INTO test.base VALUES (10),(20);
CREATE USER def@localhost;
GRANT SELECT ON test.* TO def@localhost;
CREATE USER inv@localhost;
GRANT SELECT ON test.deftest TO inv@localhost;
exit

# mysql -u def
CREATE VIEW test.deftest AS SELECT * FROM test.base;
exit

# mysql -u root
DROP USER def@localhost;
exit

#mysql -u inv
SELECT * FROM test.deftest;

Suggested fix:
Fix up error reporting for this situation, to consistently maintain security.
Never provide "too much info".

Verified on 5.0.19-BK (ChangeSet@1.2038.2.1, 2006-02-14 16:22:37+03:00):

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
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 4 to server version: 5.0.19

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

mysql> CREATE TABLE test.base (id INT);
INQuery OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test.base VALUES (10),(20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

EATE USER dmysql> CREATE USER def@localhost;
Query OK, 0 rows affected (0.00 sec)

Gmysql> GRANT SELECT ON test.* TO def@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER inv@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON test.deftest TO inv@localhost;
eERROR 1146 (42S02): Table 'test.deftest' doesn't exist
mysql> GRANT SELECT ON test.base TO inv@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -udef test
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 5 to server version: 5.0.19

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

mysql> select user();
+---------------+
| user()        |
+---------------+
| def@localhost |
+---------------+
1 row in set (0.00 sec)

mysql> CREATE VIEW test.deftest AS SELECT * FROM test.base;
ERROR 1142 (42000): CREATE VIEW command denied to user 'def'@'localhost' for table 'deftest'
mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
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 6 to server version: 5.0.19

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

mysql> GRANT CREATE VIEW ON test.* TO def@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -udef test
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 7 to server version: 5.0.19

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

mysql> CREATE VIEW test.deftest AS SELECT * FROM test.base;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
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 8 to server version: 5.0.19

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

mysql> drop user def@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uinv test
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 9 to server version: 5.0.19

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

mysql> select * from deftest;
ERROR 1449 (HY000): There is no 'def'@'localhost' registered

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/15845

ChangeSet@1.2317, 2006-11-27 16:15:32+03:00, evgen@moonbone.local +3 -0
  Bug#17254: Error for DEFINER security on VIEW provides too much info
  
  If a view was created with the DEFINER security and later the definer user
  was dropped then a SELECT from the view throws the error message saying that
  there is no definer user is registered. This is ok for a root but too much
  for a mere user.
  
  Now the st_table_list::prepare_view_securety_context() function reveals
  the absence of the definer only to a superuser and throws the 'access denied'
  error to others.

Pushed in 5.0.32/5.1.14-beta

Noted in 5.0.32, 5.1.14 changelogs.