Search code examples
mysqlalter-tablesql-grant

How to prevent user from drop column in mysql 8

I have an existing user with all privileges on a specific database. The user was created like this:

GRANT ALL PRIVILEGES ON test_db.* TO 'tester1'@'%%'

However, I want to restrict this user from dropping any table and any column of the database. I can revoke the drop table privilege from the user but i cannot revoke the alter table privilege because he should still be able to add new column. Is there a way to prevent this user from dropping any column?

Solution

  • Sorry, MySQL does not have a distinct privilege for dropping a column. If the user has the ALTER privilege, they can both add or drop a column.