MySQL Bugs: #20570: CURRENT_USER() in a VIEW with SQL SECURITY DEFINER returns invoker name

Bug #20570	CURRENT_USER() in a VIEW with SQL SECURITY DEFINER returns invoker name
Submitted:	20 Jun 2006 11:25	Modified:	2 Aug 2006 18:26
Reporter:	Tomash Brechko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.23	OS:	Any (any)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
SQL SECURITY DEFINER for VIEWs doesn't seem to work.  SQL body is executed in the context of the current user, not definer.

How to repeat:
The simplest test is:

CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER();

If another user executes a query 'SELECT * FROM v1' he gets his own name, not that of the definer.

Thank you for a problem report. Verified just as described with 5.0.23-BK on Linux:

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 1 to server version: 5.0.23

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

mysql> CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER();
Query OK, 0 rows affected (0.00 sec)

mysql> create user u1@localhost identified by 'u1';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on test.* to u1;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uu1 -pu1 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 3 to server version: 5.0.23

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

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

mysql> select * from v1;
+----------------+
| CURRENT_USER() |
+----------------+
| u1@localhost   |
+----------------+
1 row in set (0.01 sec)

It is a bug, because manual clearly states (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html):

"As of MySQL 5.0.10, within a stored routine that is defined with the SQL SECURITY DEFINER  characteristic, CURRENT_USER() returns the creator of the routine."

So, why it is not the same for VIEW with SECURITY DEFINER characteristic? It is inconsistent and, thus, a bug.

It appeared that only CURRENT_USER() is affected, so the bug is renamed.

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

The approach taken in the patch is approved.
There will be another patch that implements the changes proposed by Tomash.

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

Did a second review.

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

The final version of the patch is approved.

Fixed in 5.0.24 and 5.1.12

Noted in 5.0.24, 5.1.12 changelogs.

In a view defined with SQL SECURITY DEFINER, the CURRENT_USER()
function returned the invoker, not the definer.