...

Next, ensure that the ACLs returned by the authority connector are of the same kind and can match the access tokens returned by the data table/view you used in the job configuration of the JDBC connector. If this is not the case, then you will need to modify your table/view so that they are compatible and that a user allowed to access to a specific database document has matching authority connector token(s) with the access tokens of the document.

2. Database documents ACLs managed by the Database itself

...

• go to the MCD admin UI and navigate to Authorities => List Authority Connections and add a new connection

• enter a name (mandatory) and a description (optionnal) in the “Name” tab

  

• Select the “JDBC” connection type and the authority group corresponding to the authority group associated to the repository connector used in the crawl job of your database

  

• Click on the “Continue” button and in the “Database Type” tab that will appear, select the database type corresponding to yours and select the “by label” access method.
  NB: The access method serves to find the columns in the resultsets by name or by label, “by name” will search for original column name whereas the “by label” option will search for the column label. A column label is by default the original column name if there is no mapping (e.g ‘SELECT id AS doc_id’ is a mapping of the original column name ‘id’ to ‘doc_id’), if a mapping is defined, then the label will be the mapping name. In our case we definitely want to use labels instead of original column names !

  

• In the “Server” tab, fulfill the parameter as you did for the repository connector

  

• Do the same in the “Credentials” tab

  

• In the “Queries” tab you must define two queries:
  - the “user ID query” that must return the database user ID based on the username provided by Datafari (which is samaccount@domain when an Active Directory is configured). Its query template is:

  Code Block
  language sql
  SELECT idfield AS $(IDCOLUMN) FROM usertable WHERE login = $(USERNAME)

...

The “user ID query” is only mandatory if the table/view used in the authorization tokens query only works with database user IDs and that you need to perform a mapping between usernames (samaccount@domain) and database user IDs. If defined, it must use the $(IDCOLUMN) parameter that is used by the connector to identify the user database ID, and the $(USERNAME) parameter that is replaced by the connector by the username provided by Datafari during search.
The “authorization tokens query” must use the $(TOKENCOLUMN) that is used by the connector to identify in a result set the auth token of the user, and either the $(UID) or the $(USERNAME) or both depending if the table/view used in the query is working with usernames (samaccount@domain) or database user IDs (the $(UID) parameter is replaced by the user ID returned by the “user ID query”). Also, the “authorization tokens query” must return one result tuple per user authorization token. Here is an example of a compatible user_authorizations table/view for the “authorization tokens query”:

...

If the authority is working and properly configured with THE CORRECT AUTHORITY GROUP (the same configured for the repository connector used in the crawl job), your database documents searches are now secured !.