MySQL Bugs: #14705: VIEW security is checked once

Bug #14705	VIEW security is checked once
Submitted:	7 Nov 2005 13:59	Modified:	8 Nov 2005 10:30
Reporter:	Alexander Abramov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16-BK, 5.0.15-nt	OS:	Linux (Linux, Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
We have got seriouse problem in security system:
Privileges are checked once when we are creating View. After that every users who has rights to select this View get access to the View's data.

How to repeat:
For example, user A has all privilegies in two schemas. 
One of them contains Tables. Second contains Views for this Tables. Then he creates Views for all Tables. After that Root denide access of user A to all Tables. But this way user A still has access to the data of this Tables (for example, to mysql.user).

Suggested fix:
You have two fields in CREATE VIEW syntax: DEFINER & SQL SECURITY. But no one of this works.

Thank you for a problem report. Please, provide a sequence of statements you performed and the results you got (as well as what you expected to get). We have to create a test case, and it looks like you have one already.

As for your suggestion: "You have two fields in CREATE VIEW syntax: DEFINER & SQL SECURITY. But no one of this works.", I found no such fields in the manual (http://dev.mysql.com/doc/refman/5.0/en/create-view.html), so I am not sure what are you writing about. That fields exists for stored procedures, though.

Thank you for bugreport. This bug should already be fixed as part of bugfix for bug#9505. This bugfix is included in 5.0.16 version of server. If you will be so kind to provide us with repeatable test case (as it described in the previous post) we will check that above guess is really true.

As user root, say:

mysql> create database tst;
Query OK, 1 row affected (0.00 sec)

mysql> use tst;
Database changed

mysql> create table t1 (password varchar(11));
Query OK, 0 rows affected (0.01 sec)

mysql> inset into t1 (password) values (buba_odessa);
Query OK, 1 rows affected (0.01 sec)

mysql> create database view;
Query OK, 1 row affected (0.00 sec)

mysql> use view;
Database changed

mysql> CREATE ALGORITHM=UNDEFINED DEFINER= root SQL SECURITY INVOKER VIEW hole AS select * from t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant select on view to hacker@remotehost;
Query OK, 0 rows affected (0.00 sec)

As user hacker@remotehost (on another shell and another copy of mysql client),
say:

mysql> use tst;
ERROR 1044 (42000): Access denied for user 'hacker'@'remotehost' to database 'tst'

mysql> use view;
Database changed

mysql> select * from hole;
+-------------+
| password     |
+-------------+
| buba_odessa |
+-------------+
1 row in set (0.00 sec)

Verified similar to the steps described, on 5.0.16-BK (ChangeSet@1.1972, 2005-11-05 22:45:54-08:00, igor@rurik.mysql.com):

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23 to server version: 5.0.16

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

mysql> drop database tst;
Query OK, 1 row affected (0,00 sec)

mysql> create database tst;
Query OK, 1 row affected (0,00 sec)

mysql> use tst;
Database changed
mysql> create table t1 (password varchar(11));
Query OK, 0 rows affected (0,01 sec)

mysql> insert into t1 (password) values ('buba_odessa');
Query OK, 1 row affected (0,00 sec)

mysql> create database view;
ERROR 1007 (HY000): Can't create database 'view'; database exists
mysql> drop database view;
Query OK, 0 rows affected (0,00 sec)

mysql> create database view;
Query OK, 1 row affected (0,00 sec)

mysql> use view;
Database changed
mysql> CREATE ALGORITHM=UNDEFINED DEFINER= root SQL SECURITY INVOKER VIEW hole
    -> AS select * from t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'SQL SECURITY INVOKER VIEW hole
AS select * from t1' at line 1
mysql> CREATE ALGORITHM=UNDEFINED DEFINER= root VIEW hole AS select * from t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'VIEW
hole AS select * from t1' at line 1

(So, I still do not understand where you got these clauses from...)

mysql> CREATE ALGORITHM=UNDEFINED VIEW hole AS select * from t1;
ERROR 1146 (42S02): Table 'view.t1' doesn't exist
mysql> CREATE ALGORITHM=UNDEFINED VIEW hole AS select * from tst.t1;
Query OK, 0 rows affected (0,00 sec)

mysql> grant select on hole to hacker@localhost;
Query OK, 0 rows affected (0,00 sec)

Then, from the other session:

[openxs@Fedora 5.0]$ bin/mysql -uhacker
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24 to server version: 5.0.16

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

mysql> use tst;
ERROR 1044 (42000): Access denied for user 'hacker'@'localhost' to database 'tst'
mysql> use view;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from hole;
+-------------+
| password    |
+-------------+
| buba_odessa |
+-------------+
1 row in set (0,01 sec)

It is not the same problem as solved in http://bugs.mysql.com/bug.php?id=9505.

Sorry, CREATE VIEW should read as

CREATE VIEW hole AS SELECT * from tst.t1

We should read from another database.

Thank you for bugreport. this bug was fixed as part od bug#9505:
[bell@sanja client]$ ./mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.16-debug

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

mysql> create database tst;
Query OK, 1 row affected (0.00 sec)

mysql> use tst;
Database changed
mysql> create table t1 (password varchar(11));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 (password) values ('buba_odessa');
Query OK, 1 row affected (0.01 sec)

mysql> create database view;
Query OK, 1 row affected (0.00 sec)

mysql> use view;
Database changed
mysql> CREATE VIEW hole AS SELECT * from tst.t1;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on hole to hacker@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> \q
Bye
[bell@sanja client]$ ./mysql -u hacker
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.16-debug

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

mysql> use view;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from hole;
+-------------+
| password    |
+-------------+
| buba_odessa |
+-------------+
1 row in set (0.00 sec)

mysql> \q
Bye
[bell@sanja client]$ ./mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.16-debug

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

mysql> use view;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter SQL SECURITY INVOKER VIEW hole AS select * from t1;
ERROR 1146 (42S02): Table 'view.t1' doesn't exist
mysql> alter SQL SECURITY INVOKER VIEW hole AS select * from tst.t1;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
[bell@sanja client]$ ./mysql -u hacker
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.16-debug

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

mysql> select * from hole;
ERROR 1046 (3D000): No database selected
mysql> use view
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from hole;
ERROR 1356 (HY000): View 'view.hole' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql>       

I.e. if we have SQL SECURITY DEFINER (default) the hacker allowed to select. if SQL SECURITY INVOKER the hacker prohibited to use view.

BTW standard behaviour for view is SQL SECURITY DEFINER analog, but this clause and allowing to execute view with invoker rights is mysql extention.

Clauses DEFINER and SQL SECURITY are documenting now and will appear soon in our documentation.