MySQL Bugs: #17900: CREATE VIEW command denied to user ... on SuSE 9.3

Bug #17900	CREATE VIEW command denied to user ... on SuSE 9.3
Submitted:	3 Mar 2006 16:31	Modified:	9 Jun 2006 18:43
Reporter:	Gabriel Zenarosa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15 & 5.0.18	OS:	Linux (SuSE 9.3)
Assigned to:		CPU Architecture:	Any

Description:
On MySQL server 5.0.15 and 5.0.18 for SuSE 9.3, views cannot be created by users that have been granted "all" privileges on a database.

The problem does not occur for the 5.0.15 release of MySQL server for Windows.

How to repeat:
Below is a snapshot containing sequences of commands and events showing the problem on SuSE 9.3.

-----

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

mysql> grant all on priorities.* to 'priorities'@'%' identified by 'priorities';

Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye

user@host ~
$ mysql -upriorities -ppriorities priorities
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 109560 to server version: 5.0.15-nt

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

mysql> create table temp (col1 int);
Query OK, 0 rows affected (0.08 sec)

mysql> create view temp_view as select * from temp;
Query OK, 0 rows affected (0.00 sec)

-----

Below is a snapshot containing the same sequences of commands and events above showing the problem does not exist on Windows.

-----

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

mysql> grant all on priorities.* to 'priorities'@'%' identified by 'priorities';

Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye

host:~ #
mysql -upriorities -ppriorities priorities
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23 to server version: 5.0.15-standard

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

mysql> create table temp (col1 int);
Query OK, 0 rows affected (0.08 sec)

mysql> create view temp_view as select * from temp;
ERROR 1142 (42000): CREATE VIEW command denied to user 'priorities'@'localhost' for table 'temp_view'

-----

The snapshots are reversed.

Thank you for a problem report. This is a known problem that seems to be solved now. Look:

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 3 to server version: 5.0.21

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

mysql> create database priorities;
Query OK, 1 row affected (0.01 sec)

mysql> grant all on priorities.* to 'priorities'@'localhost' identified by 'priorities';
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -upriorities -ppriorities priorities
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.21

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

mysql> create table temp(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create view temp_v as select * from temp;
Query OK, 0 rows affected (0.00 sec)

So, please, try to repeat with a newer version, 5.0.20.

5.0.21 seems to still have this bug

environment: Suse Linux 9.3
mysql Ver 14.12 Distrib 5.0.21, for pc-linux-gnu (i686) using readline 5.0

Using UML2PHP generated creation statements - here is a protocol of the problem:

create database with command 'CREATE DATABASE smartRQM;'
grant privileges with command 'GRANT ALL PRIVILEGES ON smartRQM.* TO smartrqm@localhost identified by '<passwordhidden>' with grant option;'
flushing privileges with command 'flush privileges;'

CREATE/ALTER TABLE for GV1_UserRole asked for ...
using ...'CREATE TABLE GV1_UserRole (oid varchar(32) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY(name));'

CREATE VIEW GV1_UserRoleView AS SELECT GV1_UserRole.* FROM GV1_UserRole; [nativecode=1142 ** CREATE VIEW command denied to user 'smartrqm'@'localhost' for table 'GV1_UserRoleView'][-1]

/usr/bin/mysql_fix_privilege_tables

fixes the problem - so the statement
"NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!"
is a bit misleading and you might want to fix it

Thanks! "/usr/bin/mysql_fix_privilege_tables" seems to be the key!