Database Security in Applications

Security spans every layer

Database security is the protection of data against accidental or intentional loss, destruction, or misuse. It cannot be solved in one place — a perfectly secured database is still exposed if the network is breached or an authorized user abuses their privileges — so it must be enforced across the database, network, operating system, physical facilities, and people (“defense in depth”).

The five categories of threats

A comprehensive security plan must address all five:

1. Accidental losses — human error, hardware/software failure. *Countered by* operating procedures, authorization, maintenance schedules.
2. Theft and fraud — intentional unauthorized access. *Countered by* physical security, firewalls, encryption, access controls.
3. Loss of privacy or confidentiality — *privacy* = individuals' personal data exposed (SSNs, medical records); *confidentiality* = strategic organizational data exposed (trade secrets, forecasts). *Countered by* access controls, views, legal compliance.
4. Loss of data integrity — data corrupted or made invalid. *Countered by* constraints, transactions, backup/recovery.
5. Loss of availability — sabotage, viruses, denial-of-service. *Countered by* antivirus, redundancy, disaster recovery.

Security measures in the DBMS

- Views — expose only the rows/columns a user may see, hiding sensitive columns (a security *and* simplification layer; see Lesson 6.8). - Authorization rules — GRANT privileges to users/roles and REVOKE them, controlling who may SELECT/INSERT/UPDATE/DELETE on each object. - Authentication — verify identity (passwords, multi-factor) before any access. - Encryption / SSL (TLS) — protect data in transit (HTTPS) and at rest so intercepted or stolen data is unreadable. - Backup and journaling — the transaction log and backups so integrity and availability survive failure (the full mechanics are Lesson 8.3). - Integrity controls — domain/entity/referential constraints stop invalid data at the source.

SQL injection: the web-specific threat

In a three-tier web app, any internet user can send input to the server, so untrusted input is the central risk. SQL injection happens when user input is concatenated directly into a SQL string and then executed — the input becomes *code*, not *data*:

# UNSAFE: input glued into the query
query = "SELECT * FROM users WHERE name = '" + user_input + "'"
# attacker enters:  admin' --
# resulting SQL:  SELECT * FROM users WHERE name = 'admin' --'
#                 the -- comments out the rest, bypassing the password check

The fix is parameterized queries (prepared statements): the SQL structure is fixed and the input is bound as a parameter — treated strictly as a value, never executable code.

# SAFE: input bound as a parameter, never parsed as SQL
query = "SELECT * FROM users WHERE name = ?"
execute(query, [user_input])   # admin' -- is just a (failing) literal name

Regulatory context

Security programs also satisfy law: GDPR (EU personal-data protection, 2018), HIPAA (US health data), and SOX (Sarbanes-Oxley, financial reporting controls). Data administrators write the policies; database administrators implement them through access controls, views, encryption, and audit trails.

Worked example: lock down a sensitive salary column

A support clerk needs employee names and titles but must not see salaries. Combine two measures:

CREATE VIEW emp_public AS
    SELECT emp_id, name, title, dept_id FROM employees;   -- view drops the salary column
GRANT SELECT ON emp_public TO support_role;               -- authorization: read the view only
REVOKE SELECT ON employees FROM support_role;             -- deny the base table directly

The view hides the sensitive column, and GRANT/REVOKE enforce that the clerk can reach only the view, never the underlying employees table — a single, layered control answering the exam's “which security features protect this data?” question.