Security and User Access

Last updated
Save as PDF

In this article, users will get the information about individual access and security of Data Warehouse.

TeamConnect has a comprehensive security feature that prevents unauthorized users from viewing certain objects types or viewing specific records. By default, reports that run against Data Warehouse will return all data that matches their filter criteria, without regard to the TeamConnect rights associated with a specific user. However, you can use an optional feature of Data Warehouse to restrict a report to just the data that would have been viewable in TeamConnect. To do this, your report must call a specific database function, passing a specific user name as a parameter. The report will use that user name to filter the results.

Security Functions

In Oracle Data Warehouse databases, these functions will be present in package WH_TC_SECURITY:

WH_TC_SECURITY.CHECK_USER_ACCESS_ACCOUNT
WH_TC_SECURITY.CHECK_USER_ACCESS_APPOINTMENT
WH_TC_SECURITY.CHECK_USER_ACCESS_CONTACT
WH_TC_SECURITY.CHECK_USER_ACCESS_DOCUMENT
WH_TC_SECURITY.CHECK_USER_ACCESS_EXPENSE
WH_TC_SECURITY.CHECK_USER_ACCESS_HISTORY
WH_TC_SECURITY.CHECK_USER_ACCESS_INVOICE
WH_TC_SECURITY.CHECK_USER_ACCESS_INVOLVED
WH_TC_SECURITY.CHECK_USER_ACCESS_LINEITEM
WH_TC_SECURITY.CHECK_USER_ACCESS_MILESTONE
WH_TC_SECURITY.CHECK_USER_ACCESS_PROJECT
WH_TC_SECURITY.CHECK_USER_ACCESS_TASK

In SQL Server, the functions are:

WH_CHECK_USER_ACCESS_ACCOUNT
WH_CHECK_USER_ACCESS_APPOINTMENT
WH_CHECK_USER_ACCESS_CONTACT
WH_CHECK_USER_ACCESS_DOCUMENT
WH_CHECK_USER_ACCESS_EXPENSE
WH_CHECK_USER_ACCESS_HISTORY
WH_CHECK_USER_ACCESS_INVOICE
WH_CHECK_USER_ACCESS_INVOLVED
WH_CHECK_USER_ACCESS_LINEITEM
WH_CHECK_USER_ACCESS_MILESTONE
WH_CHECK_USER_ACCESS_PROJECT
WH_CHECK_USER_ACCESS_TASK

Two additional functions, WH_USER_ALLOWED and WH_USER_DENIED, are for internal use and should not be called by your reporting tools.

Note: All custom objects' rights are determined by the WH_TC_SECURITY.CHECK_USER_ACCESS_PROJECT function (Oracle) or WH_CHECK_USER_ACCESS_PROJECT function (SQL Server), regardless of the actual object type.

See System Table Definitions for details about the structure of the tables that implement this security feature.

Implementing Security in Business Objects

Data Warehouse provides some example files that ease the process of implementing security in Business Objects. There are files for both Oracle and SQL Server.

In your installation media, locate subfolder bo-universe/oracle/03_Security. In that folder, file how_to_make_WH_secured_universe_oracle.txt contains the necessary Oracle queries, plus some preceding comments that explain the other steps that are required to use the queries in a security strategy.

In installation media subfolder bo-universe/mssql/03_Security there is an equivalent file, how_to_make_WH_secured_universe_mssql.txt, with relevant instructions for SQL Server databases.

Implementing Security in Other Reporting Tools

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