MySQL Bugs: #24040: Create View don't succed with "all privileges" on a database

Bug #24040	Create View don't succed with "all privileges" on a database
Submitted:	7 Nov 2006 12:37	Modified:	4 Apr 2007 4:08
Reporter:	Francesco Dalla Ca'	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.27,5.0.28,5.0.30	OS:	Linux (Linux SLES 8,WinXP)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	bfsm_2007_01_18, create view, grant all privileges

Description:
A user foo with "all privileges" granted on a database foo, does not succeed a "create view" command on that database.

How to repeat:
mysql@xantippe:~> /usr/local/mysql/bin/mysql --version 
/usr/local/mysql/bin/mysql  Ver 14.12 Distrib 5.0.27, for pc-linux-gnu (i686)
using  EditLine wrapper
mysql@xantippe:~> /usr/local/mysql/bin/mysql -u root -S
/var/lib/mysql/mysql_3.sock -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1278 to server version: 5.0.27-log

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

mysql> create database foo;
Query OK, 1 row affected (0.02 sec)

mysql> create user foo@localhost identified by 'xxxxxx';
Query OK, 0 rows affected (0.05 sec)

mysql> grant all privileges on foo.* to foo@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table test.t;
Query OK, 0 rows affected (0.04 sec)

mysql> use test;
Database changed
mysql> create table t (x int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> grant select on test.t to foo@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> \q
Bye
mysql@xantippe:~> /usr/local/mysql/bin/mysql -u foo -S
/var/lib/mysql/mysql_3.sock -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1280 to server version: 5.0.27-log

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

mysql> select * from test.t;
+------+
| x    |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> use foo
Database changed
mysql> create view v_t as select * from test.t;
ERROR 1143 (42000): create view command denied to user 'foo'@'localhost' for
column 'x' in table 'v_t'
mysql> \q
Bye
mysql@xantippe:~> /usr/local/mysql/bin/mysql -u root -S
/var/lib/mysql/mysql_3.sock -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1281 to server version: 5.0.27-log

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

mysql> revoke all privileges on foo.* from foo@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select, create view on foo.* to foo@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for foo@localhost;
+-------------------------------------------------------------------------------
-----------------------------+
| Grants for foo@localhost                                                      
                            |
+-------------------------------------------------------------------------------
-----------------------------+
| GRANT USAGE ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD
'*EDC7D88E9F520C88CD331C21112413BB49C11AC6' | 
| GRANT SELECT, CREATE VIEW ON `foo`.* TO 'foo'@'localhost'                     
                            | 
| GRANT SELECT ON `test`.`t` TO 'foo'@'localhost'                               
                            | 
+-------------------------------------------------------------------------------
-----------------------------+
3 rows in set (0.00 sec)

mysql> \q
Bye
mysql@xantippe:~> /usr/local/mysql/bin/mysql -u foo -S
/var/lib/mysql/mysql_3.sock -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1283 to server version: 5.0.27-log

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

mysql> use foo
Database changed
mysql> create view v_t as select * from test.t;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_t;
+------+
| x    |
+------+
|    1 | 
+------+
1 row in set (0.01 sec)

mysql>

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.30-BK on Linux:

mysql> create user foo1@localhost identified by 'xxxxxx';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on foo.* to foo1@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.db where user='foo1'\G
*************************** 1. row ***************************
                 Host: localhost
                   Db: foo
                 User: foo1
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
1 row in set (0.00 sec)

mysql> exit

So, we have CREATE VIEW privilege at the database level.

openxs@suse:~/dbs/5.0> bin/mysql -ufoo1 -pxxxxxx foo
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 2
Server version: 5.0.30-debug Source distribution

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

mysql> select * from test.t;
+------+
| i    |
+------+
|   42 |
+------+
1 row in set (0.01 sec)

mysql> create view v_t as select * from test.t;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v_t as select * from test.t;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_t;
+------+
| i    |
+------+
|   42 |
+------+
1 row in set (0.01 sec)

mysql> show grants for foo1@localhost;
+-------------------------------------------------------------------------------
------------------------------+
| Grants for foo1@localhost
                              |
+-------------------------------------------------------------------------------
------------------------------+
| GRANT USAGE ON *.* TO 'foo1'@'localhost' IDENTIFIED BY PASSWORD '*4661D72F443C
FC758BECA246B5FA89525BF23E91' |
| GRANT ALL PRIVILEGES ON `foo`.* TO 'foo1'@'localhost'
                              |
+-------------------------------------------------------------------------------
------------------------------+
2 rows in set (0.00 sec)

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

Please, check if I missed something from your test case.

I have removed all the privileges on test.* to all users.
You have:
GRANT USAGE ON *.* TO 'foo1'@'localhost' IDENTIFIED BY PASSWORD '*4661D72F443CFC758BECA246B5FA89525BF23E91'
GRANT ALL PRIVILEGES ON `foo`.* TO 'foo1'@'localhost'

How you can create a view on test.t without select privilege on test.t?

Please, try to create the view on a table on another database than "test". (i.e.: foo2.t).

mysql@xantippe:~> /usr/local/mysql/bin/mysql -u root -S /var/lib/mysql/mysql_3.sock -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3272 to server version: 5.0.27-log

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

mysql> create database foo2;
Query OK, 1 row affected (1.01 sec)

mysql> use foo2;
Database changed
mysql> create table t (x int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.02 sec)

mysql> grant select on foo2.t to foo@localhost;
Query OK, 0 rows affected (0.04 sec)

mysql> \q
Bye
mysql@xantippe:~> /usr/local/mysql/bin/mysql -u foo -S /var/lib/mysql/mysql_3.sock -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3279 to server version: 5.0.27-log

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

mysql> use foo
Database changed
mysql> show grants;
+------------------------------------------------------------------------------------------------------------+
| Grants for foo@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*EDC7D88E9F520C88CD331C21112413BB49C11AC6' | 
| GRANT ALL PRIVILEGES ON `foo`.* TO 'foo'@'localhost'                                                       | 
| GRANT SELECT ON `foo2`.`t` TO 'foo'@'localhost'                                                            | 
+------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> create view v_t as select * from foo2.t;
ERROR 1143 (42000): create view command denied to user 'foo'@'localhost' for column 'x' in table 'v_t'
mysql>

This is not a bug. Views are intended to hide table details,
so that we can not check privileges on the base tables -- when
a view is accessed, only privileges of the view must be taken
into account.

The problem in the test case is that we are creating a view
in the database, where the user has ALL PRIVILEGES. So, the view
is (potentially) updatable. If we allowed creating such a view,
it could be updatable, so the user would be able to update data
in the base tables. This is a security breach.

If one wants to create read-only view, one should gives only SELECT
privilege for that view.

Another option, which can be considered to be implemented in the
future is to implement something like "WITH READ ONLY" clause
in Oracle. The idea is that we introduce a new syntax in order to
explicitly specify that view will be (non)updatable, (non)insertable,
...

I completely disagree. MySQL can assign privileges at the table or even the column levels. When a VIEW is created against a table for which we have limited privileges, we should get a limited-privilege view (take the lesser subset of privs).

This is a bug because it fails to meet the "reasonable use" rule. It is reasonable to assume that if you are creating a view against a table for which you have read-only privileges that the view would grant you only read-only privileges.

I will try to describe the quite common real-world situation for which this will apply:

A data warehouse is created in database DW. Several users need to do research and analysis on the data in DW but cannot be given rights to work within that database directly. These users have been granted read-only privileges for a subset of tables in the DW database. A separate database, RESEARCH, is created and these users are given full privileges to it.

Because the tables in DW are rather large, it is impractical to materialize local copies of the data within RESEARCH. However, it is well within the expectations of the database users to be able to generate VIEWs within RESEARCH against the base tables in DW and use those VIEWs as the basis for further analysis. However, because the VIEW is not respecting the permissions of the base tables in DW but is instead trying to respect the permissions the user has within the RESEARCH database we cannot create those views. This is the basis of the argument that the implementation of VIEW security is flawed and needs to be repaired.

Why would it be impractical for the server to generate a VIEW with the intersection of privileges that the users have against the base data? If a view is composed of a JOIN between a read-only table and full-rights table then the VIEW is created with just read-only privileges. This security check would also ensure that on each call to the VIEW that the user still has rights to view the underlying data. Alternatively elevated privileges could be granted, for a short period of time, allowing the user to use their VIEW to perform maintenance on the data in the base tables.

This is a bug and needs to be fixed.

After the second discussion, it was considered as a bug.
I'll be working on it.

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

ChangeSet@1.2487, 2007-03-22 00:34:15+03:00, anozdrin@booka.opbmk +3 -0
  Fix for BUG#24040: Create View don't succed with "all privileges"
  on a database.
  
  The problem was that we required not less privileges on the base tables
  than we have on the view.
  
  The fix is to be more flexible and allow to create such a view (necessary
  privileges will be checked at the runtime).

Pushed into 5.0.40 and 5.1.18

Noted in 5.0.40, 5.1.18 changelogs.

Some views could not be created even when the user had the requisite
privileges.

Pushed into 5.1.18-beta

Pushed into 5.0.42

Moved the 5.0.40 changelog entry to 5.0.42.