If you have not already done so, run ORACLE_WH_TC_SECURITY.SQL (or MSSQL_WH_TC_SECURITY.SQL) in the Data Warehouse schema. This script builds several database functions (described in Security Functions) that can then be used in joins in queries. These functions always expect a user ID, and the primary key of a record, as arguments. Typically they require other arguments as well. Here is an example that could be used when querying Invoice records:

FUNCTION CHECK_USER_ACCESS_INVOICE (

p_user varchar2

,p_primary_key number

,p_security varchar2

,p_created_by_contact_pk number

) return VARCHAR2 ;

p_security is a string containing "Public" or "Private" and p_created_by_contact_pk is the primary key of a Contact record.

This function returns a "Y" or an "N", indicating whether the p_user value passed to the function is permitted to access the record designated by p_primary_key.

Your database queries should include a WHERE clause that contains a call to this function and selects "Y" return values. For greatest ease of use, the reporting tool user ID should be the same as the value that you intend to pass to p_user when calling this function.

Here is an example of a WHERE clause in a query that uses the Project and Task views. The user ID is contained in variable BOUSER and calls to security functions are highlighted in bold.

WHERE

(

WH_PROJECTS_VW.PROJECT_PRIMARY_KEY=WH_TASK.PROJECT_PRIMARY_KEY(+)  )

AND ( WH_PROJECTS_VW.PROJECT_PRIMARY_KEY=WH_CF_TRAN.PROJECT_ID  )

AND ( WH_TC_SECURITY.CHECK_USER_ACCESS_PROJECT(@Variable('BOUSER'),

WH_PROJECTS_VW.PROJECT_PRIMARY_KEY , WH_PROJECTS_VW.SECURITY,

WH_PROJECTS_VW.CREATED_BY_CONTACT_PK,

WH_PROJECTS_VW.OBJECT_UNIQUE_CODE,

WH_PROJECTS_VW.OBJECT_IS_EMBEDDED)='Y'  )

AND ( WH_TC_SECURITY.CHECK_USER_ACCESS_TASK(@Variable('BOUSER'),

WH_TASK.TASK_PRIMARY_KEY , WH_TASK.SECURITY,

WH_TASK.CREATED_BY_CONTACT_PK)='Y'  )

AND . . .