Database Security: User Authentication and Authorization

1. What is Database Security?

  • Definition: Protecting databases from unauthorized access, misuse, corruption, or loss.
  • Key Areas:
  1. Authentication: Verifying the identity of users.
  2. Authorization: Controlling user access to database resources.

2. User Authentication

What is Authentication?
  • Definition: Process of verifying the identity of a user or system before granting access.
  • Goal: Ensure that only legitimate users access the database.
Types of Authentication:
  1. Password-based Authentication:
  • User provides a username and password.
  • Strong passwords should include a mix of letters, numbers, and symbols.
  1. Multi-Factor Authentication (MFA):
  • Combines two or more factors:
    • Something you know (password).
    • Something you have (OTP, token).
    • Something you are (biometric).
  1. Biometric Authentication:
  • Uses physical characteristics like fingerprints, facial recognition, or retina scans.
  1. Token-based Authentication:
  • Users are given a temporary token (e.g., access token in API-based systems).
  1. Certificate-based Authentication:
  • Digital certificates verify user identity using public-key infrastructure (PKI).
Best Practices for Authentication:
  • Use strong, unique passwords.
  • Implement MFA for sensitive databases.
  • Regularly update and rotate credentials.
  • Lock accounts after multiple failed login attempts.

3. User Authorization

What is Authorization?
  • Definition: Process of determining what actions a user can perform after authentication.
  • Goal: Enforce access control policies.
Types of Authorization:
  1. Role-based Access Control (RBAC):
  • Users are assigned roles, and each role has specific permissions.
  • Example:
    • Role: Admin → Full access.
    • Role: User → Read-only access.
  1. Discretionary Access Control (DAC):
  • Owner of the data decides who can access it.
  • Example: A user grants read access to specific files.
  1. Mandatory Access Control (MAC):
  • System enforces strict access policies based on classification levels (e.g., Top Secret, Confidential).
  1. Attribute-based Access Control (ABAC):
  • Access is granted based on user attributes (e.g., location, device type).
Access Control Levels:
  1. Object Level:
  • Restrict access to specific database objects like tables or views.
  1. Column Level:
  • Allow access to certain columns only (e.g., hide salary details).
  1. Row Level:
  • Allow access to specific rows (e.g., a sales manager sees only their team’s data).

4. Security Best Practices

  1. Use Encryption:
  • Encrypt sensitive data (e.g., passwords, credit card details).
  1. Regular Audits:
  • Monitor access logs to detect unauthorized activities.
  1. Grant Minimum Privileges:
  • Follow the principle of least privilege (users get only the access they need).
  1. Database Firewalls:
  • Use firewalls to block unauthorized access.
  1. Regular Updates:
  • Keep database software patched to protect against vulnerabilities.

5. Examples for Quick Understanding

  • Scenario 1: Login to a Database
  • Authentication: Verify username and password.
  • Authorization: Check if the user is allowed to run queries.
  • Scenario 2: Access Control in a Banking Database
  • Role:
    • Teller → Access to account balance details only.
    • Manager → Full access to account and transaction details.
  • Scenario 3: Online Shopping Database
  • Customers can view their own orders but cannot access other customers’ orders.

6. Key Differences Between Authentication and Authorization

AspectAuthenticationAuthorization
DefinitionVerifies user identity.Grants or denies user permissions.
FocusWho is the user?What actions can the user perform?
Order of ProcessHappens first.Happens after authentication.
ExamplesLogin with username/password.Access granted to read/write data.

7. Quick Tips for Last-Minute Revision

  1. Mnemonic for Authentication Types: “P-M-B-T-C”
  • Password, Multi-factor, Biometric, Token, Certificate.
  1. Mnemonic for Authorization Types: “R-D-M-A”
  • RBAC, DAC, MAC, ABAC.
  1. Key Focus:
  • Authentication = Verifying WHO.
  • Authorization = Verifying WHAT.
  1. Example Rules:
  • Strong passwords are key for Authentication.
  • Roles define user permissions in Authorization.