3 min read
Topic 10 of 18
10
Integrity and security
Data Integrity ensures that the data stored in a database is accurate, consistent, and reliable throughout its lifecycle.
🔐 Integrity and Security in Databases
1. What is Data Integrity?
Data Integrity ensures that the data stored in a database is accurate, consistent, and reliable throughout its lifecycle.
- It prevents data corruption and unauthorized modification.
- Maintains correctness and validity of data.
2. Types of Data Integrity
| Type | Description | Example |
|---|---|---|
| Entity Integrity | Ensures every table row has a unique, non-null primary key. | Primary key constraint enforcement. |
| Referential Integrity | Ensures relationships between tables remain consistent. | Foreign key constraints prevent orphan records. |
| Domain Integrity | Ensures data values fall within valid ranges or sets. | Age column must be between 0 and 120. |
| User-Defined Integrity | Custom business rules specific to application logic. | Employee salary must not be less than minimum wage. |
3. Integrity Constraints
- Primary Key Constraint: Unique identifier for each record.
- Foreign Key Constraint: Enforces valid references between tables.
- Unique Constraint: Ensures uniqueness of a column’s values.
- Check Constraint: Enforces domain-specific conditions.
- Not Null Constraint: Ensures a column cannot have NULL values.
4. What is Database Security?
Database Security protects the database from unauthorized access, misuse, and threats, ensuring confidentiality, integrity, and availability (the CIA triad).
5. Components of Database Security
| Aspect | Description |
|---|---|
| Authentication | Verifies the identity of users or systems. |
| Authorization | Grants or denies permissions to users. |
| Auditing | Tracks user actions for accountability. |
| Encryption | Protects data privacy by converting data into unreadable format without a key. |
| Backup and Recovery | Ensures data can be restored after loss or corruption. |
6. Authentication Methods
- Password-based authentication.
- Multi-factor authentication (MFA).
- Biometric authentication.
- Integration with external systems (LDAP, Active Directory).
7. Authorization and Access Control
- Role-Based Access Control (RBAC): Users assigned roles with specific privileges.
- Discretionary Access Control (DAC): Owners control access to their objects.
- Mandatory Access Control (MAC): System-enforced policies based on security levels.
8. SQL Security Mechanisms
- GRANT and REVOKE commands to manage user privileges.
- Example:
GRANT SELECT, INSERT ON Employees TO user_john;
REVOKE UPDATE ON Employees FROM user_john;
9. Auditing
- Monitors database activities such as logins, queries, and changes.
- Helps detect suspicious or unauthorized actions.
- Often required for compliance (HIPAA, GDPR, PCI DSS).
10. Encryption in Databases
- Data-at-Rest Encryption: Encrypts stored data files.
- Data-in-Transit Encryption: Protects data moving over the network (SSL/TLS).
- Column-Level Encryption: Encrypts specific sensitive columns (e.g., credit card numbers).
11. Summary Table
| Topic | Purpose |
|---|---|
| Data Integrity | Accuracy and consistency of data |
| Integrity Constraints | Enforce business and domain rules |
| Authentication | Verify user identity |
| Authorization | Control user access |
| Auditing | Track user actions |
| Encryption | Protect data confidentiality |
Why Are Integrity and Security Crucial?
- Prevents data corruption and unauthorized changes.
- Protects sensitive information from breaches.
- Ensures compliance with legal and regulatory standards.
- Maintains user trust and system reliability.
Past Papers
Open this section to view and download past papers
Click on Show Past Papers to load available exam papers.