week 8

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.

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:

Privilege	What It Allows
SELECT	Read data from tables
INSERT	Add new rows
UPDATE	Modify existing rows
DELETE	Remove rows
CREATE	Create new tables or databases
DROP	Delete tables or databases
INDEX	Create/drop indexes
ALL PRIVILEGES	All of the above

Teaching and Learning Methods to be Employed