Update Account

Select Update Account to enter the name of a table and its database to update according to the Provisioning Plan. The connector forms a full SQL query using the attributes defined in the Provisioning Plan.

The query is as follows:

Update <database_name.table_name>

For example, to update sales_accounts table in the human_resources database with an email attribute and an associated value defined in the Provisioning Plan, enter:

Update human_resources.sales_accounts

The connector generates the following SQL query based on the Provisioning Plan:

Update human_resources.sales_accounts set Email=<value_defined_in_provisioning_plan>

Important
You only need to enter the name of the table and its database for this query. Changes to the information you want to update should be made in the Provisioning Plan.

If the schema attribute name is different than the database column name, map the schema attribute name to its associated database column name in the Attribute Database Mappings field. Select Add to create a new mapping by adding the schema attribute name and its associated database column name.

Prerequisites

To support this feature, you must ensure that Attribute Sync is enabled in the source for the attributes you need to change.

Add Entitlements

Select Add Entitlement to add SQL queries to add entitlements during update 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) values ($plan.attribute1$, $plan.Roles$)

        Or

        insert into databasename.tablename (attribute1,attribute2) values ($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) values ($response.attribute$, $plan.Roles$)

    • Store Procedure

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

  4. Repeat this process for multiple entitlement types.

Remove Entitlements

Select Remove Entitlement to add SQL queries to remove entitlements during updated account operations.

  1. Under Remove Entitlement, select Add.

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

    <entitlement attribute name>.removeEntitlementSQL

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

    Roles.removeEntitlementSQL

  3. In the Query field, add an SQL query to remove the Roles entitlement from an account.

    • SQL Query

      • If the attribute is included in the Provisioning Plan:

        delete from databasename.tablename where attribute1 = $plan.nativeIdentity$ and attribute2 = $plan.Roles$

        Or

        delete from databasename.tablename where attribute1 = $plan.attribute1$ and attribute2 = $plan.Roles$

      • If the attribute is not present 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$;

        delete from databasename.tablename where attribute1 = $response.attribute.int$ and attribute2 = $plan.Roles$

    • Stored Procedure

      EXEC DeleteEmployeeRole @EmpId = $plan.nativeIdentity$, @RoleId = $plan.Roles$

  4. Repeat this process for multiple entitlement types.