Understanding MySQL User Permissions

There are numerous permissions you can grant to a user in MySQL. This guide will explain these in more detail.

Defining a User’s Privileges and Why They Are Important

You need specific users to have permissions to perform website management tasks, like running queries or modifying databases. This is when we grant privileges. Privileges determine how a user is able to interact with the database. For example, privileges will dictate whether or not the user can add and delete information.

The User Permissions

  • ALL – Allow complete access to a specific database. If a database is not specified, then allow complete access to the entirety of MySQL.
  • CREATE – Allow a user to create databases and tables.
  • DELETE – Allow a user to delete rows from a table.
  • DROP – Allow a user to drop databases and tables.
  • EXECUTE – Allow a user to execute stored routines.
  • GRANT OPTION – Allow a user to grant or remove another user’s privileges.
  • INSERT – Allow a user to insert rows from a table.
  • SELECT – Allow a user to select data from a database.
  • SHOW DATABASES- Allow a user to view a list of all databases.
  • UPDATE – Allow a user to update rows in a table.

When adding a user to a database through the cPanel MySQL Database Wizard you will be given the option to assign privileges as shown below.

In this case the user will have full access and be able to take advantage of all functions in the database.