week 8

8.1 Database Security Threats

Database security aims to protect data against accidental or intentional disclosure to unauthorised persons, and data modification or destruction. Major threats include:

8.2 Authentication and Authorisation

AUTHENTICATION verifies WHO the user is (identity verification). AUTHORISATION determines WHAT the authenticated user can do (access control).

1.    Authentication methods: Username/Password, Multi-Factor Authentication (MFA — widely required by CBN for banking apps), biometrics, digital certificates

2.    Authorisation implements the principle of LEAST PRIVILEGE — users are given only the minimum permissions needed for their role

8.3 SQL Data Control Language (DCL)

MySQL uses GRANT and REVOKE to manage user privileges:

-- Create a database user for a bank teller

CREATE USER 'teller_user'@'localhost' IDENTIFIED BY 'SecureP@ss123';

-- Grant only SELECT on customer table (tellers can view but not modify)

GRANT SELECT ON bank_db.CUSTOMER TO 'teller_user'@'localhost';

-- Grant SELECT and INSERT on TRANSACTION table

GRANT SELECT, INSERT ON bank_db.TRANSACTION TO 'teller_user'@'localhost';

-- Grant full privileges to the DBA

GRANT ALL PRIVILEGES ON bank_db.* TO 'dba_user'@'localhost' WITH GRANT OPTION;

-- Revoke UPDATE on CUSTOMER from teller (they had it mistakenly)

REVOKE UPDATE ON bank_db.CUSTOMER FROM 'teller_user'@'localhost';

-- Show what privileges a user has

SHOW GRANTS FOR 'teller_user'@'localhost';

PRIVILEGE TYPES in MySQL:

8.4 Database Views for Security

A VIEW is a virtual table based on the result of a SELECT query. Views provide security by allowing users to see only specific rows and columns, hiding sensitive data.

-- Create a view that hides salary information from non-HR staff

CREATE VIEW EMPLOYEE_PUBLIC AS

SELECT EmployeeID, FirstName, LastName, Department, JobTitle, Extension

FROM EMPLOYEE

WHERE IsActive = TRUE;

-- Create a view for a specific branch manager — only their branch's customers

CREATE VIEW MINNA_BRANCH_CUSTOMERS AS

SELECT CustomerID, FirstName, LastName, AccountType, Balance

FROM CUSTOMER

WHERE BranchCode = 'MNA001';

-- Grant access to the view, not the base table

GRANT SELECT ON bank_db.EMPLOYEE_PUBLIC TO 'branch_user'@'localhost';

8.5 SQL Injection — Attack and Prevention

SQL injection is one of the most common and dangerous web application vulnerabilities. An attacker inputs malicious SQL code into a form field that gets incorporated into a database query.

ATTACK EXAMPLE: A login form sends the query:

SELECT * FROM USERS WHERE Username='' + input_username + '' AND Password='' + input_password + ''

If the attacker enters: Username = ' OR '1'='1 and Password = ' OR '1'='1

The query becomes: SELECT * FROM USERS WHERE Username='' OR '1'='1' AND Password='' OR '1'='1'

'1'='1' is always TRUE, so the attacker gains access without a valid password!

PREVENTION METHODS:

1.    Parameterised Queries (Prepared Statements) — NEVER concatenate user input directly into SQL

2.    Input Validation — Validate and sanitise all user inputs

3.    Stored Procedures — Use pre-compiled SQL

4.    Principle of Least Privilege — DB user for the app should have minimal rights

5.    Web Application Firewalls (WAF) — Detect and block injection attempts

SAFE QUERY (Java PreparedStatement):

PreparedStatement stmt = conn.prepareStatement('SELECT * FROM USERS WHERE Username=? AND Password=?');

stmt.setString(1, inputUsername);

stmt.setString(2, inputPassword);

8.6 Encryption

Encryption converts data into an unreadable format that can only be decoded with the correct key.

8.7 NDPR Compliance for Nigerian Database Systems

The Nigeria Data Protection Regulation (NDPR) 2019, issued by NITDA, places significant obligations on organisations that collect and process personal data of Nigerians. Key database-relevant requirements:

8.8 Database Integrity Constraints

Beyond entity and referential integrity, DBMS supports additional integrity mechanisms:

1.    CHECK Constraints — Enforce domain-specific rules: CHECK (Age >= 18 AND Age <= 120); CHECK (Salary > 0);

2.    UNIQUE Constraints — Ensure no duplicates: BVN VARCHAR(11) UNIQUE;

3.    NOT NULL Constraints — Prevent null values in critical fields

4.    Triggers — Automatic actions that execute when specified events occur (INSERT, UPDATE, DELETE)

TRIGGER EXAMPLE — Audit log for salary changes:

CREATE TRIGGER salary_audit BEFORE UPDATE ON STAFF

FOR EACH ROW

INSERT INTO AUDIT_LOG (TableName, FieldChanged, OldValue, NewValue, ChangedBy, ChangeDate)

VALUES ('STAFF', 'Salary', OLD.Salary, NEW.Salary, USER(), NOW());

Reading List / References

1.    Elmasri, R. & Navathe, S. B. (2016). Fundamentals of Database Systems, Chapter 24: Database Security and Authorisation. Pearson.

2.    NITDA (2019). Nigeria Data Protection Regulation (NDPR). www.nitda.gov.ng

3.    OWASP (2023). SQL Injection. owasp.org/www-community/attacks/SQL_Injection

Activities