Create Account

Select Create Account to provide an SQL query or stored procedure to create an account. Enter the SQL query or stored procedure into the SQL Query or Stored Procedure field as appropriate.

SQL Query

The SQL query format is as follows:

insert into databasename.tablename (column1, column2) values ($plan.attribute1$, $plan.attribute2$)

For example:

insert into Employee (PersonID, FirstName) values ($plan.PersonID$, $plan.FirstName$)

If the native identity is included in the provisioning plan:

insert into Employee (PersonID, FirstName) values ($plan.nativeIdentity$, $plan.FirstName$)

If the native identifier is not included in the Provisioning Plan, enter an SQL query to pull the native identifier into the Get Native Identifier SQL Query field.

For example, PersonID is returned as the native identifier in the following SQL query:

select PersonID from Persons where firstName = $plan.firstName$

Stored Procedure

The stored procedure format is as follows:

EXEC JDBCSWAP.dbo.InsertEmployee @Name = $plan.name$, @City = $plan.city$, @Salary = $plan.salary$

Prerequisites

To support this feature, you must set up a provisioning policy and define the account attribute mappings as required. When SailPoint provisions new accounts to a directly connected JDBC source, it uses the attributes in the provisioning policy as a set of instructions or a template to create a new account.

Add Entitlement

Select Add Entitlement to add SQL queries to add entitlements during create operations.

  1. Under Add Entitlement, select Add.

  2. In the Entitlement Type field, add the entitlement key in the following format:

    <entitlement attribute name>.addEntitlementSQL

    For example, if the entitlement attribute in the account schema is Roles, add the following:

    Roles.addEntitlementSQL

  3. In the Query field, add an SQL query or a stored procedure to add the Roles entitlement to an account.

    • SQL Query

      • If the attribute is included in the Provisioning Plan:

        insert into databasename.tablename (attribute1,attribute2) value($plan.attribute1$, $plan.Roles$)

        Or

        insert into databasename.tablename (attribute1,attribute2) value($plan.nativeIdentity$,$plan.Roles$)

      • If the attribute is not included in the Provisioning Plan, you can fetch it using a SELECT query, then insert the response of the first query into the second query:

        SELECT attribute FROM databasename.tablename WHERE fname = $plan.attribute1$;

        insert into databasename.tablename (attribute1,attribute2) value($response.attribute$,$plan.Roles$)

    • Store Procedure

      EXEC HR.dbo.InsertEmployeeRole @EmpId = $plan.nativeIdentity$, @RoleId = $plan.Roles$

  4. Repeat this process for multiple entitlement types.