Custom Tables within IdentityIQ

Custom tables within IdentityIQ are a great way to store sets of data or structures that can  easily used within IdentityIQ.  The tables can be used in a variety of use cases:

  • Staging table for data load or preprocessing of data inputs
  • Input/Output table for syncing data to other systems
  • Historical username storage
  • Lists of batch emails, notifications, or processes
  • Form value lookups

Using a custom database table, you gain the security and control inherently in the identityiq database as well as benefiting from the backup and disaster recovery features implemented for the identityiq database itself.

IdentityIQ does include custom XML objects.  Custom objects within IdentityIQ are great for storing maps of key/value pairs.  While custom objects can be written to by IdentityIQ, database tables provide more flexibility by allowing for full SQL queries as well as a variable data structure.

Within the identityiq database, most tables start with spt_.  It is best practice to add a prefix to a custom database tables that corresponds with the customer name or code base.  As an example, let’s consider a use cases where we want to store a list of every username that IdentityIQ creates within AD.  As part of that data, we would like to include the employee id that is associated with the AD account that is being created as well as the date it was created.  Using the SQL Server Management Studio, I created a table with the following SQL:

CREATE TABLE idm_username (

[username] [varchar](255) NULL,

            [employeeid] [varchar](255)

            [date] [varchar](255) NULL

)

Once the table is created within the IdentityIQ database, it can be queried like any JDBC connection.  However, the advantage of using a table within the IdentityIQ database is that the connection to the database can be obtained from the IIQ context rather than having to store the URL, username, and password within the code itself.  To obtain the JDBC connection object to the database, use the following command:

Connection conn = context.getJdbcConnection();

Once the connection is established, normal SQL queries can be used to read and write to the table.  In our scenario, the AD account name generation rule needs to check to see if the name has been used before.

      Statement stmt = null;

      String sql = "SELECT username from idm_username WHERE name='"+name+"'";
      stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(sql);

      if (rs!=null)
      {
            //username in use                  
      } else {
            //username not found
      }