What this SQL Statement means

[Dukester0122]

Our external auditors gave us audit documents but they said its not updated to show tables for our ERP system. I know these are PeopleSoft tables because I've work with the system a few years back.

Somebody tell me what results I you get if ran, you don't need to get too detailed because I just want to update them and show the same statement on our current ERP system:

1) SELECT B.OPRID, B.OPRCLASS, A.MENUNAME, A.BARNAME, A.BARITEMNAME, A.PNLITEMNAME, A.AUTHORIZEDACTIONS, A.DISPLAYONLY
FROM PSAUTHITEM A, PSOPRCLS B
WHERE A.OPRID = B.OPRCLASS

2) SELECT A.OPRID, A.OBJGROUPID, A.DISPLAYONLY
FROM PSOPROBJ A

3)SELECT A.OBJGROUPID, ENTTYPE, ENTNAME
FROM PSOBJGROUP

4)SELECT A.OPRID, A.MENUNAME, A.BARNAME, A.BARITEMNAME, A.PNLITEMNAME, A.DISPLAYONLY, A.AUTHORIZEDACTIONS
FROM PSAUTHITEM A
WHERE A.MENUNAME = PROCESS_SCHEDULER

5)SELECT A.OPRID, A.SRVRDESTFILE, A.SRVRDESTPRNT, A.CLIENTDESTFILE, A.CLIENTDESTPRNT, A.DISABLEREFRESH, A.REFRESHRATE, A.LOADMONITOR, A.PRCSNOTIFY, A.NOTIFYAUDIBLE, A.OVRDOUTDEST, A.OVRDSRVRPARMS, A.RQSTSTATUSUPD, A.RQSTSTATUSVIEW, A.SRVRSTATUSUPD, A.SRVRSTATUSVIEW, A.RECURUPD
FROM PSPRCSPRFL A

6)SELECT A.OPRID, A.DAYOF WEEL, A.STARTTIME, A.ENDTIME FROM PSAUTHSIGNON A

7)SELECT A.OPRID, A.QRY_RUN_ONLY, A.QRY_CREATE_PUBLIC, A.QRY_CREATE_WFLOW, A.QRY_MAX_FETCH, A.QRY_MAX_RUN, A.QRY_ADV_DISTINCT, A.QRY_ADV_ANY_JOIN, A.QRY_ADV_SUBQUERY, A.QRY_ADV_UNION, A.QRY_ADV_EXPR, A.QRY_MAX_JOINS, A.QRY_MAX_IN_TREE, A.QRY_OUT_LISTBOX, A.QRY_OUT_NVISION, A.QRY_OUT_CRYSTAL FROM PS_SCRTY_QUERY A

 

[notadba]

Hi Dukester,

These select statemetns are mostly about the security definitions within the application.

1. Gives you the menu path down to page/panel and the authorised actions available for an operator class/permission list. This will only bring back those classes that have been assigned to users - not those that are unused at present.

2. Gives the object security group permissions for a class/permission list.

3. Gives the list of objects that are included within the object security group (eg a group will include certain tables. trees, tree branches, queries etc)

4. Similar to (1)- but gives all classes and their permissions - but only those that have access to anything under the process scheduler menu path.

5. Gives the process profile for permissions for all classes that have one defined. ie where proceses can be run, recurrence definitions, default output, able to view/update/delete other user's processes etc.

6. Gives the signon times for each class - ie day and times that the class has the ability to sign on to the system.

7. Gives the security setup for a class for query. These setting allow for limiting the number of table joins, types of queries that a user can create, if a user can create or only run pre-defined queries etc.

This is probably a little bit brief - your best bet to get a better understanding is to run the selects and have a look at some of the data that is returned.

Hope this helps.