Snowflake

jdbc:snowflake://example.snowflakecomputing.com/?warehouse=COMPUTE_WH&db=SNOWFLAKE&schema=ACCOUNT_USAGE&JDBC_QUERY_RESULT_FORMAT=JSON&JDBC_TREAT_DECIMAL_AS_INT=false

-- Validates connection and returns one sample user with schema detection
SELECT 
    LOGIN_NAME as integration_specific_id,  -- User's login identifier
    LOGIN_NAME as username,
    COALESCE(EMAIL, LOGIN_NAME || '@snowflake.local') as email,  -- Use email if available, otherwise construct one
    FIRST_NAME as given_name,
    LAST_NAME as family_name,
    CASE 
        WHEN DELETED_ON IS NOT NULL THEN 'DELETED'  -- User has been deleted
        WHEN DISABLED = 'false' OR DISABLED IS NULL THEN 'ACTIVE'  -- User is active (DISABLED stores 'false' when enabled)
        ELSE 'INACTIVE'  -- User is disabled
    END as user_status,
    'USER' as account_type,
    -- Custom attributes for schema detection
    CREATED_ON as created_on,
    LAST_SUCCESS_LOGIN as last_login,
    HAS_MFA as mfa_enabled,
    DEFAULT_WAREHOUSE as default_warehouse,
    DEFAULT_ROLE as default_role,
    EXT_AUTHN_DUO as has_duo_mfa,
    EXT_AUTHN_UID as external_auth_id,
    MUST_CHANGE_PASSWORD as must_change_password,
    SNOWFLAKE_LOCK as is_locked,
    COMMENT as description,
    HAS_PASSWORD as has_password,
    HAS_RSA_PUBLIC_KEY as has_rsa_key
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
WHERE DELETED_ON IS NULL  -- Only get non-deleted users
LIMIT 1  -- Return just one record for validation

-- Retrieves all users including inactive and deleted for comprehensive access reviews
SELECT 
    LOGIN_NAME as integration_specific_id,  -- Primary identifier users use to authenticate
    LOGIN_NAME as username,
    COALESCE(EMAIL, LOGIN_NAME || '@snowflake.local') as email,  -- Fallback email construction
    FIRST_NAME as given_name,
    LAST_NAME as family_name,
    CASE 
        WHEN DELETED_ON IS NOT NULL THEN 'DELETED'  -- User has been removed
        WHEN DISABLED = 'false' OR DISABLED IS NULL THEN 'ACTIVE'  -- DISABLED column contains 'false' when user is enabled
        ELSE 'INACTIVE'  -- User is disabled
    END as user_status,
    CASE
        -- Identify service accounts vs regular user accounts
        WHEN TYPE = 'SERVICE' THEN 'SERVICE'
        WHEN TYPE = 'SNOWFLAKE_SERVICE' THEN 'SERVICE'
        WHEN NAME IN ('SNOWFLAKE', 'SNOWFLAKE_MARKETPLACE', 'WORKSHEETS_APP_USER') THEN 'SERVICE'  -- Known system accounts
        ELSE 'USER'
    END as account_type,
    -- Custom attributes for access reviews - cast to VARCHAR for consistent string output
    CAST(CREATED_ON AS VARCHAR) as created_on,
    CAST(LAST_SUCCESS_LOGIN AS VARCHAR) as last_login,
    CAST(HAS_MFA AS VARCHAR) as mfa_enabled,
    DEFAULT_WAREHOUSE as default_warehouse,
    DEFAULT_ROLE as default_role,
    DEFAULT_SECONDARY_ROLE as default_secondary_roles,
    CAST(EXT_AUTHN_DUO AS VARCHAR) as has_duo_mfa,
    EXT_AUTHN_UID as external_auth_id,
    CAST(MUST_CHANGE_PASSWORD AS VARCHAR) as must_change_password,
    CAST(SNOWFLAKE_LOCK AS VARCHAR) as is_locked,
    COMMENT as description,
    CASE 
        WHEN DISABLED = 'false' OR DISABLED IS NULL THEN NULL  -- Don't show disabled date for active users
        ELSE CAST(DISABLED AS VARCHAR)  -- Show timestamp when user was disabled
    END as disabled_date,
    CAST(DELETED_ON AS VARCHAR) as deleted_date,
    CAST(HAS_PASSWORD AS VARCHAR) as has_password,
    CAST(HAS_RSA_PUBLIC_KEY AS VARCHAR) as has_rsa_key,
    LOGIN_NAME as login_name,
    NAME as name_id,  -- Keep internal NAME for reference (used in grants)
    DISPLAY_NAME as display_name,
    OWNER as created_by,
    DEFAULT_NAMESPACE as default_namespace,
    CAST(EXPIRES_AT AS VARCHAR) as expires_at,
    CAST(LOCKED_UNTIL_TIME AS VARCHAR) as locked_until,
    CAST(PASSWORD_LAST_SET_TIME AS VARCHAR) as password_last_set,
    CAST(BYPASS_MFA_UNTIL AS VARCHAR) as bypass_mfa_until,
    TYPE as user_type,
    USER_ID as user_id
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
ORDER BY CREATED_ON DESC  -- Most recent users first

-- Lists all types of access grants available in Snowflake
SELECT * FROM (
    -- System and Custom Roles (global entitlements that can be assigned to users)
    SELECT 
        'ROLE' as entitlement_type,
        'ROLE:' || NAME as integration_specific_id,
        '' as integration_specific_resource_id,  -- Roles are global, not tied to a resource
        TRUE as is_assignable,  -- Boolean type required by Lumos
        NAME as label
    FROM SNOWFLAKE.ACCOUNT_USAGE.ROLES
    WHERE DELETED_ON IS NULL
    
    UNION ALL
    
    -- Role-to-Role grants (shows role inheritance hierarchy)
    SELECT DISTINCT
        'INHERITED_ROLE' as entitlement_type,
        'ROLE_GRANT:' || gtr.GRANTEE_NAME || ':' || gtr.NAME as integration_specific_id,
        'ROLE:' || gtr.GRANTEE_NAME as integration_specific_resource_id,  -- The parent role
        TRUE as is_assignable,
        'Inherits ' || gtr.NAME as label  -- Child role inherits from parent
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES gtr
    JOIN SNOWFLAKE.ACCOUNT_USAGE.ROLES r ON gtr.NAME = r.NAME
    WHERE gtr.GRANTED_ON = 'ROLE'  -- Only role-to-role grants
        AND gtr.DELETED_ON IS NULL
        AND r.DELETED_ON IS NULL
    
    UNION ALL
    
    -- Database access privileges granted to roles
    SELECT DISTINCT
        'DATABASE_ACCESS' as entitlement_type,
        'DB_ACCESS:' || NAME || ':' || GRANTEE_NAME as integration_specific_id,
        'DATABASE:' || NAME as integration_specific_resource_id,  -- Links to database resource
        TRUE as is_assignable,
        GRANTEE_NAME || ' on ' || NAME as label
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
    WHERE GRANTED_ON = 'DATABASE'
        AND DELETED_ON IS NULL
        AND PRIVILEGE IN ('USAGE', 'OWNERSHIP', 'CREATE SCHEMA', 'MONITOR')  -- Key database privileges
    
    UNION ALL
    
    -- Warehouse access privileges granted to roles
    SELECT DISTINCT
        'WAREHOUSE_ACCESS' as entitlement_type,
        'WH_ACCESS:' || NAME || ':' || GRANTEE_NAME as integration_specific_id,
        'WAREHOUSE:' || NAME as integration_specific_resource_id,  -- Links to warehouse resource
        TRUE as is_assignable,
        GRANTEE_NAME || ' on ' || NAME as label
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
    WHERE GRANTED_ON = 'WAREHOUSE'
        AND DELETED_ON IS NULL
        AND PRIVILEGE IN ('USAGE', 'OPERATE', 'MONITOR', 'OWNERSHIP')  -- Key warehouse privileges
    
    UNION ALL
    
    -- Schema access privileges granted to roles
    SELECT DISTINCT
        'SCHEMA_ACCESS' as entitlement_type,
        'SCHEMA_ACCESS:' || TABLE_CATALOG || '.' || NAME || ':' || GRANTEE_NAME as integration_specific_id,
        'SCHEMA:' || TABLE_CATALOG || '.' || NAME as integration_specific_resource_id,  -- Links to schema resource
        TRUE as is_assignable,
        GRANTEE_NAME || ' on ' || TABLE_CATALOG || '.' || NAME as label
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
    WHERE GRANTED_ON = 'SCHEMA'
        AND DELETED_ON IS NULL
        AND PRIVILEGE IN ('USAGE', 'CREATE TABLE', 'CREATE VIEW', 'OWNERSHIP', 'MONITOR')  -- Key schema privileges
) entitlements
ORDER BY entitlement_type, label

-- Maps users to their roles and shows effective permissions through role inheritance
SELECT * FROM (
    -- Direct user-to-role assignments
    SELECT 
        u.LOGIN_NAME as account_id,  -- Map internal NAME to LOGIN_NAME for consistency
        'ROLE:' || gtu.ROLE as integration_specific_entitlement_id,
        '' as integration_specific_resource_id  -- Direct role grants are global
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS gtu
    JOIN SNOWFLAKE.ACCOUNT_USAGE.USERS u ON gtu.GRANTEE_NAME = u.NAME  -- Join to get LOGIN_NAME
    WHERE gtu.DELETED_ON IS NULL
    
    UNION ALL
    
    -- Role-to-role inheritance (shows which roles inherit from other roles)
    SELECT 
        GRANTEE_NAME as account_id,  -- The role that receives the grant
        'ROLE_GRANT:' || GRANTEE_NAME || ':' || NAME as integration_specific_entitlement_id,
        'ROLE:' || GRANTEE_NAME as integration_specific_resource_id
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
    WHERE GRANTED_ON = 'ROLE'
        AND DELETED_ON IS NULL
    
    UNION ALL
    
    -- User's effective database access through their assigned roles
    SELECT DISTINCT
        u.LOGIN_NAME as account_id,
        'DB_ACCESS:' || r.NAME || ':' || r.GRANTEE_NAME as integration_specific_entitlement_id,
        'DATABASE:' || r.NAME as integration_specific_resource_id
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS gtu
    JOIN SNOWFLAKE.ACCOUNT_USAGE.USERS u ON gtu.GRANTEE_NAME = u.NAME
    JOIN SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES r 
        ON gtu.ROLE = r.GRANTEE_NAME  -- User's role has database access
    WHERE r.GRANTED_ON = 'DATABASE'
        AND r.DELETED_ON IS NULL
        AND gtu.DELETED_ON IS NULL
        AND r.PRIVILEGE IN ('USAGE', 'OWNERSHIP', 'CREATE SCHEMA', 'MONITOR')
    
    UNION ALL
    
    -- User's effective warehouse access through their assigned roles
    SELECT DISTINCT
        u.LOGIN_NAME as account_id,
        'WH_ACCESS:' || r.NAME || ':' || r.GRANTEE_NAME as integration_specific_entitlement_id,
        'WAREHOUSE:' || r.NAME as integration_specific_resource_id
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS gtu
    JOIN SNOWFLAKE.ACCOUNT_USAGE.USERS u ON gtu.GRANTEE_NAME = u.NAME
    JOIN SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES r 
        ON gtu.ROLE = r.GRANTEE_NAME  -- User's role has warehouse access
    WHERE r.GRANTED_ON = 'WAREHOUSE'
        AND r.DELETED_ON IS NULL
        AND gtu.DELETED_ON IS NULL
        AND r.PRIVILEGE IN ('USAGE', 'OPERATE', 'MONITOR', 'OWNERSHIP')
    
    UNION ALL
    
    -- User's effective schema access through their assigned roles
    SELECT DISTINCT
        u.LOGIN_NAME as account_id,
        'SCHEMA_ACCESS:' || r.TABLE_CATALOG || '.' || r.NAME || ':' || r.GRANTEE_NAME as integration_specific_entitlement_id,
        'SCHEMA:' || r.TABLE_CATALOG || '.' || r.NAME as integration_specific_resource_id
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS gtu
    JOIN SNOWFLAKE.ACCOUNT_USAGE.USERS u ON gtu.GRANTEE_NAME = u.NAME
    JOIN SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES r 
        ON gtu.ROLE = r.GRANTEE_NAME  -- User's role has schema access
    WHERE r.GRANTED_ON = 'SCHEMA'
        AND r.DELETED_ON IS NULL
        AND gtu.DELETED_ON IS NULL
        AND r.PRIVILEGE IN ('USAGE', 'CREATE TABLE', 'CREATE VIEW', 'OWNERSHIP', 'MONITOR')
) associations

-- Lists all databases, warehouses, and schemas as resources that can have entitlements
SELECT * FROM (
    -- Get all active databases
    SELECT 
        'DATABASE:' || DATABASE_NAME as integration_specific_id,  -- Prefix with type for unique ID
        DATABASE_NAME as label,
        'DATABASE' as resource_type
    FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES
    WHERE DELETED IS NULL  -- Only non-deleted databases
    
    UNION ALL
    
    -- Get warehouses from metering history (no dedicated WAREHOUSES view exists)
    SELECT DISTINCT
        'WAREHOUSE:' || WAREHOUSE_NAME as integration_specific_id,
        WAREHOUSE_NAME as label,
        'WAREHOUSE' as resource_type
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE START_TIME >= DATEADD('day', -365, CURRENT_TIMESTAMP())  -- Warehouses used in past year
        AND WAREHOUSE_NAME IS NOT NULL
    
    UNION ALL
    
    -- Get all schemas with their database context
    SELECT DISTINCT
        'SCHEMA:' || CATALOG_NAME || '.' || SCHEMA_NAME as integration_specific_id,  -- Include database.schema
        CATALOG_NAME || '.' || SCHEMA_NAME as label,
        'SCHEMA' as resource_type
    FROM SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA
    WHERE DELETED IS NULL
        AND SCHEMA_NAME NOT IN ('INFORMATION_SCHEMA')  -- Exclude system schemas
) resources
ORDER BY resource_type, label

-- Retrieves last login activity for a specific user
SELECT 
    USER_NAME as account_id,
    'last_login' as event_type,
    TO_CHAR(MAX(EVENT_TIMESTAMP), 'YYYY-MM-DD"T"HH24:MI:SS') as happened_at
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE IS_SUCCESS = 'YES'
GROUP BY USER_NAME