BuycPanel Blog

BuycPanel Blog

Latest news and updates

5/11/2015 – How to Delete a MySQL and a PostgreSQL User

Posted by Jamison on 11 05 2015.

Apart from a web administrator, several other users are allowed to access corporate databases that store large amounts of information. Users need to be granted privileges by an administrator regardless of the system used – whether MySQL or PostgreSQL – as these would define how each can interact with the database. Privileges include fundamental functions like input, update, select or delete database values. Depending on involvement, a user can be given one or all of these privileges.

 

To ensure security, nonetheless, it is recommended for a web administrator to grant database access only to users who need to be directly involved. Limiting access lessens the risk of internal data theft and fraud, especially if a database contains large quantities of network information, like in the case of corporate websites with a lead generation feature.

 

If needed, there are administrator options to permanently delete users or make appropriate changes to current users’ privileges. There is no need to alter database rights when choosing to remove a user as these will simply cease to take effect once the user gets deleted.

 

Deleting a MySQL database user

 

remove-user-iconDeleting a user permanently bars him from accessing a MySQL database, and removes him from all database associations. Doing this is relatively simple especially if the administrator’s web host has a built in cPanel. He first needs to sign in with his username and password, and locate the database that has the user he would like to remove. Once in the database, he would search for the users column, and click the X button next to the person’s username. He would then be asked to confirm permanent removal by clicking YES on the succeeding window.

 

Manually, this can be done using the DROP USER command followed by a semicolon as delimiter. Depending on the MySQL version, this command can either remove accounts without existing user rights only, or remove user privileges as well. To drop a user, the administration can check on the user’s existing privileges by keying in SHOW GRANTS. He can then revoke any of the given privileges, or drop the user entirely.

 

Deleting  a PostgreSQL database user

 

Similarly, on the PostgreSQL system, deleting a database user can be done through the DROP USER command or the dropuser executable command line. Both share the same function of permanently removing a user’s access to a PostgreSQL database, but dropuser is more flexible as it can be executed from the shell. Deleting a user does not alter anything in the database content.

 

If it happens that the user to be deleted is tagged as owner of the database, an error message will prompt. In this case, there would be a need to either drop the entire database, or change its ownership settings. Note, however, that when dropping an entire database with existing active users, an administrator first has to remove individual associations before being allowed to delete.  Also, ownership can only be changed if the administrator is also a database owner; otherwise, he would have to ask permission to alter settings.