Linux Notepad

Create MariaDB/MySQL Database and User

This guide explains how to properly create a database and user in MariaDB/MySQL following security best practices.
We'll focus on implementing the principle of least privilege by creating specific users with limited permissions for individual databases.

This setup was tested on Debian 11 (Bullseye), but it should work on other Debian-based systems, like Ubuntu or Raspberry Pi OS.
These instructions were tested on MariaDB 10.11 and MySQL 8.0, but they should work on other versions with minimal modifications.

Important Security Note

Using the root user for applications is dangerous because:

  • SQL injection vulnerabilities could give attackers access to all databases
  • Leaked credentials would compromise all databases
  • It violates the principle of least privilege

Instead, create dedicated users with minimal required permissions for each application.

Database and User Creation

1. Connect to MySQL Server

sudo mysql -u root -p

Note: You'll be prompted for:

  1. System user password (for sudo)
  2. MySQL root password (press ENTER if not set)

2. Create Database

CREATE DATABASE `new_mysql_database`;

3. Create User

CREATE USER 'new_mysql_user'@'localhost' IDENTIFIED BY 'new_mysql_password';

4. Grant Permissions

For single database access (recommended):

GRANT ALL PRIVILEGES ON new_mysql_database.* TO 'new_mysql_user'@'localhost';

For read-only access:

GRANT USAGE ON *.* TO 'new_mysql_user'@'localhost';

For all databases (use with caution):

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

5. Apply Changes

FLUSH PRIVILEGES;

6. Verify User Permissions

SHOW GRANTS FOR 'new_mysql_user'@'localhost';

Security Best Practices

  1. Use strong, unique passwords for each database user
  2. Grant only necessary permissions
  3. Restrict database access to specific hosts when possible
  4. Never use root credentials in applications
  5. Regularly audit user permissions

Variables to Replace

  • new_mysql_database: Your database name
  • new_mysql_user: Your username
  • new_mysql_password: A strong password

Example with real values:

CREATE DATABASE `myapp_db`;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'StrongPass123!';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

Additional Notes

  • Document all created users and their permissions
  • Consider using environment variables for credentials
  • Regular security audits are recommended
  • Different applications should use different database users

The process is reversible - you can always modify permissions or remove users/databases if needed.

Resources

Back to homepage