Managing Databases and Users

Introduction

In professional system administration, using the root user for everyday application connections is a major security risk. If an attacker compromises an application using the root account, they gain full control over every database on the server.

Best practice dictates that every application should have its own dedicated user with permissions restricted to only its own dedicated database.

This is the database equivalent of separating application identities at the operating-system level. You want access control to reflect purpose, not just convenience.

This is the database version of the least-privilege principle you already saw in Linux user management. If one application account is compromised, restricted privileges reduce the blast radius.

In this lab, you will secure your environment by following these standard procedures:

Create a new database for a hypothetical application.
Create a specific user account for that application.
Grant the user permission only for their specific database.
Verify the setup by logging in as the new user.

Create a New Database

First, you need to log in to the MySQL server with administrative privileges to create the new database.

Open your terminal and run the following command to access the MySQL shell:

sudo mysql

Note: This uses the sudo command to authenticate as root via the system socket. If prompted for a password you don't know, try mysql -u root -p.

Once you see the mysql> prompt, you are ready to issue SQL commands.

At this stage you are connected as an administrative database user because creating databases is a privileged operation. Regular application accounts typically do not have this level of control.

Create a new database named app_db. This will be the container for our application's data.

CREATE DATABASE app_db;

If successful, MySQL will reply with Query OK. You can verify the database exists by listing all databases:

SHOW DATABASES;

You should see app_db in the list.

Create a Dedicated User

Now that we have a database, we need a user to manage it. We will create a user named app_user.

In the same MySQL shell, execute the following command:

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

Breaking down the command:

'app_user': The username.
'@'localhost': Restricts the user to connecting only from this server (a security measure).
IDENTIFIED BY 'password': Sets the password to "password".

At this point, the user exists but has no permissions. They can log in, but they cannot read or write data.

This is a useful security model to understand: identity and privilege are separate. Creating an account does not automatically grant the ability to use every database object.

You can verify the user exists by checking the system user table:

SELECT User, Host FROM mysql.user WHERE User = 'app_user';

Grant Permissions

Now we must explicitly give app_user permission to use app_db.

We want to grant ALL rights (reading, writing, modifying tables) on app_db to our user.

Run this command in the MySQL shell:

GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'localhost';

Breaking down the syntax:

GRANT ALL PRIVILEGES: Gives full access rights.
ON app_db.*: Applies these rights to the app_db database and * (all tables inside it).
TO 'app_user'@'localhost': Specifies who gets these rights.

The database.* pattern is important because privileges can be scoped. In MySQL/MariaDB, rights can be granted globally, per database, per table, and sometimes even more narrowly.

This ensures app_user is powerful within app_db, but powerless everywhere else.

To confirm the permissions were assigned correctly:

SHOW GRANTS FOR 'app_user'@'localhost';

Verify User Access

To ensure everything works as expected, we will log out of the root account and try to log in as the new user.

First, exit the root session:

EXIT;

Now, back in the Linux terminal, log in as app_user:

mysql -u app_user -p

Enter the password password when prompted.

Once logged in, check which databases you can see:

SHOW DATABASES;

What to look for:

You should see app_db and information_schema.
You should not see mysql or sys.

This confirms that your permission settings are working correctly: the user sees only what they are allowed to access.

That verification step is critical in access control. It is not enough to issue a GRANT statement; you should confirm that the resulting view of the system matches your intended least-privilege design.

Switch to your database to finish the lab:

USE app_db;
EXIT;

Summary

In this lab, you successfully implemented a secure database access model. You learned how to:

Create a new database (app_db).
Create a dedicated user (app_user).
Grant precise privileges using the GRANT command.
Verify that access controls are working as intended.

This "Least Privilege" model is a cornerstone of database security and should be used for every application you deploy.