Define the Security SQL Map

Note: Follow these instructions only if you are using fully-mapped AD Security Mapping.

The following parameters are NOT SUPPORTED:

  • [SPW_ID]
  • [SPW_SUBID]
  • [SPW_GROUPID]

The following parameters ARE SUPPORTED:

  • @I_SPW_ID (for int value)
  • @S_SPW_ID (for string value)
  • @S_SPW_SUBID (for string value)
  • @I_SPW_SUBID (for int value)
  • @S_SPW_GROUPID (for string value)
  • @I_SPW_GROUPID (for int value)

Load User and Group Lists

Use the Security:Scripts tab to enter SQL statements to load the listings of groups and users.

  1. In Connectivity Hub, select the Connections tab.
  2. Click your SQL connection and click the Security: Scripts tab.
  3. Select the Security tab and enter the following information as appropriate for your environment:
  4. User Load SQL:
    1. Enter a SQL statement to return a list of all users in the system.
      • SPW_USERID is a required column and must be unique for the user.
      • SPW_USERNAME is a required column and is the user's login name.
      • SPW_ACTIVE is optional, and return values of T/F, Y/N or 0/1 to flag if a user is currently active or not.
      • SPW_ADID is optional, and overrides the mapping to the AD account.
        • Must be a fully qualified domain\login.

          Example          :
             select User ID as SPW_USERID, User Name as SPW_USERNAME,
             ADID as SPW_ADID, valid as SPW_ACTIVE from UserTable

  5. Group Load SQL:

    • Enter a SQL statement to return list of all the groups in the system.

      • SPW_GROUPID: A required column and must be unique for the group.

      • SPW_GROUPNAME: A required column and is the group's name.

      • SPW_ACTIVE

        • Optional

        • Should return values of T/F, Y/N or 0/1 if used to flag if a group is currently active or not.

      • SPW_ADID

        • Optional

        • Reveals the group is already mapped into Active Directory (AD)

          • Gives the full path to that group

          • Group is then flagged as "static"

          • No attempt to synchronize it is made

            Example:
               select GroupID as SPW_GROUPID, UserName as SPW_GROUPNAME,
               valid as SPW_ACTIVE from GroupTable

  6. Group Users SQL:

    • Enter a SQL statement to return the list of all the users that belong to the group ID.

    • This enables the synchronization of the group memberships between Active Directory and the source system.

      • [SPW_GROUPID] is a required parameter.

      • SPW_USERID is a required column to return.

        Example:
           SELECT UserId AS SPW_USERID
           FROM GroupUser
           WHERE GroupId = [SPW_GROUPID]

  7. For Group Hierarchy SQL, enter a SQL statement that returns the list of groups that belong to a passed in group ID.

    1. Enter a VB.NET script to filter the groups or custom map to static AD Group.
      Note: Use only if your system supports group hierarchies.

    2. SPW_GROUPID is a required column.

    INFO: User load Script and Group load Script fields are the same for all connectors.

    The fields are explained here: How to Override User and Group Loading