DWH tables to implement row level security for Investments and Resources

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Investment and Resource data is available in the Data Warehouse (DWH). However, we do not want to show all the records to all the users in our organization, when reports are built using DWH. How to implement security so that records that are visible to users in Clarity, are the only ones shown in DWH based reports?

Environment

Version: 15.9.1

Resolution

There are two views available in DWH schema that can be used to implement data security:

1. DWH_RES_SECURITY_V
2. DWH_INV_SECURITY_V

USER_KEY in these views are IDs derived from SRM_RESOURCES table in PPM schema.

A sample query is as below.

SELECT
    *
FROM
    dwh_inv_investment dii
    JOIN dwh_inv_security dis ON dis.investment_key = dii.investment_key
WHERE
    dis.user_key = 5005004;

A prerequisite for the use of this table is that the Load Data Warehouse Access Rights job is scheduled and completes successfully in the system.

Additional Information

Do not use the following views for new development. They are available to support legacy reports only.

1. DWH_INV_SECURITY
2. DWH_RES_SECURITY