The Cure for IGAD

Use Baton-SQL to build a custom connector

Build a custom connector to sync users, groups, and roles from SQL-enabled applications.

The Baton-SQL connector is a generic connector for applications that use a SQL database. If you have back-office, home-grown, or on-prem applications that do not have an API but are structured around an SQL database, use the Baton-SQL connector to bring those apps’ access data into ConductorOne.

This document provides detailed instructions for configuring a custom connector using Baton-SQL. The Baton-SQL connector allows you to sync identities, resources, and permissions from various SQL databases into ConductorOne.

Supported database engines

The Baton-SQL connector currently supports the following database engines:

  • PostgreSQL
  • MySQL
  • Microsoft SQL Server
  • Oracle

Configuration overview

The Baton-SQL connector is configured using a YAML file that defines:

  • Application metadata
  • Database connection details
  • Resource types to sync (users, groups, roles, etc.)
  • Entitlements that can be granted to resources
  • Grants that define which principals have which entitlements
  • Provisioning rules for granting/revoking entitlements

This document walks you through the process of composing the YAML file. You can see examples of complete YAML files for various apps and services at the end of this doc, and in the Examples folder in the baton-sql connector repo.

Configuring the YAML file

The basic structure of a Baton-SQL connector configuration file includes:

# Application metadata
app_name: Your Application Name
app_description: Optional description of your application

# Database connection
connect:
  dsn: "connection_string"
  # Optional: separate credentials if needed
  user: "username"
  password: "password"

# Resource definitions
resource_types:
  # Resource type configurations...

Database connection configuration

The connect section defines how to connect to your database:

connect:
  # Connection string (Data Source Name)
  dsn: "mysql://user:password@hostname:port/database?parseTime=true"
  
  # Optional: Separate credentials (useful if username/password require URL encoding)
  user: "${DB_USER}"  # Environment variable support
  password: "${DB_PASSWORD}"

Connection string examples for supported databases:

MySQL:

connect:
  dsn: "mysql://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}?charset=utf8mb4&parseTime=True&loc=Local"

Oracle:

connect:
  dsn: "oracle://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}"

SQL Server:

connect:
  dsn: "sqlserver://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}?database=${DB_NAME}"

Resource type configuration

Resource types define the entities you want to sync to ConductorOne. Common resource types include users, groups, and roles.

Basic structure:

resource_types:
  user:  # Resource type key
    name: "User"  # Display name
    description: "User accounts in the system"
    
    # Resource configuration sections:
    list: # How to list resources
      # ...
    
    static_entitlements: # Predefined entitlements
      # ...
    
    grants: # How to discover existing grants
      # ...
    
    account_provisioning: # Settings for creating new accounts
      # ...

Listing resources

The list section defines how to query resources from your database:

list:
  # Variables available in the query
  vars:
    status: "'active'"
  
  # SQL query to fetch resources
  query: |
    SELECT
      id,
      username,
      email,
      status
    FROM users
    WHERE status = ?<status>
    ORDER BY id ASC
    LIMIT ?<Limit> OFFSET ?<Offset>
  
  # Mapping configuration
  map:
    # ...
  
  # Pagination configuration
  pagination:
    # ...

Mapping resources

The map section defines how to transform database query results into ConductorOne resources:

map:
  # Required fields
  id: ".id"  # Maps the 'id' column to the resource ID
  display_name: ".username"  # Human-readable name
  description: "string(.department) + ' department user'"  # Can use CEL expressions
  
  # Optional traits specific to this resource type
  traits:
    user:  # For user resources
      emails:
        - ".email"  # Direct field mapping
      status: ".status"  # Maps to active, inactive, etc.
      login: ".username"
      profile:
        department: ".department"
        joined_date: ".created_at"
    
    # Or for role resources
    role:
      profile:
        role_name: ".role_name"

Field mappings use CEL (Common Expression Language) to transform data. The dot syntax (e.g., .column_name) references a column from the query result.

Pagination

The pagination section defines how to handle large result sets:

pagination:
  strategy: "offset"  # Options: "offset" or "cursor"
  primary_key: "id"  # Column used for pagination tracking

Pagination strategies:

  • offset: Uses LIMIT and OFFSET in SQL queries
  • cursor: Uses a value-based approach where records after a certain key value are fetched

Entitlements

Entitlements define permissions that can be granted to resources.

Static entitlements

Static entitlements are predefined and don’t require a database query:

static_entitlements:
  - id: "access"  # Unique identifier
    display_name: "Basic Access"
    description: "Provides basic access to the application"
    purpose: "access"  # Purpose: "access", "assignment", "permission"
    grantable_to:
      - "user"  # Resource types that can receive this entitlement
    
    # Optional: Provisioning configuration
    provisioning:
      # ...

Dynamic entitlements

Dynamic entitlements are fetched from the database:

entitlements:
  query: |
    SELECT
      id,
      name,
      description
    FROM permissions
    ORDER BY id ASC
    LIMIT ?<Limit> OFFSET ?<Offset>
  
  map:
    - id: ".id"
      display_name: ".name"
      description: ".description"
      purpose: "permission"
      grantable_to:
        - "user"
        - "group"
  
  pagination:
    strategy: "offset"
    primary_key: "id"

Grants

Grants define which principals (users/groups) have which entitlements:

grants:
  - query: |
      SELECT
        user_id,
        role_name
      FROM user_roles
      LIMIT ?<Limit> OFFSET ?<Offset>
    
    map:
      - skip_if: "phpDeserializeStringArray(string(.role_name))[0] != resource.ID"
        principal_id: ".user_id"
        principal_type: "user"
        entitlement_id: "member"
    
    pagination:
      strategy: "offset"
      primary_key: "user_id"

The skip_if field uses a CEL expression to determine whether to skip a grant mapping.

Provisioning

Provisioning defines how to implement entitlement changes:

provisioning:
  vars:
    # Variables available in provisioning queries
    principal_id: "principal.ID"
    role_id: "resource.ID"
  
  grant:
    # SQL statements to execute when granting
    no_transaction: false  # Whether to disable transaction wrapping
    queries:
      - |
        INSERT INTO user_roles (user_id, role_id)
        VALUES (?<principal_id>, ?<role_id>)
  
  revoke:
    # SQL statements to execute when revoking
    queries:
      - |
        DELETE FROM user_roles
        WHERE user_id = ?<principal_id>
        AND role_id = ?<role_id>

Account provisioning

Account provisioning allows creating new accounts directly through ConductorOne:

account_provisioning:
  schema:
    - name: "username"
      description: "The username for the user"
      type: "string"
      placeholder: "newuser"
      required: true
    - name: "email"
      description: "The email of the user"
      type: "string"
      placeholder: "user@example.com"
      required: true
  
  credentials:
    no_password:
      preferred: true
    # Or with random password
    random_password:
      max_length: 128
      min_length: 12
      disallowed_characters: "!@#$%^&*()_+"
      preferred: false
  
  validate:
    vars:
      username: "username"
    query: |
      SELECT id FROM users WHERE username = ?<username>
  
  create:
    vars:
      username: "input.username"
      email: "input.email"
    queries:
      - |
        INSERT INTO users (username, email)
        VALUES (?<username>, ?<email>)

Running the connector

To run the connector, use the following command:

baton-sql --config-path /path/to/config.yaml

Common flags:

  • --client-id and --client-secret: Authentication with ConductorOne
  • --config-path: Path to your configuration file
  • -f/--file: Path to save the sync data
  • -p/--provisioning: Enable provisioning actions
  • --log-level: Set logging verbosity (debug, info, warn, error)

Learn more about deploying self-hosted connector in our docs.

Example configurations

The following are example configurations for commonly used SQL tools.

app_name: Example Application

connect:
  dsn: "mysql://${DB_USER}:${DB_PASS}@${DB_HOST}:3306/${DB_NAME}?parseTime=true"

resource_types:
  user:
    name: "User"
    description: "Represents a user account in the system"
    list:
      query: |
        SELECT 
          id,
          username,
          email,
          created_at,
          status,
          department
        FROM users
        WHERE status = 'active'
        ORDER BY id ASC
        LIMIT ?<Limit> OFFSET ?<Offset>
      map:
        id: ".id"
        display_name: ".username"
        description: "string(.department) + ' department user'"
        traits:
          user:
            emails:
              - ".email"
            status: ".status"
            profile:
              department: ".department"
              joined_date: ".created_at"
      pagination:
        strategy: "offset"
        primary_key: "id"
    static_entitlements:
      - id: "access"
        display_name: "Basic Access"
        description: "Provides basic access to the application"
        purpose: "access"
        grantable_to:
          - "user"
        provisioning:
          vars:
            user_id: "principal.ID"
            access_level: "'basic'"
          grant:
            queries:
              - |
                INSERT INTO user_access (user_id, level)
                VALUES (?<user_id>, ?<access_level>)
          revoke:
            queries:
              - |
                DELETE FROM user_access
                WHERE user_id = ?<user_id>
    grants:
      - query: |
          SELECT 
            user_id,
            access_level,
            granted_at
          FROM user_access
          LIMIT ?<Limit> OFFSET ?<Offset>
        map:
          - skip_if: ".access_level != 'basic'"
            principal_id: ".user_id"
            principal_type: "user"
            entitlement_id: "access"
        pagination:
          strategy: "offset"
          primary_key: "user_id"

app_name: WordPress

connect:
  dsn: "mysql://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}?charset=utf8mb4&parseTime=True&loc=Local"

resource_types:
  user:
    name: "User"
    description: "A user within the WordPress system"
    list:
      query: |
        SELECT
          u.ID AS user_id,
          u.user_login AS username,
          u.user_email AS email,
          u.user_registered AS created_at
        FROM wp_users u
        ORDER BY user_id ASC
        LIMIT ?<Limit> OFFSET ?<Offset>
      map:
        id: ".user_id"
        display_name: ".username"
        description: ".email"
        traits:
          user:
            emails:
              - ".email"
            status: "active"
            login: ".username"
            profile:
              name: ".username"
      pagination:
        strategy: "offset"
        primary_key: "user_id"
    account_provisioning:
      schema:
        - name: "username"
          description: "The username of the user"
          type: "string"
          placeholder: "user"
          required: true
        - name: "email"
          description: "The email of the user"
          type: "string"
          placeholder: "user@example.com"
          required: true
      credentials:
        no_password:
          preferred: true
      validate:
        vars:
          email: "email"
        query: |
          SELECT u.ID FROM wp_users u WHERE u.user_email = ?<email>
      create:
        vars:
          username: "input.username"
          email: "input.email"
        queries:
          - "INSERT INTO wp_users (user_login, user_email) VALUES (?<username>, ?<email>)"

app_name: Oracle

connect:
  dsn: "oracle://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_SERVICE}"

resource_types:
  user:
    name: "User"
    description: "A user within the Oracle system"
    list:
      query: |
        SELECT
          USERNAME, USER_ID, ACCOUNT_STATUS, CREATED, LAST_LOGIN
        FROM
          DBA_USERS
      map:
        id: ".USERNAME"
        display_name: ".USERNAME"
        description: ""
        traits:
          user:
            status: ".ACCOUNT_STATUS == 'OPEN' ? 'enabled' : 'disabled'"
            status_details: ".ACCOUNT_STATUS != 'OPEN' ? .ACCOUNT_STATUS : ''"
            login: ".USERNAME"
            profile:
              username: ".USERNAME"
              user_id: ".USER_ID"
              created_at: ".CREATED"
              last_login: ".LAST_LOGIN"
    account_provisioning:
      schema:
        - name: "username"
          description: "The username for the Oracle user"
          type: "string"
          placeholder: "NEW_USER"
          required: true
      credentials:
        no_password:
          preferred: true
      validate:
        vars:
          username: "username"
        query: |
          SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER(?<username>)
      create:
        vars:
          username: "username"
        no_transaction: true
        queries:
          - |
            CREATE USER ?<username|unquoted> DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK
          - |
            GRANT CREATE SESSION TO ?<username|unquoted>

app_name: SQL Server AD Integration

connect:
  dsn: "sqlserver://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}?database=master"

resource_types:
  login:
    name: "SQL Server Login"
    description: "A login account in SQL Server"
    list:
      query: |
        SELECT 
          name AS login_name,
          principal_id,
          type_desc AS login_type,
          CASE 
            WHEN is_disabled = 1 THEN 'disabled'
            ELSE 'enabled'
          END AS account_status,
          create_date,
          modify_date,
          default_database_name
        FROM sys.server_principals
        WHERE type IN ('S', 'U', 'G')
        ORDER BY principal_id ASC
        OFFSET ?<Offset> ROWS FETCH NEXT ?<Limit> ROWS ONLY
      map:
        id: ".login_name"
        display_name: ".login_name"
        description: "'SQL Server ' + .login_type + ' login'"
        traits:
          user:
            status: ".account_status"
            login: ".login_name"
            profile:
              created_at: ".create_date"
              modified_at: ".modify_date"
              default_database: ".default_database_name"
              login_type: ".login_type"
      pagination:
        strategy: "offset"
        primary_key: "principal_id"
  
  server_role:
    name: "Server Role"
    description: "A server-level role in SQL Server"
    list:
      query: |
        SELECT
          name AS role_name,
          principal_id,
          type_desc,
          create_date,
          modify_date,
          is_fixed_role
        FROM sys.server_principals
        WHERE type = 'R'
        ORDER BY principal_id ASC
        OFFSET ?<Offset> ROWS FETCH NEXT ?<Limit> ROWS ONLY
      map:
        id: ".role_name"
        display_name: ".role_name"
        description: "(.is_fixed_role == 1 ? 'Fixed' : 'User-defined') + ' server role'"
        traits:
          role:
            profile:
              created_at: ".create_date"
              modified_at: ".modify_date"
              is_fixed_role: ".is_fixed_role"
              type: ".type_desc"
      pagination:
        strategy: "offset"
        primary_key: "principal_id"

    static_entitlements:
      - id: "member"
        display_name: "resource.DisplayName + ' Membership'"
        description: "'Member of the ' + resource.DisplayName + ' server role'"
        purpose: "assignment"
        grantable_to:
          - "login"
        provisioning:
          vars:
            principal_name: "principal.ID"
            role_name: "resource.ID"
          grant:
            queries:
              - |
                ALTER SERVER ROLE [?<role_name>] ADD MEMBER [?<principal_name>]
          revoke:
            queries:
              - |
                ALTER SERVER ROLE [?<role_name>] DROP MEMBER [?<principal_name>]
    
    grants:
      - query: |
          SELECT 
            rm.role_principal_id,
            sr.name AS role_name,
            rm.member_principal_id,
            sp.name AS member_name
          FROM sys.server_role_members rm
          JOIN sys.server_principals sr ON rm.role_principal_id = sr.principal_id
          JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
          WHERE sr.type = 'R'
          ORDER BY sr.name
          OFFSET ?<Offset> ROWS FETCH NEXT ?<Limit> ROWS ONLY
        map:
          - skip_if: ".role_name != resource.ID"
            principal_id: ".member_name"
            principal_type: "login"
            entitlement_id: "member"
        pagination:
          strategy: "offset"
          primary_key: "role_principal_id"

app_name: HR System

connect:
  dsn: "sqlserver://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}?database=HRDatabase"

resource_types:
  employee:
    name: "Employee"
    description: "Employee records in the HR system"
    list:
      query: |
        SELECT
          EmployeeID,
          FirstName,
          LastName,
          Email,
          JobTitle,
          Department,
          CASE 
            WHEN Status = 'Active' THEN 'enabled'
            ELSE 'disabled'
          END AS account_status,
          HireDate,
          ManagerID
        FROM Employees
        ORDER BY EmployeeID
        OFFSET ?<Offset> ROWS FETCH NEXT ?<Limit> ROWS ONLY
      map:
        id: ".EmployeeID"
        display_name: ".FirstName + ' ' + .LastName"
        description: ".JobTitle + ' in ' + .Department"
        traits:
          user:
            emails:
              - ".Email"
            status: ".account_status"
            login: ".Email"
            profile:
              employee_id: ".EmployeeID"
              job_title: ".JobTitle"
              department: ".Department"
              hire_date: ".HireDate"
              manager_id: ".ManagerID"
      pagination:
        strategy: "offset"
        primary_key: "EmployeeID"
  
  department:
    name: "Department"
    description: "Departments in the organization"
    list:
      query: |
        SELECT
          DepartmentID,
          DepartmentName,
          Description,
          ManagerID
        FROM Departments
        ORDER BY DepartmentID
        OFFSET ?<Offset> ROWS FETCH NEXT ?<Limit> ROWS ONLY
      map:
        id: ".DepartmentID"
        display_name: ".DepartmentName"
        description: ".Description"
        traits:
          group:
            profile:
              manager_id: ".ManagerID"
      pagination:
        strategy: "offset"
        primary_key: "DepartmentID"
    
    static_entitlements:
      - id: "member"
        display_name: "resource.DisplayName + ' Department Member'"
        description: "'Member of the ' + resource.DisplayName + ' department'"
        purpose: "assignment"
        grantable_to:
          - "employee"
        provisioning:
          vars:
            employee_id: "principal.ID"
            department_id: "resource.ID"
          grant:
            queries:
              - |
                UPDATE Employees
                SET Department = (SELECT DepartmentName FROM Departments WHERE DepartmentID = ?<department_id>)
                WHERE EmployeeID = ?<employee_id>
          revoke:
            queries:
              - |
                UPDATE Employees
                SET Department = NULL
                WHERE EmployeeID = ?<employee_id>
                AND Department = (SELECT DepartmentName FROM Departments WHERE DepartmentID = ?<department_id>)
    
    grants:
      - query: |
          SELECT
            e.EmployeeID,
            d.DepartmentID,
            d.DepartmentName
          FROM Employees e
          JOIN Departments d ON e.Department = d.DepartmentName
          ORDER BY d.DepartmentID
          OFFSET ?<Offset> ROWS FETCH NEXT ?<Limit> ROWS ONLY
        map:
          - skip_if: ".DepartmentID != resource.ID"
            principal_id: ".EmployeeID"
            principal_type: "employee"
            entitlement_id: "member"
        pagination:
          strategy: "offset"
          primary_key: "DepartmentID"

app_name: CRM System

connect:
  dsn: "sqlserver://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}?database=CRM"

resource_types:
  user:
    name: "CRM User"
    description: "Users of the CRM system"
    list:
      query: |
        SELECT
          UserID,
          Username,
          Email,
          FirstName,
          LastName,
          UserRole,
          CASE 
            WHEN IsActive = 1 THEN 'enabled'
            ELSE 'disabled'
          END AS status,
          CreatedDate,
          LastLoginDate
        FROM CRM_Users
        ORDER BY UserID
        OFFSET ?<Offset> ROWS FETCH NEXT ?<Limit> ROWS ONLY
      map:
        id: ".UserID"
        display_name: ".FirstName + ' ' + .LastName"
        description: "'CRM user with role: ' + .UserRole"
        traits:
          user:
            emails:
              - ".Email"
            status: ".status"
            login: ".Username"
            last_login: ".LastLoginDate"
            profile:
              created_date: ".CreatedDate"
              role: ".UserRole"
      pagination:
        strategy: "offset"
        primary_key: "UserID"
    
    account_provisioning:
      schema:
        - name: "username"
          description: "Username for CRM access"
          type: "string"
          placeholder: "john.doe"
          required: true
        - name: "email"
          description: "Email address"
          type: "string"
          placeholder: "john.doe@example.com"
          required: true
        - name: "first_name"
          description: "First name"
          type: "string"
          placeholder: "John"
          required: true
        - name: "last_name"
          description: "Last name"
          type: "string"
          placeholder: "Doe"
          required: true
      credentials:
        random_password:
          max_length: 16
          min_length: 12
          disallowed_characters: "<>&\""
          preferred: true
      validate:
        vars:
          username: "input.username"
          email: "input.email"
        query: |
          SELECT UserID FROM CRM_Users WHERE Username = ?<username> OR Email = ?<email>
      create:
        vars:
          username: "input.username"
          email: "input.email"
          first_name: "input.first_name"
          last_name: "input.last_name"
          password: "credentials.password"
        queries:
          - |
            INSERT INTO CRM_Users (
              Username, Email, FirstName, LastName, 
              PasswordHash, UserRole, IsActive, CreatedDate
            )
            VALUES (
              ?<username>, ?<email>, ?<first_name>, ?<last_name>,
              HASHBYTES('SHA2_256', ?<password>), 'Basic User', 1, GETDATE()
            )
  
  permission:
    name: "CRM Permission"
    description: "Access control permissions within the CRM"
    list:
      query: |
        SELECT
          PermissionID,
          PermissionName,
          Description,
          Module
        FROM CRM_Permissions
        ORDER BY PermissionID
        OFFSET ?<Offset> ROWS FETCH NEXT ?<Limit> ROWS ONLY
      map:
        id: ".PermissionID"
        display_name: ".PermissionName"
        description: ".Description + ' in ' + .Module + ' module'"
        traits:
          role:
            profile:
              module: ".Module"
      pagination:
        strategy: "offset"
        primary_key: "PermissionID"
    
    static_entitlements:
      - id: "access"
        display_name: "resource.DisplayName + ' Access'"
        description: "'Permission to use ' + resource.DisplayName"
        purpose: "permission"
        grantable_to:
          - "user"
        provisioning:
          vars:
            user_id: "principal.ID"
            permission_id: "resource.ID"
          grant:
            queries:
              - |
                INSERT INTO CRM_UserPermissions (UserID, PermissionID, GrantedDate)
                VALUES (?<user_id>, ?<permission_id>, GETDATE())
          revoke:
            queries:
              - |
                DELETE FROM CRM_UserPermissions
                WHERE UserID = ?<user_id> AND PermissionID = ?<permission_id>
    
    grants:
      - query: |
          SELECT
            up.UserID,
            up.PermissionID,
            p.PermissionName,
            up.GrantedDate
          FROM CRM_UserPermissions up
          JOIN CRM_Permissions p ON up.PermissionID = p.PermissionID
          ORDER BY up.PermissionID
          OFFSET ?<Offset> ROWS FETCH NEXT ?<Limit> ROWS ONLY
        map:
          - skip_if: ".PermissionID != resource.ID"
            principal_id: ".UserID"
            principal_type: "user"
            entitlement_id: "access"
        pagination:
          strategy: "offset"
          primary_key: "PermissionID"