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 database system listed below with a description. The latter privileges are admin-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.
Create a new user from the MySQL console:
``.shell
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
The user **newuser** will not have rights to do anything with the databases
Let's give the user access to the information he needs.
``.shell
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
The asterisks in this command specify the database and table, respectively, that the user will have access to. Specifically this command allows the user to read, edit, and perform any action on all databases and tables.
Once you have finished setting up access rights for new users, make sure you have updated all access rights:
``.shell
FLUSH PRIVILEGES;
The changes will now take effect.
You can use the following scheme to assign permissions to a particular user:
``.shell
GRANT [permission type] ON [database name].[table name] TO '[username]'@'localhost'';
If you want to grant access to any database or any table, put an asterisk (*) instead of the database or table name.
After you change access rights, you must use the Flush Privileges command.
Deprivation of access rights is almost identical to their intended purpose:
``.shell
REVOKE [permission type] ON [database name].[table name] FROM '[username]'@'localhost'';
Similar to using the DROP command to delete a database, you can use this command to delete a user as well.
``.shell
DROP USER 'demo'@'localhost';
To test the account of the created user, log out with the command:
``.shell
quit
And log in again by typing the following command in the terminal:
``.shell
mysql -u [username]-p