Grant permissions on views, deny select on tables

Question

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?

Answer 1

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 !!!

Answer 2

I know this is old now but here is what solved this problem for me...

The answer was with the "SQL SECURITY" syntax.

my original view definition had SQL SECURITY set to be "INVOKER". This meant the user was forced to have select permission on both the view and the table.

When I changed the SQL SECURITY to be "DEFINER" I can give the user select permission only on the view.