Database Access Control

Proper access control is fundamental to database security. This guide covers implementing role-based access, managing read-only users, establishing connection profiles, and enforcing security policies to protect your databases while enabling productive work.

Access Control Fundamentals

Principle of Least Privilege

Grant users only the minimum permissions necessary to perform their job functions.

Least Privilege Benefits:

• Reduces blast radius of compromised credentials
• Minimizes accidental data damage
• Simplifies security audits
• Improves compliance posture
• Enables precise accountability tracking

Implementation Process:

1. Document each user's actual job requirements
2. Grant read-only access initially
3. Expand permissions only when justified
4. Review periodically and remove unused access
5. Audit changes regularly

Dangerous Anti-Patterns:

• Sharing credentials between team members
• Using administrative accounts for routine work
• Granting broad permissions "just in case"
• Not reviewing access after role changes
• Maintaining access after team transitions

Database User Categories

Design your access control strategy around user categories.

Role-Based Access Control (RBAC)

Designing Your RBAC Schema

Establish a systematic approach to managing roles and permissions.

RBAC Structure Example:

Database: production
├── Organization Role
│   ├── department_sales_read
│   ├── department_marketing_read
│   └── department_finance_read
├── Function Role
│   ├── analyst_read
│   ├── developer_app
│   ├── engineer_etl
│   └── admin_full
└── Data Role
    ├── can_access_customer_pii
    ├── can_access_financial
    ├── can_access_internal_only
    └── can_modify_production

Naming Conventions:

Format: [environment]_[function]_[permission]

Examples:
- dev_analyst_read          (development, analytics, read-only)
- prod_app_readwrite        (production, application, read+write)
- staging_engineer_full     (staging, data engineering, full access)
- prod_admin_emergency      (production, admin, emergency access)

Role Hierarchy

Organize roles hierarchically to simplify administration.

Hierarchy Example:

admin_full
├── developer_app
│   ├── analyst_read
│   └── user_basic
├── engineer_etl
│   ├── analyst_read
│   └── user_basic
└── support_tier1
    └── user_basic

Benefits:

• Inheriting permissions reduces duplication
• Changes to parent roles propagate automatically
• Clear permission hierarchy understood by teams
• Easier onboarding and offboarding

Implementing RBAC

Read-Only Access Management

Creating Safe Read-Only Users

Read-only users are essential for non-destructive database access.

Read-Only User Best Practices:

• Use for analysts, consultants, auditors
• Default to read-only, expand only when justified
• Combine with IP restrictions and time-based limits
• Monitor for suspicious activity patterns
• Rotate credentials regularly

Read-Only Verification

Verification Queries:

-- PostgreSQL: Verify user has no write permissions
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='your_table'
  AND grantee='analyst_user';

-- Should return: SELECT only, not INSERT, UPDATE, DELETE

-- MySQL: Check user privileges
SHOW GRANTS FOR 'analyst_user'@'%';

-- Verify only SELECT is granted

-- MongoDB: List user roles and privileges
db.getUser("analyst_user")
// Verify role contains only read actions

View-Based Read-Only Access

Use views to provide controlled, read-only access to specific data.

Sensitive Data Masking View:

-- Hide sensitive columns for read-only users
CREATE VIEW customers_redacted AS
SELECT
  id,
  name,
  city,
  country,
  '***' as email,           -- Mask email
  '***' as phone,           -- Mask phone
  FALSE as is_premium       -- Hide business logic
FROM customers;

-- Grant read-only access to view only
GRANT SELECT ON customers_redacted TO analyst_readonly;

Time-Series Data View:

-- Provide read-only access to recent data only
CREATE VIEW recent_transactions AS
SELECT *
FROM transactions
WHERE created_at >= NOW() - INTERVAL '90 days';

-- Grant access to view, not underlying table
GRANT SELECT ON recent_transactions TO analyst_readonly;
REVOKE SELECT ON transactions FROM analyst_readonly;

Connection Profile Management

Establishing Connection Profiles

WhoDB connection profiles securely store database credentials and settings.

Connection Profile Best Practices:

• Use environment-specific profiles (dev, staging, prod)
• Never hardcode credentials in configuration files
• Rotate credentials regularly
• Use least privilege database users
• Enable SSL/TLS for all connections
• Document connection purpose and usage

Organizing Connection Profiles

Credential Rotation

Credential Rotation Process:

Step 1: Prepare new credentials
├── Generate new password (16+ chars, mixed case, symbols)
├── Test in development environment first
└── Verify old credentials still work (for rollback)

Step 2: Update database
├── Create new user with same permissions
├── Verify new user can connect
└── Keep old user active temporarily

Step 3: Update applications
├── Update connection string in WhoDB
├── Verify all services connect successfully
├── Monitor logs for connection errors

Step 4: Verify and cleanup
├── Confirm all services use new credentials
├── Run security audit
├── Remove old credentials
└── Document rotation date and approver

Rotation Schedule:

Production Databases:  Quarterly + immediate after separation
Staging Databases:     Semi-annually + after breach suspicion
Development Databases: Annually + after onboarding
Service Accounts:      Quarterly + after vulnerability scan

Security Policies

Data Classification

Classify data by sensitivity to guide access control decisions.

Data Classification Levels:

Classification Process:

1. Audit all tables and columns
2. Document sensitivity level
3. Define access restrictions
4. Implement technical controls
5. Review and update annually

Example Classification:

Table: users
├── id: PUBLIC
├── name: INTERNAL
├── email: SENSITIVE (PII)
├── password_hash: RESTRICTED
└── ssn: RESTRICTED (highly sensitive)

Implementing Access Policies

Audit Logging

What to Log:

• User login/logout events
• Query execution (SELECT, INSERT, UPDATE, DELETE)
• Schema modifications
• Security policy changes
• Failed authentication attempts
• Administrative operations

PostgreSQL Audit Configuration:

-- Install pgAudit extension
CREATE EXTENSION pgaudit;

-- Log all write operations
ALTER SYSTEM SET pgaudit.log = 'write, ddl';

-- Log which tables accessed
ALTER SYSTEM SET pgaudit.log_relation = on;

-- Log statement details
ALTER SYSTEM SET pgaudit.log_statement = off;
ALTER SYSTEM SET pgaudit.log_statement_once = off;

-- Reload configuration
SELECT pg_reload_conf();

Audit Log Review Process:

Daily:
  - Check for failed authentication attempts
  - Review administrative operations
  - Monitor unusual access patterns

Weekly:
  - Analyze access by user and role
  - Identify overprivileged accounts
  - Review data exports

Monthly:
  - Comprehensive access review
  - Compliance verification
  - Detection of suspicious patterns

Quarterly:
  - Formal access audit
  - Recertification of access
  - Policy effectiveness review

Managing Access Lifecycle

User Onboarding

Access Reviews

User Offboarding

Offboarding Checklist:

Effective Date: [departure date]
Employee: [name]

Immediate (Day 0):
  [ ] Disable database user account
  [ ] Revoke all role memberships
  [ ] Disable SSH keys if applicable
  [ ] Remove VPN access
  [ ] Notify security team

Within 24 Hours:
  [ ] Confirm account is disabled
  [ ] Check for running queries/sessions
  [ ] Audit recent query history
  [ ] Document access used over final period

Within 7 Days:
  [ ] Archive credentials securely
  [ ] Document any outstanding work
  [ ] Transfer owned queries to team
  [ ] Update access documentation
  [ ] File security incident if needed

Access Control Checklist

Initial Setup:

• Database roles defined for each user type
• Least privilege principle implemented
• Read-only users created for analysts
• Connection profiles configured securely
• SSL/TLS enabled for all connections

Role Management:

• RBAC hierarchy established
• Default permissions set
• New table permissions automated
• Role documentation complete
• Service accounts use least privilege

Security Policies:

• Data classified by sensitivity
• Column-level security implemented
• Row-level security configured
• IP restrictions enforced
• Audit logging enabled

Ongoing Maintenance:

• Quarterly access reviews scheduled
• Credential rotation calendar maintained
• Dormant accounts identified monthly
• Audit logs reviewed regularly
• Policy violations investigated

Incident Response:

• Breach response procedures documented
• Escalation path defined
• Audit log preservation process established
• Forensic analysis capabilities in place
• Communication template prepared

Summary

Robust access control requires careful planning, consistent implementation, and ongoing maintenance. Use database roles, dedicated credentials, network controls, and database-native audit logs to protect your data while keeping WhoDB useful for daily inspection and query workflows. Access control is not a one-time setup; review it as team members, environments, and threats change.