Database Query Examples

Ready-made query sets for common database engines. Each section below provides a JDBC connection string and capability queries you can paste into the Database connector configuration, then adapt to your environment.

How to use these examples

Copy the queries for the capabilities you need. At minimum, Validate Credentials and List Accounts.
Adapt table names, columns, and status logic to your environment. The engine-account queries work as-is against system catalogs. Application-database queries always need adapting to your schema.
Strip comments and flatten each query to a single line before pasting. Lumos does not support comments in SQL queries. On macOS, this flattens your clipboard:

pbpaste | tr '\n' ' ' | sed -e 's/[[:space:]]/ /g' -e 's/ \{2,\}/ /g' | pbcopy

Sample Application Database

A complete worked example of governing a custom application through the Database connector, using a sample schema in Microsoft SQL Server. It demonstrates the read and write capabilities except List Updated Accounts and Update Account. In production, your application has its own schema and column names, which you model the same way.

Schema

The sample models identity data the way most applications do: an accounts table, a resources table, an entitlements table, a join table for assignments, and an activity log.

CREATE TABLE Accounts (
    integration_specific_id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    email NVARCHAR(255) NOT NULL,
    given_name NVARCHAR(100),
    family_name NVARCHAR(100),
    username NVARCHAR(100) NOT NULL,
    user_status NVARCHAR(20) CHECK (user_status IN ('ACTIVE', 'INACTIVE', 'SUSPENDED', 'DEPROVISIONED', 'PENDING', 'DELETED')),
    account_type NVARCHAR(20) CHECK (account_type IN ('SERVICE', 'USER')),
    created_at DATETIME2 DEFAULT GETDATE(),
    CONSTRAINT UQ_Email UNIQUE (email),
    CONSTRAINT UQ_Username UNIQUE (username)
);

CREATE TABLE Resources (
    integration_specific_id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    label NVARCHAR(255) NOT NULL,
    resource_type NVARCHAR(50) CHECK (resource_type IN ('Site', 'Workspace')),
    created_at DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE Entitlements (
    integration_specific_id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    integration_specific_resource_id UNIQUEIDENTIFIER,
    entitlement_type NVARCHAR(50) CHECK (entitlement_type IN ('Group', 'Role')),
    is_assignable BIT DEFAULT 1,
    label NVARCHAR(255) NOT NULL,
    created_at DATETIME2 DEFAULT GETDATE(),
    CONSTRAINT FK_Entitlements_Resources FOREIGN KEY (integration_specific_resource_id)
        REFERENCES Resources(integration_specific_id)
);

CREATE TABLE EntitlementAssociations (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    account_id UNIQUEIDENTIFIER,
    integration_specific_entitlement_id UNIQUEIDENTIFIER,
    integration_specific_resource_id UNIQUEIDENTIFIER,
    assigned_at DATETIME2 DEFAULT GETDATE(),
    CONSTRAINT FK_EntitlementAssociations_Accounts FOREIGN KEY (account_id)
        REFERENCES Accounts(integration_specific_id),
    CONSTRAINT FK_EntitlementAssociations_Entitlements FOREIGN KEY (integration_specific_entitlement_id)
        REFERENCES Entitlements(integration_specific_id),
    CONSTRAINT FK_EntitlementAssociations_Resources FOREIGN KEY (integration_specific_resource_id)
        REFERENCES Resources(integration_specific_id)
);

CREATE TABLE ActivityLog (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    account_id UNIQUEIDENTIFIER,
    event_type NVARCHAR(100) NOT NULL,
    happened_at DATETIME2 DEFAULT GETDATE(),
    CONSTRAINT FK_ActivityLog_Accounts FOREIGN KEY (account_id)
        REFERENCES Accounts(integration_specific_id)
);

CREATE INDEX IX_Accounts_Status ON Accounts(user_status);
CREATE INDEX IX_Resources_Type ON Resources(resource_type);
CREATE INDEX IX_Entitlements_Type ON Entitlements(entitlement_type);
CREATE INDEX IX_ActivityLog_AccountId ON ActivityLog(account_id);
CREATE INDEX IX_EntitlementAssociations_AccountId ON EntitlementAssociations(account_id);

A Python generator script populates the schema with realistic sample data (200 users, 10 resources, 20 entitlements by default, with industry-standard status distributions). Install its dependencies with pip install pyodbc faker tqdm and run it with your server, database, and credentials.

python lumos_generator.py --server sql.example.com --database LumosDemo --uid your_username --pwd your_password

Sample data generator script (lumos_generator.py)

lumos_generator.py

import pyodbc
import uuid
import random
from datetime import datetime, timedelta
import faker
import tqdm
import argparse
import os
from typing import Optional

class LumosDataGenerator:
    def __init__(self, connection_string, num_users=200, num_resources=10, num_entitlements=20):
        self.conn = pyodbc.connect(connection_string)
        self.cursor = self.conn.cursor()
        self.fake = faker.Faker()
        
        # Configuration
        self.num_users = num_users
        self.num_resources = num_resources
        self.num_entitlements = num_entitlements
        
        # Distributions
        self.user_status_dist = {
            'ACTIVE': 0.80,
            'INACTIVE': 0.10,
            'SUSPENDED': 0.07,
            'PENDING': 0.03
        }
        
        self.account_type_dist = {
            'USER': 0.90,
            'SERVICE': 0.10
        }
        
        self.resource_type_dist = {
            'Site': 0.40,
            'Workspace': 0.60
        }
        
        self.entitlement_type_dist = {
            'Group': 0.60,
            'Role': 0.40
        }

    def create_tables(self):
        """Create all necessary tables if they don't exist"""
        print("Creating/verifying tables...")
        
        # Accounts table
        self.cursor.execute("""
            IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Accounts')
            CREATE TABLE Accounts (
                integration_specific_id UNIQUEIDENTIFIER PRIMARY KEY,
                email NVARCHAR(255) NOT NULL,
                given_name NVARCHAR(100),
                family_name NVARCHAR(100),
                username NVARCHAR(100) NOT NULL,
                user_status NVARCHAR(50) NOT NULL,
                account_type NVARCHAR(50) NOT NULL,
                created_at DATETIME DEFAULT GETDATE()
            )
        """)

        # Resources table
        self.cursor.execute("""
            IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Resources')
            CREATE TABLE Resources (
                integration_specific_id UNIQUEIDENTIFIER PRIMARY KEY,
                label NVARCHAR(255) NOT NULL,
                resource_type NVARCHAR(50) NOT NULL
            )
        """)

        # Entitlements table
        self.cursor.execute("""
            IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Entitlements')
            CREATE TABLE Entitlements (
                integration_specific_id UNIQUEIDENTIFIER PRIMARY KEY,
                integration_specific_resource_id UNIQUEIDENTIFIER,
                entitlement_type NVARCHAR(50) NOT NULL,
                is_assignable BIT NOT NULL,
                label NVARCHAR(255) NOT NULL,
                FOREIGN KEY (integration_specific_resource_id) REFERENCES Resources(integration_specific_id)
            )
        """)

        # EntitlementAssociations table
        self.cursor.execute("""
            IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'EntitlementAssociations')
            CREATE TABLE EntitlementAssociations (
                account_id UNIQUEIDENTIFIER,
                integration_specific_entitlement_id UNIQUEIDENTIFIER,
                integration_specific_resource_id UNIQUEIDENTIFIER,
                FOREIGN KEY (account_id) REFERENCES Accounts(integration_specific_id),
                FOREIGN KEY (integration_specific_entitlement_id) REFERENCES Entitlements(integration_specific_id),
                FOREIGN KEY (integration_specific_resource_id) REFERENCES Resources(integration_specific_id),
                PRIMARY KEY (account_id, integration_specific_entitlement_id, integration_specific_resource_id)
            )
        """)

        # ActivityLog table
        self.cursor.execute("""
            IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'ActivityLog')
            CREATE TABLE ActivityLog (
                id BIGINT IDENTITY(1,1) PRIMARY KEY,
                account_id UNIQUEIDENTIFIER,
                event_type NVARCHAR(50) NOT NULL,
                happened_at DATETIME NOT NULL,
                FOREIGN KEY (account_id) REFERENCES Accounts(integration_specific_id)
            )
        """)
        
        self.conn.commit()

    def generate_resources(self):
        print("Generating resources...")
        resources = []
        departments = ['Engineering', 'HR', 'Finance', 'Marketing', 'Sales', 'IT', 'Operations']
        locations = ['US', 'EU', 'APAC', 'LATAM']
        
        for _ in tqdm.tqdm(range(self.num_resources)):
            resource_id = str(uuid.uuid4())
            resource_type = random.choices(list(self.resource_type_dist.keys()),
                                        weights=list(self.resource_type_dist.values()))[0]
            
            if resource_type == 'Site':
                label = f"{random.choice(locations)} {random.choice(['Production', 'Development', 'Staging', 'Test'])} Site"
            else:
                label = f"{random.choice(departments)} {random.choice(['Team', 'Project', 'Division'])} Workspace"
            
            resources.append((resource_id, label, resource_type))
            
        self.cursor.executemany("""
            INSERT INTO Resources (integration_specific_id, label, resource_type)
            VALUES (?, ?, ?)
        """, resources)
        self.conn.commit()
        return resources

    def generate_entitlements(self, resources):
        print("Generating entitlements...")
        entitlements = []
        resource_ids = [r[0] for r in resources]
        
        for _ in tqdm.tqdm(range(self.num_entitlements)):
            entitlement_id = str(uuid.uuid4())
            resource_id = random.choice(resource_ids)
            entitlement_type = random.choices(list(self.entitlement_type_dist.keys()),
                                           weights=list(self.entitlement_type_dist.values()))[0]
            label = f"{entitlement_type} {self.fake.job()}"
            entitlements.append((entitlement_id, resource_id, entitlement_type, 1, label))
            
        self.cursor.executemany("""
            INSERT INTO Entitlements (
                integration_specific_id, integration_specific_resource_id,
                entitlement_type, is_assignable, label
            )
            VALUES (?, ?, ?, ?, ?)
        """, entitlements)
        self.conn.commit()
        return entitlements

    def generate_accounts(self):
        print("Generating accounts...")
        accounts = []
        
        for i in tqdm.tqdm(range(self.num_users)):
            account_id = str(uuid.uuid4())
            account_type = random.choices(list(self.account_type_dist.keys()),
                                       weights=list(self.account_type_dist.values()))[0]
            
            if account_type == 'SERVICE':
                service_name = f"svc_{self.fake.slug()}_{i}"
                email = f"{service_name}@example.com"
                status = random.choices(list(self.user_status_dist.keys()),
                                     weights=list(self.user_status_dist.values()))[0]
                accounts.append((
                    account_id, email, 'Service', 'Account',
                    service_name, status, account_type
                ))
            else:
                first_name = self.fake.first_name()
                last_name = self.fake.last_name()
                username = f"{first_name[0].lower()}{last_name.lower()}{i:04d}"
                email = f"{username}@example.com"
                status = random.choices(list(self.user_status_dist.keys()),
                                     weights=list(self.user_status_dist.values()))[0]
                accounts.append((
                    account_id, email, first_name, last_name,
                    username, status, account_type
                ))
            
        self.cursor.executemany("""
            INSERT INTO Accounts (
                integration_specific_id, email, given_name, family_name,
                username, user_status, account_type
            )
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, accounts)
        self.conn.commit()
        return accounts

    def generate_entitlement_associations(self, accounts, entitlements):
        print("Generating entitlement associations...")
        associations = []
        active_account_ids = [a[0] for a in accounts if a[5] in ['ACTIVE', 'INACTIVE', 'SUSPENDED', 'PENDING']]
        
        for account_id in tqdm.tqdm(active_account_ids):
            num_entitlements = random.randint(2, 5)
            selected_entitlements = random.sample(entitlements, min(num_entitlements, len(entitlements)))
            
            for entitlement in selected_entitlements:
                associations.append((
                    account_id,
                    entitlement[0],  # entitlement_id
                    entitlement[1]   # resource_id
                ))
        
        self.cursor.executemany("""
            INSERT INTO EntitlementAssociations (
                account_id, integration_specific_entitlement_id,
                integration_specific_resource_id
            )
            VALUES (?, ?, ?)
        """, associations)
        self.conn.commit()
        return associations

    def generate_activity_logs(self, accounts):
        print("Generating activity logs...")
        activity_logs = []
        event_types = ['LOGIN', 'LOGOUT', 'PASSWORD_CHANGE', 'RESOURCE_ACCESS',
                      'FAILED_LOGIN_ATTEMPT', 'PROFILE_UPDATE', 'API_ACCESS']
        
        start_date = datetime.now() - timedelta(days=30)
        
        for account in tqdm.tqdm(accounts):
            if account[5] != 'DELETED':  # Only generate logs for non-deleted accounts
                account_id = account[0]
                num_events = random.randint(3, 10)
                
                for _ in range(num_events):
                    event_time = start_date + timedelta(
                        days=random.randint(0, 30),
                        hours=random.randint(0, 23),
                        minutes=random.randint(0, 59)
                    )
                    event_type = random.choice(event_types)
                    activity_logs.append((account_id, event_type, event_time))
        
        self.cursor.executemany("""
            INSERT INTO ActivityLog (account_id, event_type, happened_at)
            VALUES (?, ?, ?)
        """, activity_logs)
        self.conn.commit()

    def generate_all(self):
        """Generate all data in the correct order"""
        try:
            # Create tables first
            self.create_tables()
            
            # Clear existing data
            tables = ['ActivityLog', 'EntitlementAssociations', 'Entitlements', 'Resources', 'Accounts']
            for table in tables:
                self.cursor.execute(f"DELETE FROM {table}")
            self.conn.commit()
            
            # Generate new data
            resources = self.generate_resources()
            entitlements = self.generate_entitlements(resources)
            accounts = self.generate_accounts()
            self.generate_entitlement_associations(accounts, entitlements)
            self.generate_activity_logs(accounts)
            
            print("\nData generation complete!")
            
            # Print summary
            for table in reversed(tables):
                self.cursor.execute(f"SELECT COUNT(*) FROM {table}")
                print(f"Total {table}: {self.cursor.fetchone()[0]}")
                
        except Exception as e:
            self.conn.rollback()
            print(f"Error during data generation: {str(e)}")
            raise
        finally:
            self.cursor.close()
            self.conn.close()

def create_connection_string(server: str, database: str, uid: Optional[str] = None, 
                           pwd: Optional[str] = None) -> str:
    """Create a connection string from parameters"""
    conn_str = [
        "DRIVER={SQL Server}",
        f"SERVER={server}",
        f"DATABASE={database}",
        "TrustServerCertificate=yes"
    ]
    
    if uid and pwd:
        conn_str.extend([f"UID={uid}", f"PWD={pwd}"])
    else:
        conn_str.append("Trusted_Connection=yes")
    
    return ";".join(conn_str)

def parse_args():
    parser = argparse.ArgumentParser(description='Generate sample data for Lumos database')
    
    # Database connection parameters
    parser.add_argument('--server', required=True, help='SQL Server instance name')
    parser.add_argument('--database', required=True, help='Database name')
    parser.add_argument('--uid', help='Database user ID (optional if using Windows auth)')
    parser.add_argument('--pwd', help='Database password (optional if using Windows auth)')
    
    # Alternative: use environment variables for credentials
    parser.add_argument('--use-env', action='store_true', 
                       help='Use LUMOS_DB_UID and LUMOS_DB_PWD environment variables for credentials')
    
    # Data generation parameters
    parser.add_argument('--num-users', type=int, default=200, 
                       help='Number of users to generate (default: 200)')
    parser.add_argument('--num-resources', type=int, default=10, 
                       help='Number of resources to generate (default: 10)')
    parser.add_argument('--num-entitlements', type=int, default=20, 
                       help='Number of entitlements to generate (default: 20)')
    
    return parser.parse_args()

def main():
    args = parse_args()
    
    # Handle credentials
    uid = None
    pwd = None
    
    if args.use_env:
        uid = os.environ.get('LUMOS_DB_UID')
        pwd = os.environ.get('LUMOS_DB_PWD')
        if not (uid and pwd):
            raise ValueError("When using --use-env, LUMOS_DB_UID and LUMOS_DB_PWD "
                           "environment variables must be set")
    else:
        uid = args.uid
        pwd = args.pwd
    
    try:
        # Create connection string
        connection_string = create_connection_string(
            server=args.server,
            database=args.database,
            uid=uid,
            pwd=pwd
        )
        
        # Initialize and run generator
        generator = LumosDataGenerator(
            connection_string=connection_string,
            num_users=args.num_users,
            num_resources=args.num_resources,
            num_entitlements=args.num_entitlements
        )
        generator.generate_all()
        
    except pyodbc.Error as e:
        print(f"Database error occurred: {e}")
        exit(1)
    except Exception as e:
        print(f"An error occurred: {str(e)}")
        exit(1)

if __name__ == "__main__":
    main()

Verify the setup

Run these queries to confirm the tables, account distribution and entitlement associations.

-- Verify table creation
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE';

-- Check account distribution
SELECT user_status, account_type, COUNT(*) as count 
FROM Accounts 
GROUP BY user_status, account_type;

-- Verify entitlement associations
SELECT 
    a.username,
    r.label as resource_name,
    e.label as entitlement_name
FROM EntitlementAssociations ea
JOIN Accounts a ON ea.account_id = a.integration_specific_id
JOIN Resources r ON ea.integration_specific_resource_id = r.integration_specific_id
JOIN Entitlements e ON ea.integration_specific_entitlement_id = e.integration_specific_id;

Sample connection string

jdbc:sqlserver://sql.example.com:1433;databaseName=LumosDemo;encrypt=true;trustServerCertificate=true

Sample read capabilities

Validate Credentials

SELECT TOP 1 integration_specific_id as integration_specific_id, email as email, given_name as given_name, family_name as family_name, username as username, user_status as user_status, account_type as account_type, created_at as custom_created_date FROM Accounts WHERE user_status != 'DELETED';

List Accounts

SELECT integration_specific_id as integration_specific_id, email as email, given_name as given_name, family_name as family_name, username as username, user_status as user_status, account_type as account_type, created_at as custom_created_date FROM Accounts WHERE user_status IN ('ACTIVE', 'INACTIVE', 'SUSPENDED', 'PENDING') ORDER BY email;

Get Account

SELECT integration_specific_id AS integration_specific_id, email AS email, given_name AS given_name, family_name AS family_name, username AS username, user_status AS user_status, account_type AS account_type, created_at AS custom_created_date FROM Accounts WHERE email = {email};

List Entitlements

SELECT entitlement_type AS entitlement_type, integration_specific_id AS integration_specific_id, integration_specific_resource_id AS integration_specific_resource_id, is_assignable AS is_assignable, label AS label FROM Entitlements WHERE entitlement_type IS NOT NULL AND entitlement_type <> '' AND label IS NOT NULL AND label <> '' AND integration_specific_id IS NOT NULL AND integration_specific_resource_id IS NOT NULL AND is_assignable IS NOT NULL;

Find Entitlement Associations

SELECT a.integration_specific_id as account_id, e.integration_specific_id as integration_specific_entitlement_id, ea.integration_specific_resource_id as integration_specific_resource_id FROM EntitlementAssociations ea JOIN Accounts a ON ea.account_id = a.integration_specific_id JOIN Entitlements e ON ea.integration_specific_entitlement_id = e.integration_specific_id WHERE a.user_status != 'DELETED';

Get Last Activity

SELECT account_id as account_id, event_type as event_type, happened_at as happened_at FROM ActivityLog al JOIN Accounts a ON al.account_id = a.integration_specific_id WHERE a.user_status != 'DELETED' ORDER BY happened_at DESC;

List Resources

SELECT integration_specific_id as integration_specific_id, label as label, resource_type as resource_type FROM Resources;

Sample write capabilities

Create Account (the SELECT goes in the Create Account Return ID field)

INSERT INTO Accounts (integration_specific_id, email, username, given_name, family_name, user_status, account_type) VALUES (NEWID(), {email}, {username}, {given_name}, {family_name}, {user_status}, 'USER');

SELECT integration_specific_id AS id FROM Accounts WHERE email = {email};

Activate Account

UPDATE Accounts SET user_status = 'ACTIVE' WHERE integration_specific_id = {account_id};

Deactivate Account

UPDATE Accounts SET user_status = 'INACTIVE' WHERE integration_specific_id = {account_id};

Delete Account (a soft delete, preserving referential integrity)

UPDATE Accounts SET user_status = 'DELETED' WHERE integration_specific_id = {account_id};

Assign Entitlement

INSERT INTO EntitlementAssociations (account_id, integration_specific_entitlement_id, integration_specific_resource_id) VALUES ({account_integration_specific_id}, {entitlement_integration_specific_id}, {resource_integration_specific_id});

Unassign Entitlement

DELETE FROM EntitlementAssociations WHERE account_id = {account_integration_specific_id} AND integration_specific_entitlement_id = {entitlement_integration_specific_id} AND integration_specific_resource_id = {resource_integration_specific_id};

Microsoft SQL Server

Discovers server logins and per-database users with their role memberships, across all databases on the instance via dynamic SQL.

SQL Server connection string

jdbc:sqlserver://sql.example.com:1433;databaseName=master;encrypt=true;trustServerCertificate=true

SQL Server List Accounts

Unions server principals (logins) with each database’s principals, identifying accounts as name@server or name@database:

DECLARE @SQL NVARCHAR(MAX) = N'';
SET @SQL = N'
SELECT
    CAST(sp.name + ''@server'' AS VARCHAR(255)) AS integration_specific_id,
    sp.name AS username,
    sp.name AS email,
    '''' AS given_name,
    '''' AS family_name,
    CASE WHEN sp.is_disabled = 0 THEN ''ACTIVE'' ELSE ''INACTIVE'' END AS user_status,
    CASE WHEN sp.type_desc IN (''SQL_LOGIN'', ''WINDOWS_LOGIN'', ''WINDOWS_GROUP'') THEN ''USER'' ELSE ''SERVICE'' END AS account_type,
    @@SERVERNAME AS server_name,
    ''master'' AS database_name
FROM master.sys.server_principals sp
WHERE sp.type_desc IN (''SQL_LOGIN'', ''WINDOWS_LOGIN'', ''WINDOWS_GROUP'') ';

SELECT @SQL = @SQL + N'
UNION ALL
SELECT
    CAST(dp.name + ''@'' + ''' + d.name + ''' AS VARCHAR(255)) AS integration_specific_id,
    dp.name AS username,
    dp.name AS email,
    '''' AS given_name,
    '''' AS family_name,
    CASE WHEN dp.type_desc IN (''SQL_USER'', ''WINDOWS_USER'', ''WINDOWS_GROUP'') THEN ''ACTIVE'' ELSE ''INACTIVE'' END AS user_status,
    ''USER'' AS account_type,
    @@SERVERNAME AS server_name,
    ''' + d.name + ''' AS database_name
FROM ' + QUOTENAME(d.name) + '.sys.database_principals dp
WHERE dp.type_desc IN (''SQL_USER'', ''WINDOWS_USER'', ''WINDOWS_GROUP'') '
FROM sys.databases d;

EXEC(@SQL);

Validate Credentials is the same query wrapped to return one row: replace the final EXEC(@SQL); with SET @SQL = 'SELECT TOP 1 * FROM (' + @SQL + ') AS AllResults;'; EXEC(@SQL);

SQL Server List Entitlements

Server roles plus each database’s roles:

DECLARE @SQL NVARCHAR(MAX) = N'';
SET @SQL = N'
    SELECT ''ROLE'' AS entitlement_type, srp.name AS integration_specific_id, ''server'' AS integration_specific_resource_id, 1 AS is_assignable, srp.name AS label
    FROM master.sys.server_principals srp
    WHERE srp.type_desc = ''SERVER_ROLE''
';

SELECT @SQL = @SQL + N'
    UNION ALL
    SELECT ''ROLE'' AS entitlement_type, drp.name AS integration_specific_id, ''' + d.name + ''' AS integration_specific_resource_id, 1 AS is_assignable, drp.name AS label
    FROM ' + QUOTENAME(d.name) + '.sys.database_principals drp
    WHERE drp.type_desc = ''DATABASE_ROLE''
'
FROM sys.databases d;

EXEC(@SQL);

SQL Server Find Entitlement Associations

Server role memberships plus each database’s role memberships:

DECLARE @SQL NVARCHAR(MAX) = N'';

SET @SQL = N'
    SELECT
        CAST(member_principal.name + ''@server'' AS VARCHAR(255)) AS account_id,
        role_principal.name AS integration_specific_entitlement_id,
        ''server'' AS integration_specific_resource_id
    FROM master.sys.server_role_members srm
    JOIN master.sys.server_principals member_principal ON srm.member_principal_id = member_principal.principal_id
    JOIN master.sys.server_principals role_principal ON srm.role_principal_id = role_principal.principal_id
    WHERE member_principal.type_desc IN (''SQL_LOGIN'', ''WINDOWS_LOGIN'', ''WINDOWS_GROUP'')
        AND role_principal.type_desc = ''SERVER_ROLE''
';

SELECT @SQL = @SQL + N'
    UNION ALL
    SELECT
        CAST(dp.name + ''@'' + ' + QUOTENAME(d.name, '''') + N' AS VARCHAR(255)) AS account_id,
        drp.name AS integration_specific_entitlement_id,
        ' + QUOTENAME(d.name, '''') + N' AS integration_specific_resource_id
    FROM ' + QUOTENAME(d.name) + N'.sys.database_role_members drm
    JOIN ' + QUOTENAME(d.name) + N'.sys.database_principals dp ON drm.member_principal_id = dp.principal_id
    JOIN ' + QUOTENAME(d.name) + N'.sys.database_principals drp ON drm.role_principal_id = drp.principal_id
    WHERE dp.type_desc IN (''SQL_USER'', ''WINDOWS_USER'', ''WINDOWS_GROUP'')
        AND drp.type_desc = ''DATABASE_ROLE''
'
FROM sys.databases d;

EXEC(@SQL);

SQL Server List Resources

SELECT @@SERVERNAME AS integration_specific_id, 'SQL Server Instance' AS label, 'SERVER_INSTANCE' AS resource_type
UNION ALL
SELECT d.name AS integration_specific_id, d.name AS label, 'DATABASE' AS resource_type FROM sys.databases d;

Using stored procedures

Stored procedures work as long as the final result has the correct column labels. For reads, extract the procedure’s results into a table variable, then SELECT from it with the right aliases:

DECLARE @TempTable TABLE (
    id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    uname VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL,
    external_id VARCHAR(50) NOT NULL,
    custom_attribute VARCHAR(50) NULL
);
INSERT INTO @TempTable
EXEC selectAllUsers;

SELECT CAST(id AS varchar(50)) AS integration_specific_id, first_name AS given_name, last_name as family_name, uname as username, status as user_status, custom_attribute from @TempTable;

For writes, call the procedure with the templated field:

EXEC deactivateAccount @account_id = {account_id};

Oracle DB

Discovers Oracle accounts and granted roles from the DBA views.

Oracle connection string

Using the SID:

jdbc:oracle:thin:@<host>:<port>:<SID>

Using the service name:

jdbc:oracle:thin:@//<host>:<port>/<service>

Oracle List Accounts

SELECT
    username as integration_specific_id,
    username,
    username as email,
    CASE WHEN account_status = 'LOCKED' THEN 'INACTIVE'
         WHEN account_status = 'EXPIRED' THEN 'DEPROVISIONED'
         ELSE 'ACTIVE' END as user_status,
    CASE WHEN profile = 'DEFAULT' THEN 'USER'
         ELSE 'SERVICE' END as account_type
FROM dba_users

Validate Credentials is the same query with WHERE ROWNUM = 1 appended.

Oracle List Entitlements

SELECT
    'ROLE_' || role as integration_specific_id,
    SYS_CONTEXT('USERENV', 'DB_NAME') as integration_specific_resource_id,
    'ROLE' as entitlement_type,
    'Role - ' || role as label,
    1 as is_assignable
FROM dba_roles

Oracle Find Entitlement Associations

SELECT
    grantee as account_id,
    'ROLE_' || granted_role as integration_specific_entitlement_id,
    SYS_CONTEXT('USERENV', 'DB_NAME') as integration_specific_resource_id
FROM dba_role_privs

Oracle versions older than 12.2 limit identifiers to 30 characters. Use integ_specific_entitlement_id and integ_specific_resource_id as the field names in that case.

Oracle List Resources

SELECT
    SYS_CONTEXT('USERENV', 'DB_NAME') as integration_specific_id,
    SYS_CONTEXT('USERENV', 'DB_NAME') as label,
    'DATABASE' as resource_type
FROM dual

Oracle write capabilities

Oracle account writes are DDL, so identifiers take the ! suffix (unquoted), while string literals don’t: Create Account

CREATE USER {email!} IDENTIFIED BY TempPass123 PASSWORD EXPIRE

TempPass123 is a placeholder. Replace it with a password that meets your Oracle password policy. Lumos needs the new account’s ID back from Create Account, so also fill in the Create Account Return ID query. Update Account

ALTER USER {id!} IDENTIFIED EXTERNALLY AS {distinguished_name}

PostgreSQL

Discovers roles from pg_catalog, mapping login capability and validity to account status.

PostgreSQL connection string

jdbc:postgresql://sql.example.com:5432/postgres?ssl=true

PostgreSQL List Accounts

SELECT
    r.rolname as integration_specific_id,
    r.rolname as username,
    COALESCE(r.rolname || '@' || current_database(), r.rolname) as email,
    CASE
        WHEN NOT r.rolcanlogin THEN 'INACTIVE'
        WHEN r.rolvaliduntil < CURRENT_TIMESTAMP THEN 'DEPROVISIONED'
        ELSE 'ACTIVE'
    END as user_status,
    CASE
        WHEN r.rolsuper OR NOT r.rolcanlogin THEN 'SERVICE'
        ELSE 'USER'
    END as account_type
FROM pg_catalog.pg_roles r;

Validate Credentials is the same query with LIMIT 1.

PostgreSQL List Entitlements

SELECT
    'ROLE_' || r.rolname as integration_specific_id,
    current_database() as integration_specific_resource_id,
    'ROLE' as entitlement_type,
    'Role - ' || r.rolname as label,
    true as is_assignable
FROM pg_catalog.pg_roles r;

PostgreSQL Find Entitlement Associations

SELECT
    m.member::regrole::text as account_id,
    'ROLE_' || r.rolname as integration_specific_entitlement_id,
    current_database() as integration_specific_resource_id
FROM pg_catalog.pg_roles r
JOIN pg_catalog.pg_auth_members m ON r.oid = m.roleid;

PostgreSQL List Resources

SELECT
    current_database() as integration_specific_id,
    current_database() as label,
    'DATABASE' as resource_type
FROM pg_database
WHERE datname = current_database();

MySQL

Discovers users from mysql.user, plus privileges and roles from information_schema and mysql.role_edges.

MySQL connection string

jdbc:mysql://mysql.example.com:3306/mysql

MySQL List Accounts

SELECT
    CONCAT(u.User, '@', u.Host) as integration_specific_id,
    u.User as username,
    CONCAT(u.User, '@', u.Host) as email,
    CASE
        WHEN u.account_locked = 'Y' THEN 'INACTIVE'
        WHEN u.password_expired = 'Y' THEN 'DEPROVISIONED'
        ELSE 'ACTIVE'
    END as user_status,
    CASE
        WHEN u.Host = 'localhost' THEN 'SERVICE'
        ELSE 'USER'
    END as account_type
FROM mysql.user u;

Validate Credentials is the same query with LIMIT 1.

MySQL List Entitlements

Schema privileges, roles, and global privileges as three unioned entitlement sets:

SELECT DISTINCT
    CONCAT(privilege_type, '_', table_schema) as integration_specific_id,
    table_schema as integration_specific_resource_id,
    CASE WHEN is_grantable = 'YES' THEN 'GRANT' ELSE 'PRIVILEGE' END as entitlement_type,
    CONCAT(privilege_type, ' ON ', table_schema) as label,
    true as is_assignable
FROM information_schema.schema_privileges
UNION
SELECT DISTINCT
    CONCAT('ROLE_', CONCAT(TO_USER, '@', TO_HOST)) as integration_specific_id,
    COALESCE(SCHEMA_NAME, '') as integration_specific_resource_id,
    'ROLE' as entitlement_type,
    CONCAT('Role - ', TO_USER, '@', TO_HOST) as label,
    true as is_assignable
FROM mysql.role_edges
CROSS JOIN information_schema.schemata
WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'sys')
UNION
SELECT DISTINCT
    CONCAT('GLOBAL_', privilege_type) as integration_specific_id,
    '' as integration_specific_resource_id,
    'GLOBAL_PRIVILEGE' as entitlement_type,
    privilege_type as label,
    true as is_assignable
FROM information_schema.user_privileges;

MySQL Find Entitlement Associations

SELECT
    CONCAT(grantee) as account_id,
    CONCAT('GLOBAL_', privilege_type) as integration_specific_entitlement_id,
    '' as integration_specific_resource_id
FROM information_schema.user_privileges
UNION
SELECT
    CONCAT(grantee) as account_id,
    CONCAT(privilege_type, '_', table_schema) as integration_specific_entitlement_id,
    table_schema as integration_specific_resource_id
FROM information_schema.schema_privileges
UNION
SELECT
    CONCAT(FROM_USER, '@', FROM_HOST) as account_id,
    CONCAT('ROLE_', CONCAT(TO_USER, '@', TO_HOST)) as integration_specific_entitlement_id,
    '' as integration_specific_resource_id
FROM mysql.role_edges;

MySQL List Resources

SELECT
    schema_name as integration_specific_id,
    schema_name as label,
    'DATABASE' as resource_type
FROM information_schema.schemata;

MongoDB

Uses the JDBC driver’s shell-syntax queries against the admin database’s system.users and system.roles collections.

MongoDB connection string

jdbc:mongodb://mongodb.example.com:27017/database

MongoDB Validate Credentials

db.runCommand({
    connectionStatus: 1,
    showPrivileges: true
}).authInfo.authenticatedUserRoles.map(role => ({
    integration_specific_id: role.role,
    username: role.role,
    email: role.role,
    user_status: 'ACTIVE',
    account_type: role.db === 'admin' ? 'SERVICE' : 'USER'
}))[0];

MongoDB List Accounts

db.getSiblingDB('admin').system.users.find({}, {
    user: 1,
    roles: 1
}).map(user => ({
    integration_specific_id: user.user,
    username: user.user,
    email: user.user,
    user_status: 'ACTIVE',
    account_type: user.roles.some(r => r.db === 'admin') ? 'SERVICE' : 'USER'
}));

MongoDB List Entitlements

db.getSiblingDB('admin').system.roles.find({}, {
    role: 1,
    db: 1
}).map(role => ({
    integration_specific_id: 'ROLE_' + role.role,
    integration_specific_resource_id: role.db,
    entitlement_type: 'ROLE',
    label: 'Role - ' + role.role,
    is_assignable: true
}));

MongoDB Find Entitlement Associations

db.getSiblingDB('admin').system.users.find({}, {
    user: 1,
    roles: 1
}).map(user =>
    user.roles.map(role => ({
        account_id: user.user,
        integration_specific_entitlement_id: 'ROLE_' + role.role,
        integration_specific_resource_id: role.db
    }))
).flat();

MongoDB List Resources

[{
    integration_specific_id: db.getName(),
    label: db.getName(),
    resource_type: 'DATABASE'
}];

Snowflake

Discovers users, roles, role inheritance, and effective database, warehouse, and schema access from the SNOWFLAKE.ACCOUNT_USAGE views. Note that a dedicated Snowflake connector also exists in the Integration Library. Use these queries when you want the query-driven JDBC approach instead.

Snowflake connection string

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

Snowflake List Accounts

Rich account listing with MFA, warehouse, and lock state surfaced as custom attributes:

SELECT
    LOGIN_NAME as integration_specific_id,
    LOGIN_NAME as username,
    COALESCE(EMAIL, LOGIN_NAME || '@snowflake.local') as email,
    FIRST_NAME as given_name,
    LAST_NAME as family_name,
    CASE
        WHEN DELETED_ON IS NOT NULL THEN 'DELETED'
        WHEN DISABLED = 'false' OR DISABLED IS NULL THEN 'ACTIVE'
        ELSE 'INACTIVE'
    END as user_status,
    CASE
        WHEN TYPE = 'SERVICE' THEN 'SERVICE'
        WHEN TYPE = 'SNOWFLAKE_SERVICE' THEN 'SERVICE'
        WHEN NAME IN ('SNOWFLAKE', 'SNOWFLAKE_MARKETPLACE', 'WORKSHEETS_APP_USER') THEN 'SERVICE'
        ELSE 'USER'
    END as account_type,
    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,
    CAST(MUST_CHANGE_PASSWORD AS VARCHAR) as must_change_password,
    CAST(SNOWFLAKE_LOCK AS VARCHAR) as is_locked,
    CAST(HAS_PASSWORD AS VARCHAR) as has_password,
    CAST(HAS_RSA_PUBLIC_KEY AS VARCHAR) as has_rsa_key,
    NAME as name_id,
    DISPLAY_NAME as display_name,
    TYPE as user_type
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS
ORDER BY CREATED_ON DESC

Validate Credentials is the same shape with WHERE DELETED_ON IS NULL LIMIT 1.

Snowflake List Entitlements

Roles, role-to-role inheritance, and database, warehouse, and schema access grants as five unioned sets:

SELECT * FROM (
    SELECT
        'ROLE' as entitlement_type,
        'ROLE:' || NAME as integration_specific_id,
        '' as integration_specific_resource_id,
        TRUE as is_assignable,
        NAME as label
    FROM SNOWFLAKE.ACCOUNT_USAGE.ROLES
    WHERE DELETED_ON IS NULL
    UNION ALL
    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,
        TRUE as is_assignable,
        'Inherits ' || gtr.NAME as label
    FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES gtr
    JOIN SNOWFLAKE.ACCOUNT_USAGE.ROLES r ON gtr.NAME = r.NAME
    WHERE gtr.GRANTED_ON = 'ROLE' AND gtr.DELETED_ON IS NULL AND r.DELETED_ON IS NULL
    UNION ALL
    SELECT DISTINCT
        'DATABASE_ACCESS' as entitlement_type,
        'DB_ACCESS:' || NAME || ':' || GRANTEE_NAME as integration_specific_id,
        'DATABASE:' || NAME as integration_specific_resource_id,
        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')
    UNION ALL
    SELECT DISTINCT
        'WAREHOUSE_ACCESS' as entitlement_type,
        'WH_ACCESS:' || NAME || ':' || GRANTEE_NAME as integration_specific_id,
        'WAREHOUSE:' || NAME as integration_specific_resource_id,
        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')
    UNION ALL
    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,
        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')
) entitlements
ORDER BY entitlement_type, label

Snowflake Find Entitlement Associations

Direct role grants, role inheritance, and effective database, warehouse, and schema access through assigned roles:

SELECT * FROM (
    SELECT
        u.LOGIN_NAME as account_id,
        'ROLE:' || gtu.ROLE as integration_specific_entitlement_id,
        '' 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
    WHERE gtu.DELETED_ON IS NULL
    UNION ALL
    SELECT
        GRANTEE_NAME as account_id,
        '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
    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
    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
    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
    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
    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
    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

Snowflake List Resources

SELECT * FROM (
    SELECT
        'DATABASE:' || DATABASE_NAME as integration_specific_id,
        DATABASE_NAME as label,
        'DATABASE' as resource_type
    FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES
    WHERE DELETED IS NULL
    UNION ALL
    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())
        AND WAREHOUSE_NAME IS NOT NULL
    UNION ALL
    SELECT DISTINCT
        'SCHEMA:' || CATALOG_NAME || '.' || SCHEMA_NAME as integration_specific_id,
        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')
) resources
ORDER BY resource_type, label

Snowflake Get Last Activity

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

Databricks

Discovers grantees and their catalog, schema, and table privileges from Unity Catalog’s system.information_schema. A dedicated Databricks connector exists in the Integration Library, and Databricks can also connect via Universal REST. Use these queries for the SQL-warehouse approach.

Databricks connection string

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

Databricks Validate Credentials

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

Databricks List Accounts

Derives the account list from grantees across all privilege tables:

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

Databricks List Entitlements

Catalog, schema, and table privileges as three unioned entitlement sets:

SELECT * FROM (
    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
    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
    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

Table-level privileges can be numerous. Drop the third UNION block if you only need catalog and schema governance.

Databricks Find Entitlement Associations

SELECT * FROM (
    SELECT
        grantee as account_id,
        CONCAT(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
    SELECT
        grantee as account_id,
        CONCAT(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
    SELECT
        grantee as account_id,
        CONCAT(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

Databricks List Resources

SELECT * FROM (
    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
    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
    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

Databricks Get Last Activity

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

Compatible engines without a dedicated example

Many databases are wire-compatible with an example above. Use the matching example and change only the connection string:

Azure SQL Database and Azure SQL Managed Instance: use the Microsoft SQL Server queries. Authenticate with a SQL login (username and password). Microsoft Entra ID authentication is not supported.
Amazon RDS and Aurora: use the queries for the underlying engine (MySQL or PostgreSQL). IAM database authentication is not supported, so create a native database user.
MariaDB: use the MySQL queries.
Amazon Redshift and CockroachDB: PostgreSQL-derived. Start from the PostgreSQL queries and verify the catalog views, since system tables differ in places.
Google Cloud SQL and AlloyDB: use the queries for the underlying engine.

The rule in every case: Lumos connects with a native database user and password over JDBC, using the driver for the engine you pick in the connector’s Database Type list.

​How to use these examples

​Sample Application Database

​Schema

​Sample connection string

​Sample read capabilities

​Sample write capabilities

​Microsoft SQL Server

​SQL Server connection string

​SQL Server List Accounts

​SQL Server List Entitlements

​SQL Server Find Entitlement Associations

​SQL Server List Resources

​Using stored procedures

​Oracle DB

​Oracle connection string

​Oracle List Accounts

​Oracle List Entitlements

​Oracle Find Entitlement Associations

​Oracle List Resources

​Oracle write capabilities

​PostgreSQL

​PostgreSQL connection string

​PostgreSQL List Accounts

​PostgreSQL List Entitlements

​PostgreSQL Find Entitlement Associations

​PostgreSQL List Resources

​MySQL

​MySQL connection string

​MySQL List Accounts

​MySQL List Entitlements

​MySQL Find Entitlement Associations

​MySQL List Resources

​MongoDB

​MongoDB connection string

​MongoDB Validate Credentials

​MongoDB List Accounts

​MongoDB List Entitlements

​MongoDB Find Entitlement Associations

​MongoDB List Resources

​Snowflake

​Snowflake connection string

​Snowflake List Accounts

​Snowflake List Entitlements

​Snowflake Find Entitlement Associations

​Snowflake List Resources

​Snowflake Get Last Activity

​Databricks

​Databricks connection string

​Databricks Validate Credentials

​Databricks List Accounts

​Databricks List Entitlements

​Databricks Find Entitlement Associations

​Databricks List Resources

​Databricks Get Last Activity

​Compatible engines without a dedicated example