8/12/2019 Peoplesoft Row Level Security Example
1. User, by using the OPRID field.
Query Security:
Relationship between row-level security and Query security record definitions:
2. Primary permission list, by using the OPRCLASS field.
Sep 29, 2018 Bija Ime - Episodi 2 - Pjesa 2. Bija Ime - Episodi 1. Seriale me titra shqip2. Deshira Haxhi - O bija ime (Official Song) ElrodiMusicOfficial. What Does The USWNT Need To Improve. Bija Ime - Episodi 47 me Titra Shqip. Disclaimer: This site does not store any files on its server! All contents are provided by non-affiliated third parties! Oct 04, 2018 Watch Bija Ime - Episodi 4 - video dailymotion - Seriale on dailymotion. For You Explore. Do you want to remove all your recent searches? Fidaim Aliu - Bija ime (Official Video HD) ArkivaShqip. Labinot Rexha & Orkestra Tirona Folk - Bija ime. May 09, 2019 Bija ime; Vajzat e Diellit; Home. Kuzgun – Episodi 47! Kuzgun – Episodi 47! Korbi Kuzgun Kuzgun Episodi 12 pjesa 4 Kuzgun episodi 47 Kuzgun episodi 47 me perkthim shqip Kuzgun episodi 47 me titra shqip Kuzgun episodi 47 shqip Kuzgun me perkthim shqip Kuzgun me titra shqip seriale shqip seriale turke seriali Kuzgun tvseriale. Bija ime episodi 47 free.
3. Row security permission list, by using the ROWSECCLASS field
1. Create a view depending on your security need.
Example: create a view which has oprid, rowsecclass and the parent key field and in sql editor write the following sql:
SELECTDISTINCTopr.oprid,
opr.rowsecclass, dtl.t_cust_idFROMpsoprdefnopr, ps_t_sjt_classcls, ps_t_sjt_class_dtldtlWHEREcls.rowsecclass=dtl.rowsecclass ANDcls.t_cust_id=dtl.t_cust_id ANDopr.rowsecclass=cls.rowsecclass
2. Attach the above view to the record in record properties (query security record).
step 7.1
Step 7.2
Step 7.3
NOTE: You should consider adding record definitions to the query trees in a hierarchy that matches the parent/child relationship of records in your database.
In my previous posting, Row Level Security, I showed how you could create your own custom security access type. In this post I want to show how you can automate the maintenance of this new Access Type. To recap our dilemma, we needed away to secure associates row level data for our regional HR Team members. The requirement was to allow them to see all associates within their region, but not their own department. This type of security is usually done via the department security tree. However, we have departments that cross regions and we needed to prevent these regional HR Team members from viewing job data for people not in there region. This is when we came up with row level security by Department/Location. How do we maintain this new security access type? Row level access for our new Access Type is controlled by the SJT_CLASS_ALL table. You can manually enter access into this table by using the Security By Permission List Component under, Main Menu>>Set Up HRMS>>Security>>Core Row Level Security>>Security by Permission List. However, maintaining this type of data could be a nightmare. So lets build an Application Engine that would allow us to control this configuration using a query associated to a Row Level Permission List. Think of it as Dynamic Row Level Security! Build a simple record and page to associate your Query to your Permission List. Record to associate Permission List to Query Page for Online Configuration: Then write an application Engine to process your query and store the results in the SJT_CLASS and SJT_CLASS_ALL. Your Query needs to return distinct combinations of your custom Access Type, in our case this is Business Unit, Deptid and Location. In the Example Above, I created a simple query from the Job Record and Location Table, where the location state is Colorado and the Department is not an HR Department. It might be more beneficial to create a view from these tables and secure the view using the Query Security Tree and bypass the row level security. This is important, if you are going to run this process in batch mode, then your batch id will need to have access to your query and row level security to all job rows in order to process the query correctly. Now that I have my query, I can associate it to a Row Level permission list and write a simple Application Engine to Loop through all my dynamic row level configurations and generate my access. Here is the Application Engine: Create a custom version of the SJT_CLASS to store your process results, prior to inserting into the delivered SJT tables. Step 1 Delete from PS_ZZ_SJT_CLASS Step 2- Peoplecode Local Rowset &rowLevel_RS; Local ApiObject &aRunQry; Local Rowset &aRowSet; Local Record &aQryPromptRec; Peoplesoft Field Level SecurityLocal number &count, &x, &r;Local string &VALID; Rem ZZ_ROW_LVL_SEC contains the relationship between the permission list and Query; &rowLevel_RS = CreateRowset(Record.ZZ_ROW_LVL_SEC); &count = &rowLevel_RS.Fill(); For &x = 1 To &count &aRunQry = %Session.GetQuery(); If (&aRunQry.Open(&rowLevel_RS(&x).GetRecord(1).QRYNAME.Value, False, False) <> 0) Then Error ('Error in opening query'); Else SQLExec('select 'FALSE' from psqryfield where qryname = :1 AND columnnum <=3 and ((columnnum = 1 AND FIELDNAME <> 'BUSINESS_UNIT') OR(columnnum = 2 AND FIELDNAME <> 'DEPTID') OR (columnnum = 3 AND FIELDNAME <> 'LOCATION'))',&rowLevel_RS(&x).GetRecord(1).QRYNAME.Value, &VALID); If &VALID = 'FALSE' Then Error ('Invalid Query ' | &rowLevel_RS(&x).GetRecord(1).QRYNAME.Value); Else &aQryPromptRec = &aRunQry.PromptRecord; &aRowSet = &aRunQry.RunToRowset(&aQryPromptRec, 0); For &r = 1 To &aRowSet.RowCount If All(&aRowSet(&r).GetRecord(1).GetField(1).Value) Then SQLExec('insert into ps_zz_sjt_class values (:1,'PPLJOB','041',:2,:3,:4)', &rowLevel_RS(&x).GetRecord(1).CLASSID.Value, &aRowSet(&r).GetRecord(1).GetField(1).Value, &aRowSet(&r).GetRecord(1).GetField(2).Value, &aRowSet(&r).GetRecord(1).GetField(3).Value); End-If; End-For; End-If; End-If; End-For; SQLExec('DELETE FROM PS_SJT_CLASS WHERE CLASSID IN ( SELECT CLASSID FROM PS_ZZ_ROW_LVL_SEC) AND SCRTY_SET_CD = 'PPLJOB' AND SCRTY_TYPE_CD = '041'); SQLExec('DELETE FROM PS_SJT_CLASS_ALL WHERE CLASSID IN ( SELECT CLASSID FROM PS_ZZ_ROW_LVL_SEC) AND SCRTY_SET_CD = 'PPLJOB' AND SCRTY_TYPE_CD = '041'); SQLExec('INSERT INTO PS_SJT_CLASS SELECT * FROM PS_ZZ_SJT_CLASS where SCRTY_SET_CD = 'PPLJOB' AND SCRTY_TYPE_CD = '041'); SQLExec('Insert into PS_SJT_CLASS_ALL (CLASSID ,SCRTY_SET_CD, SCRTY_TYPE_CD, SCRTY_KEY1, SCRTY_KEY2, SCRTY_KEY3, TREE) select CLASSID ,SCRTY_SET_CD, SCRTY_TYPE_CD, SCRTY_KEY1, SCRTY_KEY2, SCRTY_KEY3, 'N' from PS_ZZ_SJT_CLASS Where SCRTY_SET_CD = 'PPLJOB' AND SCRTY_TYPE_CD = '041'); The code above loops through the configuration table we created, then opens the query and validates that the first three rows are Business Unit, Deptid and Location then it runs the query to a rowset. Inserting the query results into our custom ZZ_SJT_CLASS table. After we have processed all the queries in the configuration, the process deletes all the current configuration for the permission lists being processed from the Delivered SJT tables and inserts the results of our processing into the SJT_CLASS AND SJT_CLASS_ALL. Access is granted.
If an organization we are working with is using PeopleSoft as it's ERP solution thendepending upon what role we play we do online transactions through various components in PeopleSoft. For example, HR users usually work in Production environment hence use components under Workforce Administration, Organizational Development, Workforce Development, Benefits etc… in order to carry out their daily business.
A Consultant (Technical or Functional) however will mostly works in Non-Production environment hence the components under the menus – Workforce Administration, Set Up HRMS, PeopleTools etc… are the ones where they visits most often to do transactions as required. A consultant at times needs to play various roles in order to expedite certain tasks part of their assignment. For example:
Etc… Please also see Simplified Way to Provide a Page Access in PeopleSoft Understanding Dynamic prompts in PeopleSoft Implementing parallel processing using Application Engine in PeopleSoft Adding and Maintaining Person Of Interest in PeopleSoft PeopleSoft Set Control Field Peoplesoft Row Level Security Search Records SQL Query for Max Effective (MAX (EFFDT) dated row from JOB table PeopleSoft Set Control Field Concept and Tableset Sharing Understanding Future dated security in PeopleSoft PeopleSoft 9.1 Person Model Creating Query Report with PS Query in PeopleSoft PeopleSoft HRMS Online Training
If we just forget about the role that one plays and list out few most common components a user deals withto carry out various transactions then the list will contain:
·JOB_DATA (Navigation: Main > Workforce Administration > Job Information > Job Data)
·JOB_DATA_CURRENT (Navigation: Main > Workforce Administration > Job Information > Current Job)
·ADD_PER_ORG_ASGN(Navigation: Main > Workforce Administration > Job Information > Add Additional Assignment)
·ADD_HOST_ASSIGN(Navigation: Main > Workforce Administration > Global Assignments > Track Assignment > Add a Host Assignment)
Transactions on Personal data
·PERSONAL_DATA (Navigation: Main > Workforce Administration >Personal Information > Modify a Person)
·DEPEND_BENEF (Navigation: Main > Workforce Administration >Personal Information > Personal Relationships >Dependent Information)
·EMERGENCY_CONTACT (Navigation: Main > Workforce Administration >Personal Information > Personal Relationships > Emergency Contact)
·DISABILITY(Navigation: Main > Workforce Administration >Personal Information > Disabilities)
Hold on. Why am I explaining all this when our topic here is “Row Level Security Search Records in PeopleSoft”?
Well, because all the components mentioned above, use security views as their search record and that is to ensure that a particular user who is doing transactions through these components, can see only those employees that they are allowed to see.I would like to list down those security views along with the components where they are used as search record:
·Current Job (JOB_DATA_CURRENT ) - EMPLMT_SRCH_COR
Peoplesoft Row Level Security Example 1
·Add Additional Assignment (ADD_PER_ORG_ASGN ) - PERS_SRCH_GBL
·Add a Host Assignment (ADD_HOST_ASSIGN ) - PERS_SRCH_GBL
Components for Personal data transactions
·Dependent Information (DEPEND_BENEF) - PERS_SRCH_GBL
·Emergency Contact (EMERGENCY_CONTACT) - PERS_SRCH_ALL
Etc…
As you can see, there are various security views created and are used in these components. Based on the component requirement, some of these views search for the employee data rows by EMPLID and EMPL_RCD and others just by the EMPLID.
For example, The view EMPLMT_SRCH_GBL used in Job data component considers EMPL_RCD field while searching the data row when a user hits on the “Search” button in the component search page whereas the view PERS_SRCH_ALL used in Personal Data component doesn’t consider EMPL_RCD filed while searching the data rows.
One easy way to find out the search record for a component, Just open the menu definition under which the component exists, in the Application Designer and locate your component, then right click on it and open the “Menu Item Properties”. Check what the search record for the component is and whether it has been overridden.
So, when the so called “Row Level Security Search Records” come in picture and how they secure the data from a user (HR Admin, Consultant etc…). This is also called Row Level Security in PeopleSoft.
Let’s take any one of the component mentioned earlier. A new employee “ST01ST20” has been hired in the organization but when HR tries to open their job data through JOB_DATA component:
Oops.! looks like employee doesn’t exist.
Is that what it looks like?No, instead the HR user is not able to see the newly hired employee because they don't have access to them and this restricted access has been enforced with the use of the row level security view EMPLMT_SRCH_GBL as search record in this component. Please also see 'Override Position Data' and 'Use Position Data' Option in Job Data Page Hiring a Person in PeopleSoft How to find the List of Users Assigned to a Role How the Various Date Fields on Employment Data Page are Updated Simplified Way to Provide a Page Access in PeopleSoft How to Resolve a Row Level Security Issue in PeopleSoft Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft SQL Query to Find Direct Reports for a Manager in PeopleSoft Understanding Component Interface in PeopleSoft How the FTE is Calculated in PeopleSoft Business Unit, Company and Regulatory Region in PeopleSoft So let’s find out what exactly happens behind the scene and how the security search view works to ensure the unintended employee data shouldn’t be displayed to HR user.
In the search page, when we enter the EMPLID ‘ST01ST20’ and hit enter:
Hope everything is clear so far and if it is then let’s give data access to the HR user so that they can see the data rows of employee ST01ST20. Thereare various different ways such access can be given and for now we will just add business unit “AUS01” to the Permission list TEST_PERMSN (Navigation: Main > Set Up HRMS > Security > Core Row Level Security > Security By Permission list).
After the access is given to HR user let’s see what happens: Simplified Way to Provide a Page Access in PeopleSoft PeopleSoft Set Control Field Adding and Maintaining Person Of Interest in PeopleSoft Unable to See Future dated transactions in Job Data page in PeopleSoft Hiring a Person in PeopleSoft Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft Understanding Component Interface in PeopleSoft SQL Query to Find Direct Reports for a Manager in PeopleSoft How to find the List of Users Assigned to a Role How the FTE is Calculated in PeopleSoft Understanding Future dated security in PeopleSoft How to Resolve a Row Level Security Issue in PeopleSoft Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft 'Override Position Data' and 'Use Position Data' Option in Job Data Page Peoplesoft Row Level Security ExamplesHow the Various Date Fields on Employment Data Page are Updated Process Security in PeopleSoft
Summary:
How can I implement row level security in Peoplesoft CRM v8.8?
Answer
Here is a note from Customer connection.
Summary:
How to implement Row-Level Security by operator ID Details: Resolution #15409: How to implement Row-Level Security by operator ID
You can design special types of SQL views -security views- to control access to individual rows of data stored within your application database tables. Your PeopleSoft applications are delivered with built-in, row-level security functions, tailored to that specific application.
For example, in PeopleSoft HRMS, we provide security tables that enable you to restrict operator access to employee rows according to organizational roles, or to permit an operator to view and update rows for employees in their department only.
Then to get the security down to the operator ID level instead of the operator class level, there are steps defined in Designing Your PeopleSoft Human Resources System manual under Using Operator IDs for Employee Data Security.
Changing operator class to operator ID
Steps to implement Organizational Security by OPRID:
1) Delete all entries for each OPERClass/OPERID in Security Tables.
2) Run SECVWTRE.WT to load the SQL text for the security views(only needs to be performed if organizational security has been turned off). 3) Run SECBYOPR.WT to change the view text of Oprdefn to select based on Oprtype of 0 (instead of 1 for operator class security. 4) Add the field OPRID in place of the OPRCLASS field in the search views and make it a key item. 5) Recreate the search views and the Oprdefn view. 6) After the departments have been loaded, create a tree with effective date the same as the effective date for the departments equating the node with the department ID. 7) Run PER505 to update the effective date of PS_R_PER505. 8) Update the security tables to show which departments each operator or operator class has authorization to read/write. 9) Run PER505 if any changes are made to the tree structure. This will update the security tables established in 6 to reflect the changes to the hierarchy.
To switch from Operator Class to Operator ID security:
1) If you have department security in place for an OprID or OprClass delete these entries now, they will not be available after updating the Oprdefn view and may give inaccurate results if left in place.
2) Run SECBYOPR.WT to change the view text of Oprdefn to select based on an Oprtype of 0. (To switch back to Operator Class security run SECBYCLS.WT to switch the view text of Oprdefn to select based on an Oprtype of 1.) 3) Recreate the Oprdefn view. 4) Change each search view and replace the OPRCLASS field with OPRID field and make OPRID a key field. 5) Set up the tree and department security as in the steps 5 through 8 under Operator ID security setup.
Load ISO With Emulator. Ultraman fighting evolution 3 download. Fight monster and battle other ultraman and unleash your maximum energy to defeat your enemiesCover ArtHow to?. Extract RAR. Download Emulator.
Change Standard Search Record into OPRDEFN_DEPT. The view text in
OPRDEFN_DEPT should look like this: SELECT A.OPRID ,A.EMPLID FROM PSOPRDEFN A
WHERE A.OPRTYPE=0
You must rebuild this view.
Documentation for this is located in Designing your Human Resource System (Chapter 4).
*Questions excerpted from ITToolBox.com* Disclaimer: Contents are not reviewed for correctness and are not endorsed or recommended by PeopleSoft-Planet.com. Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |