I would like to allow a user to create/alter/drop views in a database, but not have any ability to create/alter/drop tables. I have given the user the CREATE VIEW, SHOW VIEW, and SELECT privileges. Creating a view works fine, but when the user tries to alter the view, he gets an error that the DROP command is denied. From my reading of the manual, if I give the user the DROP privilege, he’ll be able to drop any table in the database which is not wanted.
Is there a way that I can allow this user only to drop views and not tables? Or be able to edit views in some other method?
One idea I had was to restrict the DROP privilege to a list of views based on a wildcard, assuming all views he creates starts with that wildcard, but it seems that requires an exact list of views upfront. I tried “GRANT DROP ON test.‘vw_%’ TO user@localhost”, but that gives a syntax error. I could list out the individual views, but that means I would have to run a new GRANT query each time he creates a view, which is not really ideal.
Thanks for your help.