Least Privilege Database Permissions

Defining the Application Scenario

To apply PoLP effectively, you must first understand the application's requirements. Let's consider a hypothetical e-commerce application. This application interacts with a database named ecom_db which contains several tables, but for our user, we're concerned with three:

• products: Contains product information like name, description, and price.
• customers: Stores customer profile data like name, email, and shipping address.
• orders: Holds records of customer purchases, linking customers to products.

The application user, which we will name app_user, needs to perform the following actions:

• Read product information to display on the website (SELECT on products).
• Read a customer's own profile information (SELECT on customers).
• Allow a customer to update their own profile (UPDATE on customers).
• Create new orders when a purchase is made (INSERT on orders).
• Read a customer's order history (SELECT on orders).

Crucially, this app_user should not have permission to:

• Change product prices (UPDATE on products).
• Delete customers or products (DELETE).
• Modify the database structure (ALTER, DROP).
• Access any other tables (e.g., a hypothetical employees table).

This detailed list of "cans" and "cannots" is our blueprint for crafting the GRANT statements.

Step 1: Creating the Application User

Before we can grant permissions, we need a user account. The syntax for creating a user varies slightly between database systems, but the concept is the same. The two most common open-source databases are MySQL and PostgreSQL.

In MySQL, user accounts are tied to a host, which specifies where the user can connect from. This is a powerful, built-in network access control feature. For example, 'app_user'@'localhost' can only connect from the server itself, while 'app_user'@'%' can connect from any IP address. For security, it's always best to be as specific as possible.

In PostgreSQL, the concept is called a "role". A role can be a user, a group, or both. A role that can log in is effectively a user.

For our scenario, let's assume we are creating a local application user. The commands would be:

MySQL:

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

PostgreSQL:

CREATE ROLE app_user WITH LOGIN PASSWORD 'a_very_secure_password';

(or simply CREATE USER app_user WITH PASSWORD 'a_very_secure_password';)

Step 2: Granting Minimal Privileges

With our app_user created, we can now execute the core task of this lesson: granting the precise permissions identified in our scenario. We will build our GRANT statements piece by piece.

Based on our blueprint:

1. Allow reading from the products table.
2. Allow reading and creating orders.
3. Allow reading and updating customers.

This translates to the following SQL commands:

For MySQL:

-- Grant permissions on the products table
GRANT SELECT ON ecom_db.products TO 'app_user'@'localhost';

-- Grant permissions on the orders table
GRANT SELECT, INSERT ON ecom_db.orders TO 'app_user'@'localhost';

-- Grant permissions on the customers table
GRANT SELECT, UPDATE ON ecom_db.customers TO 'app_user'@'localhost';

For PostgreSQL, there's an additional layer of security. You must also grant permission to connect to the database itself and to use the schema where the tables reside. This is an excellent example of granular control.

-- 1. Grant permission to connect to the database
GRANT CONNECT ON DATABASE ecom_db TO app_user;

-- 2. Grant permission to use the 'public' schema (or whichever schema tables are in)
GRANT USAGE ON SCHEMA public TO app_user;

-- 3. Grant the specific table-level privileges
GRANT SELECT ON products TO app_user;
GRANT SELECT, INSERT ON orders TO app_user;
GRANT SELECT, UPDATE ON customers TO app_user;

The following readings provide complete, real-world examples of setting up minimally-privileged roles for different use cases.

Notice the stark contrast between these surgical GRANT statements and the dangerous GRANT ALL PRIVILEGES ON *.*. An attacker who compromises our app_user can only perform the specific actions we've allowed, drastically limiting the potential damage.

Step 3: Verifying the Configuration

As a security analyst, you know that configuration must always be followed by verification. How do we confirm that our app_user has the correct permissions and, just as importantly, lacks the incorrect ones?

We can do this in two ways:

1. Audit the grants: Use SHOW GRANTS FOR 'app_user'@'localhost'; (in MySQL) or \z table_name (in PostgreSQL) to inspect the applied permissions.
2. Test the user: Log in as the app_user and attempt to perform allowed and disallowed actions. This is a practical, hands-on way to confirm your security posture.

The following video demonstrates this verification process perfectly. The instructor creates a user, grants SELECT, and then logs in as that user to show that SELECT works but INSERT fails. This is exactly the kind of testing you would perform.

This testing loop—configure, then verify—is a critical habit for ensuring security policies are correctly implemented.

Conclusion

In this lesson, you moved from theory to practice, applying the Principle of Least Privilege to a realistic database security task. You learned to translate application requirements into a specific set of minimal permissions and implement them using the CREATE USER and GRANT commands.

Key Takeaways:

• Define Requirements First: Before writing any SQL, clearly define what actions a user must be able to perform.
• Create a Dedicated User: Always create a separate, unprivileged user for your applications. Never use the root or postgres superuser.
• Grant Surgically: Assign permissions on the most specific objects possible (e.g., a table, not the whole database) and grant only the necessary privileges (SELECT, INSERT, etc.).
• Verify Everything: After configuring permissions, always test them by logging in as the new user and attempting both allowed and disallowed actions.
• Database Nuances Matter: Be aware of system-specific requirements, such as PostgreSQL's need for CONNECT and USAGE grants.

You are now equipped to create secure user accounts that significantly reduce your application's attack surface. In the next lesson, we will flip the script. Instead of creating a secure configuration, you will learn how to "Audit a database configuration to identify and document overly permissive access settings," putting on your security analyst hat to find and flag vulnerabilities in an existing system.