SWE-3003: Insufficient Permission Granularity in Schema

Description

Schema designs that lack proper permission granularity, allowing broad access to data through single permission grants. This includes friend-of-friend access patterns, overly permissive API endpoints, missing role-based access control (RBAC) tables, or schemas that assume application-level security without database-level enforcement. The weakness often stems from prioritizing ease of implementation over proper security boundaries.

Illustrative Cantor Point

The Cantor Point occurs when designing access control - choosing between "simple but permissive" versus "complex but secure" permission models. The decision to allow transitive access (if A grants permission, B's friends get access too) or to rely solely on application-level checks creates a divergent path where data exposure grows exponentially with system usage.

Categories: CP-Schema, CP-API, CP-Process

Real-World Examples / Observed In

Facebook Graph API (2018): Allowed apps to access friends' data without explicit consent, enabling Cambridge Analytica to harvest 87 million profiles [See: Cases-By-Year/2018 Data Integrity Failures.md#2]
India's Aadhaar (2018): APIs with minimal authentication exposed personal data of over 1 billion citizens through convenience endpoints [See: Cases-By-Year/2018 Data Integrity Failures.md#4]
Common Pattern: "Admin" boolean flag instead of proper role tables, leading to privilege escalation

Common Consequences & Impacts

Technical Impacts

- Mass data harvesting through legitimate APIs
- Difficulty retrofitting proper permissions
- Complex permission auditing
- Performance issues from permission checks

Human/Ethical Impacts

- Privacy violations at scale
- Unauthorized data usage
- Identity theft risk
- Democratic process interference

Business Impacts

- Regulatory violations (GDPR, CCPA)
- Loss of user trust
- Legal liability
- Reputational damage

Recovery Difficulty & Escalation

Recovery Difficulty:

6

Escalation Risk:

8

ADI Principles & Axioms Violated

Principle of Trust Insufficiency: Systems were trusted beyond their security guarantees
Principle of Ethical Debt Accumulation: Convenience choices created massive privacy debt

Detection / 60-Second Audit

-- Check for overly simple permission models
SELECT 
    table_name,
    column_name,
    data_type,
    (SELECT COUNT(*) FROM pg_class WHERE relname = table_name) as table_exists
FROM information_schema.columns
WHERE table_schema = 'public'
AND column_name IN ('is_admin', 'admin', 'superuser', 'is_superuser')
AND data_type = 'boolean';

-- Look for missing RBAC tables
SELECT 
    'roles' as expected_table,
    EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'roles') as exists,
    'permissions' as expected_table2,
    EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'permissions') as exists2
UNION ALL
SELECT 
    'user_roles',
    EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'user_roles'),
    'role_permissions',
    EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'role_permissions');

-- Check for row-level security
SELECT 
    schemaname,
    tablename,
    rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
AND tablename IN (SELECT table_name FROM information_schema.columns WHERE column_name LIKE '%personal%' OR column_name LIKE '%private%');

-- Check for overly simple permission models
SELECT 
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND column_name IN ('is_admin', 'admin', 'superuser', 'is_superuser')
AND data_type IN ('tinyint', 'boolean', 'bit');

-- Look for missing RBAC tables
SELECT 
    'RBAC Check' as check_type,
    GROUP_CONCAT(table_name) as existing_rbac_tables
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name IN ('roles', 'permissions', 'role_permissions', 'user_roles');

-- Check for API access without controls
SELECT 
    t.table_name,
    CASE 
        WHEN c.column_name IS NULL THEN 'Missing rate limit controls'
        ELSE 'Has controls'
    END as status
FROM information_schema.tables t
LEFT JOIN information_schema.columns c
    ON t.table_name = c.table_name
    AND c.column_name IN ('rate_limit', 'request_count', 'last_request')
WHERE t.table_schema = DATABASE()
AND (t.table_name LIKE '%api%' OR t.table_name LIKE '%token%');

-- Check for overly simple permission models
SELECT 
    t.name as table_name,
    c.name as column_name,
    TYPE_NAME(c.user_type_id) as data_type
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name IN ('is_admin', 'admin', 'superuser', 'is_superuser')
AND TYPE_NAME(c.user_type_id) = 'bit';

-- Check for RBAC implementation
SELECT 
    'RBAC Tables' as check_type,
    STRING_AGG(name, ', ') as found_tables
FROM sys.tables
WHERE name IN ('roles', 'permissions', 'role_permissions', 'user_roles');

-- Check for row-level security policies
SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) as schema_name,
    OBJECT_NAME(p.object_id) as table_name,
    p.name as policy_name,
    p.type_desc
FROM sys.security_policies p;

Prevention & Mitigation Best Practices

Implement Proper RBAC:

CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE permissions (
    id SERIAL PRIMARY KEY,
    resource VARCHAR(100) NOT NULL,
    action VARCHAR(50) NOT NULL,
    UNIQUE(resource, action)
);

CREATE TABLE role_permissions (
    role_id INTEGER REFERENCES roles(id),
    permission_id INTEGER REFERENCES permissions(id),
    PRIMARY KEY (role_id, permission_id)
);

Explicit Consent Model: Never assume transitive permissions
Row-Level Security: Use database RLS features when available
API Rate Limiting: Prevent mass data extraction
Audit Logging: Track all permission grants and data access
Principle of Least Privilege: Default to minimal access
Regular Permission Reviews: Audit who has access to what

Real World Examples

• Initial design allowed apps to access friends' data if one friend consented
• Cambridge Analytica harvested 87 million profiles through just 270,000 app users
• The schema allowed transitive access: If user A grants permission, app gets data for users B, C, D (A's friends)
• Cost: $5 billion FTC fine, congressional hearings, global privacy law changes
• Root cause: Permission model assumed friend relationships implied data sharing consent

-- Common but dangerous pattern
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255),
    is_admin BOOLEAN DEFAULT false
);

-- Vulnerability: Single SQL injection can escalate privileges
-- UPDATE users SET is_admin = true WHERE email = 'attacker@email.com';

-- No audit trail, no granular permissions, no way to limit scope

-- Granular permission system
CREATE TABLE permissions (
    id SERIAL PRIMARY KEY,
    resource VARCHAR(100) NOT NULL,
    action VARCHAR(50) NOT NULL,
    UNIQUE(resource, action)
);

CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE role_permissions (
    role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
    permission_id INTEGER REFERENCES permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE user_roles (
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
    granted_at TIMESTAMP DEFAULT NOW(),
    granted_by BIGINT REFERENCES users(id),
    expires_at TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

-- Audit trail for all permission changes
CREATE TABLE permission_audit (
    id SERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    action VARCHAR(50) NOT NULL,
    target_user_id BIGINT,
    role_id INTEGER,
    permission_id INTEGER,
    timestamp TIMESTAMP DEFAULT NOW(),
    ip_address INET,
    user_agent TEXT
);

-- Row-level security for sensitive data
ALTER TABLE user_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_data_access ON user_data
    FOR ALL
    USING (
        user_id = current_setting('app.current_user_id')::BIGINT
        OR EXISTS (
            SELECT 1 FROM user_roles ur
            JOIN role_permissions rp ON ur.role_id = rp.role_id
            JOIN permissions p ON rp.permission_id = p.id
            WHERE ur.user_id = current_setting('app.current_user_id')::BIGINT
            AND p.resource = 'user_data'
            AND p.action = 'read_any'
        )
    );

AI Coding Guidance/Prompt

Prompt: "When designing access control schemas:"
Rules:
  - Flag any boolean 'is_admin' fields
  - Require explicit RBAC tables for any multi-user system
  - Warn against transitive permission patterns
  - Suggest row-level security for sensitive data
  - Require audit tables for permission changes
  
Example:
  # Bad: Simple boolean admin flag
  CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255),
    is_admin BOOLEAN DEFAULT false
  );
  
  # Good: Proper RBAC with audit trail
  CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255)
  );
  
  CREATE TABLE user_roles (
    user_id BIGINT REFERENCES users(id),
    role_id INTEGER REFERENCES roles(id),
    granted_at TIMESTAMP DEFAULT NOW(),
    granted_by BIGINT REFERENCES users(id),
    PRIMARY KEY (user_id, role_id)
  );
  
  -- Row-level security example
  CREATE POLICY user_data_access ON sensitive_data
    FOR SELECT
    USING (
      user_id = current_user_id() 
      OR EXISTS (
        SELECT 1 FROM user_permissions
        WHERE user_id = current_user_id()
        AND resource = 'sensitive_data'
        AND action = 'read'
      )
    );

Relevant Keywords

insufficient permission granularity schema Symptoms: slow queries, data inconsistency, constraint violations Preventive: schema validation, constraint enforcement, proper typing Tech stack: PostgreSQL, MySQL, SQL Server, Oracle Industry: all industries, enterprise, SaaS