PeopleSoft Security: Row Level Security Part 1

One of the topics which needs attention in PeopleSoft Security is Row level Security.
In Organizations, we have different people occupying different roles and each role
has its own responsibilities.
A Single person may handle two roles which he needs to perform in the organization , say Employee role and Manager role etc. Through Row level security data is secured such that it will be viewed by only authorized users.
 
Let's consider a college which offers courses in different Domain. To make it more simple lets consider there is Computer Science Department and Information Technology Department and Civil Department.

And below are the entities
Students
Assistants Professors
Professors
Head of the Department

As we know there are certain subjects which are common between Computer Science Department and IT Department, same professor can teach same subject for both of the Departments. Some Professors only teach CSE or only teach IT.

Let's say Professor A teaches CSE and IT Departments
Professor B teaches only CSE Department.

From the above scenario,  Professor B can access the records of students only from CSE Department and Professor A can access records of students from both CSE and IT Departments. This is nothing but making data available to authorized users by encapsulating unwanted data. The term we use in PeopleSoft for this is 'Row Level Security' where each entry in the database table is considered as a row of data.

If we are familiar with Set Theory in Mathematics, its the same concept used here.  Below is the diagram which shows the level of access each Professor has to the data in the system.

As of now we have just seen the conceptual part. Let's see how to setup the data level security in PeopleSoft with an Example in the next post.

PeopleSoft Security Overview

Before going into the details of PeopleSoft Security, let's see where all in the PeopleSoft application we come across security related stuff .

From the user's perspective:

1. To access application through PIA , one must have an UserID to login to the application.

2. Once logged in, we certainly have to do some tasks, like an Employee can go and apply for leave or see his performance data or view paychecks etc. That means we require access to certain pages in the application.

3. Once Employee access the pages there might be certain fields which need to be hidden .

4. Also if the Employee has a role of Manager, and if the Manager wants to know the list of resources available from his department so that he/she can assign them to different projects in such a case the search results should show only the Resources under his department which is nothing but access restriction at data level.

5. If user wants to run a job then a process profile is required so that access is given to run only those jobs.

6. Suppose user wants to run a query through PIA, then the same can be achieved through Query Manager. To build a query , a table is required. To restrict user to build queries on only certain tables you need to assign query access profile which controls user to access certain tables.

7. Also if the Client has more than one PeopleSoft application and user wants to connect to other application without providing credentials again , this can be achieved through Single Sign on.
8. If we want user not to update any data on the page and just view it then we can set the same at the Page Permissions.

Now lets dig further and see what restrictions are there at Developer level :
1. Developers login to the tool called App Designer to build applications. If we want developers to restrict that certain objects like CI etc then we assign security at Definition level.

At PSAdmin Level:
1. The Webserver and Appserver config files will have information related to security which is used in validation of the requests that come browser and then give access to valid users.

2. Not everyone will have access to webserver and App Server. These tasks are handeled by PeopleSoft Admins (PS Admin)

This post is intended to give an Overview of  PeopleSoft Security.

BI Publisher: Basics in creating a RTF template

BI Publisher in PeopleSoft helps us to view output in different formats.
We use templates to design the output layouts. Let's see a sample example below.

Data Preparation:
I had an XML file which has Band and Description.
There are 20 rows of data. Now lets load this data into the template.

                                           

Loading Data into the Template:           

Open the MS Window and Go to Add-ins tab and click on Data-->Load XML Data. 

Designing the Template:

After that drag the fields from the Field Brower onto the document as shown below.

Below is the final draft of the document which has Band and Description.

Now Click on Preview to view the Output. In the Output our expectation is to view all 20 rows but to our surprise there is only row which comes up. This is because we have not looped through all the rows of XML.

To loop through all the rows, we need to add the for-each tag. Now let's add this tag and view the output.
Tag used in this example: for-each:@row_Band

Now lets see the preview below. As expected now we get all rows in the output.

This completes the sample example of designing a report layout using RTF template.
We will see in next post on how to place headers in the template.

SQR: BEGIN-SELECT to get Count value

In SQR, we use below select construct to retrieve data from database.

BEGIN-SELECT
COL1
COL2
.
.
FROM TABLE_NAME
WHERE CRITERIA
END-SELECT

Now I need count of EE working in Department '10'. Below is the code to achieve this.

BEGIN-SELECT
COUNT(EMPLID) &Count_EE
FROM EE_DEPT_TBL
WHERE DEPARTMENT_ID = 10
END-SELECT

Sometimes, by mistake we might use #Count_EE as column variable since the value of Count is numeric. So coming to basics, all column variables will be referred with & (Ampersand).

PeopleSoft Migration Queries

Most of us have come across the term Migration, where we migrate objects developed from one environment to other. Below are few queries related to Migration issues.


Migration Queries: BI Publisher

Scenario: There is a change done to the RTF template and this new template should be migrated to other environment. What are things to keep in mind while creating a project.
Most of us have come across the term Migration, where we migrate objects developed from one environment to other. Below are few queries related to Migration issues.

You need to include below objects in the project.
BI File Definition
BI Template Definition

BI Report Definition.

Note: If the template is using a new XML file as template file, then it is mandatory to have the file ID associated with the Template included in the project. If this is not done and migrated only Template and Report Defn, then you will end up with a weird error message while opening the report defn through PIA. This is because the FileID used for template does  not exist in target database.

Migration Queries: App Engine.
Scenario: Changes are done only to few step properties in a section. Now these steps should be migrated to other envrionment.

You need to include below objects in the project.
Section
Steps for which change is done.

Migration Queries: Record Properties. Adding AUDIT record.

There was Audit Record added to the Record at Record Properties and after migration through STAT the change is not getting reflected in the target environment.

To resolve this issue, in the project Compare options, there will be option to Keep Target changes flag for Audit. It should be unchecked so that the change will take place in the target environment.