phdata-logo Docs About phData
User Manual

Tram User Documentation #

Overview #

Tram is a user and resource provisioning and management application for Snowflake. Tram unifies the creation and management of users, roles, and other Snowflake resources into one comprehensive tool.

Tram is able to manage users, warehouses, databases, schemas, roles, and grants. Resources can be requested individually, or organized into ‘workspaces’ for more complex information architectures that require multiple databases and multiple user access levels.

Users interact with Tram through a connection with Active Directory, or with configuration files.

Tram will print out the changes it is making to the environment. The below image shows Tram generated changes.

Installation Requirements #

One of the best features of the TRAM application beyond its provisioning abilities is TRAM’s fairly minimal footprint when installed in your infrastructure; TRAM is built to plug right in. Commodity hardware will usually suffice whether setup on premise or in the cloud as most of what TRAM does is network traffic back and forth between the host machine and Snowflake.

At a minimum, we would recommend the following configurations:

  • A dual core machine with at least 2GB of RAM
  • A few gigabytes of general purpose storage
  • JAVA 11 JDK (Snowflake JDBC compatibility)
  • A Snowflake account with SYSADMIN & SECURITYADMIN

Optional but strongly encouraged:

  • A hosted git repository (ex: GitHub, Bitbucket, GitLab) to store the information architecture models
  • A build pipeline tool (ex: Github actions, AWS code build, Bitbucket pipelines) for information architecture deployment automation via pull requests

Entirely optional:
Instead of using pull requests in order to automate your provisioning with Snowflake, TRAM can also integrate with the following sources of change:

  • IT Service Management(ITSM) Ticketing System, TRAM currently supports JIRA Service Management and ServiceNow.

Application Flags #

Currently, trams supports provision, and deprovision subcommands. If you attempt to pass a command that isn’t supported by Tram, you will get an error, and a printout of the acceptable commands, along with their optional parameters.

Usage: <main class> [command] [command options]
  Commands:
    provision      Command used to provision various Snowflake resources
      Usage: provision [options]
        Options:
          --ad-groups
            Provision groups that sync with Active Directory. Cannot be run 
            with the --objects or --groups arguments
            Default: false
          --colorize
            Pretty print output will be colored:  Green (Added), Red 
            (Removed), Yellow (Changed)
            Default: false
          --dry-run
            Validate files and print statements that would be run into the 
            file 'tram-statements.sql'.
            Default: false
          --groups
            Provision groups, not including Active Directory synced groups. 
            Cannot be run with the --ad-groups flag
            Default: false
          --no-connection
            Disable the connection to Snowflake and run without credentials
            Default: false
          --objects
            Provision resources in the the 'objects' directory. Cannot be run 
            with the --ad-groups flag.
            Default: false
        * --stack
            Directory location of the stack to process
          --validate-sql
            Beta feature. Attempt to validate resulting SQL against an 
            internal grammar
            Default: false
          --disable-pretty-print
            Disable pretty printing. Useful with the '--ad-groups --dry-run' 
            flags where pretty print output can be large.

    deprovision      Command used to deprovision various Snowflake resources
      Usage: deprovision [options]
        Options:
          --delete-everything
            Deprovision all resources in directory location
            Default: false
          --dry-run
            Validate files and print statements that would be run into the 
            file 'tram-statements.sql'.
            Default: false
          --no-connection
            Disable the connection to Snowflake and run without credentials
            Default: false
        * --stack
            Directory location of the stack to process

Application Configuration #

Application can be configured through application.properties.

Specific application configuration properties #

  • tram.provisioning.properties.sync - If this configuration property is enabled then object properties will be updated, and object properties which are not specified will be reset to default values.

  • tram.snowflake.url can be provided in two formats, plain url or url with private key.

    1. Plain url: snowflake://domain.snowflakecomputing.com. In this case user will be authenticated with his password.
    2. Url with private key: snowflake://domain.snowflakecomputing.com/?private_key_file=pahtToKeyFile&private_key_file_pwd=password.
      This url format can be used if user’s public key is stored in snowflake, so instead of basic password authentication, authentication with public-private key will be used.
      • private_key_file - path to private key on disk and
      • private_key_file_pwd - private key password
  • tram.lease.name should be set to something like TRAM_LEASE to eliminate the possibility of two pipelines running at the same time. If a lease already exists, the process will exit with exit code ‘2’

Configurable owner roles #

TRAM supports configuring owner roles to manage users, objects, roles, grants, and monitors. Owner roles are configured in application.properties. When the owner roles are configured, TRAM uses the configured role for managing respective objects. If the owner roles are not configured or blank with in application.properties, then TRAM uses the below defaults:

tram.owners.roles=SECURITYADMIN
tram.owners.users=SECURITYADMIN
tram.owners.objects=SYSADMIN
tram.owners.grants=SECURITYADMIN
tram.owners.monitors=ACCOUNTADMIN

You can add the above properties in application.properties and modify the role name to use different object owners.

User Deprovisioning #

when the deprovision flag is set to true for a given user, TRAM generates the DISABLE statement for the user by default instead of generating a DROP USER Statement. This behaviour can be changed by adding the below property to application.properties

tram.users.deprovision=drop

Tram generates the DROP USER statement for the user when the above property is set to drop

Individual Resource Creation #

Tram can create resources (database/user/grant/etc) individually. See API documentation below for more details.

Workspaces #

Tram uses workspaces to group together other types of resources. For example, in a more complex ETL job you might have three databases for data: a ‘raw’ table for landing data, ‘staging’ for transformations, and ‘modeled’ for the finalized, ready-to-consume product. Different levels of access control then need to be created for these databases. Developers might have write access to all three, while analysts only have read access to the final ‘modeled’ database.

Workspace templates can be used from any of Tram’s APIs.

Snowflake Group/Model Provisioning #

Stacks #

Tram uses stacks to group resources that will be provisioned together. A stack is a directory containing two sub-directories: groups and models

my-stack/
    groups/
      <group1>.yml
      <group2>.yml
      <dir>/
        <group>.yml
      <dir>/
        <group>.yml
      ...
    models/
      <model1>.yml
      <model2>.yml
      <dir>/
        <model>.yml
      <dir>/
        <model>.yml
      ...

Notice groups and models directories can be nested arbitrarily deep. Groups will scanned regardless of their nesting levels. Models must be referred to by their relative path to the models directory when named in a group file.

When Tram runs, it will iterate over the members of each group, combine the member with the model defined in the group, and create the resulting objects.

NOTE: If you are looking for a Tram model stack to start from, consider the source-product stack in the Tram CICD Template repository.

Groups #

Groups are used to create standardized infrastructure for group members. Groups consist of:

  • a reference to a model
  • a set of default arguments
  • a list of members with member attributes

Group Example #

A group definition within your standard-project.yaml group directory might look like the following:

model: "standard-project"
defaults:
  env: dev
members:
  - username: bduncan
    email: bduncan@gmail.com
  - username: awiley
    email: awiley@gmail.com

Where:

  • model: name of the model to be used with this group
  • defaults: default attributes to apply to each member
  • members: list of members, members each having attributes that become arguments to the model

Groups With Active Directory Synchronization #

Instead of defining individual group members in the file, Tram can be configured to read group members and attributes from Active Directory using the groupDN or groupDNs properties. This will create a workspace for each member in your active directory group for the specified model.

(Note: the Active Directory integration uses the LDAP interface from either a standard domain controller or Active Directory Domain Services)

Example using a single AD group and the groupDN property:

model: "standard-project"
defaults:
  env: dev
groupDN: "CN=project_members,OU=groups,DC=phdata,DC=io"

Example using multiple AD groups using the groupDNs property:

model: "standard-project"
defaults:
  env: dev
groupDNs: 
- "CN=project_members_1,OU=groups,DC=phdata,DC=io"
- "CN=project_members_2,OU=groups,DC=phdata,DC=io"
- "CN=project_members_3,OU=groups,DC=phdata,DC=io"

Deprovisioning Groups and Members #

In addition to the --deprovision flag, entire groups or individual members can be deprovisioned using the deprovision: true flag.

Adding the flag to the top-level group will deprovision the objects associated with every member in the group.

Adding the flag to a single member will deprovision only the objects associated with that member.

model: "standard-project"
deprovision: true # Deprovision the entire group
defaults:
  env: dev
members:
  - username: bduncan
    deprovision: true # Deprovision a single member
    email: bduncan@gmail.com
  - username: awiley
    email: awiley@gmail.com

Schemas, roles and roleGrants can also be dropped individually by setting deprovision flag within the model file

databases:
  - name: "USER_WORKSPACES"
    schemas:
      - name: "USER_{USERNAME}"
        deprovision: CASCADE # Options are CASCADE | RESTRICT | TRUE (uses CASCADE)

roles:
  - name: "{{env}}_{{role_name}}"
    deprovision: true

roleGrants:
  - name: {{env}}_{{project}}_{{access}}
    users: 
      - {{username}}
    deprovision: true

Dropping Objects #

When using the provision subcommand, Tram will using a pending_drops table to collect all drops.

When using the deprovision subcommand, drops will be applied at runtime.

Pending Drops Table #

By default, Tram pushes drop statements to the pending_drops table. After the records in the pending_drops table has been reviewed, the SQL can be manually executed by an administrator.

Example query for pending drops:

SELECT 'USE ROLE ' || role_name || ';
 ' ||  sql || ';' FROM pending_drops where processed_time IS NULL ORDER BY insert_time ASC, sequence ASC;
  • ID STRING: unique id of this statement
  • OBJECT_NAME STRING: name of the object being dropped
  • OBJECT_TYPE STRING: type of the object being dropped
  • SEQUENCE NUMBER: order in which the statement should be processed
  • ROLE_NAME STRING: name of the role that should be used to execute this statement
  • SQL STRING: the statement
  • INSERT_TIME TIMESTAMP_NTZ(9): time this record was added to the table
  • PROCESSED_TIME TIMESTAMP_NTZ(9): time this record was executed (intended to be set by the administrator)
Executing Drops at Runtime #

Tram can be configured to execute drops at runtime using the --max-drops <number> flag.

If this flag is enabled, Tram will calculate the number of drops that will be executed. If the number of drops is greater than --max-drops, an exception will be thrown to give the administrator a chance to review the drops.

The --max-drops flag is also enforced when the --dry-run flag is set.

Models #

Models define the set of Snowflake objects that will be created for a single group member. They use the Handlebars templating for group member attribute arguments.

A model has a section for each available resource type. Available resource types are:

  • users
  • roles
  • databases
  • warehouses
  • privileges
  • roleGrants
  • resourceMonitors

Each resource type takes a list of the resource to be created. For example, if you wanted to provision multiple roles:

roles:
  - role1
  - role2

Models are used in conjunction with Groups.

Using the group above, we can parameterize the username and email fields to create users for each member of a group.

users:
  - name: "{{username}}"
    parameters:
      - name: EMAIL
        value: "'{{email}}'"

Handlebars String Helpers

Handlebars string helpers are available to help with string manipulation.

Example uppercasing using the string helper:

{{upper username}}

A full list of string helpers can be found here.

Identifer Extension Tram provides a special Handlebar tag called identifier that will force strings to conform to Snowflake identifier standards.

To use the identifier tag, wrap any string in the tag: {{#identifier}}$first ^name{{/identifier}}.

The identifier tag will:

  • Remove special characters except $ dollar signs
  • Remove all leading $ dollar signs
  • Replace all spaces with _ underscores

For example, the above example will replace $$first ^name with first_name.

Example User Workspace Model #

This is a model used for creating user workspaces. The model will create a user that can login, is required to change their password, and has access to a database/warehouse for running queries:

users:
  - name: "{{username}}"
    parameters:
      - name: PASSWORD
        value: "'default_pw'"
      - name: MUST_CHANGE_PASSWORD
        value: true
      - name: EMAIL
        value: "'{{email}}'"
      - name: DEFAULT_ROLE
        value: "WS_{{username}}"
      - name: LOGIN_NAME
        value: "'{{email}}'"
      - name: DEFAULT_WAREHOUSE
        value: "INTERVIEW_WH"
roles:
  - name: "WS_{{username}}"
databases:
  - name: "USER_{{username}}"
    schemas:
      - name: schema1
        transient: false
        stages:
          - name: my_external_stage
            storageIntegration: "YOUR_STORAGE_INTEGRATION"
            url: "s3://yourbucket/data/user_{{username}}/"
            parameters:
            - name: FILE_FORMAT
              value: "PARQUET"
          - name: my_internal_stage
            parameters:
            - name: FILE_FORMAT
              value: "(type = 'PARQUET')"
warehouses:
 - name: WAREHOUSE_DEFAULT
   parameters:
     - name: WAREHOUSE_SIZE
       value: MEDIUM
privileges:
  - privilege: "ALL PRIVILEGES"
    objectType: DATABASE
    future: false // Optional. Defaults to 'false'
    objectName: "USER_{{username}}"
    roleName: "WS_{{username}}"
    grantOption: false // Optional. Defaults to 'false'
    currentGrants: REVOKE | COPY // Optional, only applies to 'ownership' grants
roleGrants:
  - name: WAREHOUSE_DEFAULT
    roles:
      - "WS_{{username}}"
  - name: PHDATA_S3_READ
    roles:
      - "WS_{{username}}"
resourceMonitors:
  - name: "{{env}}_{{name}}"
    creditQuota: 100
    frequency: WEEKLY
    startTimestamp: "'IMMEDIATELY'"
    endTimestamp: ""
    triggers:
      - threshold: 50
        action: SUSPEND

Object Types, Parameters, and Arguments #

The sections below document required and optional fields for each object type.

Extra properties objectProperties and objectParams can be configured in the parameters block

Not all properties can be updated after an object is created. See Updating Snowflake objects for more information.

Users #

users:
  - name: <username>
    parameters: 
      - name: <param name>
        value: <param value>

Warehouses #

warehouses:
 - name: <warehouse name>
   parameters: 
     - name: <param name>
       value: <param value>

Roles #

roles:
  - name: <name of role>

Databases #

databases:
  - name: "USER_{{username}}"
    schemas: // (optional)
      - name: schema1
        transient: false
        stages: // (optional)
          - name: <stage name>
            storageIntegration: <storage integration>
            url: <url>
            parameters: 
             - name: <param name>
               value: <param value>

Privileges #

Privileges support ROLES and SHARES. If a privilege is granted to a role, use the roleName attribute as shown in the example below

privileges:
  - privilege: <privilege type> // e.g 'USAGE'
    objectType: <object type> // e.g 'WAREHOUSE'
    future: false // Optional. Defaults to 'false'
    objectName: <object name> // e.g 'WAREHOUSE_1'
    roleName: <role name to grant privilege> // e.g 'ROLE_1'
    grantOption: false // Optional. Defaults to 'false'

If the privilege is granted to a share, use the shareName attribute as shown in the example below

privileges:
  - privilege: <privilege type> // e.g 'USAGE'
    objectType: <object type> // e.g 'DATABASE'
    objectName: <object name> // e.g 'DEMO_DATABASE'
    shareName: <share name to grant privilege> // e.g 'DEMO_SHARE1'
  - privilege: 'USAGE'
    objectType: 'SCHEMA'
    objectName: 'DEMO_DATABASE.SCHEMA'
    shareName: 'DEMO_SHARE1'
  - privilege: 'SELECT'
    objectType: 'ALL TABLES IN SCHEMA'
    objectName: 'DEMO_DATABASE.SCHEMA'
    shareName: 'DEMO_SHARE1'

roleGrants #

roleGrants:
  - name: <role name> // Role to grant access to
    roles:
      - <role name> // role name to grant the role to (optional if 'users' is supplied)
    users:
      - <username> // username to grant the role to (optional if 'roles' is supplied)

resourceMonitors #

resourceMonitors:
  - name: <name of monitor>
    creditQuota: <how many credits allocated>
    frequency: <how frequently to credit quota resets>
    startTimestamp: <when you want the monitor to start>
    endTimestamp: <when you want the monitor to end>
    triggers:
      - threshold: <At a certain percentage>
        action: <Perform this action>

Name is required, but all other properties are optional and can be completely omitted. If you define a trigger, both the threshold and the action must be defined with a value.

Metadata and Tagging #

Snowflake Tagging Setup #

A tagging admin role must be created and configured before using Snowflake tagging:

The below example will use the role SYSADMIN for applying tags. If you use a different role you would need to grant CREATE TAG for each schema as well as USAGE on the database and schema.

USE ROLE accountadmin;
GRANT apply tag ON account TO ROLE sysadmin;

Set the tag owner to SYSADMIN:

tram.owners.tags=SYSADMIN

By default Snowflake tagging is disabled. Enable tagging by setting the property:

tram.tagging.implementation=snowflake

Creating Snowflake Tags #

Snowflake tags can be created within schema objects:

databases:
  - name: "tag_db"
    schemas:
      - name: tag_schema
        tagObjects:
          - name: "PII" // (required)
            comment: "True if this object contains Personally Identifiable Information (PII) else False" // (optional)
            allowedValues: // (optional)
              - true
              - false

Applying Snowflake Tags #

Tags are applied using the tag name. Multiple tags can be added to each object.

If a tag is being applied to anything other than the schema it was created in, it must be qualified with the database and schema name.

roles:
  - name: service_loans
    tags:
      tag_db.tag_schema.PII: true // qualified tag name
databases:
  - name: "loans"
    schemas:
      - name: schema1
        tags:
          tag_db.tag_schema.PII: true // qualified tag name

Tram Tagging #

Tram keeps track of tags in its own table for cases where the permission model around Snowflake tags is too restrictive. Tags can be queried from the metadata table <tram.metadata.database>.<tram.metadata.database>.metadata. using the SYSADMIN role.

Metadata columns include:

  • id (STRING): identifier of the object that along with type forms a unique key
  • type (STRING): type of object: database, schema, stage, warehouse, role, user, or monitor
  • tags (OBJECT): key value pairs used to annotate or describe objects

Snowflake Object Provisioning #

This type of provisioning is for when you want a “what you see is what you get” type approach to your workspace. This format uses the same template as above, but doesn’t allow for variable substitution.

Updating Snowflake Objects #

Tram supports properties update on Users, Databases, and Warehouses. Properties that have changed between Tram executions in either Group or Model yaml files will be recognized and modified. For example, if you change a user’s display_name, Tram will recognize the display name has changed and emit the sql to update the property.

Tram will not attempt to update some properties, like PASSWORD and DISABLED. Below are lists of properties which can be updated for each object type.

NOTE: Use the following command to change a password post user creation. #

ALTER USER <USER> SET password = '<PASSWORD>' must_change_password = true;

User updatable properties: #

  • login_name
  • display_name
  • first_name
  • middle_name
  • last_name
  • email
  • snowflake_support
  • days_to_expiry
  • mins_to_unlock
  • default_warehouse
  • default_namespace
  • default_role
  • ext_auth_duo
  • ext_authn_uid
  • mins_to_bypas_mfa
  • disable_mfa
  • rsa_public_key
  • rsa_public_key_2
  • comment
  • default_secondary_roles
  • network_policy

Note: default_secondary_roles property must be defined following the syntax as per Snowflake documentation. Refer to the example below.

users:
  - name: <username>
    parameters:
      - name: default_secondary_roles
        value: "('ALL')"

User object properties: #

alterable_only #

Tram creates a user when the specified user does not exist in Snowflake. To restrict this behavior for externally managed users(eg: users managed through SCIM) alterable_only object property can be configured using the below syntax. If alterable_only property is set to true, Tram will not generate CREATE and DROP statements but ALTER statements are generated for updatable user properties listed in above section. The default for alterable_only is false for which Tram generates CREATE, DROP and ALTER statements for a given user object.

Example Model - user.yml

users:
  - name: <username>
    alterable_only: "{{alterable_only}}"
    parameters:
      - name: <param_name>
        value: <param_value>

Database updatable properties: #

  • data_retention_time_in_days
  • default_ddl_collation
  • comment

An example:

databases:
  - name: <database_name>
    parameters:
      - name: DATA_RETENTION_TIME_IN_DAYS
        value: 0
    schemas:
      - name: <schema_name>
        parameters:
          - name: DATA_RETENTION_TIME_IN_DAYS
            value: 0

Warehouse updatable properties: #

  • max_cluster_count
  • min_cluster_count
  • scaling_policy
  • auto_suspend
  • auto_resume
  • resource_monitor
  • comment
  • max_concurrency_lever
  • statement_queued_timeout_in_seconds
  • statement_timeout_in_seconds

Note: Property resource_monitor requires to be managed by ACCOUNTADMIN role. Tram user has to have granted this role otherwise this property cannot be set.

Change Sources #

Tram supports several sources of changes.

Self-service with a Ticketing System #

Introduction #

Tram can integration with ticketing systems, commonly known as ITSM (IT Service Management) tools. We support JIRA Service Management, ServiceNow and will build out support for other tools based on user demand.

In this model, Tram pulls requests from the ITSM tool, extracts fields from the data, and then adds this resulting metadata to Trams metadata repository in git.

Note that tram.lease.name is configured.

JIRA #

The JIRA trigger can be configured via JQL query and JsonPath which can be tested using the Jayway Json Evaluator App.

Here is an example snippet from the application.properties file:

tram.trigger.type = jira
tram.trigger.jira.url = https://example.atlassian.net/
tram.trigger.jira.user = tram@example.com
tram.trigger.git.remote.url = https://bitbucket.org/acme/snowflake-provisioning.git
tram.trigger.git.remote.user = tram_acme
tram.lease.name = TRAM_LEASE

In addition, the following values TRAM_TRIGGER_JIRA_TOKEN and TRAM_TRIGGER_GIT_REMOTE_PASSWORD need to be populated via environment variables.

The following configuration, adds triggers to the Source-Product template.

Here is the configuration for a new source. We use JQL query to find approved requests for new sources. Then use JSONPath to map the custom fields on the request to fields in the source model. The generator takes the resulting source an updates the custom field, so the value can be used in access requests for sources.

trigger:
  mapping:
    source: $.fields.customfield_10055
    env: $.fields.customfield_10054.value
  config:
    query: "project = SNOW AND \"Approvals[Approvals]\" = approved() AND status in (\"Waiting for support\") AND \"Request Type\" = \"Create a new source (SNOW)\" AND createdDate > -14d ORDER BY updatedDate"
  generator:
    source: customfield_10056

Here is the configuration for a source access request. We remove the domain from the email address field after mapping it in with a JSONPath.

trigger:
  mapping:
    source: $.fields.customfield_10056.value
    env: $.fields.customfield_10054.value
    username: $.fields.reporter.emailAddress
    function: $.fields.customfield_10058.value
  config:
    query: "project = SNOW AND \"Approvals[Approvals]\" = approved() AND status in (\"Waiting for support\") AND \"Request Type\" = \"Request access to a source (SNOW)\" AND createdDate > -14d ORDER BY updatedDate"
  removeDomain:
  - username

Change sources also support updates as well as inserts. Below is a configuration used to provide key rotation. Here Tram updates an existing record if the username is equal between two records and otherwise adds the record to the list.

---
adgroup: false
model:
  name: user
  defaults:
    rsa_public_key_2: ""
    rsa_public_key: ""
members:
- email: polly@4nnnn.net
  username: polly
deprovision: false
trigger:
  mapping:
    email: $.fields.reporter.emailAddress
    username: $.fields.reporter.emailAddress
    rsa_public_key: $.fields.customfield_10060.content[0].content[0].text
    rsa_public_key_2: $.fields.customfield_10061.content[0].content[0].text
  config:
    query: "project = SNOW AND \"Approvals[Approvals]\" = approved() AND \"Request Type\" = \"Rotate public key (SNOW)\" AND createdDate > -14d ORDER BY updatedDate"
  missingAllowed:
  - rsa_public_key
  - rsa_public_key_2
  removeDomain:
  - username
  primaryKeys:
  - username

NOTE: The custom fields for rsa_public_key and rsa_public_key_2 must be multi-line text boxes as single-line are too short.

For example, given the above configuration, if the JQL query responses with two tickets :

{
  "fields":[
    {
      "reporter":{
        "emailAddress":"polly@4nnnn.net"
      }
    },
    {"customfield_10060": {
      "version": 1,
      "type": "doc",
      "content": [
        {
          "type": "paragraph",
          "content": [
            {
              "type": "text",
              "text": "MIIBIjANBgkqhkiG9w0BAQEFAAOC..."
            }
          ]
        }
      ]
    }
    },
    {"customfield_10061":""}
  ]
}

and

{
  "fields":[
    {
      "reporter":{
        "emailAddress":"tim@4nnnn.net"
      }
    },
    {"customfield_10060":""},
    {"customfield_10061":"MIIBIjANBgkqhkiG9w0BAQEFAAOC..."}
  ]
}

The resulting members section from the above example will be as below. Polly’s record will be updated while Tims will be inserted.

members:
- email: polly@4nnnn.net
  username: polly
  rsa_public_key: "MIIBIjANBgkqhkiG9w0BAQEFAAOC..."
- email: tim@4nnnn.net
  username: tim
  rsa_public_key_2: "MIIBIjANBgkqhkiG9w0BAQEFAAOC..."

ServiceNow #

The ServiceNow trigger depends on two components, the first being Scripted REST APIs installed in ServiceNow to provide the APIS we require. The second is configuration to extract data from those REST Apis. This configuration configured via query and JsonPath which can be tested using the Jayway Json Evaluator App.

Steps to install Scripted REST APIs in ServiceNow:

  1. Login into service now account, Search and open the Scripted REST APIs.
  2. Create a new REST APIs service
  3. Then create resources based on below service now REST API scripts
  4. Use Explore REST API for testing the resources

The following scripts are used to create REST APIs resources:

  • Get Tram Service Requests

    It takes two parameters as query, limit and responds with list of tickets.

    GET https://example.service-now.com/api/645364/phdata_tram/ritm
    Query Parameters:
    query=<Right click on tickets table filters and `copy query` based on the applied filter, as in below picture>
    limit=<No.of records>
    

  • Get Choices

    Provides the list of variable values based on a given variable name, if no value found will return with an empty list.

    GET https://example.service-now.com/api/645364/phdata_tram/choices
    Query Parameters:
    variable_name=<Variable name>
    limit=<No.of records>
    
  • Add Choices

    Inserting new value into given variable_name

    POST https://example.service-now.com/api/645364/phdata_tram/choices
    Request Body:
    variable_name=<Variable name>
    variable_values=<Variable values>
    
  • Add Comment

    Posting comments to the respective ticket based on ticket number(key).

    POST https://example.service-now.com/api/645364/phdata_tram/comment
    Request Body:
    comment=<Post comment to the  respective ticket>
    key=<Ticket number>
    

Here is an example snippet from the application.properties file to configure ServiceNow REST APIs:

tram.trigger.type = servicenow
tram.trigger.servicenow.url = https://example.service-now.com/api/645364/
tram.trigger.servicenow.user = <username>
tram.trigger.servicenow.password = ********
tram.trigger.git.remote.url = https://bitbucket.org/acme/snowflake-provisioning.git
tram.trigger.git.remote.user = tram_acme

In addition, TRAM_TRIGGER_GIT_REMOTE_PASSWORD needs to be populated via environment variables.

The following configuration adds triggers to the Source-Product template.

Here is the configuration for a new source. We use query to find approved requests for new sources. The generator takes the resulting source and updates the variable values in service now.

trigger:
  mapping:
    env: $.workspace_environment
    source: $.workspace_name
  config:
    query: "cat_item%3Dc0fbe29c0710301097faf0269c1ed01f%5Eactive%3Dtrue%5Eclosed_atISEMPTY%5Estage%3DOrder%20Fulfillment%5Eapproval%3DApproved%5Eshort_description%3DRequest%20access%20to%20a%20Snowflake%20Workspace"
  generator:
    source: workspace_name

Just before provisioning the source, which compare the source with existing sources and take out new source from it. Those new source provisioning into snowflake as well as updating source name in servicenow variables(workspace_name). Finally, posting comment to the respective ticket.

---
adgroup: false
model:
  name: project
  defaults:
    warehouse_size: XSMALL
members:
  - source: marketing
    env: dev
deprovision: false
trigger:
  mapping:
    env: $.workspace_environment
    source: $.workspace_name
  config:
    query: "cat_item%3Dc0fbe29c0710301097faf0269c1ed01f%5Eactive%3Dtrue%5Eclosed_atISEMPTY%5Estage%3DOrder%20Fulfillment%5Eapproval%3DApproved%5Eshort_description%3DRequest%20access%20to%20a%20Snowflake%20Workspace"
  generator:
    source: workspace_name

Here, query will respond with single/multiple tickets. The resulting members section for the multiple tickets will be as below

- source: finance
  env: dev
- source: marketing
  env: dev
- source: accounts
  env: dev

Active Directory Synchronization #

Introduction #

Tram can synchronize infrastructure with Active Directory groups. This can be useful to manage role membership or user-workspace provisioning with existing corporate Active Directory approval processes.

When an Active Directory group is used in a Tram run, Tram will:

  • Fetch all member DNs for each group
  • If a user has been added or removed since the previous run, Tram will fetch the details for that user DN.
  • Generate and apply the needed Snowflake SQL statements

Groups and users are cached locally in the application per Tram-run, so Active Directory calls are minimized if a group is used multiple times or a user is added simultaneously to multiple groups.

During a run where groups have not changed, Tram will fetch only the members of each group, and take no further action.

Active Directory Synchronization Setup #

Active Directory synchronization requires Tram to store data in a datastore, either an embedded Derby database or Snowflake configured with the tram.provisioning.ldap.storage property.

When using the embedded derby option no additional setup is needed. If using snowflake it’s necessary to provide additional properties to connect to Snowflake. See the application.properties for additional configuration documentation.

NOTE: If you are using secure ldap you must specify the trust store in the Tram command with -Djavax.net.ssl.trustStore=/path/to/truststore

Snowflake Database Setup #

Tram will automatically provision the following objects using the SYSADMIN role:

  • Database: <tram.metadata.database>
  • Schema: <tram.metadata.schema>
  • Table: <tram.metadata.statements.table>
  • Table: <tram.metadata.workspaces.table>
  • Table: concert_objects
  • Table: concert_objects_merge
  • Table: concert_attributes
  • Table: concert_attributes_merge

Other Tool Comparisons #

Tram vs Snowflake SCIM integration #

Snowflake can use Cross-Domain Identity Management (SCIM) integrating snowflake authentication with other authentication providers, including Okta and Azure Active Directory. Through this integration, Snowflake is able to create users and sync roles with groups.

Tram can connect directly to Active Directory in Azure, AWS, or with a federated on-premise installation.

In addition to creating and synchronizing users and roles, Tram is also able to provision and manage databases, schemas, warehouses, and grants.