# Account Schema Query Setup

Set up how the connector queries the JDBC source for account and/or group schema information.

> [!NOTE]
>
> **Screenshot**
>
> [![Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/query_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/query_settings.png>)

Select the **Edit** icon ![](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/query_configure_button.png>) for the schema you want to configure to launch additional settings.

Perform the following tasks to configure the queries for JDBC accounts.

## Account Connection Settings

Configure how SailPoint connects to the JDBC server to load account information.

### Account Connection Settings for Other Database

Enter the connection information to allow SailPoint to locate and connect to the JDBC server to load account information.

> [!NOTE]
>
> **Screenshot**
>
> [![Account Connection Settings for Other Database](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_conn_other.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_conn_other.png>)

1. Select **Other** from the **Database Name** dropdown.

   > **Note**
   >
   > Other is selected by default. If another value is selected, select **Other**.
2. Enter the **User Name** required to access the data files server.
3. Enter the **Password** associated with the user name to access the server.
4. Enter the server's address into the **Database URL** field. The generic format is as follows:

   ```text
   jdbc:<database_type>://<host>:<port>/<database_name>
   ```

   Substitute the values for your database in the bolded text. For example:

   ```text
   jdbc:snowflake://sailpoint-conn.snowflakecomputing.com
   ```

   > **Note**
   >
   > The JDBC connector is a generic connector that supports connecting to a database, provided that the database has a JDBC driver available.
5. Enter the **Driver Class** path according to database you want to connect. For example:

   ```text
   net.snowflake.client.jdbc.SnowflakeDriver
   ```
6. (Optional) In the **Additional Connection Parameters** field, enter the parameters to be passed in the form of key-value pair. If you need to specify multiple parameters, press the Enter key after adding each value. For example:

   ```text
   resultSetType= TYPE_FORWARD_ONLY
   db=TEST
   schema=PUBLIC
   ```
7. Select **Save**.

### Account Connection Settings for Snowflake

Enter the connection information to allow SailPoint to locate and connect to the JDBC server to load account information from a Snowflake database.

> [!NOTE]
>
> Upload the Snowflake JDBC driver before you configure connection settings. For more information, refer to [Upload a JAR File Using Connectivity Customizers](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/upload_jar_using_customizer.html>). SailPoint requires Snowflake JDBC driver version 3.24.0 or later.

**To configure account connection settings, complete the following steps:**

1. Select **Snowflake** from the **Database Name** dropdown.
2. Enter the **User Name** required to access the data files server. Enter the user name that matches the authentication type you select. For OAuth 2.0, enter the `LOGIN_NAME` value from Snowflake.
3. In the **Database URL** field, provide the JDBC connection string corresponding to the database with which to connect. The generic format is as follows:

   ```text
   jdbc:dbtype:@ipaddress:databasename
   ```

   For example:

   ```text
   jdbc:snowflake://test.snowflakecomputing.com
   ```
4. Enter the **Driver Class** path according to the database you want to connect. For example:

   ```text
   net.snowflake.client.jdbc.SnowflakeDriver
   ```
5. Select an **Authentication Type**.

   1. OAuth 2.0 Authentication

      > **Screenshot**
      >
      > [![Snowflake OAuth 2.0 Authentication](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_conn_snowflake_auth.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_conn_snowflake_auth.png>)

      Complete the following steps when you select **OAuth 2.0** as the authentication type. For more information about configuring the external identity provider, refer to [Configuring an External IdP for OAuth 2.0](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/index.html#configuring-an-external-idp-for-oauth-20>).

      1. Select **Client Credentials** as the **Grant Type**.

         > **Note**
         >
         > The JDBC connector supports only the Client Credentials grant type for Snowflake OAuth 2.0.
      2. Enter the **Token URL** required to obtain the access token.
      3. Enter the **Client ID** required for authentication.
      4. Enter the **Client Secret** required for authentication.
      5. Enter the **OAuth2 Scope** required to access the server.
   2. Key Pair

      > **Screenshot**
      >
      > [![Snowflake Key Pair Authentication](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_conn_snowflake_keypair.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_conn_snowflake_keypair.png>)

      Complete the following steps when you select **Key Pair** as the authentication type.

      1. Enter the **Private Key** used to authenticate with Snowflake. For more information about generating private keys, refer to [Generating Keys for Snowflake](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/index.html#generating-keys-for-snowflake>).
      2. Enter the **Passphrase** to validate the private key. Enter the passphrase you created when you generated the public and private keys.
6. (Optional) In the **Additional Connection Parameters** field, enter the parameters to be passed in the form of key-value pair. If you need to specify multiple parameters, press the Enter key after adding each value. For example:

   ```text
   <truststorefileparameter>=<pathoftruststore>
   <truststoretypeparameter>=<truststoretype>
   ```
7. Select **Save**.

## Account Query Settings

Configure the SQL queries SailPoint uses to retrieve account data from your JDBC server.

> [!IMPORTANT]
>
> SQL entries can't end with a semicolon (;). Ending an SQL entry with a semicolon (;) can cause a general error message to display.

> [!NOTE]
>
> **Screenshot**
>
> [![Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query1_1.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query1_1.png>) [![Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query2.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query2.png>) [![Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query3.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query3.png>)

1. (Optional) Enter a simple SQL query to test the connection to the database in the **Test Connection SQL Query** field. Select the **Use Stored Procedure for Test Connection SQL Query** checkbox to execute a stored procedure on the database.
2. Enter an SQL query to load all accounts into the **Account SQL Query** field. For example, you can use the following:

   ```sql
   SELECT userid, fname, lname FROM users
   ```

   Select the **Use Stored Procedure for Account SQL Query** checkbox to execute a stored procedure on the database.
3. (Optional) Enter an SQL query to load a single account into the **Single Account SQL Query** field. This query may match the Account SQL Query, but the single account query includes a `WHERE` clause to filter results for one specific user. To accomplish this, the query uses the `$(identity)` placeholder which the connector dynamically replaces with the actual NativeIdentity of the user at runtime. For example, if your NativeIdentity is `userid`, you can use the following:

   ```sql
   SELECT userid, fname, lname FROM users WHERE userid = '$(identity)'
   ```

   Select the **Use Stored Procedure for Single Account SQL Query** checkbox to execute a stored procedure on the database.
4. Select **Save**.

## Additional Query Settings

Set additional query settings to support the SQL queries.

> [!IMPORTANT]
>
> SQL entries can't end with a semicolon (;). Ending an SQL entry with a semicolon (;) can cause a general error message to display.

> [!NOTE]
>
> **Screenshot**
>
> [![Additional Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_addquery_saas.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_addquery_saas.png>)

1. (Optional) Select the **Use executeQuery** checkbox to return a single ResultSet object when it queries the JDBC database.
2. (Optional) Enable **Direct Permission** and add the **Direct Permission SQL** query. For more information on supported direct permissions, refer to [Direct Permissions](<#direct-permissions>).

   The **Direct Permission Execute Query** is used to pull the direct permission data from a permission table. The permission table must contain at least an Identity attribute column. SailPoint pulls permission data using an SQL query to refer to the Identity attribute during aggregation process.

   The SQL query must be written in such a way that ResultSet data contains Target as a first column and Permission as a second column. Optionally, you can add a third column and set it as Annotation.

   For example, if you reference a permission table named Permission and you use `$(identity)` as the Identity attribute, you could use the following SQL query:

   ```sql
   SELECT column4 AS TARGET,column5 AS PERMISSION FROM Permission p WHERE CONCAT(TRIM(CONCAT(p.column1,'\ ')), TRIM(p.column2)) = '$(identity)'
   ```

   Another example:

   ```sql
   select TABLENAME,PRIVILEGES from JDBC_SNOWFLAKE.PUBLIC.PERM where grantee='$(identity)';
   ```

   Select the **Use Stored Procedure for Single Account SQL Query** checkbox to execute a stored procedure on the database.
3. Select **Save**.

### Direct Permissions

The JDBC source supports:

- Permissions directly assigned to accounts and groups as direct permissions during account and entitlement aggregation.
- Revocation of the aggregated permissions for an account through certification.

## Account Delta Aggregation

Enable Delta Aggregation when SailPoint queries the JDBC database. Delta Aggregation enables SailPoint to only update new information since the previous data query.

> [!IMPORTANT]
>
> You must configure a delta aggregation table for delta aggregation to work. For more information, refer to [Delta Aggregation Tables](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/aggregation.html#delta-aggregation-tables>).

> [!NOTE]
>
> **Screenshot**
>
> [![Delta Aggregation](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_deltaagg.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_deltaagg.png>)

1. In the JDBC source configuration, go to **Query Settings**, select the **Edit** icon ![](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/query_configure_button.png>) for the schema you want to configure, and then select **Delta Aggregation**.
2. Select **Enable Delta Aggregation** to enable delta aggregation.
3. (This field is only available when delta aggregation is enabled) In the **Database Table Name that Contains the Delta Changes** field, provide the table name in which delta changes are captured.
4. Use the **Delta Aggregation SQL** field when the alias is used in the main SQL. Example of delta aggregation SQL query:

   ```sql
   select * from EMPLOYEES_DELTA;
   ```

   For more information, refer to [Delta Aggregation Tables](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/aggregation.html#delta-aggregation-tables>).
5. Select the **Use Stored Procedure** checkbox to execute a stored procedure on the database.
6. Select **Save**.

## Provisioning Query Settings

You can configure provisioning operations through a series of SQL queries and stored procedures.

### Supported Functions

This feature supports the following functionality.

- Single SQL query execution
- Multiple SQL query execution
- Stored procedures

  For example:

  Call `procedure_name($plan.attribute1$, $plan.attribute2$)`
- Inline variables for defined queries

  Defined queries support inline variables from the provisioning plan using the `$plan.fieldname.fieldtype$` variable syntax.
- Response items from priorly executed queries in the same SQL query set

  Queries can use response items from the result set of prior queries, via the `$response.fieldname.fieldtype$` variable syntax.
- Type safety

  The provisioning plan and prior-query response objects may be restricted to specific data types.

  If a data type isn't defined, the field is treated as a string. Define a data type using the following entry:

  ```text
  $plan.fieldname.type$
  ```

  For example:

  - `$plan.firstName.string$`
  - `$response.id.int$`

  The connector supports the following data types:

  - String - `Str`, `Varchar`
  - Integer - `Int`
  - Boolean - `Bool`
  - Date - `Date`
  - Double - `Dbl`
  - Float - `Flt`

  > **Note**
  >
  > Data type definitions are case-insensitive

### Configure Provisioning Operations

Create SQL queries and stored procedures to handle provisioning in the JDBC SaaS connector.

To create provisioning operations in the connector configuration, select **Configure Provisioning Operations in the Connector**, then select any of the following options:

### 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:

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

If the database name is already specified as an additional attribute in the query settings:

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

For example:

```sql
insert into Employee (name, city) values ($plan.NAME$, $plan.CITY$)
```

If the native identity is included in the provisioning plan:

```sql
insert into Employee (EmpID, 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, `EmpID` is returned as the native identifier in the following SQL query:

```sql
select EmpID from Employee where firstName=$plan.firstName$
```

#### Stored Procedure

The stored procedure format is as follows:

```sql
CALL InsertEmployee($plan.name$, $plan.city$, $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 SaaS 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 which 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:

   ```text
   <entitlement attribute name>.addEntitlementSQL
   ```

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

   ```text
   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:

       ```sql
       insert into databasename.tablename (attribute1,attribute2) values ($plan.attribute1$, $plan.Roles$)
       ```

       Or

       ```sql
       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:

       ```sql
       SELECT attribute FROM databasename.tablename WHERE fname = $plan.attribute1$;
       insert into databasename.tablename (attribute1,attribute2) values ($response.attribute$, $plan.Roles$)
       ```
   - Store Procedure

     ```sql
     CALL InsertEmployeeRole($plan.nativeIdentity$, $plan.Roles$)
     ```
4. Repeat this process for multiple entitlement types.

### 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:

```sql
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:

```sql
Update human_resources.sales_accounts
```

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

```sql
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:

   ```text
   <entitlement attribute name>.addEntitlementSQL
   ```

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

   ```text
   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:

       ```sql
       insert into databasename.tablename (attribute1,attribute2) values ($plan.attribute1$, $plan.Roles$)
       ```

       Or

       ```sql
       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:

       ```sql
       SELECT attribute FROM databasename.tablename WHERE fname = $plan.attribute1$;
       insert into databasename.tablename (attribute1,attribute2) values ($response.attribute$, $plan.Roles$)
       ```
   - Store Procedure

     ```sql
     CALL InsertEmployeeRole($plan.nativeIdentity$, $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:

   ```text
   <entitlement attribute name>.removeEntitlementSQL
   ```

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

   ```text
   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:

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

       Or

       ```sql
       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:

       ```sql
       SELECT attribute FROM databasename.tablename WHERE fname = $plan.attribute1$;
       delete from databasename.tablename where attribute1 = $response.attribute.int$ and attribute2 = $plan.Roles$
       ```
   - Stored Procedure

     ```sql
     CALL DeleteEmployeeRole($plan.nativeIdentity$, $plan.Roles$)
     ```
4. Repeat this process for multiple entitlement types.

### Enable, Disable, and Unlock Account

Select **Enable Account**, **Disable Account**, or **Unlock Account** to provide SQL queries or stored procedures to enable or disable accounts. Provide the query or stored procedure according to the column which defines the account Enable, Disabled, and Locked statuses.

For example:

Query:

```sql
update DatabaseName.SchemaName.[TableName] set AttributeName='value' where AttributeName=$plan.nativeIdentity$
```

Stored Procedure:

```sql
CALL StoredPrecedureName($plan.nativeIdentity$)
```

#### Use Case Examples

This section lists examples the SQL queries and stored procedures to enable, disable, and unlock accounts.

##### Enable Account Examples

Query:

```sql
update Employee set isactive='1' where empid=$plan.nativeIdentity$
```

Stored Procedure:

```sql
CALL UpdateEmployeeStatusDisable($plan.nativeIdentity$)
```

##### Disable Account Examples

Query:

```sql
update Employee set isactive='0' where empid=$plan.nativeIdentity$
```

Stored Procedure:

```sql
CALL UpdateEmployeeStatusDisable($plan.nativeIdentity$)
```

##### Unlock Account Examples

Query:

```sql
update Employee set islock='0' where empid=$plan.nativeIdentity$
```

Stored Procedure:

```sql
CALL UpdateEmployeeLockStatus($plan.nativeIdentity$)
```

### Delete Account

Select **Delete Account** to provide an SQL query or stored procedure to delete 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:

```sql
DELETE FROM DatabaseName.dbo.[TableName]
WHERE AccountID = $plan.nativeIdentity$;
```

#### Stored Procedure

The stored procedure format is as follows:

```sql
EXEC StoredProcedureName @VariableName =
$plan.nativeIdentity$;
```

#### Use Case Examples

##### SQL Query

```sql
DELETE FROM HR.dbo.[Employee] WHERE empid =
$plan.nativeIdentity$;
```

##### Stored Procedure

```sql
EXEC HR.dbo.DeleteEmployee @EmpID =
$plan.nativeIdentity$;
```

### Multiple Query Execution

Multiple SQL query execution is supported for create, add/remove entitlement, enable, disable, and unlock operations.

Queries can use response items from the result set of prior queries, via the `$response.fieldname.fieldtype$` variable syntax.

For example, if the response of the first SQL query is used in the execution of the second entry, use the following SQL query:

```sql
SELECT id FROM databasename.tablename WHERE fname = $plan.firstname$;
INSERT INTO databasename.tablename (fname, lname, email) VALUES ($response.id.Int$, $plan.lastname$, $plan.mail.string$);
```