Database develop. life cycle - Security and Access Control

1. Database Security

Definition:
Database security ensures that data is protected against unauthorized access, misuse, loss, or corruption.

Key Goals:

  • Confidentiality: Only authorized users can access data.

  • Integrity: Data remains accurate and consistent.

  • Availability: Authorized users can access data when needed.

Threats to Database Security:

  • Unauthorized access (hackers, insider threats)

  • SQL injection attacks

  • Data corruption or accidental deletion

  • Malware or ransomware

2. Access Control

Access control defines who can access what data and what actions they can perform.

Types of Access Control:

  1. Discretionary Access Control (DAC):

    • The owner of the data decides who can access it.

    • Flexible but less secure.

    • Example: A database user grants SELECT permission on a table to another user.

  2. Mandatory Access Control (MAC):

    • Access is controlled by system-enforced policies based on classifications.

    • Users cannot override the policy.

    • Example: Top-secret data only accessible to users with top-secret clearance.

  3. Role-Based Access Control (RBAC):

    • Access is assigned based on user roles.

    • Easier to manage in large organizations.

    • Example:

      • Admin → full access

      • Analyst → read-only access to reports

      • Clerk → insert/update access to certain tables

  4. Attribute-Based Access Control (ABAC):

    • Access is determined by attributes (user, resource, environment).

    • Very fine-grained and dynamic.

    • Example: A sales manager can view customer data only for their region.

3. Security Mechanisms in Databases

  1. Authentication: Verify identity of users (username/password, multi-factor authentication).

  2. Authorization: Grant permissions to users based on roles or policies.

  3. Encryption:

    • Data-at-rest: Encrypt stored data on disk.

    • Data-in-transit: Encrypt data over networks using TLS/SSL.

  4. Auditing and Logging: Track who accessed or modified data and when.

  5. Backup and Recovery: Protect against data loss.

4. Example: Access Control in SQL

-- Create roles
CREATE ROLE analyst;
CREATE ROLE admin;

-- Grant privileges
GRANT SELECT ON Sales TO analyst;
GRANT ALL PRIVILEGES ON Sales TO admin;

-- Assign roles to users
GRANT analyst TO Alice;
GRANT admin TO Bob;

  • Alice can only read the Sales table.

  • Bob can read, insert, update, delete data.

Summary

  • Database security = protect confidentiality, integrity, and availability.

  • Access control = decide who can do what with data.

  • Mechanisms include authentication, authorization, encryption, auditing, and backup.

  • Common models: DAC, MAC, RBAC, ABAC.