access-management（访问管理）

Access Management Skill

Overview

Comprehensive access management patterns for Unity Catalog including RBAC, ABAC, row-level security, column masking, and automated access reviews.

RBAC (Role-Based Access Control)

Standard Role Framework

STANDARD_ROLES = {
    "data_consumer": {
        "privileges": ["USE CATALOG", "USE SCHEMA", "SELECT"],
        "scope": "gold layer",
        "description": "Read-only access to curated data"
    },
    "data_producer": {
        "privileges": ["USE CATALOG", "USE SCHEMA", "SELECT", "MODIFY", "CREATE TABLE"],
        "scope": "bronze, silver layers",
        "description": "Transform and load data"
    },
    "data_steward": {
        "privileges": ["USE CATALOG", "USE SCHEMA", "SELECT", "MODIFY", "ALTER", "GRANT"],
        "scope": "all layers",
        "description": "Manage schema and metadata"
    },
    "platform_admin": {
        "privileges": ["ALL PRIVILEGES"],
        "scope": "all catalogs",
        "description": "Full administrative access (minimal assignments)"
    }
}

Role Assignment

def assign_role(principal: str, role: str, catalog: str):
    """Assign standard role to user or group."""
    role_def = STANDARD_ROLES[role]

    for privilege in role_def["privileges"]:
        if role_def["scope"] == "all catalogs":
            grant_sql = f"GRANT {privilege} ON CATALOG {catalog} TO `{principal}`"
        elif role_def["scope"] == "gold layer":
            grant_sql = f"GRANT {privilege} ON SCHEMA {catalog}.gold TO `{principal}`"

        spark.sql(grant_sql)

    log_role_assignment(principal, role, catalog)

ABAC (Attribute-Based Access Control)

Attribute-Based Policies

def create_abac_policy(resource: str, attributes: dict):
    """Create attribute-based access policy."""
    policy_conditions = []

    # Department-based access
    if "department" in attributes:
        policy_conditions.append(
            f"department = '{attributes['department']}' OR IS_ACCOUNT_GROUP_MEMBER('admin')"
        )

    # Clearance level-based
    if "clearance_level" in attributes:
        policy_conditions.append(
            f"user_clearance >= {attributes['clearance_level']}"
        )

    # Time-based access
    if "business_hours_only" in attributes:
        policy_conditions.append(
            "HOUR(NOW()) BETWEEN 8 AND 18"
        )

    # Generate policy function
    policy_sql = f"""
    CREATE FUNCTION abac_{resource}_policy()
    RETURNS BOOLEAN
    RETURN {' AND '.join(policy_conditions)};
    """

    spark.sql(policy_sql)

Row-Level Security

Pattern 1: Regional Access Control

CREATE FUNCTION regional_access(region STRING)
RETURNS BOOLEAN
RETURN CASE
  WHEN IS_ACCOUNT_GROUP_MEMBER('global_access') THEN TRUE
  WHEN IS_ACCOUNT_GROUP_MEMBER('us_team') AND region = 'US' THEN TRUE
  WHEN IS_ACCOUNT_GROUP_MEMBER('eu_team') AND region = 'EU' THEN TRUE
  ELSE FALSE
END;

ALTER TABLE sales_data SET ROW FILTER regional_access ON (region);

Pattern 2: Multi-Tenant Isolation

CREATE FUNCTION tenant_filter(tenant_id STRING)
RETURNS BOOLEAN
RETURN current_user() LIKE CONCAT(tenant_id, '@%')
    OR IS_ACCOUNT_GROUP_MEMBER('support_admin');

ALTER TABLE saas.customer_data SET ROW FILTER tenant_filter ON (tenant_id);

Pattern 3: Department-Based Access

CREATE FUNCTION department_access(dept STRING)
RETURNS BOOLEAN
RETURN IS_ACCOUNT_GROUP_MEMBER(CONCAT('dept_', LOWER(dept)))
    OR IS_ACCOUNT_GROUP_MEMBER('hr_admin');

ALTER TABLE employees SET ROW FILTER department_access ON (department);

Column Masking

Pattern 1: Conditional Masking

CREATE FUNCTION mask_email(email STRING)
RETURNS STRING
RETURN CASE
  WHEN IS_ACCOUNT_GROUP_MEMBER('pii_admin') THEN email
  WHEN IS_ACCOUNT_GROUP_MEMBER('pii_viewer') THEN
    CONCAT(LEFT(email, 3), '***@', SPLIT(email, '@')[1])
  ELSE 'REDACTED'
END;

ALTER TABLE customers ALTER COLUMN email SET MASK mask_email;

Pattern 2: SSN Masking

CREATE FUNCTION mask_ssn(ssn STRING)
RETURNS STRING
RETURN CASE
  WHEN IS_ACCOUNT_GROUP_MEMBER('hr_full') THEN ssn
  WHEN IS_ACCOUNT_GROUP_MEMBER('hr_partial') THEN CONCAT('XXX-XX-', RIGHT(ssn, 4))
  ELSE 'XXX-XX-XXXX'
END;

ALTER TABLE employees ALTER COLUMN ssn SET MASK mask_ssn;

Least Privilege Enforcement

def enforce_least_privilege(catalog: str):
    """Remove excessive permissions and enforce least privilege."""
    # Find overly permissive grants
    all_grants = spark.sql(f"SHOW GRANTS ON CATALOG {catalog}").collect()

    for grant in all_grants:
        # Replace ALL PRIVILEGES with specific grants
        if grant.privilege == "ALL PRIVILEGES" and grant.principal not in ['platform_admin']:
            revoke_excessive_grant(grant)
            apply_minimal_grants(grant.principal, catalog)

        # Remove MODIFY from read-only users
        if grant.privilege == "MODIFY" and grant.principal in readonly_users:
            spark.sql(f"REVOKE MODIFY ON CATALOG {catalog} FROM `{grant.principal}`")

Access Reviews

Quarterly Access Review

def conduct_quarterly_review(catalog: str):
    """Quarterly access recertification."""
    grants = get_all_grants(catalog)

    review_items = {
        "excessive_access": find_excessive_permissions(grants),
        "unused_access": find_unused_permissions(grants),
        "stale_accounts": find_stale_users(grants),
        "orphaned_grants": find_orphaned_grants(grants)
    }

    # Generate review report
    report = generate_review_report(review_items)

    # Send to data stewards for approval
    send_for_recertification(report, data_stewards)

    return report

Best Practices

1. Least Privilege: Grant minimum required permissions
2. Separation of Duties: No single user has complete control
3. Regular Reviews: Quarterly access recertification
4. Time-Bound Access: Temporary grants for contractors
5. Service Principal Ownership: Use SPs not individuals
6. Audit Trail: Log all permission changes

Templates

• rbac-framework.sql: Complete RBAC setup
• row-filter-patterns.sql: Row-level security patterns
• masking-functions.sql: Column masking library
• access-review.py: Automated review workflow

Examples

• regional-isolation: Multi-region access control
• multi-tenant-security: Tenant isolation patterns
• conditional-masking: Dynamic data masking