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

[20 Jun 2006 11:25] Tomash Brechko
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.
[20 Jun 2006 11:45] Valeriy Kravchuk
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.
[20 Jun 2006 14:44] Tomash Brechko
It appeared that only CURRENT_USER() is affected, so the bug is renamed.
[21 Jun 2006 11:33] 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/commits/8008
[30 Jun 2006 14:18] Konstantin Osipov
The approach taken in the patch is approved.
There will be another patch that implements the changes proposed by Tomash.
[30 Jun 2006 19:21] 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/commits/8569
[30 Jun 2006 20:40] Konstantin Osipov
Did a second review.
[2 Jul 2006 10:35] 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/commits/8612
[3 Jul 2006 12:25] Konstantin Osipov
The final version of the patch is approved.
[2 Aug 2006 14:10] Konstantin Osipov
Fixed in 5.0.24 and 5.1.12
[2 Aug 2006 18:26] Paul Dubois
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.