Query Security

 

Query Security:

 

• PeopleSoft Query uses query access group trees to control the access of the tables in the PeopleSoft database.
• You create and update query access group trees using Query Access Manager.
• You should create query access group trees based on your organization’s needs and on any customizations you’ve made.

 

Relationship between row-level security and Query security record definitions:

 

• PeopleSoft applications implement row-level security by using a SQL view that joins the data table with an authorization table.

 

• To apply row level security:
• Open the record on which you want to apply row-level security. Click the Properties button, and select the Use tab from the Record Properties dialog box.
• Select the security record definition (usually a view) in the Query Security Record list box.

 

• Row-Level (Data Permission) Security Views:

1. Using PeopleSoft row-level security views enables you to restrict users from seeing certain rows of data.
2. You can restrict data by:

1. User, by using the OPRID field.

2. Primary permission list, by using the OPRCLASS field.

3. Row security permission list, by using the ROWSECCLASS field

3.             3.   To implement row-level security through a security view:
1. In Application Designer, insert one of the three row-level security fields(OPRID, OPRCLASS, ROWSECCLASS) into the record definition.
2. Configure the field as a Key, but not a List Box Item.
3. Save the record and build the view.
4. Use the record as the search record or query security record.

• Steps for creating query security record:

          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:

 

SELECT DISTINCT opr.oprid,
                opr.rowsecclass,
                dtl.t_cust_id FROM   psoprdefn opr,
       ps_t_sjt_class cls,
       ps_t_sjt_class_dtl dtl WHERE  cls.rowsecclass = dtl.rowsecclass
       AND cls.t_cust_id = dtl.t_cust_id
       AND opr.rowsecclass = cls.rowsecclass 

 

2. Attach the above view to the record in record properties (query security record).

 

3.            3. Grant security to that record to which the query security view is attached.
4.            4. In PIA, traverse to query access manager (Oracle PS Tools ->people tools –> Query  Security -. Query Access Manager). Then CLICK ON CREATE NEW TREE.

 

 

5. 5. If access group is already present, then search for that access group in the prompt.
6. 6. If you want create a new access group then enter the access group name and PRESS ENTER, it will be redirected to the below page where you can create your own access group.       
   
7. 7. Then insert the child records to the access group.

 

step 7.1

Step 7.2   

Step 7.3

 

8. 8. Go to permission list and traverse to query. (People Tools -> Security -> Permission & Roles -> Permission list (Select the permission list)).

 

9. 9. In access group permissions, assign the tree name and access group.

 

 

10. 10. In query manager, add the record to which the query security view is attached.

 

 

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.