Creating a database user and giving it the right privileges

A database without a user is no use to anyone, and a user with too many privileges is an open door. In cPanel you do it in MySQL Databases: create the database, create the user, link the two and choose what that user may do. It takes three minutes.

cPanel adds your account prefix and an underscore to the name of both the database and the user. If the account is called abc and you type shop, the real name is abc_shop. That full name is what goes into your application.

Step by step

1 In cPanel open MySQL Databases. Under Create New Database type a name and create it.
2 Further down, under MySQL Users, choose a name and a strong password. The password generator makes a good one. Save it now, because you will not see it again.
3 Under Add User To Database, pick the user and the database and click Add.
4 The privilege list appears. For an application (WordPress, a shop, your own app) tick ALL PRIVILEGES on this database. For a reporting query, tick SELECT only.
5 In the application use three values: the full database name, the full user name and the password. The server is localhost.

Which privileges to give

Privilege What it allows When to give it
SELECT Reading data. Always. On its own it suits reports and read-only dashboards.
INSERT, UPDATE, DELETE Writing, changing and deleting rows. Any application that stores data.
CREATE, ALTER, DROP, INDEX Changing the structure of tables. Installing and updating applications. A read-only user does not need them.
ALL PRIVILEGES Everything cPanel allows, on this database only. The normal choice for the application that owns the database.
What goes wrong: you change the user’s password in cPanel and forget to change it in the application’s configuration file. The site starts showing Error establishing a database connection. See the causes, in order. Another trap: deleting the user does not delete the database, and deleting the database does not delete the user.

More than one user on the same database

You can link several users to the same database, each with its own privileges. For example: one user for the site, with all privileges; another with SELECT only, for whoever looks up figures; and another with SELECT and LOCK TABLES only, for the automatic backup. If one password leaks, you change only that one. In MySQL Databases, the Current Databases section shows the users linked to each database and lets you remove a link without deleting the user.

One user per application. If one application is compromised, only its own database is lost. And do not use your cPanel password for the database: they are different things and should have different passwords. See where each application keeps its connection details.

Created the user and the application still will not connect? Tell us the database name and the error message and we will look.

Open a support ticket

SEE ALSO

Creating databases and using phpMyAdmin

Error establishing a database connection: the causes, in order

Password generator

RECOMMENDED PRODUCT

Web hosting with cPanel

Domain and SSL included, daily backups and the panel you already know. from KSh858.00/mo (3-year plan, with coupon)

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Creating databases and using phpMyAdmin Almost every site that isn't just static pages needs a database. In cPanel you create one in about a minute - and there...
What time the server is on, and why PHP and MySQL can disagree A form that records the wrong time, an order dated yesterday, a scheduled job firing two hours early - it is nearly alwa...
Backing up the database of a Drupal site Drupal files are the skeleton; the database is everything else. Pages, users, menus, permissions, site configuration and...
Connecting to your MySQL database with MySQL Workbench MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a spreadsh...
When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line of wh...
Exporting a database with phpMyAdmin In cPanel open phpMyAdmin, click the database name in the left column, go to the Export tab, leave the method on Quick a...