Database Security

The hotel with one master key 🏨

In a well-run hotel, a guest's key card opens one room and the gym. A cleaner's card opens the rooms of one floor. The master key sits in the manager's safe. If a guest's card is stolen, the thief gets one room, not the whole building.

Database security works the same way. Attackers rarely break the database itself; they come in through the application: a query that pastes user input into SQL, a password committed to a repository, a backup left in a public bucket. Once inside, they can do whatever the application's account is allowed to do. So most database security is about two things: keeping attackers from running their own SQL, and limiting what a compromised application account can do.

What you'll learn

• How SQL injection happens and why parameterized queries stop it; what to do for column names
• Least privilege with roles, GRANT and REVOKE, and how privileges are inherited
• Protecting credentials, and encryption in transit and at rest
• Audit trails that record who changed what, and that attackers cannot quietly erase

⚠️ Ethics first: these techniques are for defending systems and testing your own databases and applications, or ones you have written permission to test. Trying injection or stolen credentials against systems you do not own is a crime. Every example in this lesson runs against a small sample database the program creates itself.

SQL injection: when data becomes code

Here is a search the way it is too often written:

sql = "SELECT id, item FROM orders WHERE customer = '" + name + "'"

With name = "amal" the database receives ... WHERE customer = 'amal'. With name = "x' OR '1'='1" it receives ... WHERE customer = 'x' OR '1'='1'. The quote in the input closed the string, and OR '1'='1' became part of the SQL code: the condition is now true for every row, and every customer's orders are returned. Other inputs can change data or read other tables. Even an honest customer called D'Arcy breaks this query with a syntax error.

The fix is to keep code and data apart with a parameterized query (a prepared statement): the SQL text contains a placeholder, and the value is sent separately, so it can never be parsed as SQL.

db.execute("SELECT id, item FROM orders WHERE customer = ?", (name,))       # SQLite, Python

PostgreSQL: WHERE customer = $1        psycopg: WHERE customer = %s  (a placeholder, NOT % formatting)
JDBC:       WHERE customer = ?         named:   WHERE customer = :name

Rules from the OWASP SQL Injection Prevention Cheat Sheet, in its order of preference: use prepared statements with parameters; stored procedures are safe only if they do not build SQL from strings themselves; use allow-list validation where parameters cannot be used; escaping user input yourself is strongly discouraged.

Placeholders work for values only. Table and column names are identifiers and sort directions (ASC, DESC) are keywords: none of them can be bound. When the user picks a sort column, map their choice through a fixed allow-list to a column name you wrote yourself, and reject everything else. ORMs use parameters for you, but their "raw SQL" escape hatches are injectable in exactly the same way.

Least privilege: roles, GRANT and REVOKE

Least privilege means every account gets exactly the permissions its job needs, and nothing more. If the application only reads products and writes orders, an attacker who steals its credentials (or finds an injection hole) can only read products and write orders: no DROP TABLE, no reading the staff table, no creating new users.

In PostgreSQL, users and groups are both roles. A common pattern is a group role that holds the permissions and a login role for the application that joins it:

CREATE ROLE shop_rw NOLOGIN;                         -- the permissions live here
GRANT CONNECT ON DATABASE shop TO shop_rw;
GRANT USAGE ON SCHEMA app TO shop_rw;
GRANT SELECT ON app.products TO shop_rw;
GRANT SELECT, INSERT, UPDATE ON app.orders, app.order_lines TO shop_rw;

CREATE ROLE shop_api LOGIN;                          -- the app logs in as this; password set separately
GRANT shop_rw TO shop_api;                           -- membership: shop_api inherits shop_rw's privileges

REVOKE ALL ON DATABASE shop FROM PUBLIC;             -- PUBLIC means "every role"

How privileges add up:

• A role has the privileges granted to it, plus those of every role it is a member of (and their roles in turn), plus those granted to PUBLIC, which every role belongs to.
• Membership flows one way: shop_api gains shop_rw's privileges, never the reverse.
• REVOKE removes only a direct grant. If the same privilege still arrives through a group role or PUBLIC, the role keeps it, a classic surprise when cleaning up access.
• PostgreSQL 15 stopped letting PUBLIC create objects in the public schema by default; on older versions, revoke that yourself.

Practical rules:

• Separate accounts per job: the application (data changes only), migrations (schema changes, used only by deploys), read-only reporting, and personal accounts for humans. Never run the application as a superuser such as postgres.
• Expose a subset of columns through a view, and in PostgreSQL restrict rows with row-level security (ALTER TABLE ... ENABLE ROW LEVEL SECURITY and CREATE POLICY).
• Check what a role can really do: PostgreSQL's has_table_privilege('shop_api', 'app.orders', 'DELETE') answers the same question as the next lab.

Credentials and encryption

Credentials are the keys to the building. Keep them out of source code and out of the repository: load them from the environment or, better, from a secrets manager, with a different account and password for every environment (development, staging, production). Rotate them when people leave or a leak is suspected, and never print a connection string in logs. Use the strongest authentication the server offers: PostgreSQL uses scram-sha-256 password hashing by default since version 14, and a database should not accept connections from the whole internet at all, only from the application's network.

Encryption in transit (TLS) stops anyone on the network path from reading queries, results and passwords. Turning it on is not enough; the client must also verify the server, or an attacker in the middle can present their own certificate. PostgreSQL's client library calls this sslmode: the default prefer silently falls back to plain text, require encrypts without checking who answers (unless a root certificate file is present, in which case it verifies like verify-ca), and verify-full checks the certificate and the host name. On the server side, make encryption mandatory: hostssl lines in PostgreSQL's pg_hba.conf match only encrypted connections, so let remote clients in through hostssl lines only; MySQL has require_secure_transport.

Encryption at rest protects data files, disks and backups if they are stolen or mislaid: volume or disk encryption, and features such as Transparent Data Encryption in SQL Server and Oracle, or InnoDB's data-at-rest encryption in MySQL. Know its limit: the running database decrypts data for anyone who can log in, so at-rest encryption does nothing against a compromised application account. That is the job of least privilege. For a few especially sensitive fields you can add application-level encryption, with keys kept outside the database.

Passwords of your users are never stored, only hashed, with a slow, salted password-hashing function (OWASP recommends Argon2id, then scrypt or bcrypt), never a fast hash like plain SHA-256 and never reversible encryption.

Audit trails

When something goes wrong, the first questions are who changed what, and when? An audit trail answers them. Typical sources:

• Database logs: connections, failed logins, schema changes. In PostgreSQL, settings such as log_connections and the pgAudit extension record sessions and statements; managed cloud databases offer audit logging as a setting.
• Change history: a trigger that copies the old and new values of sensitive rows into an audit table, together with the acting user.
• Application events: "user 42 exported the customer list" is meaningful in the app, not in SQL.

UPDATE accounts SET balance = 70 WHERE id = 1
        │
        └─ AFTER UPDATE trigger ──▶ audit_log: UPDATE account 1: 100 -> 70 by amal, at 10:42

An audit trail is only worth something if an attacker cannot quietly edit it. The application account must not be allowed to UPDATE or DELETE audit rows (privileges again), the table can reject changes outright, and important logs are copied to a separate system the database accounts cannot reach. Remember the limit of triggers: anyone with rights to change the schema can drop them, which is one more reason the application must not hold those rights. Finally, record what you need and no more: an audit log full of passwords or card numbers becomes a target itself.

Recap and what to practice next

• Attackers usually reach the database through the application, so the goals are: no attacker-controlled SQL, and an application account that can do as little as possible.
• Parameterized queries keep data out of the SQL code; allow-lists handle identifiers such as sort columns. Never build SQL by pasting input, not even in an ORM's raw queries.
• Least privilege: separate roles per job, privileges granted to group roles, no superuser for the app. A role inherits from the roles it belongs to and from PUBLIC, and REVOKE removes only the direct grant.
• Keep credentials in a secrets manager, encrypt in transit with server verification (verify-full) and at rest (including backups), and hash users' passwords with a slow, salted function.
• Audit trails record who changed what; protect them with privileges, append-only rules and a copy outside the database.

Practice next: in the first lab, add a direction option (asc or desc) through the same allow-list idea. In the privilege lab, add support for REVOKE group FROM role. Finally, revisit your backup plan with these eyes: every backup and replica holds the same data as the database, so it needs the same protection: encrypted, reachable only by the accounts that need it, and kept where a compromised application account cannot delete it.