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 foundhere.
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:
-
Table Name
-
Record Count
The SQL query used is:
|
SQL Query: |
|
SELECT 'PER_ALL_PEOPLE_F' TABLENAME, (SELECT COUNT(*) FROM PER_ALL_PEOPLE_F) recordcount FROM PER_ALL_PEOPLE_F UNION SELECT 'PER_PERSON_SECURED_LIST_V' TABLENAME, (SELECT COUNT(*) FROM PER_PERSON_SECURED_LIST_V) recordcount FROM PER_PERSON_SECURED_LIST_V |
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.
Inference
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!
Apps2Fusion Articles 
Comments
I have always disliked the idea because of the costs.
But he's tryiong none the less. I've been using WordPress on a variety of websites for about a year and am
worried about switching to another platform. I have heard very good things about
blogengine.net. Is there a way I can transfer all my wordpress content into it?
Any kind of help would be really appreciated!
I really hope to check out the same high-grade blog posts from you later on as well.
In fact, your creative writing abilities has encouraged me to get my own site now ;)
in recent years anybody don’t have always time to meet with your friends, Ukrainian forums may be a great place to set up our night out. do not ever go from appointment to different person! whenever you are a man ukrainian women and would like a woman up to now in today's market, looking for Ukraine all women might just be the best consideration to help make. She can end up being the one from the old saying “Behind one useful husband you do have a strong woâ€. One of the favourite beauty products in the strategy of Ukrainian chick is usually dry. These are the reasons why Ukrainian spouse are stand out. That enables them to to fulfill its purpose to their quest for usual Ukrainian the ladies, entirely women or caring girlfriends. Ukraine most women uncover holy matrimony come to be the lot more than supportive life partners mainly because they are really marvelous cuties located on consumer and furthermore eye-catching goddesses sleeping. if you work with some good sense though it is in fact very readily available a popular indisputable Ukrainian a relationship source site where you want will come across some pleasant Ukrainian brides suitable for partnership. internet dating, You get the chance to concur with someone you wish to create the best decision as to whether most people prefer meet insurance firms her have to come across or even otherwise.
they cook every bit of procedures much more. explanation for this is my wife expended all then in starting her own blow, make up, but also picking out a fantastic clothing, very your own woman won’t realize why that are going to send you to even remotely outraged. an amazingly other half listens to your requirements and requirements, setting aside time for every her very own best to help you relieved. any of them just want remove their being alone and have absolutely a Slavic soulmate. associated with us do business with Ukrainian single ladies who would like to change those people's lives and find abroad soulmate. your company's soulmate may well much magnified within you think. perhaps, incredible Ukrainian young women think official marriage ceremony can be proof a man’s are attracted to, Which is a vital help romances. disfavours of wedding to a Ukrainian young lady. you'll be able to register your marriage your own states - read the natural laws to your respective country, They may differ. my spouse and i follow through diligently before taking your forms.
which happens to be in Ukrainian courting heritage - the girl appeals to the person with a overall look and accommodate individual after which you'll, in case he has an interest, brings out their hidden knowledge using the lady coronary heart. in addition to right here is the nice post teaching you how to get a Ukrainian woman’s affection. 3 what very warm Ukrainian your girls exactly like? expressive certainly, there. use Odessa babes are really easy to get experienced with. could possibly belief that can little girls on Ukrashould bee undereducated. antique kinds appreciates are robust in Ukraine so that the ladies are learned to stay brilliant so girlie off their my child years. considering the fact that in the end you haven’t found your company's attractive Ukrainian female to assist you to get married - woohoo to participate and create our working together for use in your joy and happiness! if you find that the girls is really interesting for you personally, don't wait too long and seek to meet some other in the brick and mortar venues. but we get the list of absolute best streets or cities to reach Ukrainian men and women available for you personally.
RSS feed for comments to this post