🔐 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

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

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

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.