Part 3: Solving Permissions and RBAC in Cloned Databases

CREATE DATABASE dev_project_db CLONE production_db;
SHOW GRANTS ON DATABASE dev_project_db;

| privilege | grantee_name   |
|-----------|----------------|
| OWNERSHIP | PROD_ADMIN     |  ⚠️ Wrong environment!
| USAGE     | PROD_READ_ONLY |  ⚠️ Dev needs different roles

Stage 1: Temporary Ownership Transfer
   ↓
Stage 2: Permission Cleanup
   ↓
Stage 3: Dynamic Role Creation
   ↓
Stage 4: RBAC Mapping Application

// Pseudocode for understanding
for each schema in clone_database:
    GRANT OWNERSHIP ON SCHEMA to SERVICE_ROLE COPY CURRENT GRANTS

    for each object_type in [TABLES, VIEWS, PROCEDURES, ...]:
        GRANT OWNERSHIP ON ALL object_type to SERVICE_ROLE COPY CURRENT GRANTS

CALL sp_grant_temp_ownership('dev_project_db');

-- Result:
-- {
--   "schemas_granted": 4,
--   "objects_transferred": 1250,
--   "errors": []
-- }

-- Example: Revoke future ownership from production roles
REVOKE OWNERSHIP ON FUTURE TABLES IN SCHEMA dev_db.analytics 
  FROM ROLE PROD_ANALYTICS_OWNER;

// Pseudocode
for each schema in schemas:
    prod_role = source_database + schema_owner_suffix

    for each object_type in [TABLES, VIEWS, PROCEDURES, ...]:
        REVOKE OWNERSHIP ON FUTURE object_type IN SCHEMA 
          FROM ROLE prod_role

<DATABASE>_<SCHEMA>_<LEVEL>

Examples:
DEV_PROJECT_DB_ANALYTICS_READ
DEV_PROJECT_DB_ANALYTICS_READ_WRITE
DEV_PROJECT_DB_DATA_ADMIN

// Simplified version for understanding
for each schema in schemas:
    for each access_level in [READ, READ_WRITE, ADMIN]:
        role_name = clone_db + "_" + schema + "_" + access_level

        CREATE ROLE IF NOT EXISTS role_name
        GRANT USAGE ON DATABASE to role_name
        GRANT USAGE ON SCHEMA to role_name

        if (access_level == READ):
            GRANT SELECT ON ALL TABLES to role_name
            GRANT SELECT ON FUTURE TABLES to role_name
        else if (access_level == READ_WRITE):
            GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES to role_name
        else if (access_level == ADMIN):
            GRANT ALL ON SCHEMA to role_name

CALL sp_create_clone_roles(
    'DEV_PROJECT_DB',
    ARRAY_CONSTRUCT('ADMINISTRATION', 'ANALYTICS', 'DATA')
);

-- Result:
-- {
--   "roles_created": [
--     "DEV_PROJECT_DB_ADMINISTRATION_READ",
--     "DEV_PROJECT_DB_ADMINISTRATION_READ_WRITE",
--     "DEV_PROJECT_DB_ADMINISTRATION_ADMIN",
--     "DEV_PROJECT_DB_ANALYTICS_READ",
--     "DEV_PROJECT_DB_ANALYTICS_READ_WRITE",
--     "DEV_PROJECT_DB_ANALYTICS_ADMIN",
--     "DEV_PROJECT_DB_DATA_READ",
--     "DEV_PROJECT_DB_DATA_READ_WRITE",
--     "DEV_PROJECT_DB_DATA_ADMIN"
--   ],
--   "total_created": 9
-- }

CLONE ROLE                           FUNCTIONAL ROLE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
DEV_PROJECT_DB_ANALYTICS_READ   →    DATA_ANALYST_ROLE
DEV_PROJECT_DB_ANALYTICS_WRITE  →    DATA_ENGINEER_ROLE  
DEV_PROJECT_DB_ANALYTICS_ADMIN  →    PROJECT_ADMIN_ROLE

CREATE TABLE rbac_mapping (
    schema_role VARCHAR(255),         -- Clone-specific role
    environment VARCHAR(40),           -- DEV, QA, STAGING
    functional_role VARCHAR(255),     -- User's actual role
    operation VARCHAR(40),            -- GRANT or REVOKE
    execute_indicator VARCHAR DEFAULT 'Y'
);

-- Example mappings with placeholder
INSERT INTO rbac_mapping (schema_role, environment, functional_role, operation)
VALUES 
    ('{{CLONE_DB}}_ANALYTICS_READ', 'DEV', 'DATA_ANALYST_ROLE', 'GRANT'),
    ('{{CLONE_DB}}_ANALYTICS_WRITE', 'DEV', 'DATA_ENGINEER_ROLE', 'GRANT'),
    ('{{CLONE_DB}}_ANALYTICS_ADMIN', 'DEV', 'PROJECT_ADMIN_ROLE', 'GRANT');

// Pseudocode
mappings = SELECT * FROM rbac_mapping WHERE environment = target_env

for each mapping in mappings:
    schema_role = mapping.schema_role.replace('{{CLONE_DB}}', actual_clone_db)

    if mapping.operation == 'GRANT':
        GRANT ROLE schema_role TO ROLE mapping.functional_role
    else:
        REVOKE ROLE schema_role FROM ROLE mapping.functional_role

CALL sp_apply_rbac_mapping('DEV_PROJECT_DB', 'DEV');

-- Now developers can use their normal roles:
USE ROLE DATA_ANALYST_ROLE;
SELECT * FROM dev_project_db.analytics.customer_summary;  -- ✅ Works!

CALL sp_setup_clone_permissions(
    clone_db => 'DEV_PROJECT_DB',
    source_db => 'PRODUCTION_DB',
    environment => 'DEV'
);

-- Behind the scenes:
-- ✅ Temporary ownership transferred
-- ✅ Production grants revoked
-- ✅ 9 new environment-specific roles created
-- ✅ RBAC mappings applied
-- ✅ Developers have appropriate access

-- ❌ Hardcoded in procedures
GRANT ROLE dev_analytics_read TO ROLE data_analyst;

-- ✅ Configuration-driven
INSERT INTO rbac_mapping VALUES (...);
CALL sp_apply_rbac_mapping(...);

// This scales from 3 schemas to 300
role_name = database + "_" + schema + "_" + access_level

User requests clone
   ↓
Service role takes ownership
   ↓
Service role makes all changes
   ↓
Service role transfers ownership to target roles

-- ❌ Only current objects
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE analyst;

-- ✅ Current AND future objects
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE analyst;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics TO ROLE analyst;

-- ❌ Drops all existing grants during ownership transfer
GRANT OWNERSHIP ON SCHEMA my_schema TO ROLE new_owner;

-- ✅ Preserves grants during transfer
GRANT OWNERSHIP ON SCHEMA my_schema TO ROLE new_owner COPY CURRENT GRANTS;

// ❌ Fails if role doesn't exist
execSQL("GRANT ROLE clone_role TO ROLE functional_role");

// ✅ Graceful error handling (in our procedures)
try {
    execSQL("GRANT ROLE ...");
    success_count++;
} catch (e) {
    errors.push({error: e.message});
}