MySQL: Granting permission for CREATE VIEW

Question

I created a new database schema as 'company_security' and populate the database with the table structure of very popular Company-Employee tables which contains DEPARTMENT,DEPENDENT,DEPT_LOCATIONS,EMPLOYEE,PROJECT & WORKS_ON tables. Then I created a new user in a root user session as,

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1'

What I wanted to do is create a view WORKS_ON1(Fname,Lname,Pno) on EMPLOYEE and WORKS_ON from user1. To do this, first I granted necessary permission as root to user1 as follows.

GRANT SELECT ON company_security.WORKS_ON TO 'user1'@'localhost'
GRANT SELECT company_security.EMPLOYEE TO 'user1'@'localhost' 
GRANT CREATE VIEW ON company_security.* TO 'user1'@'localhost' 

Then I flushed the privileges and this was the output for SHOW GRANTS for 'user1'@'localhost'

Then I tried to create the view from user1 as,

CREATE VIEW WORKS_ON1 AS select Fname,Lname,Pno from EMPLOYEE,WORKS_ON;

but I got an error like this,

ERROR 1142 (42000): CREATE VIEW command denied to user 'user1'@'localhost' for table 'WORKS_ON1'

I have found a similar problem in MySQL forum but the MySQL DOC reference link he mentioned in the solution seems like removed.

So my question is how to solve this problem? even I tried to solve this by granting permission for create view in a global level for user1 but that failed too. Either we cannot grant permission for the WORKS_ON1 view as the error saying because it does not exist in the database already.

MySQL version I'm using: mysql Ver 14.14 Distrib 5.6.28, for debian-linux-gnu (x86_64) using EditLine wrapper

Answer 1

This is detailed in When Privilege Changes Take Effect in the MySQL documentation.

A grant table reload affects privileges for each existing client connection as follows:

Table and column privilege changes take effect with the client's next request.

Database privilege changes take effect the next time the client executes a USE db_name statement.

Note Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database or flushing the privileges.

Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

Answer 2

I found this solution from the MySQL script generated by forward engineering.

I was getting the same error as yours:

SELECT * FROM og_erp.sellerlist Error Code: 1142. SELECT command denied to user 'user1'@'localhost' for table 'sellerlist' 0.000 sec

when creating the view:

CREATE
VIEW sellerList
AS
SELECT SellerName FROM og_erp.seller;

The solution is to create a place holder table, with schema as of view:

-- -----------------------------------------------------
-- Placeholder table for view `og_erp`.`sellerList`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS og_erp.sellerList (
  SellerName VARCHAR(50)
);

Grant CREATE VIEW, DROP on the place holder table to the user

Also give SELECT privilege on place holder table, if you want that user is able to SELECT from the view, rather than getting following error:

12:32:05 SELECT * FROM og_erp.sellerlist Error Code: 1142. SELECT command denied to user 'user1'@'localhost' for table 'sellerlist' 0.000 sec

Now, View can be created by the user as follows,

DROP TABLE IF EXISTS og_erp.sellerlist;
CREATE
VIEW sellerList
AS
SELECT SellerName FROM og_erp.seller;

DROP TABLE IF EXISTS og_erp.sellerlist 0 row(s) affected 0.047 sec

CREATE VIEW sellerList AS SELECT SellerName FROM og_erp.seller 0 row(s) affected 0.000 sec

SELECT * FROM og_erp.sellerlist 1 row(s) returned 0.000 sec / 0.000 sec