How to create a database user in cPanel with specific privileges

You will create a new MySQL user in cPanel and assign specific privileges to that account. These steps apply to cPanel 118.x installations running on Ubuntu, AlmaLinux, or Rocky Linux. Follow the instructions to ensure the user has only the necessary permissions for your application.

Prerequisites

• Access to a cPanel account with full privileges (reseller or root).
• Knowledge of the database name and host you intend to use.
• A clear understanding of the specific permissions required (SELECT, INSERT, UPDATE, DELETE, etc.).
• cPanel 118.x installed on the server.

Step 1: Log in to cPanel

Open your web browser and navigate to your domain's cPanel URL. Enter your username and password to access the dashboard. Locate the MySQL Databases icon in the Files or Databases section.

https://your-domain.com:2083

Step 2: Create the database user

Click on Add New User under the MySQL Databases section. Enter the desired username for the new account. Choose a strong password and confirm it. Select the appropriate Privileges from the dropdown menu. You can select All Privileges or specific options like Select, Insert, Update, Delete, and Create.

Click Create User to finalize the process. The system will display a success message upon completion.

Step 3: Assign the user to a database

Scroll down to the Assign Existing Users to Databases section. Select the database you created or plan to create. Choose the new user you just added. Select the specific privileges you want to grant for this database. For example, select Select, Insert, and Update if the user only needs to read and modify data.

Click Assign to link the user to the database. This ensures the user can access only the specified database with the defined permissions.

Step 4: Create the database (if not existing)

If the database does not exist yet, click on Create New Database. Enter the database name. Choose a collation type, typically utf8mb4_unicode_ci. Click Create Database. This step must be completed before assigning the user to the database.

Step 5: Verify user privileges via SQL

Open the MySQL Databases page again. Scroll to the MySQL Privileges section. Click on Manage Users. Locate the new user in the list. Click on Edit next to the user name. Review the assigned privileges to ensure they match your requirements. If changes are needed, click Save.

SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv FROM mysql.user WHERE User = 'newuser';

Verify the installation

Run the following command in the MySQL shell to confirm the user exists and has the correct permissions. Replace newuser with your actual username.

mysql -u root -p -e "SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv FROM mysql.user WHERE User = 'newuser';"

You should see a result set showing the user with the expected privilege flags set to Y for granted permissions and N for others.

Troubleshooting

Error: Access denied for user 'newuser'@'localhost'
This occurs if the user does not exist or has no privileges. Ensure you created the user in cPanel and assigned the database. Run the SQL command in Step 5 to verify the user exists in the mysql.user table.

Error: Unknown database 'mydb'
The user was assigned to a database that does not exist. Create the database first using the Create New Database section in cPanel, then assign the user to it.

Error: Privilege grant failed
This often happens due to insufficient privileges on the cPanel account. Ensure you are logged in as a reseller or root user. If using a limited account, contact your hosting provider to enable database management features.

User cannot connect from remote host
By default, cPanel users connect from localhost. If your application is on a different server, you must explicitly grant privileges for the remote host. In cPanel, click Manage Users, select the user, and click Edit. Add the remote IP address in the Host column and assign privileges there.

GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'newuser'@'remote_ip';

After making changes, click Save to apply them. Always test the connection from the application server before deploying the final build.