Create SQL Sets

Owned by Phillip Hanegan
Feb 29, 2024
2 min read

SQL Sets are SQL queries that return a collection of resource objects from the EmpowerID Identity Warehouse, such as all people who have been hired in the last week. You can add these Sets to Query-Based Collections (SetGroups) and use them to make dynamic RBAC delegation assignments.

You cannot create SQL sets with queries that delete, drop, or update objects in the Identity Warehouse. For example, DELETE FROM PersonCommunicationType WHERE CommunicationTypeID = 2 is not acceptable. If you create a SQL set with such a query, EmpowerID cancels the operation and displays a message stating that the SQL Filter (query) is invalid.

The Set Compiler Job must be enabled on at least one EmpowerID Server hosting the Worker Role service, and the person creating the Set must have certain Access Level assignments. The job is enabled by default on all EmpowerID application (back-end) servers.

Create a SQL Set

  1. On the navbar, expand Role Management and select Query-Based Collections (SetGroups).

  2. Select the Actions tab and then click Create Query (Set).

     

  3. Enter the following information in the General section of the Set Details form:

    • Query (Set) Type – Select SQL Query

    • Name – Name of the Set

    • Display Name – Display Name of the Set

    • Description – Description of the Set

    • Is a collection of people – Select if the query returns people

    • Object Type – Select the object type. For example, if the Set returns people, select EmpowerID Person.

  4. In the Filter section of the Set Details form, in the SQL Query field, enter a SQL query that returns the desired objects. In this example, the Set returns all people hired within the last five days. Queries must begin by returning the GUID and FriendlyName of the objects or they will fail when compiled.

    Select PersonGUID, FriendlyName FROM Person (NOLOCK) WHERE CreatedDate BETWEEN DATEADD(day,-5, GETUTCDATE()) AND GETUTCDATE();

     

  5. Select Is Enabled to enable EmpowerID to compile the Set (selected by default).

  6. Click the Schedule Interval drop-down and set the schedule for when and how often the Set compiles. In this example, the Set compiles once every day at 8 am. To do this:

    • Set the Start and End dates

    • Set the Interval to Daily

    • Set the Iterations to 1

    • Set the Times (compilation time) to 8:00 am

       

  7. Select Do Not Allow Delete if you want to prevent users from deleting the Set in the EmpowerID interface.

  8. Select Create Matching Set Group if you want EmpowerID to create a Set Group (Query Based Collection) when the Set is created (recommended).

  9. Click Save.