Introduction
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.
Caution!!!
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>
<Source>
import java.io.*;
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);
while(rs.next()){
department=rs.getString("department");
name=rs.getString("name");
System.out.println(department+"__"+name);
}
rs.close();
stmt.close();
connection.close();
return "Code running successfully!";
</Source>
</Rule>
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.
Output
The rule will execute the SQL query against the backend database and display the output in two locations:
- customSQLRule return value: This will display the message “Code running successfully!” in the IdentityIQ UI.
- 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 theSystem.out.printlnstatements. 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:
-
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. -
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. -
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. -
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. -
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. -
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. -
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.