Creating a user and managing access privileges 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 uses a user system to control access to databases. An administrator or authorized user can grant other users full or restricted access to specific databases and tables.

MySQL privileges

Database and table-level privileges:

• CREATE — create databases and tables.
• DROP — delete databases and tables.
• INSERT — add rows to tables.
• UPDATE — modify row data. Not to be confused with ALTER, which changes table structure.
• DELETE — remove rows from tables.
• ALTER — modify table structure; requires CREATE and INSERT privileges.
• SELECT — read data from tables.
• GRANT OPTION — grant or revoke privileges for other users (limited to privileges the user already holds).
• LOCK TABLES — lock tables during write operations.
• REFERENCES — create foreign key relationships between tables.
• EVENT — create, modify, and delete scheduler events.
• TRIGGER — create, modify, and delete triggers for DELETE, UPDATE, and INSERT operations.
• INDEX — create and drop indexes.
• CREATE TEMPORARY TABLES — create temporary tables for the duration of a session.
• CREATE VIEW — create views that aggregate data from other tables.
• SHOW VIEW — view the SQL query behind a view definition.
• CREATE ROUTINE — create stored procedures.
• ALTER ROUTINE — modify stored procedures.
• EXECUTE — run stored procedures.
• FILE — access server files available to MySQL and create files in MySQL-writable directories.

Administrative privileges:

• CREATE TABLESPACE — create, modify, and delete tablespaces.
• CREATE USER — create, modify, and delete MySQL users.
• PROCESS — view information about active server threads.
• PROXY — log in as another user to check access rights.
• RELOAD — run the FLUSH command to clear the MySQL cache.
• REPLICATION CLIENT — run SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW BINARY LOG.
• REPLICATION SLAVE — allow replica servers to receive updates from the primary server.
• SHOW DATABASES — list all databases on the server.
• SHUTDOWN — shut down the MySQL server.
• SUPER — extended administrative rights: terminate other users' processes, modify global system variables, manage logging and replication.
• ALL — all available privileges except GRANT OPTION.

Creating a user

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

By default, the new user has no privileges on any database.

Granting privileges

Grant all privileges on all databases and tables:

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

Grant specific privileges on a specific database or table:

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

Use * in place of a database or table name to apply the privileges to all.

After making changes, reload the privilege tables:

FLUSH PRIVILEGES;

Revoking privileges

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

Deleting a user

DROP USER 'demo'@'localhost';

Verifying the account

Exit MySQL:

quit

Then log back in as the new user:

mysql -u [username] -p