SQL statements, stored procedures, and custom code

The On-premises Connector for Generic Databases offers three methods for performing operations on your on-premises database system: SQL statements, stored procedures, and custom code. You can configure these methods to import user and entitlement data into Okta, and also manage provisioning changes from Okta to your database.

SQL statements

SQL statements are single commands used to perform specific tasks, such as retrieving, updating, or deleting data. The connector can execute these statements to get user and entitlement data from your database or to send changes from Okta.

For operations that require user-specific information (such as a user's ID), the On-premises Connector for Generic Databases uses a placeholder that's represented by a question mark (?), in the SQL statement. This placeholder is dynamically replaced with the actual user attribute at the time of execution.

The following are examples of SQL statements for common operations:

  • Get Users: Fetches all users and their data from a user table:

    SELECT USER_ID, USERNAME, FIRSTNAME, LASTNAME, EMAIL FROM USERS

  • Create User: Inserts a new user into the user table.

    INSERT INTO USERS (USERNAME, USER_ID, EMAIL, FIRSTNAME, LASTNAME) VALUES (?,?,?,?,?)

  • Update User: Updates a user's information based on their ID.

    UPDATE USERS SET USERNAME = ?, FIRSTNAME = ? WHERE USER_ID=?

Stored procedures

A stored procedure is a set of pre-compiled SQL statements saved by name to your database. You can call a stored procedure by name instead of repeatedly writing complex queries. The Okta On-prem SCIM Server can call these procedures for both import and provisioning operations.

Similarly to SQL statements, you can use a ? as a placeholder to pass user or entitlement attributes to the stored procedure. The procedure can also be configured to return data row by row, which is useful for complex data retrieval tasks.

Get Users: Invokes a stored procedure to retrieve a list of active users.

GET_ACTIVE_USERS

Custom code

Custom code allows you to use your own Java code using the SCIM SDK provided by Okta to perform database operations that require more complex logic than simple SQL statements or stored procedures. While this method supports all outbound provisioning (for example: Create, Update, Activate), it can't pull data into Okta. Therefore, you must use SQL statements or stored procedures to import users and entitlements.

To use custom code, you must first develop your Java code and build it into a JAR file. Then, you must place this JAR file on the server where the Okta On-prem SCIM Server is installed. The Admin Console provides fields to specify the name of your JAR file, the class name that contains your code, and an SHA256 hash to verify the file's integrity.