🔑 Database Administration: Managing Database Access

1. What is Managing Database Access?

Managing database access involves controlling who can connect to the database and what actions they are allowed to perform on database objects (tables, views, procedures, etc.).

• Ensures data security and privacy.
• Prevents unauthorized data manipulation or leakage.
• Supports regulatory compliance.

2. Components of Database Access Management

3. Authentication

• Confirms the identity of the user connecting to the database.

• Common methods:

  • Username and password
  • Multi-factor authentication (MFA)
  • Integration with LDAP, Active Directory, or Kerberos
  • Certificate-based authentication

4. Authorization and Access Control

• Once authenticated, the database server checks what the user is authorized to do.

• Controls access at multiple levels:

  • Database level: Permission to connect or create databases.
  • Schema/Object level: Permissions on tables, views, procedures, etc.
  • Row and column level: Some systems allow fine-grained access control.

5. Privileges

• Basic permissions granted to users or roles to perform actions like:

  • SELECT – read data
  • INSERT – add new data
  • UPDATE – modify existing data
  • DELETE – remove data
  • EXECUTE – run stored procedures or functions
  • ALTER – modify database objects

6. Granting and Revoking Access

Granting Access:

GRANT SELECT, INSERT ON employees TO user_john;

Revoking Access:

REVOKE INSERT ON employees FROM user_john;

7. Role-Based Access Control (RBAC)

• Instead of granting privileges to individual users, assign privileges to roles.
• Users are then assigned these roles.
• Simplifies permission management, especially in large systems.

8. Fine-Grained Access Control (FGAC)

• Enables access control at a more detailed level than just tables.

• Examples:

  • Row-level security: Users can only see rows they are authorized for.
  • Column-level security: Restrict access to sensitive columns.

• Supported in DBMS like Oracle (VPD – Virtual Private Database), SQL Server, PostgreSQL.

9. Auditing Access

• Logging who accessed what and when.

• Useful for:

  • Detecting unauthorized access.
  • Meeting compliance requirements.
  • Investigating security incidents.

10. Summary Table

Why Managing Database Access is Critical

• Protects sensitive and confidential information.
• Prevents accidental or malicious data loss.
• Supports compliance with laws like GDPR, HIPAA, PCI-DSS.
• Enables secure multi-user database environments.