8

I want to create a user with permission to create and drop views but not drop tables or databases. This is so I can play around when testing my views without the risk of accidentally deleting a table.

There is a GRANT CREATE VIEW privilege but there doesn't appear to be a GRANT DROP VIEW counterpart. GRANT DROP apparently applies to databases, tables and views.

Is this possible in MySQL?

Flash
  • 15,945
  • 13
  • 70
  • 98

1 Answers1

9

I've been researching this, too, and the answer appears to be No. You can restrict the DROP to only tables/views within one database (or a group of LIKE pattern-matched databases). This will make sure they cannot drop the entire database. Unfortunately, you cannot do pattern-matching on the table/view names. It's either all the tables/views (*) in those databases, or only explicity mentioned tables/views.

Toddius Zho
  • 624
  • 6
  • 12
  • Good grief, I just checked for MariaDB and the answer STILL appears that you have to GRANT DROP to be able to do views - then you get to drop tables too. Unbelievable. We were trying to allow a data scientist to build views for a read only production instance and ... it appears I'm forced give them the ability to drop tables in production. Wow! – J. Gwinner Jan 19 '23 at 23:42
  • I tried "or a group of LIKE pattern-matched databases" and at least with MariaDB, GRANT DROP ON MYDATABASE.Views_* or GRANT DROP ON MYDATABASE.Views_% didn't seem to work either. – J. Gwinner Jan 19 '23 at 23:56