Database Security

What threatens a database

A database usually holds an organization's most valuable data in one place: customer records, orders, payments, credentials. That makes it a target, and most incidents follow a small number of patterns:

No single control covers all of these. Database security is defense in depth applied to one system: limit who can connect, limit what each account can do, protect the data on the wire and on disk, and record what happens.

Accounts and authentication

Authentication answers "who is connecting?". The rule that makes everything else on this page possible is simple: one account per application and one per person.

• Per application. The web shop, the nightly reporting job and the warehouse system each get their own login. When one is compromised you can disable it without stopping the others, and the logs show which system did what.
• Per person. Administrators and analysts log in as themselves, not as a shared admin or sa account. A shared login makes auditing useless ("admin deleted the table" names nobody), and its password has to be changed every time someone leaves.
• Built-in superusers (postgres in PostgreSQL, root in MySQL, sa in SQL Server) are for emergencies and installation, not for daily work or applications. Disable or rename them where the product allows, and protect the rest with a long random password kept in a vault.

Prefer stronger login methods where the platform offers them: integrated authentication with the organization's directory (Windows authentication or Microsoft Entra ID for SQL Server and Azure SQL Database, Kerberos or LDAP (Lightweight Directory Access Protocol) for PostgreSQL), client certificates, or short-lived tokens from a cloud identity service. These give you central password policies, multi-factor authentication for people, and one place to disable a leaver. Application passwords belong in a secrets manager or environment variables, never in the source code.

Roles and privileges

Authorization answers "what may this account do?". A privilege is the right to perform one kind of action on one object: SELECT on products, INSERT on orders, CREATE in a schema. Instead of granting privileges to each person, you collect them in a database role: a named set of privileges that is granted to accounts (or to other roles). A new analyst then needs one grant, membership of the reporting role, instead of twenty. This is role-based access control inside the database.

GRANT and REVOKE

GRANT gives a privilege, or a role, to a role or account; REVOKE takes it away. Both are part of the standard SQL language (sometimes called the data control language, DCL), and the core form is the same everywhere:

GRANT  privilege [, privilege ...] ON object TO   role;
REVOKE privilege [, privilege ...] ON object FROM role;

In PostgreSQL, users and groups are both roles; a role that may log in is a user. The following (not run, PostgreSQL) creates a read-only role for reporting and gives it to an analyst:

-- PostgreSQL: a read-only role for reports
CREATE ROLE reporting NOLOGIN;              -- a group: nobody logs in as "reporting"
GRANT CONNECT ON DATABASE shop TO reporting;
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

-- Tables created later by the role running this line are readable too
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting;

-- A personal login for Mei, who gets the role
CREATE ROLE mei LOGIN;                      -- set her password with \password in psql
GRANT reporting TO mei;

-- Mei moves to another team
REVOKE reporting FROM mei;

GRANT SELECT ON ALL TABLES covers only the tables that exist at that moment, which is why the ALTER DEFAULT PRIVILEGES line is there. PostgreSQL 14 and later also offer a predefined role, pg_read_all_data, for read access to everything.

Least privilege for the web application

The web shop needs to read products, create customers and orders, change an order's status and lower the stock. It never needs to delete customers, drop tables or read other applications' data. So it gets exactly that (not run, PostgreSQL):

-- PostgreSQL: the account the web shop connects with
CREATE ROLE shop_web LOGIN;                 -- password from the secrets manager
GRANT CONNECT ON DATABASE shop TO shop_web;
GRANT USAGE ON SCHEMA public TO shop_web;

GRANT SELECT ON products TO shop_web;
GRANT UPDATE (stock) ON products TO shop_web;   -- one column only
GRANT SELECT, INSERT, UPDATE ON customers, orders, order_items TO shop_web;
-- No DELETE, no CREATE/ALTER/DROP, no access to the employees table.

-- By default every role may connect to a new database; close that door
REVOKE CONNECT ON DATABASE shop FROM PUBLIC;

PUBLIC is a keyword meaning "every role". If the shop is ever hit by SQL injection or a stolen application password, the damage is now limited to what this account can do: no DROP TABLE, no reading the employees table, no deleting the order history. Schema changes run through a separate migration account that is used only during deployments.

If the tables use serial columns, also run GRANT USAGE ON SEQUENCE … TO shop_web; for their sequences, or inserts fail; identity columns do not need this.

How other systems do it

SQL injection and parameterized queries

SQL injection happens when an application builds an SQL statement by pasting user input into the SQL text. A crafted input such as x' OR '1'='1 closes the string early and adds its own SQL. The demonstration on the Web Application Security page shows the attack and the fix side by side; this section focuses on using the fix correctly.

A parameterized query (also called a prepared statement or bind variables) sends the SQL text with placeholders, and the values separately. The database parses the statement first and only then plugs in the values, so a value can never become SQL. In Python's sqlite3 module the placeholder is ? or a name like :city:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute(
    "CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, first_name TEXT, city TEXT)"
)
conn.executemany(
    "INSERT INTO customers (first_name, city) VALUES (?, ?)",
    [("Sanne", "Utrecht"), ("Kwame", "Rotterdam"), ("Tom", "Utrecht")],
)


def customers_in(city):
    # ? is a placeholder: the driver sends city as a value, never as SQL
    sql = "SELECT first_name FROM customers WHERE city = ? ORDER BY first_name"
    return [row[0] for row in conn.execute(sql, (city,))]


print(customers_in("Utrecht"))
print(customers_in("Utrecht' OR '1'='1"))

# Named placeholders read better when a query has several values
sql = """SELECT first_name FROM customers
         WHERE city = :city AND customer_id > :after ORDER BY customer_id"""
print(conn.execute(sql, {"city": "Utrecht", "after": 1}).fetchall())

# Placeholders hold values only, never table or column names.
# For a user-chosen sort order, pick from fixed, complete queries.
SORT_QUERIES = {
    "name": "SELECT first_name FROM customers ORDER BY first_name",
    "city": "SELECT first_name FROM customers ORDER BY city, first_name",
}


def sorted_customers(sort_key):
    sql = SORT_QUERIES.get(sort_key)
    if sql is None:
        raise ValueError(f"unknown sort order: {sort_key!r}")
    return [row[0] for row in conn.execute(sql)]


print(sorted_customers("city"))
try:
    sorted_customers("city; DROP TABLE customers")
except ValueError as error:
    print("Rejected:", error)

['Sanne', 'Tom']
[]
[('Tom',)]
['Kwame', 'Sanne', 'Tom']
Rejected: unknown sort order: 'city; DROP TABLE customers'

The crafted input in the second call is treated as a city name; no city is literally called Utrecht' OR '1'='1, so the result is empty. Every database driver has the same mechanism, only the placeholder differs: %s in the common PostgreSQL and MySQL drivers for Python, @name in .NET for SQL Server, $1 in PostgreSQL's own PREPARE statement. Object-relational mappers (ORMs) use parameters too, as long as you do not hand them raw SQL built with string formatting.

Parameterized queries are the fix; least privilege is the safety net. An injection through the shop_web account from the previous section still cannot drop tables or read the employees table.

Encryption in transit and at rest

In transit: TLS

Every connection between an application and a database carries passwords and data. Encrypt it with TLS (Transport Layer Security), and make the client verify the server's certificate so it cannot be redirected to an impostor:

• PostgreSQL: ssl = on on the server, only hostssl lines (no plain host lines) in pg_hba.conf, so unencrypted logins are refused, and sslmode=verify-full in the client's connection settings.
• MySQL: require_secure_transport = ON on the server; clients use --ssl-mode=VERIFY_IDENTITY (or the driver's equivalent).
• SQL Server: Encrypt=True in the connection string, without TrustServerCertificate=True, which would skip the certificate check.

"Inside our own network" is not a reason to skip TLS: internal networks are exactly where an attacker who got a foothold listens next.

At rest: TDE and its limits

Encryption at rest protects data files, logs and backups if the disk, the server or a backup copy is stolen. Transparent data encryption (TDE) does this inside the database engine: data pages are encrypted as they are written to disk and decrypted as they are read into memory. "Transparent" means applications and queries need no change. SQL Server, Azure SQL Database (where it is on by default for new databases) and Oracle offer TDE; MySQL's InnoDB has tablespace encryption; community PostgreSQL relies on disk or file system encryption instead. The encryption keys belong in a key management service or hardware security module, separate from the data; see Cloud Security and Compliance for how cloud providers handle keys.

Know what TDE does not do: anyone who can log in and run SELECT sees plaintext, because the engine decrypts for them. TDE protects against a stolen disk, not against a stolen password or SQL injection. For the most sensitive columns some systems add column-level or client-side encryption (SQL Server's Always Encrypted, for example), where even the database server never sees the plaintext.

Backups are copies of all your data

A database dump contains every customer, every order and every password hash, in a single file that is easy to copy. Treat backups with the same care as the live database: encrypt them, store them where only the backup process and a few administrators can read them, and log who restores them. A forgotten shop-backup.sql on a web server or in a public storage bucket is a classic cause of data breaches. The Backup, Recovery and Replication page covers how to make them.

Network exposure

A database server should never be reachable from the internet. Automated scanners continuously probe the default ports, such as 5432 (PostgreSQL), 3306 (MySQL and MariaDB), 1433 (SQL Server), 27017 (MongoDB) and 6379 (Redis), and try default and leaked passwords within hours of a server appearing.

• Put the database in a private network or subnet; only the application servers can reach it. See Cloud Networking for public and private subnets and security groups.
• Let the database listen only on the addresses it needs (listen_addresses in PostgreSQL, bind-address in MySQL) and allow only the application's addresses in a host or network firewall.
• Administrators connect through a VPN (virtual private network), a jump server or the cloud provider's private access service, not through a public database port.

Data masking and row-level security

Privileges work per table or column. Often you need something finer: a help desk employee should see that a customer has an email address, but not the whole address; a customer logged in to the shop should see their own orders and nobody else's.

Data masking

Data masking replaces sensitive values with a partly hidden or fictitious version for users who do not need the real ones. Dynamic masking happens when the data is read, and the stored data stays intact; SQL Server and Azure SQL Database offer it with a column setting such as MASKED WITH (FUNCTION = 'email()'). Static masking creates a permanently masked copy, for example a test database built from production data. The simplest form works everywhere: a view that shows masked columns, with users granted access to the view instead of the table.

CREATE VIEW customers_masked AS
SELECT customer_id,
       first_name,
       substr(last_name, 1, 1) || '.' AS last_name,
       substr(email, 1, 1) || '***' || substr(email, instr(email, '@')) AS email,
       country
FROM customers;

SELECT * FROM customers_masked WHERE country = 'NL' ORDER BY customer_id;

customer_id  first_name  last_name  email             country
-----------  ----------  ---------  ----------------  -------
1            Sanne       d.         s***@example.com  NL
2            Kwame       M.         k***@example.com  NL
3            Mei         C.         m***@example.com  NL
6            Priya       P.         p***@example.com  NL
7            Tom         J.         t***@example.com  NL

In a system with privileges, the help desk role gets SELECT on customers_masked and nothing on customers.

Dynamic masking hides values in results but is not a security boundary: a user who can run their own queries can often infer the real values with WHERE conditions. Combine it with least privilege.

Row-level security

Row-level security (RLS) lets the database filter rows per user automatically: every query on the table gets an invisible extra condition. Because the database enforces it, a forgotten WHERE clause in the application can no longer show one customer another customer's orders. PostgreSQL (not run here):

-- PostgreSQL: each session sees only the orders of its own customer
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_orders ON orders
  FOR SELECT
  TO shop_web
  USING (customer_id = current_setting('app.customer_id')::integer);

-- The application sets the customer after login, per session or transaction
SET app.customer_id = '3';
SELECT order_id, status FROM orders;   -- only Mei's orders: 104 and 109

Once RLS is enabled on a table, roles without a matching policy see no rows at all (fail closed). Superusers and roles with the BYPASSRLS attribute always bypass the policies. The table's owner bypasses them too, unless the table is set to FORCE ROW LEVEL SECURITY. Both are reasons the application should not connect as the owner or a superuser.

This policy covers SELECT only. Once RLS is on, an INSERT by shop_web is refused and an UPDATE changes no rows until you add policies for them (FOR INSERT … WITH CHECK, FOR UPDATE …), or a policy without FOR that covers all commands. The setting app.customer_id is chosen by the application, so RLS of this kind protects against application mistakes, not against an attacker who can run SQL as shop_web. SQL Server and Azure SQL Database offer the same idea with security policies and predicate functions.

Auditing and compliance

Auditing records who did what, and when: logins (successful and failed), privilege changes, schema changes, and reads or changes of sensitive tables. Audit records answer the questions you will be asked after an incident, and their existence discourages misuse by insiders. Each system has its own tooling: SQL Server Audit (also in Azure SQL Database), the pgaudit extension for PostgreSQL, and audit plugins for MySQL and MariaDB. Send the records to a central log or security information and event management (SIEM) system, where database administrators cannot quietly edit them.

For business data, applications often keep their own history with a trigger. The example below records every price change in the shop (SQLite, run on the sample shop). In PostgreSQL or SQL Server the trigger would also store current_user or SUSER_SNAME(), the account that made the change; SQLite has no accounts, so that column is missing here.

CREATE TABLE price_audit (
  audit_id    INTEGER PRIMARY KEY,
  product_id  INTEGER NOT NULL,
  old_price   INTEGER NOT NULL,
  new_price   INTEGER NOT NULL,
  changed_at  TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER products_price_audit
AFTER UPDATE OF price ON products
WHEN OLD.price <> NEW.price
BEGIN
  INSERT INTO price_audit (product_id, old_price, new_price)
  VALUES (OLD.product_id, OLD.price, NEW.price);
END;

UPDATE products SET price = 79 WHERE product_id = 4;
UPDATE products SET price = 22 WHERE product_id = 3;
UPDATE products SET stock = 50 WHERE product_id = 1;   -- not a price change

SELECT audit_id, product_id, old_price, new_price
FROM price_audit
ORDER BY audit_id;

audit_id  product_id  old_price  new_price
--------  ----------  ---------  ---------
1         4           89         79
2         3           25         22

changed_at is filled in automatically but left out of the SELECT, because its value depends on the clock. The stock update does not appear: the trigger fires only for UPDATE OF price, and only when the price really changed.

Personal data and the GDPR

Most business databases hold personal data, so the General Data Protection Regulation (GDPR) applies in the European Union. Several of its principles land directly on the database:

• Data minimization: store only the columns you need. A column you never collect cannot leak.
• Security of processing: the GDPR itself names encryption and pseudonymization as examples of appropriate measures (Article 32), along with keeping data confidential and available, restoring it in time after an incident, and regularly testing the measures; access control and auditing are how a database delivers that.
• Storage limitation and erasure: data must be deleted when it is no longer needed or when a person exercises their right to erasure, including in copies such as test databases. Backups are usually handled by letting them expire on schedule rather than editing them.
• Breach notification: if personal data leaks, the data protection authority must normally be told within 72 hours. Audit records are what let you say which data was affected.