Database Administration & Roles: DBA Tasks, Security, Backup, User Roles & Auditing

TAKEAWAYS:

• A Database Administrator (DBA) manages database performance, security, and integrity, ensuring data availability, consistency, and recovery in case of failures.
• User roles and permissions (e.g., DBA, DEVELOPER, USER) control access to database objects, following the principle of least privilege to minimize security risks.
• Backup and recovery strategies (full, incremental, differential) protect against data loss, with point-in-time recovery enabling restoration to a specific transaction state.
• Security measures include encryption (TDE, SSL), authentication (password policies, biometrics), and authorization (row-level security, views).
• Database auditing tracks user activities, detects anomalies, and ensures compliance with regulations like GDPR or Nepal’s Electronic Transaction Act, 2008.
• Performance tuning involves indexing, query optimization, and hardware upgrades to reduce latency in high-traffic systems like NEPSE’s stock trading database.

1. Role of a Database Administrator (DBA)

A DBA is responsible for the design, implementation, maintenance, and security of a database system. Their tasks include:

• User management: Creating, modifying, and revoking user accounts and roles.
• Security enforcement: Implementing access controls, encryption, and auditing.
• Backup and recovery: Ensuring data durability and quick restoration in case of failures.
• Performance optimization: Tuning queries, indexing, and hardware configuration.
• Compliance and auditing: Ensuring the database adheres to legal and organizational policies.

Key Responsibilities of a DBA

2. User Management and Roles

Databases use a role-based access control (RBAC) model to assign permissions efficiently. Roles group related privileges, reducing administrative overhead.

Common Database Roles

How Roles Work in SQL (Example)

-- Create a role for financial analysts
CREATE ROLE financial_analyst;

-- Grant specific permissions to the role
GRANT SELECT, INSERT ON Accounts TO financial_analyst;
GRANT EXECUTE ON sp_calculate_interest TO financial_analyst;

-- Assign the role to a user
GRANT financial_analyst TO john_doe;

Principle of Least Privilege

• Users should have only the minimum permissions required to perform their tasks.
• Example: A Pathao driver should only access their trip history, not other drivers’ data.

3. Security in Databases

Security ensures confidentiality, integrity, and availability (CIA triad) of data.

Security Measures

Real-World Example: eSewa Security

• Authentication: Users log in with username + password + OTP.
• Authorization: Customers can only view/transfer their own money.
• Encryption: All transactions are SSL-encrypted during transfer.
• Auditing: Every transaction is logged for fraud detection.

4. Backup and Recovery Strategies

Databases must recover from failures (hardware crashes, cyberattacks, human errors). Common strategies:

Backup Types

Recovery Models

Worked Example: NEPSE Database Recovery

• Scenario: A power outage corrupts NEPSE’s trading database at 3:00 PM.
• Solution:
  1. Restore the last full backup (taken at midnight).
  2. Apply incremental backups from 12:00 PM to 2:00 PM.
  3. Use point-in-time recovery to roll forward to 2:59 PM (just before the crash).
• Result: Traders can resume trading with minimal data loss.

5. Database Auditing

Auditing tracks who did what, when, and from where, ensuring compliance and detecting fraud.

Auditing Components

SQL Example: Enabling Auditing in SQL Server

-- Enable server-level auditing
CREATE SERVER AUDIT DatabaseActivityAudit
TO FILE (FILEPATH = 'C:\Audits\');

-- Create an audit specification for login failures
CREATE SERVER AUDIT SPECIFICATION LoginFailuresAudit
FOR SERVER AUDIT DatabaseActivityAudit
ADD (FAILED_LOGIN_GROUP);

-- Enable the audit
ALTER SERVER AUDIT DatabaseActivityAudit WITH (STATE = ON);
ALTER SERVER AUDIT SPECIFICATION LoginFailuresAudit WITH (STATE = ON);

Real-World Example: NTC’s Auditing

• Purpose: Detect unauthorized access to billing records.
• Mechanism:
  • Logs all UPDATE and DELETE operations on customer data.
  • Alerts admins if a single user makes >100 changes in 1 hour (potential fraud).
• Outcome: Prevented a ₹50 million billing fraud in 2022.

6. Performance Tuning

DBAs optimize databases to handle high traffic (e.g., NEPSE during trading hours).

Key Tuning Techniques

Worked Example: Optimizing a Slow Query

Problem: A query to fetch all orders for a Daraz customer takes 5 seconds.

SELECT * FROM Orders WHERE customer_id = 12345;

Solution:

1. Add an index on customer_id:

   CREATE INDEX idx_customer_id ON Orders(customer_id);
   

2. Use a covering index (includes only needed columns):

   CREATE INDEX idx_customer_order_date ON Orders(customer_id, order_date) INCLUDE (order_amount);
   

3. Result: Query now takes 20 ms.

7. Three-Schema Architecture

Databases use a three-level architecture to achieve logical and physical data independence.

The Three Schemas

classDiagram
    class ExternalSchema {
        +Views tailored for specific users/apps
        +Example: eSewa’s "My Transactions" view
    }
    class ConceptualSchema {
        +Logical structure (tables, relationships)
        +Example: NEPSE’s "Stocks" and "Traders" tables
    }
    class InternalSchema {
        +Physical storage details (files, indexes)
        +Example: SQL Server’s .mdf and .ldf files
    }
    ExternalSchema --> ConceptualSchema : "Maps to"
    ConceptualSchema --> InternalSchema : "Maps to"

How It Works

1. External Schema (User View):
   • Presents data in a user-friendly format (e.g., SELECT * FROM MyTransactions in eSewa).
   • Hides complexity (e.g., joins across tables).
2. Conceptual Schema (Logical Design):
   • Defines tables, relationships, and constraints (e.g., FOREIGN KEY in NEPSE’s Trades table).
3. Internal Schema (Physical Storage):
   • Details how data is stored (e.g., B-trees for indexes, RAID for redundancy).

Advantages

• Logical Independence: Changing the conceptual schema (e.g., adding a StockPriceHistory table) does not affect external schemas.
• Physical Independence: Upgrading storage (e.g., from HDD to SSD) does not require rewriting queries.

8. Database Administration Tools

DBAs use GUI tools and command-line utilities to manage databases efficiently.

Popular Tools

In the Real World

1. eSewa’s Database Administration

   • User Roles: Customers (USER), merchants (MERCHANT), and admins (DBA).
   • Security: TDE (Transparent Data Encryption) protects payment data at rest.
   • Backup: Automated daily backups with point-in-time recovery for fraud investigations.
   • Auditing: Every transaction is logged for NRA (Nepal Rastra Bank) compliance.

2. NEPSE’s Trading Database

   • Performance Tuning: Partitioning by stock symbol reduces query time during high volatility.
   • Recovery: Full backup at midnight, incremental every hour to handle crashes during trading hours.
   • Security: Role-based access ensures traders can only view/modify their own orders.

3. Ncell’s Customer Database

   • Indexing: Composite index on customer_id and plan_id speeds up bill generation.
   • Auditing: Triggers log all SIM activations/deactivations to prevent fraud.
   • Disaster Recovery: Replicated across 3 data centers (Kathmandu, Pokhara, Biratnagar).

Exam Tip

1. Define Key Terms Clearly:

   • Always start with definitions (e.g., "A DBA is responsible for...").
   • Example answer for "What is database auditing?":

     "Database auditing is the process of tracking and logging user activities (e.g., SQL queries, data modifications) to ensure compliance, detect anomalies, and maintain data integrity. It typically involves server audits, triggers, and log analysis."

2. Use SQL Examples:

   • Exams often ask for SQL commands (e.g., CREATE ROLE, GRANT, BACKUP DATABASE).
   • Practice writing queries for:
     • Creating roles (CREATE ROLE).
     • Assigning permissions (GRANT).
     • Enabling auditing (CREATE SERVER AUDIT).

3. Compare Backup Strategies:

   • Be ready to contrast full, incremental, and differential backups in terms of:
     • Storage space (full > differential > incremental).
     • Recovery time (full is slowest; incremental is fastest for point-in-time recovery).
   • Example: "A bank should use incremental backups for daily transactions and a weekly full backup to balance storage and recovery speed."

4. Relate to Real-World Scenarios:

   • Questions may ask: "How would you secure eSewa’s database?" or "Design a backup strategy for NEPSE."
   • Structure your answer:
     1. Identify the system (e.g., eSewa’s payment database).
     2. List threats (e.g., SQL injection, data breaches, hardware failure).
     3. Propose solutions (e.g., role-based access, encryption, automated backups).
     4. Justify choices (e.g., "Encryption ensures GDPR compliance").

5. Three-Schema Architecture:

   • Always draw the class diagram (as above) and explain:
     • How external schemas hide complexity.
     • How conceptual schemas define relationships.
     • How internal schemas handle storage.

6. Performance Tuning:

   • If asked about optimizing a slow query, follow this template:
     1. Analyze the query (use EXPLAIN in MySQL/PostgreSQL).
     2. Identify bottlenecks (e.g., missing indexes, full table scans).
     3. Propose fixes (e.g., add indexes, rewrite joins).
     4. Test and measure (compare execution times before/after).

Three-schema architecture with external, conceptual, and internal layers. (Image: Original: Matthew West and Julian Fowler Vector: Razorbliss, CC BY-SA 3.0, via Wikimedia Commons)

sequenceDiagram
    participant User
    participant ExternalSchema
    participant ConceptualSchema
    participant InternalSchema
    participant Storage

    User->>ExternalSchema: Queries "My Transactions" (eSewa view)
    ExternalSchema->>ConceptualSchema: Translates to SQL (JOINs hidden)
    ConceptualSchema->>InternalSchema: Maps to tables/indexes
    InternalSchema->>Storage: Reads from SSD/RAM
    Storage-->>InternalSchema: Returns data
    InternalSchema-->>ConceptualSchema: Applies constraints
    ConceptualSchema-->>ExternalSchema: Formats results
    ExternalSchema-->>User: Displays simplified view

erDiagram
    DBA ||--o{ User : "manages"
    User ||--o{ Role : "has"
    Role ||--|{ Permission : "includes"
    Permission }|--|| Table : "applies_to"
    Table }|--|| Index : "has"
    DBA }|--|| AuditLog : "monitors"