Creating a new user and setting up access rights in MySQL

When working with a dedicated server or a VPS, one of the key tasks is organizing data storage and management. MySQL is a database management system that allows you to structure information efficiently and control user access. It is suitable for both small projects and larger-scale applications, providing security, reliability, and ease of use.

MySQL Databases

MySQL databases are entities for storing information in the form of tables. To prevent data from other databases from being accessible to every server user, MySQL uses a system of user accounts. Access to a database can be granted by an administrator or an authorized user, with permissions that can be full or limited.

All data is stored in logically related tables, which are accessed using SQL queries. MySQL is open-source and free to use. In addition, it is fast, reliable, and simple to work with, making it a good choice for most medium-sized projects.

MySQL User Privileges

MySQL provides a variety of privileges that define what actions a user can perform on databases and tables. Below are the main privileges:

CREATE — allows creating new databases and tables.
DROP — allows deleting databases or tables.
INSERT — allows adding rows to tables.
UPDATE — allows modifying the contents of table rows. Do not confuse it with ALTER, which changes the table structure (e.g., number or type of columns).
DELETE — allows deleting rows from a table.
ALTER — allows modifying table structures; requires CREATE and INSERT privileges.
SELECT — allows reading tables and querying data based on columns and conditions.
GRANT OPTION — allows assigning or revoking privileges for other users, but only those that the user already has.
LOCK TABLES — locks a table during modifications to prevent changes by other processes.
REFERENCES — allows creating relationships between tables using foreign keys.
EVENT — allows creating, modifying, or deleting scheduled events.
TRIGGER — allows creating, modifying, and deleting triggers that execute on DELETE, UPDATE, or INSERT operations.
INDEX — allows creating and dropping indexes to speed up data searches.
CREATE TEMPORARY TABLES — allows creating temporary tables for the session.
CREATE VIEW — allows creating table views that combine data from other tables without creating a separate physical table.
SHOW VIEW — allows viewing the SQL query behind a view.
CREATE ROUTINE — allows creating stored procedures, which are predefined sets of SQL commands.
ALTER ROUTINE — allows modifying stored procedures.
EXECUTE — allows executing stored procedures.
FILE — provides access to files on the server that MySQL can access and allows creating files in directories with write permissions.

Administrative-Level Privileges:

CREATE TABLESPACE (admin) — allows creating, modifying, and deleting logical tablespaces for optimized data storage.
CREATE USER (admin) — allows creating, modifying, and deleting MySQL users.
PROCESS (admin) — grants access to information about running server threads.
PROXY (admin) — allows logging in as another user for access testing.
RELOAD (admin) — allows using the FLUSH command to clear MySQL caches.
REPLICATION CLIENT (admin) — allows executing SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW BINARY LOG.
REPLICATION SLAVE (admin) — required for slave servers to receive updates from the master server.
SHOW DATABASES (admin) — allows viewing all databases on the server.
SHUTDOWN (admin) — allows shutting down the MySQL server.
SUPER (admin) — grants various rights, including terminating other users’ processes, changing global system variables, and controlling logging and replication.
ALL (admin) — grants all available privileges except GRANT OPTION.

Creating and Managing MySQL Users

Creating a new user:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

By default, the user newuser has no privileges on any databases.

Granting all privileges to a user:

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

The asterisks (*) indicate all databases and tables. This command allows the user to fully manage all databases and tables.

After modifying privileges, update them:

FLUSH PRIVILEGES;

Granting specific privileges:

GRANT [privilege type] ON [database name].[table name] TO '[username]'@'localhost';

Use an asterisk (*) for all databases or tables.

Revoking user privileges:

REVOKE [privilege type] ON [database name].[table name] FROM '[username]'@'localhost';

Deleting a user:

DROP USER 'demo'@'localhost';

Testing a user account: exit MySQL:

quit

Then log in again with the new user:

mysql -u [username] -p