Remove the DROP table privilege for a MySQL user

If you want to remove the DROP permission for a MySQL user, you can use the REVOKE statement. Assuming you want to revoke the DROP permission for a user named username on a database named database_name, you can do it with the following steps:

1. Connect to MySQL as a user with administrative privileges. For example:

bash

                      mysql -u root -p

Replace root with the appropriate MySQL administrative user, and you'll be prompted to enter the password.

  Once you are connected to MySQL, run the REVOKE statement to remove the DROP permission:

sql

                      REVOKE DROP ON database_name.* FROM 'username'@'localhost';

This statement revokes the DROP privilege for the specified user on the specified database. Adjust the database_name, username, and 'localhost' values according to your setup.

  After revoking the permission, you need to run the FLUSH PRIVILEGES statement to apply the changes:

sql

FLUSH PRIVILEGES;

This command reloads the grant tables and makes the changes take effect immediately.

Remember to replace database_name and username with your actual database name and user, respectively. Also, ensure you have the necessary administrative privileges to execute these statements.

 

LIST OF PRIVILEGES

Privilege	Description
CREATE	Create a database using the CREATE DATABASE statement, when the privilege is granted for a database. You can grant the CREATE privilege on databases that do not yet exist. This also grants the CREATE privilege on all tables in the database.
CREATE ROUTINE	Create Stored Programs using the CREATE PROCEDURE and CREATE FUNCTION statements.
CREATE TEMPORARY TABLES	Create temporary tables with the CREATE TEMPORARY TABLE statement. This privilege enable writing and dropping those temporary tables
DROP	Drop a database using the DROP DATABASE statement, when the privilege is granted for a database. This also grants the DROP privilege on all tables in the database.
EVENT	Create, drop and alter EVENTs.
GRANT OPTION	Grant database privileges. You can only grant privileges that you have.
LOCK TABLES	Acquire explicit locks using the LOCK TABLES statement; you also need to have the SELECT privilege on a table, in order to lock it.
SHOW CREATE ROUTINE	Permit viewing the SHOW CREATE definition statement of a routine, for example SHOW CREATE FUNCTION, even if not the routine owner. From MariaDB 11.3.0.