Database Security & Access Control

Syllabus tag: Paper-II, Section-B, Topic IV.14 — Database Security & Access Control

Definition

Database security is the set of mechanisms that protect a database from unauthorized access, misuse, or corruption, while preserving data availability for legitimate users.

Authentication vs Authorization

• Authentication: verifying who a user is (login credentials, tokens, biometrics)
• Authorization: verifying what an authenticated user is permitted to do (which data, which operations)

Access Control Models

SQL-Level Access Control (DCL)

GRANT SELECT, UPDATE ON Employee TO hr_clerk;
GRANT ALL PRIVILEGES ON Payroll TO payroll_admin WITH GRANT OPTION;
REVOKE UPDATE ON Employee FROM hr_clerk;

• WITH GRANT OPTION lets the grantee further grant the same privilege to others — a common trap in "what does this SQL do" questions.

Views as a Security Mechanism

• A view can expose only a restricted subset of rows/columns of underlying tables, hiding sensitive attributes (e.g., a view of Employee excluding the Salary column) — enforces access control without duplicating data or writing application-level filters.

Other Security Mechanisms

• Encryption: protects data at rest (stored on disk) and in transit (over the network) from being read even if intercepted/stolen
• Auditing: logging who accessed/modified what and when, to detect and investigate misuse after the fact
• SQL Injection: an attack where untrusted input is concatenated directly into a SQL query, altering its logic (e.g., ' OR '1'='1); prevented via parameterized queries/prepared statements and input validation — a very commonly tested application-security angle for a DB security question
• Backup and recovery as a security dimension: protects against data loss from failure or malicious destruction, complementing access control

Numerical / Applied Example — Bank Database Security Scheme (FPSC 2019, Q8(b))

A layered scheme for a bank database might combine:

1. Authentication: multi-factor login for tellers/customers
2. RBAC: roles like Teller (can view/update balances up to a limit), Branch Manager (can approve large transactions), Auditor (read-only across all branches)
3. Views: customer-facing application only sees a view exposing the customer's own account, not the full accounts table
4. Encryption: account numbers and balances encrypted at rest; all network traffic over TLS
5. Auditing: every balance-changing transaction logged with user ID, timestamp, old/new value
6. DAC + MAC combination: branch-level DAC for day-to-day grants, with MAC-style classification for highly sensitive data (e.g., large-transaction records) that only auditors/compliance officers can read

MCQ Trap

• DAC relies on the data owner's discretion; MAC is system/administrator-enforced and cannot be overridden by the object's owner — the key distinguishing test in MCQs.
• REVOKE removes a previously granted privilege — it does not delete the underlying data or the user account.

CSS Exam Angle

• FPSC 2019 asked to "describe an appropriate security scheme for a database maintained by a bank, justify your answer" — this topic is tested as an applied design question, not just definitions; always anchor DAC/MAC/RBAC/views/encryption/auditing to the specific scenario given.

Likely Questions

1. Differentiate authentication and authorization.
2. Compare DAC, MAC, and RBAC as access control models.
3. Explain how SQL GRANT/REVOKE and the WITH GRANT OPTION clause work.
4. How can a view be used as a database security mechanism?
5. Describe an appropriate security scheme for a database maintained by a bank, and justify your answer.
6. What is SQL injection, and how can it be prevented?

---

📘 Enrichment — Threats, Models and Worked Examples

The CIA Triad Applied to Databases

Common Database Threats (short-note list)

• SQL injection · Privilege abuse / excessive privileges · Insider threats · Weak authentication / default passwords · Unpatched DBMS vulnerabilities · Exposed backups · Ransomware · Inference attacks on statistical databases · Misconfigured cloud databases (publicly exposed storage/DB ports — a leading cause of large leaks).

Worked Example 1 — SQL Injection and Its Fix

Vulnerable login code (string concatenation):

query = "SELECT * FROM Users WHERE username = '" + user + "' AND password = '" + pass + "'";

Attacker enters username admin' -- and any password. The executed SQL becomes:

SELECT * FROM Users WHERE username = 'admin' --' AND password = 'anything'

• -- comments out the password check → attacker logs in as admin.
• With input ' OR '1'='1 the WHERE clause is always true → returns every user.

Fix — parameterized (prepared) statement:

stmt = conn.prepareStatement("SELECT * FROM Users WHERE username = ? AND password_hash = ?");
stmt.setString(1, user);
stmt.setString(2, hash(pass));

• Input is sent as data, never parsed as SQL. Add: input validation, least-privilege DB accounts, stored procedures, a web application firewall, and storing salted password hashes (bcrypt/Argon2), never plain passwords.

Worked Example 2 — GRANT Chain and Cascading REVOKE

-- DBA (owner) → A with grant option
GRANT SELECT ON Payroll TO A WITH GRANT OPTION;
-- A → B with grant option; B → C
GRANT SELECT ON Payroll TO B WITH GRANT OPTION;   -- executed by A
GRANT SELECT ON Payroll TO C;                     -- executed by B
-- Owner revokes from A
REVOKE SELECT ON Payroll FROM A CASCADE;

flowchart LR
    DBA["DBA (owner)"] -->|with grant option| A
    A -->|with grant option| B
    B --> C

• Result: A, B and C all lose the privilege — B's and C's privileges depended on A's. (If C had also received SELECT independently from the DBA, C would keep it — the authorization graph rule: a privilege survives while any path from the owner remains.)
• RESTRICT instead of CASCADE makes the REVOKE fail if dependent grants exist.

Worked Example 3 — Role-Based Access Control in SQL

CREATE ROLE teller;
CREATE ROLE branch_manager;
GRANT SELECT, UPDATE (balance) ON Account TO teller;
GRANT teller TO branch_manager;                     -- role hierarchy: manager inherits teller rights
GRANT SELECT ON Loan_Approvals TO branch_manager;
GRANT teller TO ahmed;                              -- users receive roles, not raw privileges
GRANT branch_manager TO fatima;

• When Ahmed is promoted, one statement changes his role — no need to revoke dozens of individual privileges.

Worked Example 4 — Views for Row- and Column-Level Security

-- Column restriction: hide salary and CNIC
CREATE VIEW Staff_Directory AS SELECT EmpId, Name, DeptId, Email FROM Employee;
-- Row restriction: each branch manager sees only own branch
CREATE VIEW My_Branch_Accounts AS
SELECT * FROM Account WHERE branch_code = SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER');
GRANT SELECT ON Staff_Directory TO PUBLIC;

MAC in Depth — Bell–LaPadula Model

Security levels: Top Secret (TS) > Secret (S) > Confidential (C) > Unclassified (U).

• Simple security property (no read up): a subject may read an object only if its clearance ≥ the object's classification.
• Star property (no write down): a subject may write an object only if its clearance ≤ the object's classification — prevents a Secret user copying Secret data into an Unclassified table.
• Polyinstantiation: the same key may exist with different values at different levels (a ship's destination is "Exercise" at level U but "Operation X" at level S) — so low-level users cannot infer that hidden data exists.
• Biba model is the integrity mirror image (no read down, no write up).

Statistical Database Security (Inference Control)

• A statistical DB allows only aggregate queries (COUNT, AVG), yet individuals can still be inferred.
• Example: COUNT(*) WHERE Dept='Legal' AND Gender='F' returns 1, and AVG(Salary) for the same condition then reveals that one woman's exact salary.
• Controls: minimum query-set size (refuse results based on fewer than k records), query-overlap restriction, adding random noise (differential privacy), data swapping, and auditing query patterns.

Encryption Choices

• At rest: Transparent Data Encryption (TDE) encrypts data files and backups; column-level encryption for CNIC/card numbers.
• In transit: TLS between application and database.
• In use / application-level: data masking for test environments (e.g., show 35202-*******-1), tokenization of card numbers (PCI-DSS).

Pakistan Legal & Policy Context

• Prevention of Electronic Crimes Act (PECA) 2016 — criminalises unauthorised access to information systems and data; amended in 2025.
• Personal data protection — a dedicated Personal Data Protection Bill has been drafted and debated for years; check its latest status before the exam and cite it as the emerging framework.
• SBP cybersecurity and IT governance frameworks require banks to implement access control, encryption, logging and incident response — useful for the "bank security scheme" question.
• Large data-leak incidents involving citizen records in recent years are a strong real-world hook for any introduction or conclusion.

---

✍️ Model Answers — Likely Questions

Q1. Differentiate authentication and authorization.

Conclusion: both are needed — authentication without authorization lets any valid user do anything; authorization without authentication cannot know who is asking.

Q2. Compare DAC, MAC and RBAC.

Use the comparison table in the notes and add one example each: DAC — the owner of Payroll grants SELECT to HR (Worked Example 2); MAC — Bell–LaPadula levels in military/intelligence databases; RBAC — teller/branch_manager roles in a bank (Worked Example 3). Add the trade-off line: DAC is flexible but vulnerable to Trojan-horse leaks (a user can pass data on); MAC is strict but rigid; RBAC balances manageability and control and supports least privilege and separation of duties.

Q3. Explain GRANT/REVOKE and WITH GRANT OPTION.

• GRANT privilege ON object TO user/role — privileges include SELECT, INSERT, UPDATE (column list possible), DELETE, REFERENCES, EXECUTE, ALL.
• WITH GRANT OPTION lets the grantee grant the privilege onward, forming an authorization graph.
• REVOKE … CASCADE removes the privilege and every privilege that depended on it; RESTRICT refuses if dependents exist.

Walk through Worked Example 2 with its diagram.

Q4. How can a view be used as a security mechanism?

A view exposes a controlled subset (columns and/or rows) of base tables; users are granted access to the view but not the base table. Column hiding (Staff_Directory without Salary/CNIC), row filtering (each manager sees own branch), and aggregate-only views (department average salary without individual salaries). Benefits: no data duplication, centrally maintained, transparent to users. Limitations: not all views are updatable; complex views may affect performance; inference through aggregates remains possible.

Q5. Describe an appropriate security scheme for a bank database, and justify it.

Structure as layers (defense in depth), each with a justification:

1. Physical & network: DB servers in a secured data centre, private network segment, firewall allowing only the application servers — reduces attack surface.
2. Authentication: MFA for staff, biometric/OTP for customers, no shared accounts — accountability.
3. Authorization (RBAC + least privilege): Teller, Branch Manager, Loan Officer, Auditor (read-only), DBA (no business-data access by default) — separation of duties prevents one insider from committing and hiding fraud.
4. MAC-style labels for highly sensitive data (large transactions, AML reports) — system-enforced, not left to discretion.
5. Views & stored procedures: applications call transfer_funds rather than updating tables — business rules enforced, SQL injection limited.
6. Encryption: TDE at rest, TLS in transit, masking of CNIC/account numbers in non-production — protects stolen media and backups.
7. Auditing & monitoring: log every balance change and privileged action; alerts for anomalies — detection and evidence.
8. Availability: replication to a DR site, regular tested backups — business continuity against ransomware/failure.
9. Compliance: SBP frameworks, PECA 2016 — legal accountability.

Close by drawing a layered "onion" diagram.

Q6. What is SQL injection and how can it be prevented?

Definition: a code-injection attack where malicious input is inserted into a dynamically built SQL statement, changing its logic. Types: tautology (' OR '1'='1), comment (admin' --), UNION-based data extraction, blind/time-based.

Show Worked Example 1 (vulnerable query → injected query → parameterized fix).

Prevention: parameterized queries/prepared statements (primary defence), stored procedures without dynamic SQL, input validation/whitelisting, least-privilege DB accounts, escaping as last resort, WAF, error messages that do not reveal SQL, regular penetration testing.

---

⚡ 60-Second Revision

• CIA = Confidentiality, Integrity, Availability.
• Authentication = who; Authorization = what.
• DAC = owner decides; MAC = system labels (no read up, no write down); RBAC = roles.
• REVOKE CASCADE follows the grant chain.
• SQL injection → prepared statements. Inference → minimum query-set size / noise.

📜 Past-Paper Questions & MCQs (CSS 2016–2026)

2019 · Paper-II · Q8(b) · 6 marks

Q. Describe an appropriate security scheme for a database maintained by a bank. Justify with an example.

Ans.

• Authentication: every user and application must prove identity (strong passwords plus multi-factor authentication).
• Authorisation (role-based access control): roles such as teller, branch manager, auditor and customer, with GRANT and REVOKE and least privilege. Views hide sensitive columns and rows. Example: a teller may only call stored procedures that read or post transactions for their own branch and cannot query the whole customer table.
• Encryption: TLS for data in transit, and transparent or column-level encryption (card numbers, PINs) for data at rest, with keys kept in a hardware security module.
• Auditing and monitoring: log every access and change, detect unusual activity, and keep tamper-proof audit trails.
• Integrity and recovery: constraints, transactions (ACID), backups and disaster recovery.
• Application and network protection: prepared statements against SQL injection, firewalls, network segmentation and patching.
• Separation of duties (the person who enters a payment cannot approve it) and data masking in test systems.
• Compliance: follow bank regulation and privacy rules.