KVGroups for SQL scripted connectors

The value in the name of the main KVGroup indicates the version of the protocol. This version of the scripted connectors requires the value of the key to be "m-tech:agents:sqlscript" and the name to be "1.1":

   "m-tech:agents:sqlscript" "1.1" = {
      "operation" "<operation name>" = {
       ...
      } # 0 or more
   } 

Each script file must only contain one main KVGroup.

The main KVGroup contains a set of inner KVGroups. These KVGroups have a key "operation" and a name <operation name> that corresponds to the actual operation name (see Supported operations for a list of supported operations).

The operation KVGroups define the implementation details for the operation of the connector; this includes: connection information, commands to issue, and return values. Operation KVGroups are defined as follows:

  "operation" "<operation name>" = {
     "login" = "<login id>"
     "password" = "<login password>"
       # See Connection credentials
    "usetransaction" = "[true|false]" # Optional; only for SQL Server
       # When true if any errors occur during the SQL sequence then the
       # successful statements are not committed (default = false).
     "proceedblocks" = "[true|false]"
       # When true if any errors occur while running one block SQL,
       # agent can proceed to run following SQL blocks (default = false).
     "filter" "" = {
       ...
     } # Optional; This provides calls to external programs
      # See Filter KVGroup 
     "return" "" = 
     } # Optional; This binds information returned for respective
       # operations. See Return KVGroup 
     "block" "<sequence number:1..N>" =
     {
       ...
     } # Mandatory; A minimum of one block is required per operation
       # See Block KVGroup 
   } 

If an operation requires the return of attribute information, a "returns" KVGroup is required.

If an operation requires a return value to indicate success or failure result, the last block must return a value using a SELECT statement.

Each operation KVGroup require credentials used when connecting to the database.

This information is defined using the "login" and "password" key-value pairs. For example:

   "login" = "<target administrator ID>"
   "password" = "<password>" 

Typically the value for these two keys will leverage the one of two built-in variables. Alternatively, hard-coded values can be used but not recommended.

Connecting with built-in variables as target system administrator:

   "login" = "%k:_adminID%"
   "password" = "%k:_adminPW" 

Connecting with built-in variables as the end user (for verify operations):

  "login" = "%k:_acctID%"
  "password" = "%k:_oldPW" 

This optional KVGroup is used to call an external program. The executed program must be in the \<instance>\plugin\ directory. This allows you to translate or generate a value for use in the SQL statements. This often is required to take a password and apply a hash algorithm.

The filter KVGroup must be in the following format:

  "filter" "" = {
     "command" = "<program name>"  # Mandatory
     "input" = "<what to send to stdin>"  # Optional
     "args" = "<command line args>" # Optional
     "outputvar" = "<name of variable to output to>" # Mandatory
   } 

The value of "command" is executed and uses any other values set, such as "input" or "args". The values for "input" or "args" can accept substitutions of existing variables. The "outputvar" value is a variable that is available when binding.

For example:

  "filter" "" = {
     "command" = "pwdhash.exe"
     "args" = "hash -encalg hex -hashalg md5 -password %k:_newPW%"
     "outputvar" = "hashedPW"
   } 

Each operation that returns attributes (such as list operations), requires a return KVGroup. This binds columns returned from the SQL statement to the attributes listed in the return KVGroup.

The return KVGroup as the following form:

  "returns" "" = {
     "column" = "<attribute name>"
     ...
   } 

For example, for listusers:

  "returns" "" = {
      "column" = "LONGID"
      "column" = "SHORTID"
      "column" = "FULLNAME"
      "column" = "FIRST_NAME"
      "column" = "LAST_NAME"
   } 

The columns must appear in the same order as the attributes in your "sql" select command. For each attribute, one or more columns are required and cannot be modified. For example, in the listusers example above, the first three columns are special:

If there are only two columns, then FULLNAME is not set, but LONGID and SHORTID are set. If there is only one column, then only LONGID is set.

All columns after the third column are treated as account attributes, and they use the same attribute names as they appear in the KVGroup.

For every column being returned, a corresponding account attribute needs to be created in the administrative web interface for that target. The case of each attribute name in this script file must match the case defined in the Bravura Security Fabric account attributes screen.

Each operation requires a minimum of one block. Each block must contain one or more "sql" key-value pair. Each block binds the variables used for each SQL statement. The format of the block KVGroup is as follows:

  "block" "<sequence number:1..N>" = {
     "bind" "<sequence number:1..N>" = {
      ...
     } # 0 or more; required for each variable bound in the SQL statement
     "sql" = "<SQL statement>" # Mandatory; this is the SQL statement that
                               # is executed
   } 

Columns are associated into account attributes via the "return" block; column names in "Select" SQL statements are irrelevant, but the order is important because it must match the order of the return block.

There are three ways of using variables in SQL statements. Some kinds are limited to the specific back end database. These types are as follows:

Binding by name has the following form:

 "block" "1" = {
      "bind" "1" = {
        "name" = "firstname" # name used within SQL statement
        "value" = "%k:FIRST_NAME%"
             # where FIRST_NAME is the request attribute or output
             # variable from a filter kvg.
       }
      "bind" "2" = {
        "name" = "appuserid"
        "value" = "%k:_acctID%"
            # where _acctID is a built-in variable passed to
            # the connector
       }
       "sql" = "update people set firstname= :firstname where appuserid = :appuserid"
   } 

Binding by position has the following form:

  "block" "1" = {
      "bind" "1" = {
         "value" = "%k:FIRST_NAME%"
             # where FIRST_NAME is the request attribute or output
             # variable from a filter kvg.
      }
      "bind" "2" = {
         "value" = "%k:_acctID%"
            # where _acctID is a built-in variable passed to
            # the connector
      }
      "sql" = "update people set firstname = ? where appuserid = ?"
   } 

Substitution requires no bind KVGroup as the value is replaced in the SQL statement as is in the variable. Substitution of variable is in the following form:

  "block" "1" = {
      "sql" = "update people set firstname = '%k:FIRST_NAME%' where appuserid = '%k:_acctID%'"
   } 

Binding of variables is only valid in the scope of each block. Multiple blocks under the same operation required rebinding in order to use variables.

A number of input parameters can be used as replacement and bind strings within your SQL queries. These strings can be replaced entirely or a substring of the replacement can be used. The following is a list of valid replacement formats:

The table below lists the built-in variables accessible in all operations of the scripted connectors. Depending on the operation, the value of these built-in variables may be blank.

You can define additional replacement variables by adding target system address attributes. This is useful when creating a scripted target system with a scripted platform definition file.

The AddressAttr operation is an exception to the rules described in this chapter, in that it does not include credential and block keys. This operation supports extending the target system address wizard by adding address attributes to the configuration script file in the following form:

 "operation" "AddressAttrs" = {
    "<attributeName>" "" = {
      "defaultValue" = "<defaultValue>"
      "required" = "<true|false>"
      "type" = "<String|Boolean|Integer|etc.>"
      "languageTag" = "!!!<languageTag>"
    }
    "sampleAttribute" "" = {
      "defaultValue" = "sampleValue"
      "required" = "false"
      "type" = "String"
      "languageTag" = "!!!sampleAttrDescription"
    }
  }