MySQL Bugs: #18515: cannot create view even though the user is granted all privileges

Bug #18515	cannot create view even though the user is granted all privileges
Submitted:	26 Mar 2006 10:10	Modified:	27 Apr 2006 14:06
Reporter:	oz b	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (Linux Fedora Core 4)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I am trying to create a view in mysql5.0.18 using a query:
mysql -userviceuser -p6666 < org_view_test.sql

where org_view_test.sql is:
create view search.orgs_view as select id from prod.organizations;

and I get the following error:
ERROR 1143 (42000) at line 1: create view command denied to user 'serviceuser'@'localhost' for column 'id' in table 'orgs_view'

serviceuser has create_view privileges on on the "search" database and select privileges on "prod" database.

WORKAROUND:
1- delete the user serviceuser(where Db=search) from mysql.db table.
2- recreate it with only "create view" privilege. FLUSH PRIVILEGES.
3- create view.
4- grant all privileges to the user again. FLUSH PRIVILEGES.

How to repeat:
1- create a user 
2- grant all privileges
3- try to create a view and you will get access denied error.

Thank you for the bug report.
Could you please provide the output using the mysql client
how you did the exact steps. Sometimes just describing it
not shows the relevant issues. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I had the same problem on 5.0.18. I have tried to upgrade at the 5.0.27-log version, but the problem persist.

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>

I forget to tell you the platform OS (and kernel):

mysql@xantippe:~> uname -a
Linux xantippe 2.4.21-231-smp #1 SMP Mon Jun 28 15:31:39 UTC 2004 i686 unknown
mysql@xantippe:~> cat /etc/SuSE-release 
SuSE SLES-8 (i386)
VERSION = 8.1