User Roles, Privileges, Authentication & Security in DBMS

TAKEAWAYS:

• Users, roles, and privileges are the three pillars of database security, with roles acting as containers for privileges to simplify management.
• Authentication verifies who you are (via passwords, tokens, or biometrics), while authorization determines what you can do (via GRANT/REVOKE commands).
• SQL injection and privilege escalation are the top two attacks on databases, and defense starts with least-privilege access and parameterized queries.
• Encryption (TDE, SSL/TLS) protects data at rest and in transit, while auditing logs every critical action for accountability.
• Role-based access control (RBAC) is the gold standard for large systems (e.g., banks) because it scales and aligns with organizational hierarchies.

---

Core Concepts: Users, Roles, and Privileges

1. Users: The Building Blocks

Every database has users, which are accounts that can connect to the database and execute commands. Users are tied to:

• Authentication credentials (username/password, certificates, or biometrics).
• Privileges (permissions to perform actions like SELECT, INSERT, or DROP TABLE).
• Profiles (resource limits like CPU, memory, or concurrent sessions).

Worked Example: Nepali Bank Loan Officer A loan officer at Nabil Bank needs to:

• View customer accounts (SELECT on customers table).
• Update loan status (UPDATE on loans table).
• Run reports (EXECUTE on generate_report() procedure).

How it’s implemented in Oracle:

-- Create a user for the loan officer
CREATE USER loan_officer IDENTIFIED BY "SecurePass123";

-- Create a role for loan officers
CREATE ROLE loan_officer_role;

-- Grant privileges to the role
GRANT SELECT ON customers TO loan_officer_role;
GRANT UPDATE ON loans TO loan_officer_role;
GRANT EXECUTE ON generate_report TO loan_officer_role;

-- Assign the role to the user
GRANT loan_officer_role TO loan_officer;

---

2. Roles: Simplifying Privilege Management

Roles are named groups of privileges that can be assigned to users. They solve two problems:

1. Privilege explosion: Without roles, granting permissions to 100 users individually is tedious.
2. Consistency: All loan officers get the same permissions via the loan_officer_role.

Comparison Table: Users vs. Roles

Real-World Example: eSewa’s User Roles eSewa uses roles to manage:

• Customers: Can view transactions (SELECT on transactions).
• Merchants: Can update inventory (UPDATE on inventory).
• Admins: Can DROP TABLE for maintenance. Roles ensure no merchant can accidentally delete customer data.

---

3. Privileges: The "What You Can Do"

Privileges are specific permissions to perform actions on database objects. Types include:

• Object privileges: Act on tables, views, procedures (e.g., SELECT, INSERT, EXECUTE).
• System privileges: Act on the database itself (e.g., CREATE SESSION, CREATE TABLE).
• Role privileges: Grant/revoke roles (GRANT role_name TO user).

Worked Example: Daraz Order Processing Daraz’s warehouse system uses privileges to:

1. Warehouse staff: INSERT into orders table (to log new orders).
2. Shipping team: UPDATE on orders (to mark as shipped).
3. Audit team: SELECT on all tables (for compliance).

-- Grant warehouse staff INSERT privilege
GRANT INSERT ON orders TO warehouse_staff;

-- Grant shipping team UPDATE privilege (only on shipped_at column)
GRANT UPDATE (shipped_at) ON orders TO shipping_team;

-- Grant audit team full SELECT (but no modifications)
GRANT SELECT ON orders TO audit_team;

---

Authentication: Proving You Are Who You Claim

Authentication answers: "Are you really User X?" Methods include:

1. Password Authentication

• How it works: User provides a username and password. The DBMS checks the password hash.
• Weaknesses: Vulnerable to brute-force attacks if passwords are weak.
• Best practices:
  • Enforce complexity rules (mix of letters, numbers, symbols).
  • Use password hashing (SHA-256, bcrypt) with salt.
  • Implement account lockout after 5 failed attempts.

Example: Ncell Customer Portal Ncell’s portal uses password authentication for:

• Checking mobile balance (SELECT on customer_balance).
• Updating contact details (UPDATE on customer_info).

---

2. Certificate Authentication

• How it works: Uses public-key cryptography. The DBMS verifies a digital certificate issued by a trusted authority (e.g., Let’s Encrypt).
• Use case: High-security environments like NEPSE’s trading system or bank core banking systems.
• Advantage: More secure than passwords; harder to steal.

---

3. Biometric Authentication

• How it works: Uses fingerprints, retina scans, or facial recognition.
• Use case: NTC’s automated toll collection (fingerprint verification for high-value transactions).
• Challenge: High cost and privacy concerns.

---

Authorization: Determining What You Can Access

Authorization answers: "What are you allowed to do?" Models include:

1. Discretionary Access Control (DAC)

• Definition: Owners of objects (e.g., tables) decide who gets access.
• Example: A sales_manager creates a monthly_sales table and grants SELECT to their team.
• Weakness: Risk of privilege escalation (e.g., a junior employee gaining admin rights).

Mermaid Diagram: DAC Flow

---

2. Mandatory Access Control (MAC)

• Definition: Access is controlled by a central authority (e.g., government security clearance).
• Example: Nepal Rastra Bank’s core banking system uses MAC to restrict access based on security levels.
• Advantage: Prevents insider threats.
• Disadvantage: Inflexible; hard to manage in dynamic environments.

---

3. Role-Based Access Control (RBAC)

• Definition: Access is granted based on job roles (e.g., HR_Manager, Finance_Auditor).
• Why it’s used: Scalable and aligns with organizational structure.
• Example: Pathao’s driver app uses RBAC:
  • Driver: Can UPDATE ride status.
  • Dispatcher: Can SELECT all rides.
  • Admin: Can DROP TABLE for system updates.

Comparison Table: Authorization Models

---

Security Threats and Mitigations

1. SQL Injection

• What it is: Attackers insert malicious SQL queries via input fields (e.g., login forms).
• Example: A hacker inputs ' OR '1'='1 in a login form to bypass authentication.
• Mitigation:
  • Use parameterized queries (prepared statements).
  • Validate all user inputs.

Worked Example: Khalti Payment System Khalti prevents SQL injection by:

1. Using prepared statements for all queries.
2. Escaping user inputs with mysqli_real_escape_string().

// Vulnerable code (SQL injection risk)
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'";

// Secure code (parameterized query)
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->execute(['username' => $_POST['username']]);

---

2. Privilege Escalation

• What it is: Exploiting flaws to gain higher permissions (e.g., a user becoming an admin).
• Example: A database user with SELECT on sys_users table might dump password hashes.
• Mitigation:
  • Follow the principle of least privilege (grant only necessary permissions).
  • Regularly audit privileges with DBA_ROLES or INFORMATION_SCHEMA.ROLES.

---

3. Data Theft

• What it is: Unauthorized access to sensitive data (e.g., customer credit card numbers).
• Mitigation:
  • Encrypt data at rest (TDE: Transparent Data Encryption).
  • Mask sensitive fields (e.g., show only last 4 digits of a credit card).

---

Security Best Practices

1. Encryption

• Types:
  • TDE (Transparent Data Encryption): Encrypts entire database files.
  • SSL/TLS: Encrypts data in transit (e.g., between app and database).
• Example: Nabil Bank uses TDE for customer data and SSL/TLS for online transactions.

---

2. Auditing

• What it does: Logs all critical actions (e.g., GRANT, DROP TABLE, UPDATE salary).
• Example: Nepal Investment Bank audits all UPDATE operations on the accounts table.
• Command (Oracle):

  AUDIT UPDATE ON accounts BY user;
  

---

3. Regular Backups and Access Reviews

• Backups: Ensure you can restore data after a breach.
• Access reviews: Periodically check who has which privileges (e.g., "Why does temp_user still have DROP TABLE?").

---

In the Real World

1. Nabil Bank’s Core Banking System

   • Idea Used: RBAC + Least Privilege
   • How: Loan officers get only the SELECT/UPDATE privileges they need. Admins have separate roles for different functions (e.g., audit_admin, backup_admin).
   • Impact: Prevents a loan officer from accidentally (or maliciously) deleting customer records.

2. eSewa’s Transaction Processing

   • Idea Used: MAC for High-Value Transactions
   • How: Transactions over Rs. 50,000 require two-factor authentication (password + OTP) and are logged in an immutable audit trail.
   • Impact: Reduces fraud and meets regulatory requirements.

3. Daraz’s Order Fulfillment

   • Idea Used: DAC with Strict Ownership
   • How: The warehouse role can only INSERT into orders, while the shipping role can only UPDATE the status column.
   • Impact: Prevents warehouse staff from modifying order amounts or shipping addresses.

---

Exam Tip

This unit is heavily tested on:

1. Definitions: Know the difference between authentication (proving identity) and authorization (granting access).
2. Commands: Be ready to write SQL for:
   • Creating users/roles (CREATE USER, CREATE ROLE).
   • Granting/revoking privileges (GRANT, REVOKE).
   • Auditing actions (AUDIT).
3. Scenarios: Expect questions like:
   • "How would you secure a bank’s loan processing system?" (Answer: RBAC + least privilege + auditing.)
   • "What’s the risk of using DAC in a hospital database?" (Answer: Doctors might accidentally grant access to sensitive patient records.)
4. Attacks: Recognize SQL injection and privilege escalation in given code snippets.
5. Real-World Mapping: Link concepts to Nepali systems (e.g., "How does Ncell use RBAC?").

Common Pitfalls:

• Confusing roles (containers for privileges) with users (individual accounts).
• Forgetting that system privileges (e.g., CREATE TABLE) are separate from object privileges.
• Overlooking auditing as a security measure—it’s often the difference between passing and failing in exams.

---

Visual Summary