Define the Security SQL Map

Note: Follow these instructions only if you are using fully-mapped AD A directory service for Windows domain networks. A hierarchical structure that stores information about objects on the network. Used to manage permissions and control access to critical network resources. Security Mapping.

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. On your SQL connection Connection defines the how Connectivity Hub connects to your Source System (which contains your documents, graphics, etc.,). Your Connection includes identifying elements such as: URL of the BA Insight web service connector you are using, (File Share connector, SharePoint Online connector, etc.), Authentication mode, User Accounts and Credentials, Database information (for database connectors) select the Edit from the drop-down menu.

3. Select the Security tab. Assign a Master security connection, if desired.

4. Select the Security: Directory tab and select options as desired.

5. Select the Security: Scripts tab and enter the following information as appropriate for your environment:

   • Users load query:
     • Enter a SQL statement to return a list of all users in the database.
       
       • 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:
           
         Copy

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

   • Groups load query:

     • Enter a SQL statement to return list of all the groups and roles in the database.

       • 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 A directory service for Windows domain networks. A hierarchical structure that stores information about objects on the network. Used to manage permissions and control access to critical network resources. (AD)

           • Gives the full path to that group

           • Group is then flagged as "static"

           • No attempt to synchronize it is made
             

             Example:

           Copy

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

     Groups users load query:

     • Enter a SQL statement to return the list of all unique user IDs within a specified group ID.

     • This enables the synchronization of the group memberships between Active Directory and the source system Your Source System is the repository where your data is stored (data to be indexed). This repository is managed by applications such as: - SharePoint O365 - SharePoint 2013/16/19 - Documentum - File Share - OpenText - Lotus Notes - etc. Your Source System repository can also be a database such as SQL or Oracle..

       • [SPW_GROUPID] is a required parameter.

       • SPW_USERID is a required column to return.

         Example:

         
         
       Copy

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

   • Group hierarchy load query:

     • Enter a SQL statement that returns the list of groups that belong to a passed in group ID.

     • 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.

     • SPW_GROUPID is a required column.

   • Users load script:

     • Enter VB.NET Script to filter the users or custom map to AD Account.

     • VB.Net script that is executed by the Security Sync Loads User and Group tables and executes the mapping based on the connector configuration. task, once per group.

     • Script must return a user received from any of the TargetDirectory.GetUserBy... methods, which are documented for each supported target A Target is a "pointer" to a specific instance of a search application, such as Elasticsearch. - (A Search application instance has one or more indexes) directory type

     • Return boolean true=add, false=skip.

     • Example:

       Copy

       'CN=Uma Thurman/O=jgdomino,Uma Thurman
       dim sysn as string = HOST.GetSystemName()
       HOST.SetADID(HOST.GetDefaultDomain() & "\" & sysn.split(",") (1))
       return true

     • For more information and samples, see VB.Net Scripting.

     • You must run the Security Purge task after updating SQL script used to load users and groups.

     • Changes to the users and group SQL scripts or running the purge task requires running a full crawl to ensure the index accurately reflects the change.

     • See Run a Security Synch/Purge Operation.

   • Group load script

     • VB.Net script that is executed by the Security Sync task, once per group.

     • Script must return a group received from any of the TargetDirectory.GetUserBy... methods, which are documented for each supported target directory type

     • Can be used to return the mapped group from target directory.

       Copy

       dim sysn as string = HOST.GetSystemName()
       return not sysn.startswith("SYS")

     • This rejects any groups starting with SYS.

     • Parameters needed for the mapping are coming from the connector and can be retrieved by Host.Get... methods.

     • Groups found in target directory are static, while others will be native.

     • Note: Use only if your system supports group hierarchies.

     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

6. Click Save.