We have a read only Amazon RDS database (MariaDB, 10.6.8 and up) that is synced to a production database.

We have a data scientist making views to handle our OLAP use cases.

You can't create a view in a read only database (at least at RDS), so I attempted to create a user in the production database that can only create views. However, attempting to create a view gives this error:

create view v_test as select * from tabItem;
ERROR 1142 (42000): ANY command denied to user 'test_view'@'%' for table 'tabItem'

It looks like this same user needs SELECT capability; granting that allows the view to be created. (I also thought perhaps it was the SELECT * but even doing select column from ... didn't work)

That would also allow them to hammer Production by executing the views in production instead of the read only database.

Are there any grants that would allow creating of views, but not executing a select statement or even using the view?

Note this is related to Grant permissions on views, deny select on tables but slightly different.

  • 1
    I removed the mysql tag, because MariaDB is not MySQL. MariaDB started as a fork of MySQL in 2010, but both products have changed significantly since then. We need to stop thinking of them as compatible or variations of the same product. In particular, the ANY command is not supported by MySQL. It's a feature specific to MariaDB 10.2 and later. Oct 19, 2022 at 22:29
  • 1
    It says in mariadb.com/kb/en/create-view: "The CREATE VIEW statement requires the CREATE VIEW privilege for the view, and some privilege for each column selected by the SELECT statement. For columns used elsewhere in the SELECT statement you must have the SELECT privilege." So the user that creates the view must have that privilege. Oct 25, 2022 at 20:53
  • 1
    Subsequently other users can query the view, even if they don't have privilege to query the base table, provided the view was created with SQL SECURITY DEFINER. That is, they gain access to the base table through the view, because the view adopts the definer's privileges. Oct 25, 2022 at 20:53
  • 1
    I don't think what you describe is an option in either brand of software. MySQL's description of the privileges required in this case is identical: dev.mysql.com/doc/refman/8.0/en/create-view.html Oct 25, 2022 at 22:42
  • 1
    I'm afraid this is something you will have to enforce in your application code. If by users you mean developers of the application code, then you're SOL. I have also had no success preventing developers from shooting themselves in the foot. Nov 17, 2022 at 18:41


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.