per_assignment_secured_list_v table

Tables and Views for HCM

Oracle Fusion Cloud HCM

Oracle Fusion Cloud HCM Tables and Views for HCM

Copyright © 2016,2024, Oracle and/or its affiliates.

Author: Shine Mathew

• Oracle Fusion Applications
• Oracle EBS R12
• Oracle Fusion Middleware
• Core Oracle Technology
• Oracle Banking & Billing Management
• Buy Certification Mock Exams

Fusion Blog

• Fusion Financials Latest Articles
• Oracle Fusion Applications Training Index
• Oracle Fusion HCM
• Fusion Procure To Pay
• Fusion PayRoll
• ADF Training
• APEX-Oracle Application Express
• Weblogic Server
• Oracle Data Integrator
• Oracle Identity and Access Management
• OFSAA - Oracle Financial Services Analytical Applications
• Common Apps Training Articles
• iProcurement
• Oracle Grants
• Financial Modules
• Supply Chain & Manufacturing
• R12 11i Differences
• Financials Documents
• AME and Self Service HRMS
• Financial Functional Documents
• Receivables
• General Ledger
• Oracle HRMS Payroll Migration
• HRMS Miscellaneous
• Oracle Payroll
• Oracle HRMS Payroll Training Index
• Oracle Workflows Training Index
• Other R12 Articles
• OA Framework Training Index
• Oracle Scripts
• General Apps Topics
• Oracle Apps Interview Questions
• Virtual Machines
• BI Publisher - XMLP
• Mobile Supply Chain Application Framework
• Sunil Khatri
• Jayashree Prakash

Ashish Harbhajanka

• Naveen Kumar
• Surinder Singh
• Krishnakumar Shanmugam
• Trending Authors
• Prasad Parmeswaran
• Kalimuthu Vellaichamy
• Kishore Ryali
• Sivakumar Ganesan
• Senthilkumar Shanmugam
• Prasad Bhogle
• Prabhakar Somanathan
• Ranu Srivastava
• Ahmad Bilal
• TheTechnoSpider
• Anshuman Ghosh

Oracle Gold Partners, our very popular training packages ,   training  schedule is listed here Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

Search Courses

Creating bi publisher data model using secured list views.

Introduction

While trying to create a Business Intelligence Publisher data model with physical SQL, we have two options. (listed below):

1. Select data directly from a database table, in which case the data you return isn't subject to data-security restrictions. Because you can create data models on unsecured data.

2. Join to a secured list view in your select statements. The data returned is determined by the security profiles that are assigned to the roles of the user who's running the report.

While at times there is a need to fetch the complete details from the database ( option 1) at times there are requirement where we want users to only fetch data which he/she is entitled to view. In such cases, using a Secured List View comes handy.

In this article, we would try to understand the impact of the Result Set returned by a SQL query if we use a Secured List View as compared to the database table.

Most of the commonly used tables do have a corresponding secured list view also available. The complete details could be found here .

For this example, we would try to create a very simple SQL query making use of a Database Table (PER_ALL_PEOPLE_F) and the secured list view (PER_PERSON_SECURED_LIST_VIEW) corresponding to this database table.

Since we are trying to demonstrate the impact of using a Secured List View in a SQL Data Model the easiest and the simplest way of showcasing this could probably be by trying to fetch the total number of records returned using the secured List View vis a vis that of the direct database table.

So at broad level we would perform the following steps:

Create a simple Data Model which would display the Table Name and Record Count for PER_ALL_PEOPLE_F and also for PER_PERSON_SECURED_LIST_V

Next we would run this data model with an Implementation User (say HCM_IMPL) and we expect to see the record count to be the same ( HCM_IMPL is a user who has all the roles available in the HCM Area)

Next we would try to run the same data model using a named user (say JAMES.AARON) and we expect to get a record count value of the data row corresponding to PER_PERSON_SECURED_LIST_V to have a lower value. The Record Count Value of the data row corresponding to PER_ALL_PEOPLE_F should anyways return the same value as in step 2.

So without much delay let’s get started.

Creating a Simple Data Model

We would be creating a simple data model which would comprise of the following fields:

Record Count

The SQL query used is:

We would save this Data Model in the Shared Folder as RecCount_dm

Running the BI Data Model with logged in user as HCM_IMPL

In this step we would login as HCM_IMPL as a user and try to run the Data Model. We expect to see the same RecordCount values for the PER_ALL_PEOPLE_F and PER_PERSON_SECURED_LIST_V database objects.

We could see that the RecordCount for both the data rows is same ( 3690 Records)

Running the BI Data Model with logged in user as JAMES.AARON

We would login to the application using JAMES.AARON as the user . navigate to the BI Data Model and ‘View the Data’. The results displayed are captured in screenshot below:

We could see that the Data Row corresponding to PER_ALL_PEOPLE_F has RecordCount value as 3890 while the same for Data Row corresponding to PER_PERSON_SECURED_LIST_V returns 11.

So now, we have seen that using the same SQL Query and the same tables we get different results depending on the roles assigned to the logged in user.

On one hand the Admin User who has access to all the roles is having the same record count returned from both database table and secured list view, the other user who only have access to specific data returns a much lower record count (corresponding to the Secured List View Data Row). This is in-line with the Secured List View properties where the data returned is determined by the security profiles that are assigned to the roles of the user who's running the report.

I hope the above post is clearly establishing the fact.

While I have tried this using one database table and its secured list view one can try with any other table and secured list view and the results would be similar.

With this, I have come to the end of my article and I hope I was able to explain the concept clearly.

Thanks for your time and have a nice day!

Comments    

Add comment.

Name (required)

E-mail (required, but will not display)

Notify me of follow-up comments

About the Author

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

More articles from this author

• ObfuscatedSwedishWorkerAutomated HCM Extract in Oracle Fusion HCM Cloud
• WHAT Is SQL
• Restrict Changes on Approved Historical Absences
• Control Absence Type Visibility Based on Plan Balance
• Performing Mathematical Calculations on an Extract Rule Attribute
• Allow Absence Recording by Specific Admin Users
• Developing Low-Code/No-Code Reports in Oracle ERP Cloud Application
• Verifying Workforce Structures Position Row In Oracle HCM Cloud Application
• Alternative Absence Flow on Overbooking Absence
• A Worked Out Example of Changing Label Name in Unified Sandbox
• Creating Fusion BIP Reports From Static Data
• Displaying Impacted Assignment Details when Position Definition is changed
• Creating 90 Days Absence Plan
• Smart Navigate to Person Management from Report Output Column
• An Overview of Master Report - Absence Data Integrity Detection
• WHY FUSION ?
• Control Absence Type Visibility Based on Plan BalanceV3
• Coexistence Mapping
• Allow Absence Recording After 50 Calendar Days from Hire Date
• An Overview of Functional Setup Manager In Oracle HCM Cloud Release 13

Search Trainings

Fully verifiable testimonials, apps2fusion - event list, enquire for training.

Get Email Updates

Powered by Google FeedBurner

Latest Articles

• OIC Agent Installation Failed !!! Incorrect OIC Username / Password provided
• OM & AR Setups In EBS R12 Part 3
• OM & AR Setups In EBS R12 Part 2
• Fixed Asset Flexfields in Oracle Assets EBS R12
• OM & AR Setups In EBS R12 Part 1

• Join us on Facebook
• Self Paced Courses
• Custom home
• About Apps2Fusion
• Corporate Trainings
• Finance Transformation Experts

Popular Articles

• Subscribe to New Oracle Apps Articles on this Website by Email
• Basic Concepts - List of useful Oracle Apps Articles
• XML Publisher and Data Template - SQL Query to develop BI Publisher Reports
• Some Commonly Used Queries in Oracle HCM Cloud
• OA Framework Tutorials Training

Apps2Fusion are passionate about Fusion Cloud E-Learning and classroom trainings. Training is our core business and we have been doing this for many many years. We work hard to advise trainees with right career paths. We have published various five star rated Oracle Press Books each was best sellers in its category. We have helped many and could help you as well. 

OUR COMPANY

• Manage VIP Account
• Register for VIP Plan
• VIP Member-Only Content
• HCM Data Loader
• HCM Extract
• BI Publisher
• Fast Formula
• OTBI Analytics
• Personalizations
• Scheduled Processes
• Absence Management
• Performance Management
• Talent Management
• Time & Labor
• HCM Extracts Questions
• HCM Data Loader Questions
• BI Reports Questions
• Report Issues/suggestions
• Your Feedback counts
• Write for Us
• Privacy Policy
• Join Telegram Group
• Join LinkedIn Group
• Join Facebook Page

Applying Fusion Application Data Security to BI Publisher Reports

• Post category: BI Publisher / Security
• Post comments: 0 Comments
• Post last modified: August 26, 2020
• Reading time: 7 mins read

In this article we will look into an interesting concept of applying data security to the BI Publisher Reports.

We all know that OTBI Analytics provides output based on the Data Security access, the user has. However the BI tables doesn’t automatically inherit the data security of the logged in user. We will have to explicitly join the Secured Views to implement this data security.

Here is the complete list of tables and security views available in fusion along with their corresponding Data Security Privilege which provides access to that security View and the Duty role which has that Data Security Privilege added.

We need to add these security views to the main tables while building the query in order to apply the data security policies and return only the users which the logged in user has access to.

Table of Contents

Sample SQL using the Security Views:

The above query will only return the PAN data for the employees that the logged in user has access to. If the logged in user doesn’t have any data access, then he won’t get any data.

Apart from the security views, we have Personally identifiable information (PII) tables which are secured at database level using the virtual private database (VPD) policies. Only authorized users can report on data in PII tables. This restriction also applies to Oracle Business Intelligence Publisher reports. The data in PII tables is protected using data security privileges that are granted by means of duty roles in the usual way.

Tables Containing PII Information and the Data Security Privilege Mapping

All of these privileges are accessible using the Workforce Confidential Reporting duty role, which the Human Resource Analyst job role inherits.

if you don’t have the right job role, you might not be able to check the data from the backend tables as well.

Hope this information will be useful when you work on implementing security in BI Reports.

You Might Also Like

SQL Query to find Worker Count by Person Type

What is the alternative for DATA_CACHE which is being deprecated from 21A?

Migration of Objects - BI Reports

Session expired

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.

HCM Extracts - Using Secure Views in Data Group Filter Criteria

Queries around secured views in data group filter criteria

Content (required):

We have an HCM extract to fetch demographic and payroll information. The extract has a non-mandatory custom parameter tied to a value set. If the user does not select any value, we'd fetch the data across all the legal entities.

The root data group, is PER_EXT_PAY_EMPLOYEES_UE. I was looking at restricting data based on the user's roles in the extract by adding a secured view, here PER_ASSIGNMENT_SECURED_LIST_V to the data group filter criteria.

When I do it using the SQL for PER_EXT_PAY_EMPLOYEES_UE via a BI query, the data is restricted based on the user's roles. However, when we do it via filter criteria, the extract still pulls in all the data regardless of the security offered by the view.

• Category 203
• Extract Flow

Howdy, Stranger!

To view full details, sign in.

Don't have an account? Click here to get started!