How to Restore Database Access of Users?

Summary
If a database user loses access to databases, you can use the restoregrants utility to restore the user’s database grants. The other way is to update the password.

How to use the restoregrants script?

Run one of the following commands from the command line to use the restoregrants script to restore the user’s database access:

/usr/local/cpanel/bin/restoregrants --cpuser=$cpuser --db=$type --dbuser=$dbuser
/usr/local/cpanel/bin/restoregrants --cpuser=$cpuser --db=$type  --all

Description of the variables used in the above example:

• $cpuser — Refers to the cPanel username who lost their database access.
• $dbuser — The database user who can restore the database.
  • If you use the --dbuser flag, $dbuser describe the database user.
  • If you use the --all flag, the script restores permissions for all database users associated with the cPanel account.
• $type — Refers to the type of database:
  • mysql — MySQL®.
  • pg — PostgreSQL®.

How to use the Update Password method?

The restoregrants script may fail to restore database access in some conditions.

In case the script fails, the cPanel user or the WHM user who controls the account (system administrator or the reseller) can restore grants by resetting the cPanel account’s password.

Procedures to be followed by a cPanel user to restore grants:

If you are a cPanel user you can use the following steps to restore grants:

1] Access the Password and Security interface in cPanel  via cPanel >> Home >> Preferences >> Password & Security.

2] Choose Allow MySQL password change.

3] In the Old Password text box enter the old password.

4] Now, enter a new password in the New Password and New Password (again) text boxes.

5] Click on Change your password now.

Procedures to be followed by a WHM user to restore grants:

If you are a Server Administrator or a Reseller you can use the following steps to restore grants:

1]  Access to WHM’s List Accounts interface  via WHM >> Home >> Account Information >> List Accounts.

2]  Next to the appropriate account click the + link.

3]  Now, choose the Sync MySQL password with account password option.

4]  In the Change Password text box enter a new password.

5] Click on Change.

Congratulation you are done restoring database access of users!