MySQL databases* are an entity for storing information in the form of tables. So that other 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 the 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 - opposite to INSERT - allows you to delete rows from a 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 those rights that the assignor himself has.
LOCK TABLES - locks the table for the time it is being 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 by an external key.
EVENT - allows you 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 the 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, defined with the help of CREATE VIEW
CREATE ROUTINE - allows you to create a procedure, which 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 the 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 accessing the information about the threads (processes) executing on the server.
PROXY (admin) - allows logging in as another user. It is used by an administrator for checking/debugging access rights of a required user.
RELOAD (admin) - allows you to use FLUSH operator, which clears 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 updates to databases and tables from the master server.
SHOW DATABASES (admin) - allows the SHOW DATABASES statement. Users who do not have this privilege can only see the databases to which they have any rights.
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 on/off the logging
- 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 for MySQL users in ISPmanager5**
On the ISPmanager main page, go to "Tools → Databases ".
Next, on the database management interface that opens, you need to create a new database by clicking the Create button.
If the database is already created then select it and click on Users to go to the database user management interface
It should be noted that when you create a new database, it will be possible to immediately create a new user to be assigned to that database. Or you can select an existing user who will also be automatically assigned to that database.
The Manage Database interface will show a list of all users in the current ISPmanager account. To edit permissions, highlight the specific user to whom we want to assign permissions and click "Edit ".
After that, we will see all the permissions that can be assigned to that user:
By default, all permissions are assigned when you create a user and a database.
This guide for the updated ISPmanager5 control panel design, available here.