Can we restricted Report data on a users basis?

Mirza_Adeel

Dear ,

We need to restrict reports data to a user base. Some user see all data which have been in report and some users see only his data. How can we do this . we need to gave access only for reports

Thanks

Nathan CCC

Hi, Here is a method to reuse the row level security already implemented by oracle in the metadata repository such that users use the same report will only see the data rows to which they are setup because the database will add where clause to your query at run time to implement data security. So the design is to add a second data set in your data model in your report to do data security.

Data set 1 is the data set you already have of type SQL with data source ApplicationDB_*

for example

select all t.person_number as "LINK_ID" from per_all_people_f t

Data set 2 for security is also a data set of type SQL but this time with data source Oracle BI EE so the query is logical sql cut paste from the advanced tab on an ad hoc analysis you built uisng one of the subject areas

for example

select all 0 s_0, "Employee"."Employee Number" as s_person_number from "Expenses - Expense Transactions Real Time"

Now in your data model create a link between the 2 data sets on the conforming column. Job Done!

The system will add a where clause at run time to restrict the user to only see the rows they are allowed to see in data set 2 then the inner join will stop that user seeing the rows they are not allowed to see in data set 1.

BTW For this report to execute you may need to grant users privilege on the data source so On Administration page /analytics/saw.dll?Admin in Manage Publisher on JDBC Connection "Oracle BI EE" at the bottom in the security section make "Bi Consumer Role" an allowed role then Apply.

Mirza_Adeel

Dear ,

Thanks but how can we do this to OTBI report because we have OTBI report

Thanks

Nathan CCC

Hi, my instructions are how to do it in a report in oracle transactional business intelligence (OTBI). Cheers, Nathan

Mirza_Adeel

Dear ,

how we do it on analysis ? subject areas because in Analysis we dont have Data models

Thanks

Nathan CCC

Hi, You need to do nothing in an analysis. That is the point above. Data security is "build in" our of the box in RPD.

The subject areas in the metadata repository database will implement row level security on your behalf. For example in subject area Expenses - Expense Transactions Real Time" the system will automatically add a where clause onto the bottom of your query at run time to 1) filter only expenses for which this user in this session is the person 2) filter only expenses for people for which this user in this session is the line manager of that person 3) filter only expenses for people with a business unit for which this user has been granted security context "business unit" on role "Expense Manager" on page "Manage data access for users".

For example, so if you build analysis to select all expenses, user A who has is granted no data access to business units and is not a line manager will see only their expenses, user B who has is granted no data access to business units but is the line manager of A will see only their expenses and A's expenses, user C who is nor a line manager but was granted business unit X in data access will see their expenses plus all expenses for people in business unit X but not people in business unit Y. So user A B C all run the same analysis with the same logical query sent to the meta data repository database but they each get different results sent back to them.

Gaurav Bharadwaj-Oracle

Subject Area security for a user Follows Fusion side security setup for that User.

If the user can see something or has access to some data in Fusion, then he automatically gets that access in OTBI as well.

.......

But if your question is about subject area security in general, this documentation might be of help :

https://docs.oracle.com/en/cloud/saas/index.html

From here onwards, click on the Fusion service(eg : Financials) you are using, and then on the left side pane click on "Analyze and Report " then "Review Financials BI subject areas "

You can follow this same process for any Fusion Service to get the details of each subject area.

Rajasekhar Bandaru-Oracle

Hi Mirza

OTBI subject area inherits the same security as per Fusion UI page.

Please review the below documentation links

https://docs.oracle.com/en/cloud/saas/human-resources/22a/ochus/reporting-data-security.html#s20044631

OTBI Subject area documentation link

Doc ID 2730788.1Fusion OTBI Subject Area Documentation

Shashank Verma-Oracle

OTBI subject area inherits the same security as per Fusion UI page.

For Example If a User have Excess to One Business unit, He will be able to see only that particular Business Units data in OTBI output

Mirithu

@Nathan CCC I would like to delve more into the row level security you have mentioned above. Are you in a position to assist?

Nathan CCC

Hi,

The best way to understand it is to view the session logs. In the log you can view the clauses added at runtime by the metadata repository database to implement the data security for that user with their data access setup.

So ask you bi administrator to grant to your user or a role your user has privileges Issue SQL Directly and Manage Session son page /analytics/saw.dll?PrivilegeAdmin. Then you go to page /analytics/saw.dll?Sessions to View Log for each query from each user.

The logical queries from subject areas from dashboard prompts and analysis etc will in their log have generated one or more physical queries, typically WITH …, to the application database view object tables and views that include the data security for that user in that session.