Databricks

jdbc:databricks://example.cloud.databricks.com:443/default;transportMode=http;ssl=1;httpPath=/sql/1.0/warehouses/example;

SELECT 
    current_user() as integration_specific_id,
    current_user() as email,
    current_user() as username,
    'ACTIVE' as user_status,
    'user' as account_type,
    current_catalog() as current_catalog,
    current_database() as current_database,
    current_metastore() as metastore_id,
    current_version() as databricks_version
LIMIT 1

-- Discovers accounts from granted privileges 
SELECT DISTINCT
    grantee as integration_specific_id,
    CASE 
        WHEN grantee LIKE '%@%' THEN grantee
        ELSE CONCAT(grantee, '@databricks.local')
    END as email,
    grantee as username,
    'ACTIVE' as user_status,
    CASE
        WHEN LOWER(grantee) LIKE '%service%' OR LOWER(grantee) LIKE '%app%' THEN 'service'
        WHEN grantee IN ('account users', 'all privileges', 'users') THEN 'service'
        ELSE 'user'
    END as account_type,
    grantee as given_name,
    '' as family_name,
    'DATABRICKS' as source_system
FROM (
    SELECT DISTINCT grantee FROM system.information_schema.catalog_privileges WHERE grantee IS NOT NULL
    UNION
    SELECT DISTINCT grantee FROM system.information_schema.schema_privileges WHERE grantee IS NOT NULL
    UNION  
    SELECT DISTINCT grantee FROM system.information_schema.table_privileges WHERE grantee IS NOT NULL
    UNION
    SELECT DISTINCT grantee FROM system.information_schema.routine_privileges WHERE grantee IS NOT NULL
) all_grantees
WHERE grantee NOT IN ('', 'null')
ORDER BY integration_specific_id

-- Discovers entitlements based on available privilege types
SELECT * FROM (
    -- Catalog-level privileges as entitlements
    SELECT DISTINCT
        CONCAT('CATALOG_PRIV:', privilege_type) as entitlement_type,
        CONCAT(catalog_name, ':', privilege_type) as integration_specific_id,
        catalog_name as integration_specific_resource_id,
        true as is_assignable,
        CONCAT('Privilege: ', privilege_type, ' on catalog ', catalog_name) as label
    FROM system.information_schema.catalog_privileges
    WHERE catalog_name IS NOT NULL
    
    UNION ALL
    
    -- Schema-level privileges as entitlements
    SELECT DISTINCT
        CONCAT('SCHEMA_PRIV:', privilege_type) as entitlement_type,
        CONCAT(catalog_name, '.', schema_name, ':', privilege_type) as integration_specific_id,
        CONCAT(catalog_name, '.', schema_name) as integration_specific_resource_id,
        true as is_assignable,
        CONCAT('Privilege: ', privilege_type, ' on schema ', catalog_name, '.', schema_name) as label
    FROM system.information_schema.schema_privileges
    WHERE schema_name IS NOT NULL
    
    UNION ALL
    
    -- Table-level privileges as entitlements (optional, can be numerous)
    SELECT DISTINCT
        CONCAT('TABLE_PRIV:', privilege_type) as entitlement_type,
        CONCAT(table_catalog, '.', table_schema, '.', table_name, ':', privilege_type) as integration_specific_id,
        CONCAT(table_catalog, '.', table_schema, '.', table_name) as integration_specific_resource_id,
        true as is_assignable,
        CONCAT('Privilege: ', privilege_type, ' on table ', table_catalog, '.', table_schema, '.', table_name) as label
    FROM system.information_schema.table_privileges
    WHERE table_name IS NOT NULL
    AND table_schema NOT IN ('information_schema')
) all_entitlements
ORDER BY entitlement_type, integration_specific_id

-- Maps users to their specific privileges
SELECT * FROM (
    -- Catalog privilege associations
    SELECT 
        grantee as account_id,
        CONCAT('CATALOG_PRIV:', privilege_type, ':', catalog_name, ':', privilege_type) as integration_specific_entitlement_id,
        catalog_name as integration_specific_resource_id
    FROM system.information_schema.catalog_privileges
    WHERE grantee IS NOT NULL
    AND is_grantable = 'YES'
    
    UNION ALL
    
    -- Schema privilege associations
    SELECT 
        grantee as account_id,
        CONCAT('SCHEMA_PRIV:', privilege_type, ':', catalog_name, '.', schema_name, ':', privilege_type) as integration_specific_entitlement_id,
        CONCAT(catalog_name, '.', schema_name) as integration_specific_resource_id
    FROM system.information_schema.schema_privileges
    WHERE grantee IS NOT NULL
    AND is_grantable = 'YES'
    
    UNION ALL
    
    -- Table privilege associations
    SELECT 
        grantee as account_id,
        CONCAT('TABLE_PRIV:', privilege_type, ':', table_catalog, '.', table_schema, '.', table_name, ':', privilege_type) as integration_specific_entitlement_id,
        CONCAT(table_catalog, '.', table_schema, '.', table_name) as integration_specific_resource_id
    FROM system.information_schema.table_privileges
    WHERE grantee IS NOT NULL
    AND table_schema NOT IN ('information_schema')
) all_associations
ORDER BY account_id

-- Lists all catalogs, schemas, and tables as resources
SELECT * FROM (
    -- Catalogs
    SELECT 
        catalog_name as integration_specific_id,
        CONCAT('Catalog: ', catalog_name, ' (Owner: ', COALESCE(catalog_owner, 'Unknown'), ')') as label,
        'CATALOG' as resource_type
    FROM system.information_schema.catalogs
    
    UNION ALL
    
    -- Schemas  
    SELECT 
        CONCAT(catalog_name, '.', schema_name) as integration_specific_id,
        CONCAT('Schema: ', catalog_name, '.', schema_name, ' (Owner: ', COALESCE(schema_owner, 'Unknown'), ')') as label,
        'SCHEMA' as resource_type
    FROM system.information_schema.schemata
    WHERE schema_name != 'information_schema'
    
    UNION ALL
    
    -- Tables (optional - can be numerous)
    SELECT 
        CONCAT(table_catalog, '.', table_schema, '.', table_name) as integration_specific_id,
        CONCAT('Table: ', table_catalog, '.', table_schema, '.', table_name, ' (Type: ', table_type, ')') as label,
        CASE 
            WHEN table_type = 'VIEW' THEN 'VIEW'
            WHEN table_type = 'MANAGED' THEN 'MANAGED_TABLE'
            WHEN table_type = 'EXTERNAL' THEN 'EXTERNAL_TABLE'
            ELSE 'TABLE'
        END as resource_type
    FROM system.information_schema.tables
    WHERE table_schema NOT IN ('information_schema')
    AND table_catalog NOT IN ('system')
) all_resources
ORDER BY resource_type, integration_specific_id

SELECT 
    table_owner as account_id,
    'TABLE_ACCESS' as event_type,
    MAX(last_altered) as happened_at
FROM system.information_schema.tables
WHERE table_owner IS NOT NULL
AND last_altered IS NOT NULL
GROUP BY table_owner

UNION ALL

-- If no audit logs are available, return current time for active users
SELECT DISTINCT
    grantee as account_id,
    'ACTIVE_GRANT' as event_type,
    current_timestamp() as happened_at
FROM system.information_schema.catalog_privileges
WHERE grantee IS NOT NULL
AND grantee != ''