DBA grant option and permission issue

Question

In MySQL database, If a DBA grant select to a user1 as well as with grant option, then this user1 grant select access to user 2, can the DBA revoke user2 but not affect user 1 permission?

I tried search answers in Google, but very hard to find answer

Answer 1

In MySQL, when a user is granted a privilege with the WITH GRANT OPTION clause, it means that user not only has the privilege themselves but can also grant that privilege to other users. However, the user with the WITH GRANT OPTION cannot revoke the privilege from others if they do not have the privilege themselves.

So, in your scenario

DBA grants SELECT to user1 with WITH GRANT OPTION. User1 grants SELECT to user2. Now, if the DBA wants to revoke the SELECT privilege from user2 but not affect user1's permission, the DBA can do so without affecting user1's permission. The key point is that the DBA, as the superuser, has the authority to revoke privileges from any user, including user2, regardless of whether user1 granted the privilege or not.

To revoke the privilege from user2, the DBA can use the following SQL command:

REVOKE SELECT ON database_name.* FROM user2;

This will revoke the SELECT privilege from user2 on the specified database, and user1's permissions will remain unaffected.

However, keep in mind that user1 can still grant the privilege again to user2 if they have not been explicitly restricted from doing so by the DBA.