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.
Wildcards like you are trying to use work for database names, but unfortunately not tables/views. And if you do want to use them for the database name field, just surround it with back ticks and escape the underscore, like test\_%.
That aside, what you could do is have a specific database where the user has CREATE VIEW, SHOW VIEW, SELECT, and DROP privileges, i.e. bobsdb. Then grant the user SELECT on the other databases they will be writing the views against (i.e. proddb). Then the user can manage the views they create in bobsdb, which have SELECT privileges to the other databases you want them to be able to see in proddb. Then as long as you do not put anything else into bobsdb, they will only have their own views to drop.
Thanks for your reply. I was considering creating a 2nd database for the views as you suggested, but there were 2 downsides I was thinking of:
all table references in views the user writes would have to be qualified with the database name. For example: “SELECT name FROM proddb.user” instead of just user. Not the worst thing, but not the ideal especially as some of the queries being written will have many joined tables, all of which will need the database name.
I also have a stagedb which mirrors proddb for database structure, but not data. If the user writes a view against stagedb, he couldn’t copy/paste his sql and run it against proddb without first editing it to update the database name.
The other idea I had with the 1 database idea was to run a cronjob every x minutes looking in the information_schema for new views the user created and grant them drop privileges on those views only. Again, not the ideal either, but could work as a workaround since the MySQL permission model doesn’t seem to support a user having drop view privileges only.