> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lumos.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

1. Copy the queries for the capabilities you need. At minimum, Validate Credentials and List Accounts.
2. 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.
3. 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:

```bash theme={null}
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.

```sql theme={null}
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.

```bash theme={null}
python lumos_generator.py --server sql.example.com --database LumosDemo --uid your_username --pwd your_password
```

<AccordionGroup>
  <Accordion title="Sample data generator script (lumos_generator.py)">
    ```python lumos_generator.py theme={null}
    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()
    ```
  </Accordion>

  <Accordion title="Verify the setup">
    Run these queries to confirm the tables, account distribution and entitlement associations.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>
</AccordionGroup>

### Sample connection string

```text theme={null}
jdbc:sqlserver://sql.example.com:1433;databaseName=LumosDemo;encrypt=true;trustServerCertificate=true
```

### Sample read capabilities

**Validate Credentials**

```sql theme={null}
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**

```sql theme={null}
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**

```sql theme={null}
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**

```sql theme={null}
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**

```sql theme={null}
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**

```sql theme={null}
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**

```sql theme={null}
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)

```sql theme={null}
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');
```

```sql theme={null}
SELECT integration_specific_id AS id FROM Accounts WHERE email = {email};
```

**Activate Account**

```sql theme={null}
UPDATE Accounts SET user_status = 'ACTIVE' WHERE integration_specific_id = {account_id};
```

**Deactivate Account**

```sql theme={null}
UPDATE Accounts SET user_status = 'INACTIVE' WHERE integration_specific_id = {account_id};
```

**Delete Account** (a soft delete, preserving referential integrity)

```sql theme={null}
UPDATE Accounts SET user_status = 'DELETED' WHERE integration_specific_id = {account_id};
```

**Assign Entitlement**

```sql theme={null}
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**

```sql theme={null}
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

```text theme={null}
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`:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
EXEC deactivateAccount @account_id = {account_id};
```

***

## Oracle DB

Discovers Oracle accounts and granted roles from the DBA views.

### Oracle connection string

Using the SID:

```text theme={null}
jdbc:oracle:thin:@<host>:<port>:<SID>
```

Using the service name:

```text theme={null}
jdbc:oracle:thin:@//<host>:<port>/<service>
```

### Oracle List Accounts

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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**

```sql theme={null}
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**

```sql theme={null}
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

```text theme={null}
jdbc:postgresql://sql.example.com:5432/postgres?ssl=true
```

### PostgreSQL List Accounts

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```text theme={null}
jdbc:mysql://mysql.example.com:3306/mysql
```

### MySQL List Accounts

```sql theme={null}
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:

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```text theme={null}
jdbc:mongodb://mongodb.example.com:27017/database
```

### MongoDB Validate Credentials

```javascript theme={null}
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

```javascript theme={null}
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

```javascript theme={null}
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

```javascript theme={null}
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

```javascript theme={null}
[{
    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

```text theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```text theme={null}
jdbc:databricks://example.cloud.databricks.com:443/default;transportMode=http;ssl=1;httpPath=/sql/1.0/warehouses/example;
```

### Databricks Validate Credentials

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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.