Implementing Row-Level Security in Oracle Analytics Cloud with ADW

Jayakumar Perumal

Hi,

We are currently using Oracle Analytics Cloud (OAC) with Autonomous Data Warehouse (ADW) and loading data from our on-premises Oracle Database to ADW using Oracle Data Integrator. In Oracle E-Business Suite (R12), we filter data based on operating units by leveraging responsibilities.

Could you please guide us on how to implement a similar row-level security (RLS) mechanism in ADW to filter data dynamically based on the logged-in user? Specifically, we would like to understand:

1. How to pass the logged-in user's information from OAC to ADW.
2. Best practices for filtering data at the row level using session variables or other methods in ADW.

Thanks,

Jay

Ashish-Oracle

@Jaykumar Perumal Please refer the below official documentation.

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acmdg/work-row-level-security.html

Jayakumar Perumal

Hi Ashish,

Thank you for your quick response. I wanted to confirm that we are using the Professional version of Oracle Analytics Cloud. Based on my understanding, I believe this version does not support the Semantic Model Dataset.

Please let me know if there are alternative approaches or workarounds that we can use in our version to achieve similar functionality.

Looking forward to your guidance.

Thanks,

Jay

Ram-Oracle

@Jayakumar Perumal Please also verify role based filters from below documentation and see if it helps.

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/use-role-based-filters-dataset.html#ACUBI-GUID-25F17720-B52E-4744-83ED-F579DFF80B59

Jayakumar Perumal

Hi Ram,

Thank you for your quick response. I went through the link you shared, and I see that it filters data based on user roles. I understand this needs to be set up for each dataset individually.

For example, if I am using the same table across 10 different datasets, I would need to configure this filter in each dataset, correct?

Instead of managing filters at the dataset level, is there a way to set up filtering directly at the database table level based on the OAC user? Can we use DBMS_RLS.ADD_POLICY to achieve this functionality?

Looking forward to your thoughts.

Thanks,
Jay

GayathriAnand-Oracle

If you are using Enterprise Edition, you could have explored Execute Before Connect or Execute On Connect connection pool scripts in rpd to invoke custom stored proc passing USER session variable as parameter OR use VPD but with Professional Edition , you don't much options using dv connection. The best option is to set filters for individual datasets where you can leverage the system session variables to define the filter.

Jayakumar Perumal

Hi GayathriAnand,

Thank you for your quick support. Your approach is working perfectly. I am trying to filter operating units in my OAC project, but we have hundreds of datasets and more than 10 operating units. This means I would need to create more than 10 filters for each dataset. Is there an alternative way to achieve this filtering?

Thanks,
Jay

Jayakumar Perumal

Hi ,

We are now planning to move OAC Enterprise version. please some share docuemnt to implement row level security function.

Thanks,

Jay

Harriet Huang-Oracle

Hi @Jayakumar Perumal

How about Building Semantic Models in Oracle Analytics Cloud since you are using OAC Enterprise version now?

Thanks

Harriet

Jayakumar Perumal

Hi Harriet,

Thanks for your quick support.

We are migrating from Oracle Discoverer to OAC and have around 500 reports that were developed and reconciled over the years. If we build a Semantic Model, we would need to re-develop the entire structure from scratch, which would be a significant effort.

Currently, we are moving data from our on-prem database to ADW using ODI and leveraging the same Discoverer queries to build datasets in OAC. Could you provide any suggestions or best practices for achieving optimal performance and efficiency in the dataset approach?

Thanks,
Jay