DynamicDatabase Connector

The DynamicDatabase Connector is a class of connector used to integrate with our customer's home-grown applications by connecting directly to the databases that store their user/entitlement information. This is meant to be used with a ClarityConnect instance, and as such, the DynamicDatabase application should be set up accordingly.

Understanding the Customer Data

The DynamicDatabase connector works differently from our other connectors because its meant to be completely configurable based on the underlying application. Because of this, its important to have a good understanding of the target application's schema. Key information to understand:

• Where is the user data stored?
• What are the different entitlement types?
• Where are the different entitlement types stored?
• How do we modify a given entitlement/user assignment?

The following example data will illustrate some common use-cases and how the DynamicDatabase connector would be configured.

Consider the following database-driven application. There is a user table which has a unique user identifier and attributes for each application user such as firstname, lastname, and email. There are additional attributes for "department" and "jobTitle". There are also columns for certain permissions, groups, and roles that are assigned directly on the user record.

In addition to the user table, there are also tables for a few entitlement types. There is a roles table which lists each role, as well as certain permissions that are associated with that role. There is also a profile table
which lists the availabler profiles and has some additional information about the given profile.

Lastly, there is a junction table which ties users to profiles in a many-to-many relationship:

Setting up the connector

Based on this information, we should now have what we need to fill out the DynamicDatabase connector's SQL statements appropriately. Within Clarity, go to the Application admin and select the Marketplace tab. Select the DynamicDatabase application and hit connect.

The DynamicDatabase connector supports several different database engines/drivers depending on the application we are trying to integrate with. Select the appropriate driver from the dropdown list. Depending on what driver you select, you may be prompted for additional information. Fill out the information for the host (can be a url or an IP address depending on the way the database server and firewall are configured), port, authentication method (typically SQL Authentication is appropriate unless you are integrating with an MSSQL database which uses AD Auth), the database you wish to connect to on the given DB server, as well as the username and password used to connect to the database. It is preferred that a bespoke service user is set up for Clarity instead of using an existing admin user. As usual, give the connector a unique name and identifier and then choose ClarityConnect for "access via".

Select the rest of the application settings as needed.

Writing the SQL Query Statements

Once you have set up the application config parameters, we should now be able to connect to the database directly, but Clarity doesn't know what SQL to actually execute to get the results we want. We will set those up now.

For any application that Clarity connects to, we primarily deal with a few simple concepts.

• What users are on this service?
• What are the different entitlements?
• What users have access to what entitlements?
• How do we get an individual user?
• How do we ADD an entitlement to a given user?
• How do we REMOVE an entitlement from a given user?
• How do we deactivate a user?
• How do we delete a user?

For most applications, we can get/put all of this information with API requests. For the DynamicDatabase connector, we need to write specific queries to answer each of these questions. On the application detail screen, you will now see a button at the top marked "SQL". This will open up the SQL query configuration dialog so you can start writing the queries needed to answer these questions. If you are not famiar with SQL syntax, you may want to get help from a developer or other customer success person who can help you. Also, each database engine has slightly different syntax, so you may need to adapt the following example to match the driver in question.

For each query you write, the field names that exist in the target application will not be uesable by Clarity without first being mapped or aliased to a normalized format that Clarity can ingest. Some key attributes are:

• identity_service_identifier - This is the unique identifier of the user on the given application. This will usually be an integer id, a guid, or a username that is unique to that individual and is the key used in any foreign table when referencing the user.
• entitlement_service_identifier - This is the unique identifier of the entitlement in question and is used in any relationship which refers to that given entitlement. This is usually an integer id or possbily a simple string.
• entitlement_type - This helps Clarity to distiguish between entitlements of different types so that it can execute the appropriate query based on the entitlement that is being added or removed.

Whenever we need to pass data to ClarityConnect regarding a specific row, a specific entitlement, a specific attribute, etc... we rely on query binding. There are two binding substitution characters: "?" for parameter bindings and "^" for column bindings. ClarityConnect will take this base query, and then automatically substitute in the values that you pass in through the bindings fields. Bindings are a comma-separated list of keys that get passed in with each query. They are substituted for ? or ^ in the order you provide. The specific bindings that are available for each query are outlined in each section.

Users

(Bindings available: none)

For the Users query, we need to write a statement that will select a list of all of the users on the application, as well as any helpful attributes that we can use to take full advantage of the features available in Clarity. Additionally, Clarity will automatically map the following standard user attributes:

• first_name
• last_name
• email
• active - this will need to be a 1 or a 0 so additional mapping may be required
• supervisor_user_identifier
• fullname

Using the table from the screenshot above, we can start by writing the following query:

SELECT 
	example_user.user_id AS `identity_service_identifier`, 
	example_user.email AS `email`,
	example_user.firstname AS `first_name`,
	example_user.lastname AS `last_name`,
	CONCAT(example_user.firstname,' ' ,example_user.lastname) AS `fullname`,
	'1' AS `active`,
	example_user.department AS `attribute_department`,
	example_user.jobtitle AS `attribute_jobTitle`
FROM example_user

In addition to the standard attributes mentioned above, any custom attributes that you may want to grab can be done so by aliasing the field in question with the 'attribute_' prefix as we've done here with the "department" and "jobTitle" field. For this example, the database in question does not have a concept of an "active" vs "inactive" user, so simply their existence in the user table signifies that they are active, so we will have just used the static constant 1 when selecting the 'active' field.

Once the SQL is written you will also see prompts for column bindings, parameter bindings, and type. Only the last one is needed and it should be user.

Entitlements

(Bindings available: none)

For entitlements, we need to write a specific query for each entitlement type we are interested in tracking. We'll start by writing the query for the "role" type from our example above. The role table has a list of all the roles, as well as some additional useful information about the roles. Key mappings that we need to be sure to include are the entitlement_service_identifier, entitlement_name, and entitlement_type. Additionally, any extra meta data that you may want to include can be fetched by mapping them to a field called "extra".

Note - the "extra" param is expected to be a JSON object, so use whatever casting is appropriate for the given database engine. Based on the schema above, we can write the following query:

SELECT example_roles.id AS entitlement_service_identifier,
	example_roles.name AS entitlement_name,
	'role' AS entitlement_type,
	JSON_OBJECT('is_admin', `is_admin`, 'can_manage_views', `can_manage_views`, 'can_punch_dolphins', `can_punch_dolphins`) AS `extra`
FROM example_roles

Similar to the users query, we dont need to worry about any of the bindings, but the "type" parameter is important. This field needs to be unique to this entitlement type and will be used by ClarityConnect when it figures out what SQL query to run based on the entitlement its trying to provision/deprovision. It needs to match the value we selected in our query, so in this case we put "role".

Now, we need to go through a similar process to fetch the other entitlement types that exist in the application. For this application the remaining standard entitlement type is "profile". First, we'd click the button "Add Additional Query". Going through the same exercise we just did for roles, but tweaking the query so that it references the correct tables, columns, etc... we have the following query:

SELECT example_profile.id AS entitlement_service_identifier,
	example_profile.name AS entitlement_name,
	'profile' AS entitlement_type,
	JSON_OBJECT('mask',mask) AS `extra_params`
FROM example_profile

Similar to the example above, for the "type" field, we would put the value "profile".

UserEntitlements

(Bindings available: none)

So we've read in users and entitlements, but now we need to figure out how those users are associated with the given entitlement. For each entitlement type, we need to write a query to fetch the data associating the users to those entitlements. The fields required for these queries' mappings are identity_service_identifier, entitlement_service_identifier, and entitlement_type. Using the example schema, we have the following query for the "role" type entitlements:

SELECT 
	example_user.user_id AS `identity_service_identifier`, 
	example_roles.id AS `entitlement_service_identifier`,
	'role' AS `entitlement_type`
FROM example_user
LEFT JOIN example_roles ON example_user.role_id = example_roles.id

The bindings can again be left blank, but the type field must match the entitlement type selected above, in this case "role".

We will also need to add an additional query for the profile entitlements, which results in this:

Inline Entitlements

(Bindings available: none)

But what about those extra fields on the user table? Aren't those also entitlements? Yes, but these are a special type of entitlement which we call "inline" entitlements. Inline entitlements are a special class of entitlement that do not exist in their own tables necessarily, but instead are attributes on the user object itself that can be modified to grant specific permissions or assign a user to a group, role, unit, etc...

If we take a look back at the user table in our example schema, you can see two columns, one called canCreateNewPages and one called userGroupAssignment. The first is something that appears to be a 0 or a 1. These types of entitlements are "inlinePermissions" and should be able to be evaluated based on its truthiness. 0/1, TRUE/FALSE, 'YES'/'NO" are all good candidates for this type of entitlement. Permissions can be added to the user query by prefixing the string "permission_" to the front of the column in question.

The second column appears to be a text value and its truthiness can't be evaluated. This is a candidate for an "inlineEntitlement". Inline entitlements can be added to the user query by prefixing the string "entitlement_".

After adding these additional entitlement types, our user query now looks like:

SELECT 
	example_user.user_id AS `identity_service_identifier`, 
	example_user.email AS `email`,
	example_user.firstname AS `first_name`,
	example_user.lastname AS `last_name`,
	CONCAT(example_user.firstname,' ' ,example_user.lastname) AS `fullname`,
	'1' AS `active`,
	example_user.department AS `attribute_department`,
	example_user.jobtitle AS `attribute_jobTitle`,
	example_user.canCreateNewPages AS `permission_canCreateNewPages`,
	example_user.userGroupAssignment AS `entitlement_userGroupAssignment`
FROM example_user

No changes are necessary for the bindings or the type of this query.

Note - As with most things, there's more than one way to skin this cat. Inline entitlements are just one of those ways. If there's 2 or 3 columns on the user table that you want to grab as entitlements, then inline entitlements are probably a good option. If there are 100 different columns on every user row, then you probably don't want to type out each one of them individually as a giant query. In this case, you probably want to do something like querying the information_schema (or similar meta data structure depending on the database engine) and then casting each row of that query as an entitlement.

Get User

(Bindings available: identity_service_identifier)

Clarity often needs to just fetch one user record at a time to verify that the user exists on the service, or to sync an individual user instead of syncing ALL users. For this, we need a query to just go fetch one user at a time, using the identity_service_identifier to get the one we want. The following query accomplishes that for us.

SELECT 
	example_user.user_id AS `identity_service_identifier`, 
	example_user.email AS `email`,
	CONCAT(example_user.firstname,' ' ,example_user.lastname) AS `fullname`,
	'1' AS `active`,
	'employee' AS `identity_type`,
	example_user.department AS `attribute_department`,
	example_user.jobtitle AS `attribute_jobTitle`
FROM example_user 
WHERE user_id = ?

In this case, we DO need to include a binding. In this case, we want to substitute this ? with the identity_service_identifier. Our configuration should now look like this:

That completes the necessary queries for READ ONLY applications. The rest of the queries in the admin are related to applications that we want to be able to provision to downstream.

Create New User

(Bindings available: first_name, last_name, fullname, email, username, org_units)

Note - the org units that are available will be dependent upon the specific Clarity deployment in question

Create new user is unique in a few ways. First off, its not directly called by Clarity itself. Instead, it is called whenever you attempt to add an entitlement to a user. When adding an entitlement, ClarityConnect will first call the "Get User" query to determine if the user already exists on the service. If not, it will call this "Create New User" query. Because of this, the parameters that are available for binding include all of the necessary information to create a new user and additionally, the default entitlement type and identifier if one has been configured.

Our example query looks like this:

INSERT INTO example_user SET 
  user_id = ?,
	email = ?,
	firstname = ?,
	lastname = ?,
	department = ?,
	jobtitle = ?

We use the bindings - username,email,first_name,last_name,department,jobTitle
giving us this resulting configuration:

Add Entitlement to User

(Bindings available: identity_service_identifier, entitlement_service_identifier, entitlement_type, grant_type, expiration, default_entitlement_identifier, default_entitlement_type)

Note - default_entitlement_identifier and default_entitlement_type will only be available if the application has been configured to use a default entitlement

For each entitlement type, we will need to write an appropriate query to associate the given user with the entitlement we want to add. In our example, we will need to write several queries, one for each of the following types: profile, role, inlinePermission, and userGroupAssignment.

These queries and bindings are as follows:

Note - for the inlinePermission type, we are using column bindings denoted with a ^. This allows us to dynamically pass in the column name of the entitlement that we are trying to modify.

Remove Entitlement from User

(Bindings available: identity_service_identifier, entitlement_service_identifier, entitlement_type)

Similar to adding entitlements to users, we also need to write separate queries for each entitlement type to allow us to remove those entitlements. Unlike adding entitlements, removing entitlements does not include the extra user information required to create a new user on the service.

For our four entitlement types, we have the following queries and bindings:

Note - for the inlinePermission type, we are using column bindings denoted with a ^. This allows us to dynamically pass in the column name of the entitlement that we are trying to modify.

Deactivate User

(Bindings available: identity_service_identifier)
If the service supports user deactivation, we want to supply the query to modify the user and set them to inactive. This might be a simple "active" column on the user which we'd set to 0 or it might be a "user_expires" timestamp that we'd want to set to NOW(). For our example, the database in question does not support this, but an example query might look something like this:

UPDATE example_user 
SET active = 0
WHERE id = ?

Delete User

(Bindings available: identity_service_identifier)
Lastly, we want the ability to completely delete a user from the service. Based on our example schema we have the following configuration: