Custom Rule to Fetch SQL Data in SailPoint IIQ


SailPoint IdentityIQ is a powerful identity governance solution that provides organizations with the ability to manage and govern user access across various systems and applications. While IdentityIQ offers a comprehensive set of features and functionalities out of the box, there may be scenarios where you need to retrieve or manipulate data directly from the backend SQL database. This blog will guide you through the process of creating a custom rule in IdentityIQ to execute SQL queries against the database, enabling data retrieval, manipulation, and analysis.

It’s important to note that accessing the backend SQL database directly from IdentityIQ should be done cautiously and in compliance with your organization’s security policies and best practices. Ensure that proper access controls are in place to restrict access to sensitive data, and that your SQL queries are optimized to minimize any potential performance impact on the database.

Example: Custom Rule to Fetch Identity Data

In this example, we’ll create a custom rule within IdentityIQ to access data from the backend SQL database. This rule will retrieve all identities of type “employee” from the database and display the name and department of each identity.

Step 1: Create the XML File

Create an XML file with the following content, which defines the custom rule:

Rule.xml (924 Bytes)

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Rule PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Rule language="beanshell" name="customSQLRule">
  <Description>This rule retrieves all identities from your SQL database and displays the name and department of each identity if the identity type is "employee".</Description>
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.Statement;

    Connection connection = context.getJdbcConnection();
    Statement stmt = connection.createStatement();
    String sql = "SELECT * FROM identityiq.spt_identity where type='employee'";
    ResultSet rs = stmt.executeQuery(sql);



    return "Code running successfully!";

Step 2: Import the XML File

Import the XML file into your IdentityIQ instance using the appropriate method (e.g., via the User Interface or through a deployment package).

Step 3: Run the Custom Rule

From the Debug page in IdentityIQ, select and run the “customSQLRule” rule.


The rule will execute the SQL query against the backend database and display the output in two locations:

  1. customSQLRule return value: This will display the message “Code running successfully!” in the IdentityIQ UI.
  2. tomcat-stdout.log: This log file, typically located in the application server’s log directory (e.g., <CATALINA_HOME>/logs/), will contain the output of the System.out.println statements. Each line will display the department and name of an identity with the type “employee”, separated by an underscore (__).

Use Cases

Implementing custom SQL rules in SailPoint IdentityIQ can be beneficial in various scenarios, enabling organizations to extend the platform’s capabilities and address specific business requirements. Here are some potential use cases where custom SQL rules can be leveraged:

  1. Data Enrichment and Transformation
    Custom SQL rules can be used to retrieve and transform data from the backend database, allowing organizations to enrich IdentityIQ’s data with additional information from external sources or perform complex data manipulations. This can be particularly useful for generating custom reports, implementing advanced access certification logic, or enabling context-aware identity governance processes.

  2. Data Cleanup and Maintenance
    Over time, the IdentityIQ database may accumulate stale or inconsistent data, which can impact the accuracy of identity governance processes. Custom SQL rules can be developed to identify and remediate data issues, such as removing orphaned accounts, updating outdated attributes, or resolving conflicting entries.

  3. Identity Lifecycle Management
    IdentityIQ’s identity lifecycle management processes can be extended using custom SQL rules. For example, rules can be created to automatically provision or deprovision accounts based on specific conditions or trigger additional workflows based on changes in identity data.

  4. Access Certification and Attestation
    During access certification campaigns, custom SQL rules can be used to implement complex access review logic or apply specific criteria for identifying potentially excessive or inappropriate access. These rules can leverage data from the IdentityIQ database and integrate with external sources to provide a comprehensive view of access rights.

  5. Audit and Compliance Reporting
    Generating audit reports and demonstrating compliance with regulatory requirements is a crucial aspect of identity governance. Custom SQL rules can be developed to extract and analyze relevant data from the IdentityIQ database, enabling organizations to create customized reports tailored to their specific compliance needs.

  6. Integration with External Systems
    IdentityIQ often needs to interact with external systems, such as HR systems, ERP applications, or other identity providers. Custom SQL rules can facilitate data synchronization and transformation between IdentityIQ and these external systems, ensuring data consistency and enabling seamless integration.

  7. Troubleshooting and Diagnostics
    In case of issues or errors within IdentityIQ, custom SQL rules can be utilized to retrieve diagnostic information from the backend database, assisting in root cause analysis and troubleshooting efforts. These rules can extract relevant logs, configuration settings, or other diagnostic data for further investigation.

It’s important to note that while custom SQL rules provide powerful capabilities, they should be developed and implemented with care, following best practices for security, performance, and maintainability. Organizations should also ensure that proper access controls and auditing mechanisms are in place to maintain the integrity and confidentiality of the data accessed through these rules.


Don’t use context.getJdbcConnection(). There’s an esoteric connection pooling-related reason for it, but it may eventually result in NPEs in more complex codebases.

It’s much safer to directly Environment.getEnvironment().getSpringDataSource().getConnection().

In 8.4, where “context” may actually refer to the Access History DB, depending on what you’re doing, the Environment version will also be guaranteed to get you the right schema.

getting database connection and then running sql query is not best practices. I would suggest to use the by passing the sql or hql query depending on the type of data you want to pull

String query = "SELECT firstname,lastname, email FROM identityiq.spt_identity where type='employee'";
Iterator itr ="sql:" + query , null,null);
 while(itr .hasNext()){
     Object[] object =;
     String firstname = object[0];
     String lastname= object[1];
     String email = object[2];

I could be critical of a lot of things here, but I recognize that this is just an example.

I’ll just say there’s a lot to getting data out of IIQ using SQL. If you really are interested, checkout my colab MC Spoofing Plugin. I have tasks that write out Identity data, Account data, and even let you spoof applications.

You have to actually look at the Identity ObjectConfig to determine where the value is stored. It could be a named column, an extended column, or an XML value.

If you are really interested the code is in the repo.

I actually will check this out, is there documentation somewhere? I have never had an issue with getJdbcConnection() but always eager to improve. Check out my colab MC Spoofing Plugin.

I will check this out but I haven’t had issues and haven’t been told that direct SQL is not appropriate. I went to direct SQL when using the API was so slow.
Of course the prime rule is to always use the API for writes, never write with SQL.

Hey @drosenbauer ,

Can you provide a sample code if you have any on how to use the environment to perform similar operation?

I’ll be grateful.

Can you provide the import needed for “Environment”? I tried org.springframework.core.env and it did not have a getEnvironment() method.

JavaDoc also says:

Return a JDBC Connection to the underlying database. This is used in a few places that need to run complex SQL queries. It should be unnecessary now that you can run SQL with<T>, sailpoint.object.QueryOptions).