PeopleSoft Application Designer and Data Mover access audit SQL

  Today I created this SQL to get all OPRID's with applications designer access (and access type, write or read-only). I also added some other relevant information (account locked, last sign on, etc...).
  I have made it so it only lists one row per user, so it will show only two permission lists:
  ONE of the permission lists that gives the user this application designer access
  ONE of the permission lists that gives the user this data mover access
  If a user has permission lists that give read only and write access, this SQL will show (one of) the write access permission list (even though I think (haven't tested) that the user will have read only, since PeopleSoft tends to prioritize the display-only flag...)

WITH

APP_DESIGNER_ACCESS AS (

                        SELECT PA.CLASSID AS PERMISSION_LIST

                              ,MAX(PA.AUTHORIZEDACTIONS) MAX_ACCESS

                              ,CASE MAX(PA.AUTHORIZEDACTIONS) WHEN 0 THEN 'NO ACCESS'

                                                              WHEN 1 THEN 'READ'

                                                                     ELSE 'WRITE'

                                                              END APP_DESIGNER_ACCESS

                        FROM PSAUTHITEM PA

                        WHERE PA.MENUNAME = 'APPLICATION_DESIGNER'

                        AND   BARNAME    <> 'CHANGE_CONTROL'

                        GROUP BY CLASSID

                       )

,

DATA_MOVER_ACCESS AS (

                      SELECT PA.CLASSID AS PERMISSION_LIST

                            ,'YES'      AS DATA_MOVER_ACCESS

                      FROM PSAUTHITEM PA

                      WHERE PA.MENUNAME = 'DATA_MOVER'

                      GROUP BY CLASSID

                     )

------------------------------------------------------------------------------------------------------------------------------

SELECT OPC.OPRID

      ,OPD.OPRDEFNDESC

      ,OPD.LASTSIGNONDTTM

      ,NVL(TRUNC(SYSDATE) - TRUNC(OPD.LASTSIGNONDTTM),99999)          AS LAST_SIGNON_DAYS_AGO

      ,CASE OPD.ACCTLOCK WHEN 1 THEN 'LOCKED' ELSE 'OPRID ACTIVE' END AS ACCOUNT_STATUS

      ,NVL(OPC.APP_DESIGNER_ACCESS,'NO ACCESS')                       AS APP_DESIGNER_ACCESS

      ,NVL(OPC.DATA_MOVER_ACCESS,'NO')                                AS DATA_MOVER_ACCESS

      ,OPC.APP_DESIGNER_PERM_LIST                                     AS SAMPLE_APP_DESIGNER_PERM_LIST

      ,OPC.DATA_MOVER_PERM_LIST                                       AS SAMPLE_DATA_MOVER_PERM_LIST

FROM (

------------------------------------------------------------------------------------------------------------------------------

      SELECT OC.OPRID

            ,MAX(APPD.APP_DESIGNER_ACCESS)          as APP_DESIGNER_ACCESS

            ,MAX(DMA.DATA_MOVER_ACCESS)             as DATA_MOVER_ACCESS

            ,NVL(MAX(APPD.PERMISSION_LIST), 'NONE') as APP_DESIGNER_PERM_LIST

            ,NVL(MAX(DMA.PERMISSION_LIST) , 'NONE') as DATA_MOVER_PERM_LIST

      FROM PSOPRCLS  OC

          ,(SELECT OC.OPRID

                  ,MAX(AD.PERMISSION_LIST)     AS PERMISSION_LIST

                  ,MAX(AD.APP_DESIGNER_ACCESS) AS APP_DESIGNER_ACCESS

            FROM APP_DESIGNER_ACCESS AD

                ,PSOPRCLS            OC

            WHERE AD.PERMISSION_LIST = OC.OPRCLASS

            AND   AD.MAX_ACCESS      = (SELECT MAX(SAD.MAX_ACCESS)

                                        FROM APP_DESIGNER_ACCESS SAD

                                        WHERE SAD.PERMISSION_LIST = OC.OPRCLASS

                                       )

            GROUP BY OC.OPRID

           ) APPD

          ,(SELECT OC.OPRID

                  ,MAX(DM.PERMISSION_LIST)   AS PERMISSION_LIST

                  ,MAX(DM.DATA_MOVER_ACCESS) AS DATA_MOVER_ACCESS

            FROM DATA_MOVER_ACCESS DM

                ,PSOPRCLS OC

            WHERE DM.PERMISSION_LIST = OC.OPRCLASS

            GROUP BY OC.OPRID

           ) DMA

      WHERE APPD.OPRID           (+) = OC.OPRID

      AND   APPD.PERMISSION_LIST (+) = OC.OPRCLASS

      AND   DMA.OPRID            (+) = OC.OPRID

      AND   DMA.PERMISSION_LIST  (+) = OC.OPRCLASS

      GROUP BY OC.OPRID

------------------------------------------------------------------------------------------------------------------------------

     ) OPC --select* from dual

     , PSOPRDEFN OPD

WHERE (OPC.APP_DESIGNER_ACCESS || OPC.DATA_MOVER_ACCESS) IS NOT NULL

AND   OPD.OPRID = OPC.OPRID

------------------------------------------------------------------------------------------------------------------------------

;

Hope it helps!

Linux Processes

List all processes running that have PSAESRV and FSDMO but not FSDMO2: (including cpu and memory usage and the command that started the process): 

ps -e -o pid,%cpu,%mem,cmd | grep "PSAESRV.*FSDMO[^2]"



Update:
Use pidstat (or top) to get proper CPU usage and other details:
pidstat -p 12514 -T ALL -u -d -w -t -I

https://linux.die.net/man/1/pidstat
https://linux.die.net/man/1/ps
https://linux.die.net/man/1/top


Processes using specific ports:

https://superuser.com/questions/42843/finding-the-process-that-is-using-a-certain-port-in-linux 

You have a couple of options: 

lsof -i tcp:80  

will give you the list of processes using tcp port 80. 

Alternatively, 

sudo netstat -nlp  

will give you all open network connections.

Run SQL*plus command or script from CMD command line

echo exit | sqlplus user/pass@connect @scriptname

Reference:

http://serverfault.com/questions/87035/run-oracle-sql-script-and-exit-from-sqlplus-exe-via-command-prompt

Update all tables that have a specific data in a specific (or similar) column(s)

A while ago, I needed to rename a user (OPRID) in PeopleSoft because it was created without the correct naming conventions and it could overlap with a future user that would conform to the standards.

No problem, I deleted the user and created a new one, but how about some of the processes and that he ran and some of the things that he changed?

I created a select to find all tables that have the OPRID column and other columns with similar names and lengths (sometimes it’s called LAST_OPRID, etc… and I’ve seen columns that store the OPRID with different data lengths!). In the interest of the future, I didn’t create a SELECT only for OPRID, I made it easy to adapt to other columns too…

Here it is, instructions on how to use it below:

WITH DATA_TO_REPLACE AS

     (SELECT 'SYSADM'    AS TABLE_OWNER          --CHANGE THIS

            ,'OPRID'     AS COLUMN_LIKE          --CHANGE THIS

            , 5          AS MINIMUM_SIZE         --CHANGE THIS

            , 55         AS MAXIMUM_SIZE         --CHANGE THIS

            , 'VARCHAR2' AS COL_TYPE_LIKE        --CHANGE THIS

            , 'NEWVALUE' AS NEW_VALUE            --CHANGE THIS

            , 'OLDVALUE' AS OLD_VALUE_TO_REPLACE --CHANGE THIS

            , UPPER('UPDATE SUB_TABLE_NAME SET SUB_COLUMN_NAME = ''''SUB_NEW_VALUE'''' WHERE SUB_COLUMN_NAME = ''''SUB_OLD_VALUE_TO_REPLACE'''';') AS SQL_TO_RUN

      FROM DUAL)

SELECT 'SELECT ''' || REPLACE(REPLACE(REPLACE(REPLACE(SQL_TO_RUN,'SUB_TABLE_NAME',TABLE_NAME)

                                                                ,'SUB_COLUMN_NAME',COLUMN_NAME)

                                                                ,'SUB_NEW_VALUE',NEW_VALUE)

                                                                ,'SUB_OLD_VALUE_TO_REPLACE',OLD_VALUE_TO_REPLACE)

                    || ''' FROM ' || TABLE_NAME || ' WHERE ' || COLUMN_NAME || '= ''' || OLD_VALUE_TO_REPLACE || ''' AND ROWNUM = 1 UNION'

--SELECT*

FROM ALL_TAB_COLUMNS, DATA_TO_REPLACE

WHERE OWNER= TABLE_OWNER

AND COLUMN_NAME LIKE '%' || COLUMN_LIKE || '%'

AND DATA_LENGTH BETWEEN MINIMUM_SIZE AND MAXIMUM_SIZE

AND DATA_TYPE LIKE '%' || COL_TYPE_LIKE || '%'

---- CHANGE ANYTHING BELOW THIS LINE

--ONLY TABLES "PS_"

AND TABLE_NAME LIKE 'PS\_%' ESCAPE '\'

-- NO AET, TMP, TAO, TEO...

AND TABLE_NAME NOT LIKE '%AET'

AND TABLE_NAME NOT LIKE '%TMP' AND TABLE_NAME NOT LIKE '%TMP_' AND TABLE_NAME NOT LIKE '%TMP__'

AND TABLE_NAME NOT LIKE '%TAO' AND TABLE_NAME NOT LIKE '%TAO_' AND TABLE_NAME NOT LIKE '%TAO__'

AND TABLE_NAME NOT LIKE '%TEO' AND TABLE_NAME NOT LIKE '%TEO_' AND TABLE_NAME NOT LIKE '%TEO__'

-- NO VIEWS

AND TABLE_NAME NOT IN (SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER = TABLE_OWNER)

You need to review and change the first few values (“CHANGE THIS”) and the tables criteria below the “CHANGE ANYTHING BELOW THIS LINE”.

Then run this, don’t worry, it’s just a SELECT statement, it will bring up the list of tables that *may* contain data that you might want to replace.

Copy the resulting SELECT … UNION … SELECT …, paste, remove the last UNION and run it (again, safe so far…)

This will result in the UPDATE statement for all tables that contain data equals to the data you wanted to replace only (say the table A has the column USER that you want to replace but this column doesn’t have the data “MYUSER” that you wanted to replace with “OTHERUSER”, then it won’t show up).

If you have any doubts, post a comment and I’ll try to help…