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?

CodePudding user response：

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.