PeopleSoft Query Security - Identify the Permission List associated with a Query Tree Table.

A very common issue faced by IT/Functional folks right after go live - Identify why a user can access a table in a query and another cannot. PeopleSoft handles Query Security using Query Trees & Access Groups within the trees. There are multiple blogs which talk about the process of setting up trees, access groups and then assigning them to the users; so I'll straightaway go to the issue what we faced today.

Issue : User 1 has access to a particular table via PS query and User 2 does not.

Step 1 - Ran the below sql to identify the permission lists associated with the table :

SELECT a.classid AS PERMISSION_LIST, A.RECNAME AS RECORD_NAME

  FROM PSQRYACCLSTRECS A

 WHERE A.recname = 'SPECIFY TABLE_NAME_HERE';

Step 2 - Using the permission list/s retrieved from step 1, identify the role/s which hold the permission list for USER 1.

SELECT DISTINCT A.rolename AS ROLE_TO_BE_ASSIGNED
  FROM PSROLEUSER A, PSROLECLASS B
 WHERE     A.ROLENAME = B.ROLENAME
       AND B.CLASSID = 'SPECIFY PERM_LIST FROM STEP 1 HERE'
       AND A.ROLEUSER = 'SPECIFY USER WHO HAS THE ACCESS TO THE TABLE';

Step 3 - Add the role/s identified in step 2 to the USER 2 (who cannot access the table).

P.S - You may certainly use your sql skills to combine the 2 sql's above into one.

Adding roles of a certain user to your Id

One of the easy ways of giving yourself all the roles/accesses of a particular user; no doubts you need to have "insert" privileges on the database where this is desired :

INSERT INTO PSROLEUSER
(SELECT 'YOUR_USER_ID', B.ROLENAME, 'N'
FROM PSROLEUSER B
WHERE B.ROLEUSER = 'USER_WHOSE_ACCESSES_ARE_REQUIRED'
AND NOT EXISTS
(SELECT 'Y'
FROM PSROLEUSER C
WHERE C.ROLEUSER = 'YOUR_USER_ID' AND C.ROLENAME = B.ROLENAME));

This sql will add all the roles of the user whose accesses are required to your id.

SQL to determine the Permission Lists associated with a component

SELECT a.classid as Permission_List,
b.menuname,
b.barname,
b.baritemname AS componentname,
b.pnlitemname AS pagename,
c.pageaccessdescr,
DECODE (b.displayonly, 0, 'No', 1, 'Yes') AS displayonly
FROM psclassdefn a, psauthitem b, pspgeaccessdesc c
WHERE a.classid = b.classid
AND b.baritemname = 'SPECIFY_COMPONENT_HERE'
AND b.authorizedactions = c.authorizedactions;

SQl to list pages in a Permission List

The below sql will list all the menus/components/pages & the kind of accesses (Update/Display) in a permission list...

SELECT b.menuname, b.barname, b.baritemname AS componentname, b.pnlitemname AS pagename,
c.pageaccessdescr,
DECODE (b.displayonly, 0, 'No', 1, 'Yes') AS displayonly
FROM psclassdefn a, psauthitem b, pspgeaccessdesc c
WHERE a.classid = b.classid
AND a.classid = 'PERMISSIONLISTNAME'
AND b.baritemname > ' '
AND b.authorizedactions = c.authorizedactions;

Creating an Oracle Sequence in SQL

A sequence can be created using the following command:
CREATE sequence my_seq start WITH 1;

Select from dual to increment your sequence number by one.
SELECT my_seq.NEXTVAL FROM dual;

What if you want to create a sequence that increments by 3?
SQL> CREATE sequence seq_3 start WITH 1 increment BY 3;
Sequence created.
SQL> SELECT seq_3.NEXTVAL FROM dual;
NEXTVAL
----------
1
SQL> SELECT seq_3.NEXTVAL FROM dual;
NEXTVAL
----------
4
SQL> SELECT seq_3.NEXTVAL FROM dual;
NEXTVAL
----------
7

PeopleSoft PeopleTools Meta-Tables

Below are most of the PeopleSoft PeopleTools Meta-Tables. I save a lot of time by using these & I'm sure you will too. Have categorized them to make life easy.

1. PeopleSoft Projects

PSPROJECTDEFN : Stores details on projects created in Application Designer.
E.g. SQL :
SELECT * FROM PSPROJECTDEFNWHERE PROJECTNAME = 'Your_Project_name';

PSPROJECTITEM : Stores objects inserted into an Application Designer project.
E.g. SQL :
SELECT * FROM PSPROJECTITEMWHERE PROJECTNAME = 'Your_Project_name';

2. Portal Structure

PSPRSMDEFN : Its a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component.

PSPRSMPERM : Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.

3. XLAT Tables

XLATTABLE : Stores translate values (PeopleSoft version prior to 8.4).

PSXLATDEFN : Stores all fields that have Xlat values. This table does not store any Xlat values.

PSXLATITEM : Stores fields with their actual translate values (PeopleSoft version 8.4 and above).

4. Record & Field Tables

PSRECDEFN: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table. The below case explains the record type.
CASE RECTYPE
WHEN 0 THEN 'Table'
WHEN 1 THEN 'View'
WHEN 2 THEN 'Derived'
WHEN 3 THEN 'Sub Record'
WHEN 5 THEN 'Dynamic View'
WHEN 6 THEN 'Query View'
WHEN 7 THEN 'Temporary Table'
ELSE TO_CHAR(RECTYPE)
END CASE

PSRECFIELD: Stores records with all their fields (sub-records are not expanded)

PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)

PSINDEXDEFN: Contains 1 row per index defined for a table.

PSKEYDEFN: Containes 1 row per key field defined for an index.

PSDBFIELD: Stores information about fields. The below case explains the fieldtype.
CASE FIELDTYPE
WHEN 0 THEN 'Character'
WHEN 1 THEN 'Long Character'
WHEN 2 THEN 'Number'
WHEN 3 THEN 'Signed Number'
WHEN 4 THEN 'Date'
WHEN 5 THEN 'Time'
WHEN 6 THEN 'DateTime'
WHEN 8 THEN 'Image'
WHEN 9 THEN 'Image Reference'
ELSE TO_CHAR(FIELDTYPE)
END CASE

5. Process Definition Tables

PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine...), and the component name associated with the process definition.

PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.

6. Message Catalog Tables

PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.

PSMSGCATLANG: language table.
-- Example
SELECT *
FROM PSMSGCATDEFN
WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM <> TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM <>

Previous PeopleSoft message catalog tables:

PS_MESSAGE_CATALOG: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.

MESSAGE_SET_TBL: Message set description table.
-- Example
SELECT * FROM PS_MESSAGE_CATALOG
WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')AND LAST_UPDATE_DTTM <>
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
-- This will return messages that have been updated/added between 2 specific dates.

7. Menu Tables

PSMENUDEFN: Store Menu related information. No related component info on this table.

PSMENUITEM: List the menu with all components attached to it.

8. Component Tables

PSPNLGRPDEFN: Stores component related information only.

PSPNLGROUP: This table will give you information regarding a specific component along with the names of pages attached to it.

9. Pages

PSPNLDEFN: Stores pages definitions.

PSPNLFIELD: Stores all items used by each page definition.

10. Security

PSPRSMPERM: Portal Structure Permissions.

PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.

PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.

PSROLEDEFN: This table stores information about Peoplesoft Role definitions. Users get
permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.

PSROLEUSER: This table stores information about the Users in Peoplesoft and the roles assigned to them.

PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.

PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.

PSOPRCLS: Users/Operator and Perm list mapping Table. This table stores information about PeopleSoft users and the permission lists attached to those users.A User gets these permission lists indirectly through the roles which are attached to the user

11. URL Definitions

PSURLDEFN: Stores URL definitions. Here is the path to create URL definitions in PeopleSoft Root >> PeopleTools >> Utilities >> Administration >> URLs

12. Application Classes

PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.


13. PeopleSoft Query Tables
Though there is not a single table which stores the Query SQL statement, the below tables might help a bit in some analysis.

PSQRYDEFN: Stores query related info.

PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).

PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.

PSQRYEXPR: Stores query expressions.

PSQRYBIND: Stores query bind variables.

PSQRYRECORD: Stores all records used in all aspects of query creation

PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.

PSQRYLINK: Stores the relationships to child queries.

PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)

PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).

14. SQL Objects

PSSQLDEFN: Stores SQL object definitions.

PSSQLDESCR: Stores SQL objects descriptions, and description long.

PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.
-- When SQL type is: 0 = Stand alone SQL objects, 1 = Application engine SQL, 2 = Views SQLs

15. Application Engines

PSAEAPPLDEFN: Table that stores Application Engine program definitions.

PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.

PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.

PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.

PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.

PSAESTEPDEFN: Steps in application engines are stored in this table.

PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.

PSAESTEPMSGDEFN: Application engine message action definition table.

AEREQUESTTBL: Application Engine request table behind the AE run control page.

AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.

16. PeopleCode Tables

PSPCMNAME: PeopleCode Reference table.

PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).

17. Process Request Tables

PSPRCSQUE: This record contains the process request information to run a process request.

PSPRCSRQST: This record contains the process request information to run a process request.

PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.

18. File Layout Definitions
PSFLDDEFN: Header Record for File Layout.

PSFLDSEGDEFN: Stores the segements for each layout.

PSFLDFIELDDEFN: Stores the individual file fields for the segment.

19. Other Useful Tables

PSSTATUS: Stores PeopleSoft information such as PS Tools release version and the UNICODE_ENABLED boolean flag where a value of 1 indicates the DB is to be treated by Tools as a UNICODE DB.

PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted.

PSMAPFIELD: Stores Field mapping of ActivityPS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.

PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.

PeopleSoft Internet Architecture

The PeopleSoft Internet Architecture (PIA) is a server-centric component architecture that enables secure end user access to PeopleSoft applications. Its components include the following:

• Internet Access Device
• Web Server
• Application Server
• Database Server

Each component fulfills a unique niche within the system, all of which are described in the PeopleSoft Internet Architecture Components lesson of this course.

With PIA there is no "traditional" client. Workstations simply need to have a supported browser installed. No other applets or connectivity software is needed on the workstation that runs the browser because all processing occurs at the server level. Dynamic HTML, rendered by the Application Server, is passed to the Web Server and sent on to a supported browser interface.
PeopleSoft Integration Technologies:

•Application Messaging - System-to-system communication.
•Component Interfaces - Transactions from external systems to PeopleSoft.
•Application Engine - Used in batch application processing.
•File Layouts - Used for integration with legacy systems.

PIA supports pure internet access for all PeopleSoft applications. It enables you to take advantage of all of the PeopleSoft intranet and internet solutions, as well as the PeopleSoft Integration Technologies, such as Application Messaging.

These technologies streamline integration of PeopleSoft applications with other PeopleSoft applications, custom internal systems, eMerchants, and customer trading partner systems. By supporting the open flow of information between systems, the PeopleSoft Integration Technologies provide true internet- based system integration.

The PeopleSoft Internet Architecture delivers intuitive, high-performance, HTML-based thin client applications that run on any machine with internet access. PIA deploys all transactions through a web browser.

Benefits of Browser-Based Deployment
•Minimizes the Training Effort
•Reduces Application Deployment Costs
•Lowers Client Hardware Requirements
•Allows Extensive Portability

There are two basic access methods with the PeopleSoft Internet Architecture:
(1) Directly, through a delivered homepage dedicated only to PeopleSoft applications, or
(2) Through a portal that may contain non-PeopleSoft content references.

Both of these deployment options are further discussed in the PeopleSoft Development and Deployment lesson of this course. Both options provide the benefits listed above of browser-based deployment.
Minimizes the Training Effort:
• Simple access
• Intuitive web look and feel

Less training is needed because most people with access to computers are very familiar with the look and feel of web pages, and know how to navigate within web browsers like Yahoo! and Amazon.com. End users just click a hyperlink to enter the PeopleSoft applications.
Reduces Application Deployment Costs:

• HTML-based for low bandwidth access
• HTML and JavaScript deployed to the browser
• No client installations required

Browser-based applications are easily deployed to end users.
By placing a hyperlink in an email or on a corporate website, users can access the applications just like they access any other website.
The cost of deploying browser-based applications is close to zero.
Lowers Client Hardware Requirements:
• Robust, scalable server-centric architecture
• Supports thousands of concurrent users

Because the browser-based applications put very small demands on the client machine, the end user does not need a high-end, expensive computer to use PeopleSoft applications. This means lower costs to customers, as they will not need to upgrade their client machines in order to use the latest PeopleSoft release.
Allows Extensive Portability:
• Web browser independence
• Client operation system independence

HTML browser-based applications are very portable across client operating systems. As long as the end user has a currently supported browser that is JavaScript 1.1 compliant, access to PeopleSoft applications can be through a Windows, Mac, Linux, or Unix client machine. From an end user's perspective, browser-based application deployment of PeopleSoft applications is cost-effective and easy to use.

Because PIA is completely server-based, client machines to this architecture can be nearly any kind of internet-enabled device, including:
• Web browser running on a PC or Macintosh
• Wireless device or cell phone
• External or third-party system

These devices use the standard internet technologies: HTTP, HTML, WML, and XML.A web browser running on a workstation (client) using the HTTP protocol is the most common internet access. The browser does not download any applets nor does it require any plug-ins. Rather, a servlet installed on the Web Server facilitates all browser connections to the Application Server through JOLT.

When the browser sends a request to the Web Server, it is forwarded to the Application Server.The Application Server sends only the following back to the browser:
• HTML
• JavaScript
• Cookies

The client workstation is free of any processing responsibility because there are no PeopleSoft executables on the client. This is why PeopleSoft Internet Architecture is termed an "architecture without a client."With PeopleSoft Internet Architecture, only a single sign-in is needed between PeopleSoft databases. This is possible by leveraging Web Browser cookies that store a unique access token for users when they are initially authenticated. The token in the browser cookie is used to re-authenticate users when they connect to other PeopleSoft systems. This way, a user does not have to go through the sign-in process again. The browser cookie is stored in memory and never written to disk. It is encrypted by the Web Server and check-summed to prevent snooping and tampering.

As you can see, there is no "traditional" client involved in PeopleSoft Internet Architecture. The system sends pure HTML to a supported browser interface, while all processing occurs at the server level.The Web Server must be Java-enabled so that it can run the PeopleSoft-delivered Java Servlets that are installed as part of PeopleSoft Internet Architecture. One of these is the Portal Servlet, which relays all inbound and outbound transaction requests for the browser.Using JOLT, the Web Server communicates browser requests to the Application Server. The pure HTML that the Application Server generates is formatted and presented in the browser by the Portal Servlet.

Together the Web Server and the Application Server make up the middle-tier of PIA; however, the Application Server does most of the work.
The Application Server is the core of PeopleSoft Internet Architecture. It handles messages from the Web Server through JOLT and executes all PeopleSoft business logic. In addition, it maintains the SQL connection to the Database Server for both browser requests and for the PeopleSoft development environment. PeopleSoft uses TUXEDO to manage database transactions.

At execution time, the Application Server fetches the most recent application definitions from the Metadata Repository of the Database Server. The Application Server caches the definitions in memory and executes the business rules, based on the definitions. Definitions such as pages, are created using the Application Designer tool in the PeopleSoft 8 development environment.The Application Server consists of numerous PeopleSoft services and server processes that handle transaction requests. One of these server processes, PSAPPSRV, performs all application processing for a PeopleSoft internet session and generates the HTML to be displayed in the browser. For example, it is the PSAPPSRV process of the Application Server that builds and loads the pages which are then transmitted to the browser, as requested, through the Web Server.

As you can see, the Application Server is truly the heart of PeopleSoft Internet Architecture.Just as in the PeopleSoft three-tier architecture, with the PeopleSoft 8 Internet Architecture, information is stored on the Database Server in three types of tables: System Catalog Tables, PeopleTools Tables, and PeopleSoft Application Data Tables. Each table type contains specific information that is related to running PeopleSoft applications.The PeopleSoft database is the repository for all information that is managed by PeopleSoft applications. Not only is application data stored in the database, but the PeopleSoft metadata is also maintained in the database. Metadata is what drives PeopleSoft Internet Architecture. Because PeopleSoft architectures have always been metadata-driven, PeopleSoft has been able to make the leap from client/server to internet-based applications without having to completely rewrite existing applications.

Several internet-related definitions were enhanced in PeopleTools 8.4 to enable full internet application development. These are the HTML Catalog, images, and style sheets. Just like fields, records, pages, menus, and other definitions, these definitions are stored in the PeopleTools Tables of the Database Server, and are fully upgradeable.

Multiple Application Servers can be connected to a single Database Server, which simultaneously handles the Application Server connections and development environment connections
---------------------------------------------------------------
[** This Article is not created by me.]