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.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';
|
View Security Benefit: A bank branch manager can SELECT from MINNA_BRANCH_CUSTOMERS and see only their branch's customers. They cannot see customers from other branches even if they try SELECT * FROM CUSTOMER (they don't have permission on the base table). This is security through abstraction. |