A database (MySQL) is an entity for storing information in the form of tables. All data in its databases are stored in the form of logically connected tables, access to which is carried out with the help of query language SQL. MYSQL verify credentials and permissions so that people’s databases are not available to every user on the server, there is a system of users for these databases. Access to any database itself can be assigned by an authorised user, and it can be limited or full.
Privileges for MySQL users
There are a number of privileges in the MySQL database management system, listed below are the most common MySQL user privileges with a description that can be assigned to a database user.
CREATE – This privilege creates new databases and tables
DROP – The privilege deletes databases or tables
INSERT – The privilege adds rows to a table.
UPDATE – The privilege changes the content of table rows.
DELETE – The privilege deletes rows from the table.
ALTER – The privilege changes the table structure which requires CREATE and INSERT privileges.
SELECT – The privilege allows reading (outputting rows) from tables using selections by columns and/or by some arithmetic and logical criteria.
GRANT OPTION – The privilege assigns specific rights to a certain user
LOCK TABLES – The privilege locks the table for the time it is artificially changed.
REFERENCES – This privilege creates links between tables using an external key.
EVENT – This privilege gives the right to create/modify/delete tasks for the scheduler
TRIGGER – The privilege creates/modifies/deletes triggers (linked to certain tables) that perform additional actions during DELETE, UPDATE or INSERT operations.
INDEX – the privilege gives the right to add/remove indexes to (from) tables. The indexes themselves are assigned manually and allow you to save time searching for rows.
CREATE TEMPORARY TABLES – The privilege creates temporary tables for session time.
CREATE VIEW – The privilege allows you to create some view in the form of a table that does not actually exist as a single one and contains only data from other tables.
SHOW VIEW – The privilege allows you to check what kind of query (which data consists of) a certain view is created by CREATE VIEW
CREATE ROUTINE – The privilege creates a procedure that is a set of SQL commands.
ALTER ROUTINE – The privilege modifies a procedure created by CREATE ROUTINE.
EXECUTE – The privilege allows you to call prepared procedures.
FILE – The privilege gives you read access to any file on the server that MySQL itself has access to, and access to create a file in directories that MySQL has write access to.
SUPER (admin) – The privilege that gives the right for many operations:
How to Assign Privileges to MySQL Users in the Control Panel
All operations with MySQL databases, database users and their permissions are done in the MYSQL databases. To initiate a connection to a database, MySQL must authenticate the hostname from which the request was made, the database user and the password. Once the connection is authorised and validated, MySQL checks that privileges have been granted to the specified database. The database user privileges need to be granted before MySQL will allow that user to execute the request on the database.
- Log into cPanel, go to Databases → MySQL Databases
- Go to the “Add User To Database” section under the MySQL Users section or select the desired user you want to grant/deny privileges
- The Current Users section will be updated, a dialog box will open for assigning privileges
- Assign all possible rights to a user
- Click the Make Changes button.
After confirming the changes made, a redirect to the page confirming that the specified rights have been granted and the Current databases section on the MySQL database and user management page will be refreshed.