Database Security and Integrity

Databases often store sensitive and valuable data — personal information, financial records, medical data. Protecting this data from unauthorised access, corruption, and loss is a critical responsibility. This lesson covers the key concepts of database security and data integrity.

---

Data Integrity

Data integrity means that the data stored in the database is accurate, consistent, and reliable. There are several types:

1. Entity Integrity

Every table must have a primary key, and no primary key value can be NULL. This ensures every record is uniquely identifiable.

2. Referential Integrity

Every foreign key value must either match an existing primary key in the referenced table or be NULL (if the field allows NULLs). This ensures that relationships between tables remain valid.

Example: If you try to insert an order with a CustomerID that does not exist in the Customers table, the database will reject the insert.

3. Domain Integrity

Every value in a field must conform to the defined data type, format, and constraints for that field.

Examples:

• A DateOfBirth field must contain a valid date.
• A Score field with CHECK (Score >= 0 AND Score <= 100) will reject a value of 150.
• A NOT NULL constraint prevents empty values.

Validation vs Verification

Concept	Definition	Example
Validation	Checking that data is reasonable, sensible, and within acceptable bounds	Range check: age must be 0–150; type check: age must be an integer
Verification	Checking that data has been entered correctly (matches the original source)	Double entry: typing the same data twice to confirm it matches

---

Access Control

User Accounts and Permissions

Databases use user accounts with different levels of access:

• Read-only access: The user can view data but not modify it.
• Read-write access: The user can view and modify data.
• Administrative access: The user can create/delete tables, manage users, and configure the database.

SQL Access Control (DCL)

-- Grant SELECT permission on Students to user 'teacher1'
GRANT SELECT ON Students TO teacher1;

-- Grant full access on Grades to user 'admin1'
GRANT ALL PRIVILEGES ON Grades TO admin1;

-- Remove INSERT permission on Students from user 'student1'
REVOKE INSERT ON Students FROM student1;

Views for Access Control

A view is a virtual table defined by a query. Views can restrict which rows and columns a user can see, providing a security layer.

-- Create a view that shows student names but not their emails
CREATE VIEW PublicStudentInfo AS
SELECT StudentID, FirstName, Surname, FormGroup
FROM Students;

-- Grant access to the view instead of the base table
GRANT SELECT ON PublicStudentInfo TO teacher1;

---

Encryption

Encryption converts data into an unreadable format using an algorithm and a key. Only someone with the correct decryption key can read the original data.

Why Encrypt Database Data?

• Data at rest: Encrypting stored data protects it if the storage medium is stolen or accessed by an unauthorised person.
• Data in transit: Encrypting data sent between the application and the database (e.g. using TLS/SSL) prevents interception.
• Password storage: Passwords should never be stored as plain text. They should be hashed (a one-way transformation) with a salt (random data added before hashing to prevent dictionary attacks).

---

Backup and Recovery

Backup Strategies