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.