User Roles, Privileges, Authentication & Security in DBMS

TAKEAWAYS:

• Database users, roles, and privileges define who can access what data and perform which operations (SELECT, INSERT, DROP).
• Authentication (passwords, certificates, biometrics) verifies user identity, while authorization enforces access control via GRANT/REVOKE.
• Security threats (SQL injection, privilege escalation, data leaks) require encryption, auditing, and least-privilege principles.
• Views and stored procedures act as security layers by restricting direct table access or exposing only necessary logic.
• Real-world tie: Ncell’s customer portal uses role-based access (e.g., agents vs. admins) to limit data exposure.
• Exam focus: Know how to write GRANT, REVOKE, and CREATE ROLE commands for TU/PU questions.

1. Database Users and Roles: Who Gets Access?

Databases are not open books—only authorized users should read, write, or delete data. Users are database entities (not OS users) with unique credentials, while roles group privileges for efficiency.

erDiagram
    USER ||--o{ ROLE : "has"
    ROLE ||--o{ PRIVILEGE : "grants"
    PRIVILEGE }|--|| OBJECT : "applies_to"
    USER {
        string username
        string password_hash
        string auth_method
    }
    ROLE {
        string role_name
        string description
    }
    PRIVILEGE {
        string operation
        string object_type
    }
    OBJECT {
        string object_name
        string object_type
    }

How Users and Roles Work

classDiagram
    class User {
        +username
        +password_hash
        +auth_method
    }
    class Role {
        +role_name
        +privileges[]
    }
    class Privilege {
        +operation (SELECT/INSERT/UPDATE/DROP)
        +object (table/view/procedure)
    }
    User "1" --> "*" Role : "assigned_to"
    Role "1" --> "*" Privilege : "grants"

• Users are created via CREATE USER (e.g., CREATE USER 'sales_clerk' IDENTIFIED BY 'SecurePass123';).
• Roles bundle privileges (e.g., CREATE ROLE 'report_reader').
• Privileges are tied to objects (tables, views) and operations (e.g., GRANT SELECT ON orders TO report_reader;).

Worked Example: Ncell’s Billing System

Ncell’s backend database has:

• Users: billing_agent, customer_service, admin.
• Roles:
  • view_bills: Can SELECT from customer_bills.
  • update_payments: Can INSERT/UPDATE into payment_records.
• Privileges:

  GRANT SELECT ON customer_bills TO view_bills;
  GRANT INSERT, UPDATE ON payment_records TO update_payments;
  

Why? Agents only see bills; admins manage all data.

2. Authentication: Proving You’re Who You Claim

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

Real Picture:

Password Policies Matter

Weak passwords (e.g., 123456) are exploited in SQL injection attacks. Enforce:

• Minimum 12 characters.
• Mix of uppercase, numbers, symbols.
• Expiry (e.g., every 90 days).

3. Authorization: What You’re Allowed to Do

Authorization answers: "What can User X do?" Controlled via:

• GRANT: Assigns privileges.

  GRANT ALL PRIVILEGES ON accounts TO bank_teller;
  

• REVOKE: Removes privileges.

  REVOKE DELETE ON loans FROM junior_staff;
  

• Default Privileges: Set for new roles/users.

  CREATE ROLE 'auditor' WITH DEFAULT TABLE PRIVILEGES SELECT;
  

Least Privilege Principle

Give users only what they need. Example:

• A traffic officer in Kathmandu’s database only needs to SELECT from fines_issued and UPDATE payment_status.
• Never grant DROP TABLE to app users—only DBAs.

4. Security Threats and Mitigations

sequenceDiagram
    participant User
    participant App
    participant Database
    User->>App: Input: '123 OR 1=1'
    App->>Database: EXECUTE('SELECT * FROM accounts WHERE id = ' + input)
    Database-->>App: All accounts returned (SQL Injection)
    alt Mitigation
        App->>Database: PREPARE('SELECT * FROM accounts WHERE id = ?')
        App->>Database: EXECUTE(123)
        Database-->>App: Only id=123 account
    end

Real Example: Daraz’s Cart System

• Threat: A hacker injects DROP TABLE cart_items; via the "Add to Cart" field.
• Fix: Daraz’s DB uses parameterized queries:

  -- Safe: Parameters are escaped
  PREPARE add_to_cart (INT) AS INSERT INTO cart_items VALUES (?, NOW());
  EXECUTE add_to_cart(12345);
  

5. Advanced Security: Views and Stored Procedures

Views as Security Layers

A view hides columns/tables. Example:

CREATE VIEW customer_orders AS
SELECT customer_id, order_id, order_date
FROM orders
WHERE status = 'completed';

• Benefit: Users can’t see credit_card_numbers in the base table.

Stored Procedures for Controlled Access

Instead of letting users run:

DELETE FROM accounts WHERE customer_id = 123;

Force them to use a procedure:

CREATE PROCEDURE safe_withdrawal(IN customer_id INT, IN amount DECIMAL)
BEGIN
    IF amount > (SELECT balance FROM accounts WHERE customer_id = customer_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    ELSE
        UPDATE accounts SET balance = balance - amount WHERE customer_id = customer_id;
    END IF;
END;

Why? Prevents accidental/deadly DELETE or UPDATE mistakes.

6. Auditing and Monitoring

Track who did what and when:

-- Enable auditing in Oracle
AUDIT SELECT, INSERT ON accounts BY user_id;
-- Check audit logs
SELECT * FROM dba_audit_trail;

Real Use Case: NEPSE’s Trading System

• Audit Trail: Logs every BUY/SELL order with timestamp and user ID.
• Forensics: If a fraudulent trade occurs, admins trace the user via logs.

7. Encryption: Protecting Data at Rest and in Transit

Real Picture:

In the Real World

1. Khalti’s Payment Gateway

   • Idea Used: Multi-factor authentication (MFA) and role-based access.
   • How: Merchants get a merchant_role with SELECT on transactions but no UPDATE rights. Users verify payments via OTP + fingerprint.

2. NMB Bank’s Loan System

   • Idea Used: Stored procedures and least privilege.
   • How: Loan officers run approve_loan(customer_id, amount) instead of direct UPDATE on loan_status. The procedure checks credit scores first.

3. Pathao’s Driver App

   • Idea Used: Views to hide sensitive data.
   • How: Drivers see only ride_id, pickup_location, and fare via a view, not the driver_salary table.

Exam Tip

1. Command-Based Questions (30% weight)

   • Write exact GRANT, REVOKE, and CREATE ROLE commands. Example:

     "Grant SELECT on employees to hr_role but revoke DELETE." Answer:

     GRANT SELECT ON employees TO hr_role;
     REVOKE DELETE ON employees FROM hr_role;
     

2. Scenario Analysis (40% weight)

   • Describe how to secure a real system (e.g., "How would you restrict a Daraz order clerk from viewing customer addresses?").
   • Key Points:
     • Use roles (order_clerk_role).
     • Grant only SELECT on order_items and UPDATE on order_status.
     • Create a view hiding customer_address.

3. Threat Mitigation (20% weight)

   • Match threats (SQL injection, privilege escalation) to fixes (prepared statements, auditing).
   • Example Question:

     "A bank’s DB was hacked via SQL injection. Suggest 2 fixes." Answer:

     1. Use prepared statements for all queries.
     2. Enable column-level permissions (e.g., GRANT SELECT (account_id, balance) ON accounts TO teller;).

4. Diagrams (10% weight)

   • Draw a role-privilege matrix or authentication flow (e.g., MFA steps).
   • Example:

     sequenceDiagram
         User->>DB: Enters username/password
         DB->>User: Requests OTP
         User->>DB: Sends OTP
         DB->>User: Grants access if OTP matches

Final Checklist Before Exam:

• Know GRANT/REVOKE syntax for tables/views/procedures.
• Understand least privilege vs. default privileges.
• Recall 3 authentication methods and their use cases.
• Practice writing a stored procedure for a real scenario (e.g., bank transfer).
• Sketch a role-privilege diagram for a given system (e.g., hospital DB).

In the real world

• Khalti’s Payment Gateway: Uses multi-factor authentication (MFA) where users must enter both a password and a one-time pin (OTP) sent to their phone to authorize transactions, preventing unauthorized access.
• Ncell’s Customer Portal: Implements role-based access control (RBAC) where agents can only view customer bills (view_bills role) while admins have full control (admin role), limiting data exposure to least privilege.
• Daraz’s Cart System: Protects against SQL injection by using parameterized queries for all user inputs, ensuring malicious SQL cannot alter or delete data.