Peoplesoft Row Level Security Example
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.
By default, when yougive Query users access to a record definition, they have access toall the rows of data in the table built using the associated recorddefinition. In some cases, though, you want to restrict users fromseeing some of those data rows. For example, you might not want yourhuman resources staff to have access to compensation data for vicepresidents or above. In other words, you want to enforce row-level security, (also called data permission security) which is offered by manyPeopleSoft applications.
This section describesthe relationship between row-level security and Query security recorddefinitions.
Row-Level Security
With row-level security, users can have access to a table without having access to all rowson that table. This type of security is typically applied to tablesthat hold sensitive data. For example, you might want users to beable to review personal data for employees in their own department,but not for people in other departments. You would give everyone accessto the PERSONAL_DATA table, but would enforce row-level security sothat they could only see rows where the DEPTID matches their own.
PeopleSoft applicationsimplement row-level security by using a SQL view that joins the datatable with an authorization table. When a user searches for data inthe data table, the system performs a related record join betweenthe view and the base table rather than searching the table directly.The view adds a security check to the search, based on the criteriayou’ve set up for row-level security. For example, to restrict usersto seeing data from their own department, the view would select fromthe underlying table just those rows where the DEPTID matches theuser’s DEPTID.
Query Security RecordDefinitions
You implement row-levelsecurity by having Query search for data using a query security recorddefinition. The query security record definition adds a security checkto the search.
Query security recorddefinitions serve the same purpose as search record definitions dofor panels. Just as a panel’s search record definition determineswhat data the user can display in the panel, the query security recorddefinition determines what data the user can display with Query.
To get Query to retrievedata by joining a security record definition to the base table, youspecify the appropriate Query Security Record when you create thebase table’s record definition.
To apply row level security:
Drag and Drop apps to completely delete an app with its files in no time. Advanced uninstaller pro mac.
In Application Designer,open the record on which you want to apply row-level security.
With the record definitionopen in Application Designer, click the Properties button,and select the Use tab from the Record Properties dialog box.
Note: You use this dialogbox to set a number of different aspects of the record definition.The only item related to Query security is Query Security Record list box.
Select the security recorddefinition (usually a view) in the Query Security Record list box.
Each PeopleSoft productline comes with a set of views for implementing its standard row-levelsecurity options. See the product documentation for details.
Note: The Parent Record list box is also relevant to Query. It identifies a record definitionthat is the current definition’s parent, meaning that it holds relateddata and that its keys are a subset of the current record definition’skeys. If you designate a parent record, Query automatically knowswhat fields to use when you join these two tables for a query.
Typically, the QuerySecurity Record definition you’ll want to select is the same one youuse as the search record definition for the panel that manages thistable. If you’re enforcing one of the standard row-level securityoptions from a PeopleSoft application, select the PeopleSoft-suppliedsecurity view for that option. See the application documentation fora list of the available views. If you’ve designed your own securityscheme, select a record definition that appropriately restricts therows a query will return.
Once you’ve set the querysecurity record definition, click OK to closethe Record Properties dialog box, then save the record definition.
If you’ve already usedSQL Create to build the table or view from this record definition,you don’t need to rebuild it.
Row-Level (DataPermission) Security Views
Using PeopleSoft row-levelsecurity views enables you to restrict users from seeing certain rowsof data. You can restrict data by:
User, by using the OPRIDfield.
Primary permission list,by using the OPRCLASS field.
Row security permissionlist, by using the ROWSECCLASS field.
To implement row-levelsecurity through a security view:
In Application Designer,insert one of the three row-level security fields (OPRID, OPRCLASS,ROWSECCLASS) into the record definition.
Configure the field asa Key, but not a List Box Item.
Save the record and buildthe view.
Use the record as the searchrecord or query security record.
Now, when the user searches,the system dynamically adds a WHERE clause — that incorporates thesecurity field — to the search SELECT statement. The value of thesecurity field is based on the current user.