Databases (MySQL) is an entity for storing information in the form of tables. So that other people's databases are not available to absolutely every user on the server, there is a system of users for these databases. Access to any database itself can be assigned by the administrator (or an authorized user) to another user, and it can be full or to some extent limited.
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 is a freely distributed system, i.e. you do not have to pay for its use. In addition, it is fast, reliable and, most importantly, easy to use DBMS, quite suitable for not too global projects.
Rights for MySQL users
There are a number of privileges in the MySQL DBMS, listed below with a description. The latter privileges are administrator level privileges, which are explicitly stated for reference only. Full information about rights/privileges is available in MySQL developer documentation:
CREATE - allows you to create new databases and tables
DROP - allows you to delete databases or tables
INSERT - allows you to add rows to a table.
UPDATE - allows you to change the content of table rows. Not to be confused with ALTER, which allows you to change the table structure itself (number of rows/columns, column types).
DELETE - the opposite of INSERT - allows you to delete rows from the table.
ALTER - allows you to change the table structure. Requires CREATE and INSERT privileges.
SELECT - allows reading (outputting rows) from tables using selections by columns and/or by some arithmetic and logical criteria.
GRANT OPTION - allows you to assign specific rights to a certain user (also select). It is possible to give/reject only the rights that the assignor himself has.
LOCK TABLES - locks the table for the time it is artificially changed (administration) so that the data inside it cannot change in its natural way (during the workflow).
REFERENCES - allows you to create links between tables using an external key.
EVENT - gives the right to create/modify/delete tasks for the scheduler
TRIGGER - allows you to create/modify/delete 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 - allows you to create temporary tables for session time.
CREATE VIEW - 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. For example, in this view you can collect certain grouped data from three tables (by SELECT operator) and in fact the collected data will only refer to the data of these 3 tables and will also be a union that does not need its own table.
SHOW VIEW - allows you to check what kind of query (which data consists of) a certain view is created by CREATE VIEW
CREATE ROUTINE - allows you to create a procedure that is a set of SQL commands.
ALTER ROUTINE - allows you to modify a procedure created by CREATE ROUTINE.
EXECUTE - allows you to call prepared procedures.
FILE - 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.
CREATE TABLESPACE (admin) - allows you to create/modify/delete tablespaces. This space itself is logical and is not related to the database structure or schema. It declares the location of database objects on physical media and is used to optimize the database system.
CREATE USER (admin) - allows you to create/change/rename/delete database users.
PROCESS (admin) - allows access to information about the threads (processes) running on the server.
PROXY (admin) - allows logging in as another user. It is used by the administrator to check/check the access rights of the desired user.
RELOAD (admin) - allows using FLUSH operator that cleans MySQL cache
REPLICATION CLIENT (admin) - allows you to SHOW MASTER STATUS, SHOW SLAVE STATUS and SHOW BINARY LOG.
REPLICATION SLAVE (admin) - this privilege is necessary for users of the slave database server so that the server can connect to the master server as a slave. Without this privilege, slave servers will not be able to request database and table updates from the master server.
SHOW DATABASES (admin) - allows the SHOW DATABASES statement. Users who do not have this privilege will only be able to see the databases to which they have any rights when executing this statement.
SHUTDOWN (admin) - this privilege allows you to execute SHUTDOWN statement, which shuts down MySQL server.
SUPER (admin) - privilege that gives the right for many operations:
- allows to terminate processes, which belong to other users
- change global system variables
- turn logging on and off
- make updates even if the reading permission is set for system variables
- start/stop replication on slave servers
ALL (admin) - the user obtaining this privilege has all rights within the privilege level (possible privileges in principle, according to the privilege context) automatically assigned. Only the GRANT OPTION privilege is not assigned in this case.
** Assigning privileges to MySQL users in the control panel**
On the main page of cPanel, go to "Databases → MySQL Databases ":
All operations with MySQL databases, database users and their permissions are done in this menu.
If we don't have a base or user, we create them in the appropriate sections of the page:
Create a user:
The Current Users section will be updated:
To assign permissions to a particular user to a particular database, we need to find the Add User to Database section on the page and add the desired user to the desired database:
After adding a user to the database, a dialog box will open for assigning privileges:
The "All Rights" button is equivalent to the ALL privilege described at the beginning of the guide, and will assign all possible rights to a user in the context of the user's membership in a particular user group at the MySQL server-wide level.
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:
Done.