Database Privileges

A MySQL database serves as an entity for storing information in the form of tables. All data in these databases is organized into logically connected tables, which can be accessed using the SQL query language. MySQL verifies credentials and permissions to ensure that database access is restricted, maintaining security by implementing a user system. Authorized users can assign varying levels of access to databases, which may be limited or full.

Privileges for MySQL Users

In the MySQL database management system, several privileges can be granted to users. Below are some of the most common MySQL user privileges along with their descriptions:

• CREATE: Allows the creation of new databases and tables.
• DROP: Permits the deletion of databases or tables.
• INSERT: Enables the addition of rows to a table.
• UPDATE: Allows changes to the content of table rows.
• DELETE: Permits the removal of rows from a table.
• ALTER: Allows changes to the table structure, requiring both CREATE and INSERT privileges.
• SELECT: Enables reading data from tables by selecting specific columns and applying logical criteria.
• GRANT OPTION: Allows a user to assign specific rights to other users.
• LOCK TABLES: Grants the ability to lock a table while it is being modified.
• REFERENCES: Creates links between tables using foreign keys.
• EVENT: Grants rights to create, modify, or delete scheduled tasks.
• TRIGGER: Allows the creation, modification, or deletion of triggers that perform additional actions during DELETE, UPDATE, or INSERT operations.
• INDEX: Grants rights to add or remove indexes from tables, which can improve search performance.
• CREATE TEMPORARY TABLES: Allows the creation of temporary tables for the duration of a session.
• CREATE VIEW: Permits the creation of views that present data from other tables.
• SHOW VIEW: Allows inspection of the query that defines a view created with CREATE VIEW.
• CREATE ROUTINE: Grants the ability to create procedures, which are sets of SQL commands.
• ALTER ROUTINE: Permits modification of procedures created with CREATE ROUTINE.
• EXECUTE: Allows calling prepared procedures.
• FILE: Grants read access to any file on the server that MySQL can access and permission to create files in writable directories.
• SUPER (admin): Provides broad rights for numerous operations.

How to Assign Privileges to MySQL Users in cPanel

All operations involving MySQL databases, users, and their permissions are handled within the MySQL databases section of cPanel. To connect to a database, MySQL must authenticate the hostname from which the request originates, along with the database username and password. Once the connection is verified, MySQL checks that the necessary privileges have been granted to the specified user.

Steps to Assign Privileges:

1. Log into cPanel and navigate to Databases → MySQL Databases.
2. Under the MySQL Users section, go to the Add User to Database area or select the user you wish to grant or deny privileges.
3. The Current Users section will update, and a dialog box will appear for assigning privileges.
4. Assign the desired privileges to the user.
5. Click the Make Changes button.
6. After confirming the changes, you will be redirected to a confirmation page, and the Current Databases section on the MySQL database and user management page will refresh.