...
This is were the real difference between the two cases presented in introduction happens !
1. Database documents ACLs managed by an Active Directory/LDAP
If the documents ACLs are managed by an Active Directory/LDAP then you need to /wiki/spaces/DATAFARI/pages/223313957. Be careful though, the authority connector needs to be linked to the same authority group as the JDBC repository connector used in the job ! If the AD/LDAP authority connector has been automatically created by the AD/LDAP configuration UI of Datafari, it will for sure not be in the same authority group, you will need to change it manually in the “Type” tab of the authority connector ! To better understand the reasons of this and the best practices concerning authority connectors please read this documentation: /wiki/spaces/DATAFARI/pages/2188115979
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
If the documents ACLs are managed internally by the database, then you will need to configure a JDBC authority connector that will connect to the database and retrieve the access tokens of users. To do that, follow these steps:
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
SSL Trust all certificates (from v6.2): For new drivers, such as MS SQL Server for versions 2012 upwards, to bypass SSL security if it is not needed in your network. This saves you from having to add a certificate to access your database. If you have PKIX errors, General OpenSslEngine problem… On connexion check, think to check this box. If the errors disappear, that means the only problem is certificate. Choose if you keep it checked or add the certificate for your database (cf. /wiki/spaces/DATAFARI/pages/797638657 ). In most cases, you can check this box, as Datafari is already covered by our secured Apache Proxy with your trusted certificates.
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)
...