Unable to Revoke Permissions on Cloud MySQL

[adamfiner]

Hello everyone,

I have added a user called 'student' to my MySQL instance and would like to restrict permissions to just SELECT and SHOW VIEW. However, when I try to do this using Workbench, I am getting error 1141.

When I run the below query, I can see that only the SELECT and SHOW VIEW are granted but when connecting with the student user, I am still able to DROP tables.

select * from mysql.user where user='student'

 Does anyone know why this could be?

Thanks in advance

[ms4446]

The reason the 'student' user can still DROP tables, despite only having the SELECT and SHOW VIEW privileges granted, might be due to the DROP privilege being granted to the 'student' user at a different level (global, database, or table).

The DROP privilege allows a user to delete tables from a database. It can be granted at various levels:

• Global Level: Allows the user to drop tables in any database on the MySQL server.
• Database Level: Allows the user to drop tables only in a specific database.
• Table Level: Allows the user to drop a specific table.

To ensure the 'student' user cannot DROP tables, you'll need to revoke the DROP privilege at all potential levels:

1. Revoke at Global Level:

REVOKE DROP ON *.* FROM 'student'@'your_host';

Replace 'your_host' with the appropriate host specification, or use '%' if you want to match any host.

2. Revoke at Database Level (for each database where the privilege might have been granted):

REVOKE DROP ON database_name.* FROM 'student'@'your_host';

Replace database_name with the name of the database.

3. Revoke at Table Level (for each table where the privilege might have been granted):

REVOKE DROP ON database_name.table_name FROM 'student'@'your_host';

Replace database_name and table_name with the appropriate names.

After revoking the DROP privilege, the 'student' user should no longer be able to DROP tables and will only have the SELECT and SHOW VIEW privileges.

To verify the privileges of the 'student' user, you can run:

SELECT * FROM mysql.user WHERE user='student';

This will show the global privileges. For database or table-specific privileges, you might need to check the mysql.db and mysql.tables_priv tables respectively.

Remember to specify the host name when revoking privileges!

[adamfiner]

Thanks very much for your swift reply. I found a YouTube video that suggested I create a new role with the privileges I want to grant and create a new student user assigned to that role. This seems to work just as desired. Thanks again!