MySQL Bugs: #18726: Cannot create a view on a table in another database

Bug #18726	Cannot create a view on a table in another database
Submitted:	3 Apr 2006 6:41	Modified:	19 Apr 2006 7:00
Reporter:	Per Holm	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	MacOS (Mac OS X 10.4.5)
Assigned to:	Hartmut Holzgraefe	CPU Architecture:	Any

Description:
This has been the subject of a thread in the Views forum. It may be related to the following bug: http://bugs.mysql.com/bug.php?id=18681

Even if you have the necessary privileges, you cannot create a view on a table in another database. For example, if you have the SELECT privilege on the table test.t, you cannot "create view v_t as select * from test.t" in another database where you have the privilege CREATE VIEW.

How to repeat:
-- do this as root

use test;
create table t (x int);
insert into t values (1);
create user readonly;
create database readonly;
grant all on readonly.* to readonly;
grant select on test.t to readonly;

-- now log in as readonly;

% mysql -A -u readonly readonly
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 152994 to server version: 5.0.18-standard

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

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

mysql> 
mysql> -- THIS DOESN'T WORK
mysql> create view v_t as select * from test.t;
ERROR 1143 (42000): create view command denied to user 'readonly'@'%' for column 'x' in table 'v_t'
mysql>

You got tricked by the anonymous user account,
it all works fine when you fix your grand statement:

  grant select on test.t to readonly@localhost;

in your test case the "@hostname" was missing

I don't know if you look at new comments for bug reports that have been marked Not a Bug? Anyway, I still cannot create the view on test.t, even with the @localhost user (which would have been strange anyway, since I want users @% to be able to create views).

I still get the same error (now in 5.0.19):

puccini% mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32 to server version: 5.0.19-standard

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

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

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

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

mysql> create user readonly@localhost;
Query OK, 0 rows affected (0.00 sec)

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

mysql> grant select on t to readonly@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
puccini% mysql -u readonly
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33 to server version: 5.0.19-standard

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

mysql> show grants;
+----------------------------------------------------------------+
| Grants for readonly@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'readonly'@'localhost'                   |
| GRANT ALL PRIVILEGES ON `readonly`.* TO 'readonly'@'localhost' |
| GRANT SELECT ON `test`.`t` TO 'readonly'@'localhost'           |
+----------------------------------------------------------------+
3 rows in set (0.00 sec)

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

I'm really sorry for bumping this (if the concept of bump applies here in bug database), but I just came across this same problem, and could not find direct answer to it.

The problem actually seems to be that select privilege for the table from where the view gets its data is not enough. I believe that this is because (simple) views are updatable, and a user can not create a view if he/she has not enough privileges to use that view - including the write operations. The create view statement should work if the user creating the view has insert, update and delete privileges in addition to select for the table used by view. I am not sure if non-updatable views require write privileges (but my guess is that they are not needed).

I am running into this same behavior with MySQL version 5.0.26-Max (community) running on SuSE Linux version 10, patch level 1 and MySQL version 5.0.41 (community) running on MS-Windows XP Pro, Version 2002, Service Pack 2.

Per Ilkka Rauta's note, if the user ID is granted insert/update/delete privileges on the base table, then the user can create the view in the second database.  However, in my case we do not want to allow updates via the view and would prefer not to grant those privileges to the ID creating the view. 

In MySQL, revoking the insert/update/delete permissions on the base table after the view is created seems to work, but if the view still functions (for select only) without those permissions, why should it's creation require them?