Mysql – Grant permissions on views, deny select on tables

MySQLpermissionsview

I have a MySQL user and I want it to view ONLY the views I want and not any other table in the database. I've granted this user permissions only on certain views as following:

GRANT SHOW VIEW ON `myDatabase`.`awesome_view` TO 'thisUser'@'%'

If I do a show grants; statement I can only see this permissions as expected.
However I'd like this user to query JUST the views and not the tables that are related to these views, but I can't find a way to do this. It seems to be that if I want the user to do a select on the view, the select must also be granted for the table, or am I wrong?

If I deny the selectstatement in the rest of the tables, and in the command line I try to do a select I got the following:

SELECT * FROM myDatabase.fordibenForYouTable;
ERROR 1142 (42000): SELECT command denied to user 'thisUser'@'localhost' for table 'fordibenForYouTable'

That's what I want indeed, but I also got denied if I select the view data.

Is there a way I can make available to the user just the views and not the tables?

Best Answer

You will have to treat the view as a table. The information_schema already does

If you run

SELECT table_name FROM information_schema.tables
WHERE engine IS NULL;

you get all the views.

Just grant SELECT on the view to the user as follows

GRANT SELECT ON `myDatabase`.`fordibenForYouTable` TO 'thisUser'@'localhost' ;

Once you do this, you should have SELECT access to the table.

To make sure, run SHOW GRANTS FOR 'thisUser'@'localhost';

You should also be able to see what table-level access is granted to 'thisUser'@'localhost'

SELECT * FROM mysql.tables_priv
WHERE user='thisUser' and host='localhost'\G

You can also see what user has table-level access to myDatabase.fordibenForYouTable

SELECT * FROM mysql.tables_priv
WHERE db='myDatabase' and table_name='fordibenForYouTable'\G

GIVE IT A TRY !!!

Related Question