3

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

  • 1
    Did you log user1 out and back in again? – Philᵀᴹ Sep 27 '16 at 11:43
  • OH.. that solves the problem once after logged out and back in again. but what is the reason? Thank you very much @Phil – Thilanka Deshan-minion91 Sep 27 '16 at 12:18
  • The reason is that the session inherits the privs granted at login time, and doesn't pick up any news ones granted until the next login. – Philᵀᴹ Sep 27 '16 at 12:20
  • ah.. got it (y) – Thilanka Deshan-minion91 Sep 27 '16 at 12:21
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.