week 8
Teaching and Learning Methods to be Employed
1. Lecture on security mechanisms with live SQL demonstrations
2. SQL injection attack demonstration and prevention
3. Lab: Creating users, granting/revoking privileges in MySQL
4. Discussion: NDPR compliance for Nigerian database systems
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Describe the major threats to database security.
2. Explain authentication, authorisation, and access control in DBMS.
3. Use SQL DCL commands (GRANT, REVOKE) to manage user privileges.
4. Explain how database views provide security through data abstraction.
5. Describe SQL injection and how to prevent it.
6. Explain encryption and audit trails as security mechanisms.
7. Describe the requirements of NDPR for database-driven applications.
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
|
Self-Assessment Quiz: 1. What is the difference between authentication and authorisation? 2. Write SQL commands to: (a) create a user 'report_user', (b) grant SELECT on all tables in 'hr_db' to that user, (c) revoke that privilege. 3. Explain how a SQL injection attack works. Give a specific example. 4. How do database views help with security? Give an example from a Nigerian bank context. |