c77_rbac/USAGE-P2.md

# c77_rbac Usage Guide - Part 2: Advanced Usage Scenarios

This is Part 2 of the comprehensive c77_rbac usage guide, covering advanced scenarios and time-based permissions.

**Complete Guide Structure:**
- Part 1: Core Concepts and Basic Usage
- **Part 2: Advanced Usage Scenarios** (this document)
- Part 3: Framework Integration (Laravel, Django, Rails)
- Part 4: Real-World Examples and Performance Optimization
- Part 5: Security Best Practices and Troubleshooting

## Table of Contents

1. [Advanced Usage Scenarios](#advanced-usage-scenarios)
2. [Bulk Operations for Large Organizations](#bulk-operations-for-large-organizations)

## Advanced Usage Scenarios

### Scenario 1: Time-Based Permissions

Implementing temporary access that expires automatically.

```sql
-- Step 1: Create temporary access tracking table
CREATE TABLE temporary_access (
    id SERIAL PRIMARY KEY,
    user_id TEXT NOT NULL,
    role_name TEXT NOT NULL,
    scope_type TEXT NOT NULL,
    scope_id TEXT NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Grant temporary access
CREATE OR REPLACE FUNCTION grant_temporary_access(
    p_user_id TEXT,
    p_role TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT,
    p_duration_hours INTEGER
) RETURNS INTEGER AS $$
DECLARE
    temp_id INTEGER;
BEGIN
    -- Record the temporary access
    INSERT INTO temporary_access (user_id, role_name, scope_type, scope_id, expires_at)
    VALUES (p_user_id, p_role, p_scope_type, p_scope_id, 
            CURRENT_TIMESTAMP + (p_duration_hours || ' hours')::INTERVAL)
    RETURNING id INTO temp_id;
    
    -- Grant the role
    PERFORM public.c77_rbac_assign_subject(p_user_id, p_role, p_scope_type, p_scope_id);
    
    RETURN temp_id;
END;
$$ LANGUAGE plpgsql;

-- Step 3: Cleanup expired access (run via cron job)
CREATE OR REPLACE FUNCTION cleanup_expired_access() RETURNS INTEGER AS $$
DECLARE
    expired_record RECORD;
    cleanup_count INTEGER := 0;
BEGIN
    FOR expired_record IN 
        SELECT * FROM temporary_access WHERE expires_at < CURRENT_TIMESTAMP
    LOOP
        -- Remove the role assignment
        PERFORM public.c77_rbac_revoke_subject_role(
            expired_record.user_id,
            expired_record.role_name,
            expired_record.scope_type,
            expired_record.scope_id
        );
        
        -- Remove tracking record
        DELETE FROM temporary_access WHERE id = expired_record.id;
        
        cleanup_count := cleanup_count + 1;
    END LOOP;
    
    RETURN cleanup_count;
END;
$$ LANGUAGE plpgsql;

-- Usage example:
-- Grant user 'temp_contractor' admin access to project 'urgent_fix' for 24 hours
SELECT grant_temporary_access('temp_contractor', 'project_admin', 'project', 'urgent_fix', 24);
```

### Scenario 2: Conditional Permissions

Permissions that depend on data state or business rules.

```sql
-- Step 1: Create business rule function
CREATE OR REPLACE FUNCTION can_edit_document(
    p_user_id TEXT,
    p_document_id INTEGER
) RETURNS BOOLEAN AS $$
DECLARE
    doc_status TEXT;
    doc_author TEXT;
    user_dept TEXT;
    doc_dept TEXT;
BEGIN
    -- Get document info
    SELECT status, author_id, department_id 
    INTO doc_status, doc_author, doc_dept
    FROM documents WHERE id = p_document_id;
    
    -- Authors can always edit their draft documents
    IF doc_author = p_user_id AND doc_status = 'draft' THEN
        RETURN TRUE;
    END IF;
    
    -- Department editors can edit department documents
    IF public.c77_rbac_can_access('edit_dept_documents', p_user_id, 'department', doc_dept) THEN
        RETURN TRUE;
    END IF;
    
    -- Published documents can only be edited by administrators
    IF doc_status = 'published' THEN
        RETURN public.c77_rbac_can_access('edit_published_documents', p_user_id, 'global', 'all');
    END IF;
    
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Step 2: Create conditional RLS policy
CREATE POLICY documents_conditional_edit ON documents
    FOR UPDATE
    USING (can_edit_document(current_setting('c77_rbac.external_id', true)::TEXT, id));

-- Step 3: Enable RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
```

### Scenario 3: Dynamic Role Assignment

Automatically assign roles based on user attributes or actions.

```sql
-- Step 1: Create dynamic role assignment function
CREATE OR REPLACE FUNCTION assign_roles_on_user_update() 
RETURNS TRIGGER AS $$
BEGIN
    -- Remove old role assignments
    PERFORM public.c77_rbac_revoke_subject_role(
        OLD.id::TEXT, 'dept_member', 'department', OLD.department_id
    );
    
    -- Assign new department role
    PERFORM public.c77_rbac_assign_subject(
        NEW.id::TEXT, 'dept_member', 'department', NEW.department_id
    );
    
    -- Auto-promote to manager if they manage others
    IF EXISTS (SELECT 1 FROM users WHERE manager_id = NEW.id) THEN
        PERFORM public.c77_rbac_assign_subject(
            NEW.id::TEXT, 'manager', 'department', NEW.department_id
        );
    END IF;
    
    -- Auto-assign admin role if they're in IT department
    IF NEW.department_id = 'IT' THEN
        PERFORM public.c77_rbac_assign_subject(
            NEW.id::TEXT, 'it_admin', 'global', 'all'
        );
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Step 2: Create trigger
CREATE TRIGGER user_role_sync 
    AFTER INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION assign_roles_on_user_update();
```

### Scenario 4: Context-Aware Permissions

Permissions that vary based on current system state or user context.

```sql
-- Example: Users can only edit their own posts during business hours
CREATE OR REPLACE FUNCTION can_edit_post_with_context(
    p_user_id TEXT,
    p_post_id INTEGER
) RETURNS BOOLEAN AS $$
DECLARE
    post_author TEXT;
    current_hour INTEGER;
BEGIN
    -- Get post author
    SELECT author_id INTO post_author FROM posts WHERE id = p_post_id;
    
    -- Admins can always edit
    IF public.c77_rbac_can_access('admin_edit_posts', p_user_id, 'global', 'all') THEN
        RETURN TRUE;
    END IF;
    
    -- Check if it's the author
    IF post_author != p_user_id THEN
        RETURN FALSE;
    END IF;
    
    -- Check business hours (9 AM to 5 PM)
    current_hour := EXTRACT(HOUR FROM CURRENT_TIME);
    IF current_hour < 9 OR current_hour >= 17 THEN
        RETURN FALSE; -- Outside business hours
    END IF;
    
    -- Check if user has regular edit permission
    RETURN public.c77_rbac_can_access('edit_own_posts', p_user_id, 'global', 'all');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply conditional policy
CREATE POLICY posts_context_edit ON posts
    FOR UPDATE
    USING (can_edit_post_with_context(current_setting('c77_rbac.external_id', true)::TEXT, id));
```

## Bulk Operations for Large Organizations

### Efficiently Managing Thousands of Users

```sql
-- Step 1: Bulk assign all employees to basic role
SELECT * FROM public.c77_rbac_bulk_assign_subjects(
    (SELECT array_agg(id::TEXT) FROM users WHERE employment_status = 'active'),
    'employee',
    'global',
    'all'
);

-- Step 2: Bulk assign department-specific roles
DO $$
DECLARE
    dept_record RECORD;
    user_ids TEXT[];
BEGIN
    FOR dept_record IN SELECT DISTINCT department_id FROM users
    LOOP
        -- Get all users in this department
        SELECT array_agg(id::TEXT) INTO user_ids 
        FROM users WHERE department_id = dept_record.department_id;
        
        -- Bulk assign department role
        PERFORM public.c77_rbac_bulk_assign_subjects(
            user_ids,
            'dept_member',
            'department',
            dept_record.department_id
        );
        
        RAISE NOTICE 'Assigned % users to department %', 
            array_length(user_ids, 1), dept_record.department_id;
    END LOOP;
END $$;

-- Step 3: Bulk promote managers
SELECT * FROM public.c77_rbac_bulk_assign_subjects(
    (SELECT array_agg(DISTINCT manager_id::TEXT) 
     FROM users WHERE manager_id IS NOT NULL),
    'manager',
    'global',
    'all'
);
```

### Batch Processing for Performance

```sql
-- Process users in batches to avoid memory issues
CREATE OR REPLACE FUNCTION bulk_assign_by_batches(
    p_role_name TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT,
    p_batch_size INTEGER DEFAULT 1000
) RETURNS INTEGER AS $$
DECLARE
    total_users INTEGER;
    processed_users INTEGER := 0;
    batch_start INTEGER := 1;
    batch_end INTEGER;
    user_batch TEXT[];
BEGIN
    -- Get total count
    SELECT count(*) INTO total_users FROM users WHERE employment_status = 'active';
    
    WHILE processed_users < total_users LOOP
        -- Calculate batch boundaries
        batch_end := LEAST(batch_start + p_batch_size - 1, total_users);
        
        -- Get batch of user IDs
        SELECT array_agg(id::TEXT) INTO user_batch
        FROM (
            SELECT id FROM users 
            WHERE employment_status = 'active'
            ORDER BY id
            OFFSET (batch_start - 1) 
            LIMIT p_batch_size
        ) batch_users;
        
        -- Process batch
        PERFORM public.c77_rbac_bulk_assign_subjects(
            user_batch, p_role_name, p_scope_type, p_scope_id
        );
        
        processed_users := processed_users + array_length(user_batch, 1);
        batch_start := batch_end + 1;
        
        RAISE NOTICE 'Processed % of % users', processed_users, total_users;
    END LOOP;
    
    RETURN processed_users;
END;
$$ LANGUAGE plpgsql;

-- Usage: Process all users in batches of 500
SELECT bulk_assign_by_batches('employee', 'global', 'all', 500);
```

### Bulk Role Transitions

```sql
-- Example: Promote all senior employees to manager role
CREATE OR REPLACE FUNCTION promote_senior_employees() RETURNS INTEGER AS $$
DECLARE
    senior_employee_ids TEXT[];
    promotion_count INTEGER;
BEGIN
    -- Find senior employees (example criteria)
    SELECT array_agg(id::TEXT) INTO senior_employee_ids
    FROM users 
    WHERE employment_status = 'active'
    AND hire_date < CURRENT_DATE - INTERVAL '5 years'
    AND salary > 75000
    AND NOT EXISTS (
        SELECT 1 FROM c77_rbac_subjects s
        JOIN c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
        JOIN c77_rbac_roles r ON sr.role_id = r.role_id
        WHERE s.external_id = users.id::TEXT
        AND r.name = 'manager'
    );
    
    IF senior_employee_ids IS NOT NULL THEN
        -- Bulk promote to manager
        SELECT count(*) INTO promotion_count
        FROM public.c77_rbac_bulk_assign_subjects(
            senior_employee_ids,
            'manager',
            'department',
            'all'  -- or specific department logic
        ) WHERE success = true;
        
        RAISE NOTICE 'Promoted % senior employees to manager role', promotion_count;
        RETURN promotion_count;
    ELSE
        RAISE NOTICE 'No senior employees found for promotion';
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;
```

### Bulk Cleanup Operations

```sql
-- Remove roles from inactive users
CREATE OR REPLACE FUNCTION cleanup_inactive_users() RETURNS INTEGER AS $$
DECLARE
    inactive_user_ids TEXT[];
    cleanup_count INTEGER := 0;
    user_id TEXT;
BEGIN
    -- Find inactive users
    SELECT array_agg(id::TEXT) INTO inactive_user_ids
    FROM users 
    WHERE employment_status = 'inactive'
    OR last_login < CURRENT_DATE - INTERVAL '6 months';
    
    -- Remove all role assignments for inactive users
    FOREACH user_id IN ARRAY inactive_user_ids LOOP
        DELETE FROM public.c77_rbac_subject_roles sr
        WHERE sr.subject_id = (
            SELECT subject_id FROM public.c77_rbac_subjects 
            WHERE external_id = user_id
        );
        
        GET DIAGNOSTICS cleanup_count = cleanup_count + ROW_COUNT;
    END LOOP;
    
    RAISE NOTICE 'Removed % role assignments from inactive users', cleanup_count;
    RETURN cleanup_count;
END;
$$ LANGUAGE plpgsql;
```

---

**Continue to [Part 3: Framework Integration](USAGE-Part3.md)** for detailed Laravel, Django, and Rails integration examples.