Description:
The below mysql-test script demonstrates this problem. A new user is granted only CREATE VIEW and SELECT on a specific table. That user then tries to create a VIEW that references that table. When that CREATE VIEW statement is reached the following error is thrown:
Errors are (from /home/emalossi/mysql-5.0/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 17: query 'CREATE VIEW privDB.v1 AS SELECT * FROM privDB.t1' failed: 1142: CREATE VIEW command denied to user 'create_view_tbl'@'localhost' for table 'v1'
This was tried on both windows and linux builds with the same results.
This same error will occur even if the CREATE VIEW statement doesn't even reference table t1, such as:
CREATE VIEW privDB.v1 AS SELECT CURRENT_USER();
IF this line is commented out, and the script allowed to finish, it is apparent from the results that this new user is in fact being created with the requested permissions, however they don't seem to be taking effect when they should.
How to repeat:
connect (root, localhost, root,,);
CREATE DATABASE privDB;
CREATE TABLE privDB.t1 (c1 INT);
GRANT CREATE VIEW ON privDB.t1 TO 'create_view_tbl'@'localhost'
IDENTIFIED BY 'create_view_tbl';
GRANT SELECT ON privDB.t1 TO 'create_view_tbl'@'localhost';
SELECT user, db, table_name, table_priv FROM mysql.tables_priv
WHERE user='create_view_tbl';
connect (create_view_tbl, localhost, create_view_tbl, create_view_tbl,);
use privDB;
SELECT CURRENT_USER();
CREATE VIEW privDB.v1 AS SELECT * FROM privDB.t1;
connection root;
SELECT * FROM privDB.v1;
DROP VIEW privDB.v1;
DROP TABLE privDB.t1;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create_view_tbl'@'localhost';
DROP USER 'create_view_tbl'@'localhost';
Description: The below mysql-test script demonstrates this problem. A new user is granted only CREATE VIEW and SELECT on a specific table. That user then tries to create a VIEW that references that table. When that CREATE VIEW statement is reached the following error is thrown: Errors are (from /home/emalossi/mysql-5.0/mysql-test/var/log/mysqltest-time) : mysqltest: At line 17: query 'CREATE VIEW privDB.v1 AS SELECT * FROM privDB.t1' failed: 1142: CREATE VIEW command denied to user 'create_view_tbl'@'localhost' for table 'v1' This was tried on both windows and linux builds with the same results. This same error will occur even if the CREATE VIEW statement doesn't even reference table t1, such as: CREATE VIEW privDB.v1 AS SELECT CURRENT_USER(); IF this line is commented out, and the script allowed to finish, it is apparent from the results that this new user is in fact being created with the requested permissions, however they don't seem to be taking effect when they should. How to repeat: connect (root, localhost, root,,); CREATE DATABASE privDB; CREATE TABLE privDB.t1 (c1 INT); GRANT CREATE VIEW ON privDB.t1 TO 'create_view_tbl'@'localhost' IDENTIFIED BY 'create_view_tbl'; GRANT SELECT ON privDB.t1 TO 'create_view_tbl'@'localhost'; SELECT user, db, table_name, table_priv FROM mysql.tables_priv WHERE user='create_view_tbl'; connect (create_view_tbl, localhost, create_view_tbl, create_view_tbl,); use privDB; SELECT CURRENT_USER(); CREATE VIEW privDB.v1 AS SELECT * FROM privDB.t1; connection root; SELECT * FROM privDB.v1; DROP VIEW privDB.v1; DROP TABLE privDB.t1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create_view_tbl'@'localhost'; DROP USER 'create_view_tbl'@'localhost';