Identity-Based Security

The SQL connector supports identity-based security. In earlier versions of the SQL connector, security of a document always had to be modeled using security attributes - which is called the user-defined security model. With this enhancement, database records secured by user/group ACL can also be modeled using the database connector.

To support identity based secure crawling, the connector supports the following additional attributes in the crawling query or view. The value in these columns for each record in the record set defined by the crawling the query or view specifies the user or group who are granted or denied access to the record.

EQ_GRANT_USER - User granted access to the record
EQ_GRANT_GROUP - Group granted access to the record
EQ_DENY_USER - User denied access to the record
EQ_DENY_GROUP - Group denied access to the record

Below is an example crawling query that includes EQ_GRANT_USER column:
SELECT T1.URL_ID KEY, URL, LANG, LAST_MODIFIED LASTMODIFIEDDATE, T2.USER_NAME EQ_GRANT_USER, TITLE CONTENT, DS_ID FROM SCH.CONTENT T1 LEFT OUTER JOIN SCH.USER_ACL T2 ON T1.URL_ID = T2.URL_ID

If there are more than one user or group in the grant/deny list, the sub-query feature of the database connector should be used. Subqueries specifying grant/deny user/group should be specified as attribute queries in the subquery file. Here is an example sub-query file content:

<?xml version="1.0" encoding="UTF-8" ?>
<sqlQueries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/ses/sqlconnector/detail-attribute-queries detail-attribute-queries.xsd" xmlns="http://xmlns.oracle.com/ses/sqlconnector/detail-attribute-queries">
<attributeQueries>
<query>
<![CDATA[select group_name EQ_GRANT_GROUP from user_acl where url_id=##KEY##]]>
</query>
<query>
<![CDATA[select user_name EQ_DENY_USER from user_acl where url_id=##KEY##]]>
</query>
</attributeQueries>
</sqlQueries>