MySQL: Granting permission for CREATE VIEW

mysql-5.6permissions

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'

output for SHOW GRANTS

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

Best Answer

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.

Related Question