Identity Governance & Administration
sara.next
  1. sara.next
  2. Technology
  3. SQL scripts: return values and an example

Technology · Documentation

SQL scripts: return values and an example

A Groovy script does not run queries itself — it returns SQL statements that sara.next executes on the configured connection. Below are the rules and an example for Microsoft SQL Server.

  • Groovy
  • SQL
  • Microsoft SQL Server
  • example

Principles

What the script returns

What the script returns
ScriptReturn valueHow it is executed
Implementation (Create, Replace, Modify, Delete)String — a single SQL statementThe statement is executed on the application's SQL connection.
Implementation — multiple statementsList<String> — a list of SQL statementsAll statements are executed in a single transaction; on error it is rolled back.
VerifierString with a SELECT querytrue if the query returns at least one row; false if it returns none.

Example

Full example: Microsoft SQL Server

The example assumes a role with two dimensions: DB_NAME (the database name) and DB_OPERATIONS (a comma-separated list of database roles or permissions). When a dimension has no value, the script assumes the defaults demo and db_datareader.

Create script

The script creates the server login if it does not exist yet, then the user in the database, and finally grants the permissions: values starting with db_ are treated as database roles (ALTER ROLE … ADD MEMBER), the rest as permissions (GRANT).

String login = user.samAccountName
String dbName = dimensions["DB_NAME"] ?: "demo"
String operations = dimensions["DB_OPERATIONS"] ?: "db_datareader"

List<String> sqls = []

sqls << "IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = '${login}') " +
        "CREATE LOGIN [${login}] WITH PASSWORD = '<temporary password>', " +
        "CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;"

sqls << "USE [${dbName}]; " +
        "IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '${login}') " +
        " CREATE USER [${login}] FOR LOGIN [${login}];"

operations.split(",").each { op ->
    String o = op.trim()
    if (o.startsWith("db_")) {
        sqls << "USE [${dbName}]; ALTER ROLE [${o}] ADD MEMBER [${login}];"
    } else {
        sqls << "USE [${dbName}]; GRANT ${o} TO [${login}];"
    }
}

return sqls*.toString()

TipHere the temporary password has been replaced with the placeholder <temporary password>. Decide how passwords are assigned in line with your organisation's password policy.

Modify script

The script reads the previous dimension values from request.oldAtar, and then:

  1. revokes the existing roles and permissions in the previous database,
  2. makes sure the user exists in the new database,
  3. grants the new roles and permissions.

The previous user's login is taken using safe navigation (oldUser?.samAccountName), and if it is missing, the current user's login is used.

Delete script

The script takes the login from oldUser, and the dimension values from request.oldAtar. In turn it:

  1. revokes the roles and permissions in the database,
  2. removes the database user if they no longer have membership of any role,
  3. removes the server login if it no longer has users in any database.

Verify script

The query checks whether a user with the login from the login variable exists in the database. For a Delete request the result is inverted by the scheduler.

String dbName = dimensions["DB_NAME"] ?: "demo"
return "SELECT 1 FROM [${dbName}].sys.database_principals WHERE name = '${login}'".toString()

NoteSample text interpolations should not be copied into production code without validation. User identifiers, roles and database names should come from allowed value lists, and queries must be designed taking into account the risk of SQL injection and the system privileges of the integration account.

Prepare SQL scripts with our team

We will choose the permission scope of the integration account and check the scripts in a test environment.