Skip to main content

Example script configuration

This topic provides an example configuration script that can be used for a MS SQL Server scripted database target system. The larger agtsqlscript.cfg in the samples directory is thoroughly commented and explains in detail the use of each operation that can be scripted, but it uses MSSQL's own sprocs to reset/verify passwords for existing SQL Logins.

The example below uses and updates a simple schema, to keep track of password hashes in a table. The example uses only one table (customapp.users) with two fields:

  • user_name: varchar(80) unique,

  • pwd: binary(64).

MS SQL Server is used as an example for this, because it should be already installed as the backend database for Bravura Security Fabric ; however, the scripting configuration allows for communicating with any other database system - the differences are mainly in how variables are bound and used in SQL statements, and that is documented in the instance's samples/agtsqlscript.cfg.

Note

For troubleshooting: most Bravura Security Fabric database connectors make use of Microsoft's ODBC layer (only the Sybase and Oracle agents use their respective SDKs directly).

In the instance's script directory, add the following file (named for example customapp.cfg), and reference that script name in a scripted target of type "Microsoft SQL Server (Script)":

"m-tech:agents:sqlscript" "1.1" = {
  "operation" "serverinfo" = {
    "login" = "%k:_adminID%"
    "password" = "%k:_adminPW%"
    
    "block" "1" = {
      "sql" = "select 'Custom App Alpha';"
    }
  }
  "operation" "testlist" = {
    "login" = "%k:_adminID%"
    "password" = "%k:_adminPW%"
    "returns"  "" = {
      "column" = "_acctid"
      "column" = "_shortid"
      "column" = "_stableid"
    }
    "block" "1" = {
      "sql" = "select user_name, user_name, user_name from customapp.users;"
    }
  }
  "operation" "listusers" = {
    "login" = "%k:_adminID%"
    "password" = "%k:_adminPW%"
    "returns"  "" = {
      "column" = "_acctid"
      "column" = "_shortid"
      "column" = "_stableid"
    }
    "block" "1" = {
      "sql" = "select user_name, user_name, user_name from customapp.users;"
    }
  }
  "operation" "reset" = {
    "login" = "%k:_adminID%"
    "password" = "%k:_adminPW%"
    "block" "1" = {
      "sql" = "UPDATE customapp.users SET pwd = HASHBYTES('SHA2_512', '%k:_newPW%')
      WHERE user_name = '%k:_acctID%'; IF @@ROWCOUNT = 0 BEGIN RAISERROR('[%s] is not a
      valid account name',8,-1,'%k:_acctID%'); SELECT 7 END;"
  
    }
  }
  "operation" "verify" = {
    "login" = "%k:_adminID%"
    "password" = "%k:_adminPW%"
    "block" "1" = {
      "sql" = "SELECT COUNT(1) FROM customapp.users WHERE user_name = '%k:_acctID%';
      IF @@ROWCOUNT = 0 BEGIN RAISERROR('[%s] is not a valid account name',8,-1,'%k:_acctID%');
      SELECT 7 END ELSE SELECT COUNT(1) FROM customapp.users WHERE user_name = '%k:_acctID%'
      AND pwd = HASHBYTES('SHA2_512', '%k:_oldPW%');"
   
  }
  "operation" "verifyreset" = {
    "login" = "%k:_adminID%"
    "password" = "%k:_adminPW%"
    "block" "1" = {
      "sql" = "SELECT COUNT(1) FROM customapp.users WHERE user_name = '%k:_acctID%';
      IF @@ROWCOUNT = 0 BEGIN RAISERROR('[%s] is not a valid account name',8,-1,'%k:_acctID%');
      SELECT 7 END ELSE SELECT COUNT(1) FROM customapp.users WHERE user_name = '%k:_acctID%'
      AND pwd = HASHBYTES('SHA2_512', '%k:_newPW%'); IF @@ROWCOUNT = 0 UPDATE customapp.users
      SET pwd = HASHBYTES('SHA2_512', '%k:_newPW%') WHERE user_name = '%k:_acctID%';"
  
    }
  }
}

When editing such config files, pay attention to always properly pair curly brackets and quotes.

Do not use double quotes for table and column names; if you have to use a T-SQL keyword for a column or table name, use brackets instead (as in [users] or [id]).

Do not place single quotes around arguments ('?') when using binding (positional or by name). The quotes are added to the variables as part of sanitizing the variable input.

When using an external program as a filter, run the filter command by hand at the command prompt first:

  • Determine how long the input is so that the database fields used to store those values can contain all of them,

  • Check what type of characters it generates; do not generate binary strings or any characters that could stripped during sanitising the variable.

Do not break the SQL statement on separate lines for pretty-printing (while it is fine for testing the SQL statement before placing it in the script, SQL strings can't be multi-line, so this will not work):

 "sql" = "
SELECT COUNT(1) FROM customapp.users WHERE user_name = '%k:_acctID%';
IF @@ROWCOUNT = 0
 RAISERROR('[%s] is not a valid account name',8,-1,'%k:_acctID%');
ELSE SELECT COUNT(1) FROM customapp.users
  WHERE user_name = '%k:_acctID%' AND
        pwd = HASHBYTES('SHA2_512', '%k:_oldPW%');
IF @@ROWCOUNT = 0
 UPDATE customapp.users SET pwd = HASHBYTES('SHA2_512', '%k:_newPW%')
  WHERE user_name = '%k:_acctID%';
"

Instead, keep each statement (until the closing semicolon) on the same line; several statements can be separated in several sql lines, like this:

 "sql" = "SELECT COUNT(1) FROM customapp.users WHERE user_name = '%k:_acctID%';"

 "sql" = "IF @@ROWCOUNT = 0 RAISERROR('[%s] is not a valid account name',8,-1,'%k:_acctID%')
      ELSE SELECT COUNT(1) FROM customapp.users WHERE user_name = '%k:_acctID%' AND pwd
      = HASHBYTES('SHA2_512', '%k:_oldPW%');"
   

 "sql" = "IF @@ROWCOUNT = 0 UPDATE customapp.users SET pwd = HASHBYTES('SHA2_512',
      '%k:_newPW%') WHERE user_name = '%k:_acctID%';"

Different Connector Pack versions may require different syntax for their script files, so during upgrades or using newer connectors because their features are needed in an older product version, the scripts may need updating.

The full example above declares the four basic Bravura Pass operations and two variants:

  • "serverinfo"

    • Returns target attributes (in this case, just a string);

    • It is the operation that returns Success or Failure when testing target connection;

    • it is very important for Bravura Privilege , because it's used to determine if a connection can be established to the target: no connection, no managing the target;

  • "listusers"

    • Is called during psupdate, and lists the existing accounts on the target

    • In this case, since we have only one field in the users table that identifies the user, and because that field is unique, we return it as the three built-in user attributes: long, short and stable IDs;

    • If account attributes are to be listed, they are returned in additional columns in the same SELECT statement;

  • "testlist"

    • Is the "listusers" variant called only from the WebUI (Manage the system> Resources> Target systems> [Manually defined or Automatically discovered] > <select target> Test connection)

    • This version should list only accounts with their built-in columns, not account attributes;

  • "reset"

    • Updates the password hash in the users table;

    • If the account is not found, it returns agent return code 7 (ACInvalidUser);

    • For security and privacy reasons, the password itself is not kept in this case, only a hash of it;

    • this can be turned into a makeshift "user-provisioning", by changing the sql to add an INSERT instead of the RAISERROR (and an additional 4th bind for another password):

      UPDATE customapp.users SET pwd = HASHBYTES('SHA2_512', '%k:_newPW%') WHERE user_name = '%k:_acctID%'; IF @@ROWCOUNT = 0 INSERT INTO customapp.users (user_name, pwd) SELECT user_name = '%k:_acctID%', pwd = HASHBYTES('SHA2_512', '%k:_newPW%');

  • "verify"

    • If user_name doesn't exist, the operation errors out with agent return code 7 (ACInvalidUser);

    • Since the user_name is unique, this can return only 1 (success, the password hashes match for that specific user_name), or 0 (failed to verify);

    • If the failure disambiguation is not needed (no such user vs. password failed to verify), the second bind can be removed, the third renamed as 2 and we can use a shorter statement:

      SELECT COUNT(1) FROM customapp.users WHERE user_name = '%k:_acctID%' AND pwd = HASHBYTES('SHA2_512', '%k:_newPW%');

  • "verifyreset"

    • Is a "reset" variant that first checks if the password is the same before changing it;

Testing

Increasing logging level to 5 (Debug) for the script agent will log the replaced SQL statements as they are sent to the SQL driver (and what driver is used).

In this section: