@WHERE:SECURITY clause on Resource not working

book

Article ID: 225211

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

We are trying to use the following syntax to restrict access to a custom object's records, based on access rights of the logged in user. But we observe that this does not work.

@WHERE:SECURITY:RESOURCE:odf_ca_custom_object_ID[email protected]

Environment

Version: 15.9.3

Resolution

It has been observed that the NSQL syntax:

@WHERE:SECURITY:RESOURCE:odf_ca_custom_object_ID[email protected]

gets converted to the following SQL for execution on the DB:

odf_ca_custom_object_ID.created_by IN (
  SELECT object_instance_id
  FROM odfsec_resource_v2
  WHERE user_id = < Internal ID OF the logged IN User >
  )

So, this approach would not work in fulfilling the requirement, since a comparison happens between unrelated data.

However, there are security views available for custom objects as well. They can be used as provided below. "custom_object_id" is a placeholder for the actual ID of an Object.

@WHERE:SECURITY:custom_object_ID:odf_ca_custom_object_ID[email protected]

This would translate to

odf_ca_custom_object_ID.ID IN (
  SELECT object_instance_id
  FROM odfsec_custom_object_ID_v2
  WHERE user_id = < Internal ID OF the logged IN User >
  )

Please note that, this method is not officially documented in the Product Documentation. Please use this approach only after thorough testing, and becoming confident of its usability for the specific implementation you are working on.