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