How to delete/purge OPRID outside PS?

Peoplesoft provides an online page to delete OPRID. However, this can become cumbersome if you need to purge or delete 10+ OPRID’s as part of your cleanup exercise. I have created the below PL/SQL script which basically mimics what the online purge process executes.

DECLARE

CURSOR C1 IS

SELECT decode(A.SQLTABLENAME,’ ‘,’PS_’||A.RECNAME,A.SQLTABLENAME) RECNAME

FROM PSRECDEFN A

WHERE RECNAME IN (SELECT RECNAME FROM PS_TBLSELECTION_VW)

AND RECNAME != ‘PSOPRDEFN’

ORDER BY 1;

CURSOR C2 IS

SELECT decode(B.SQLTABLENAME,’ ‘,’PS_’||B.RECNAME,B.SQLTABLENAME) RECNAME

FROM PSRECDEFN B

WHERE

RECNAME IN (SELECT RECNAME FROM PS_ROLEUSR_TBLS_VW)

AND RECNAME != ‘PSOPRDEFN’

Read More About OPRID

How to be any OPRID, if

Here is the scenario…

• You do not have access to PeopleSoft Security Administration pages to reset the password.
• You want to log on to PeopleSoft using your id and you have forgotten your password or you want to log on as some other operator id (without being detected).

Hmmm…

This is possible “IF” (the BIG IF)

• You are the DBA
• You are not the DBA but your generous DBA has provided you SELECT and UPDATE access to PS tools tables

Here is an example.

1.  I want to logon to F89XXXX but I do not remember my password. I sent an email to my security admin who is busy working on other priority issues.

SYSADM@f89xxxx > select oprid, operpswd from psoprdefn where oprid = ‘NPAI’;

OPRID OPERPSWD

—————————— ——————————–

NPAI 2bc+5cdNDqN4to33X9hP98N97+k=

2.  I remember my password in F89NNNN. So I query my operator password in that db.

SYSADM@f89xxxx > select oprid, operpswd from psoprdefn@f89nnnn where oprid = ‘NPAI’;

OPRID OPERPSWD

—————————— ——————————–

NPAI VLmLlgq9QsvCkDvRoqLnvhn8SkM=

3.  Execute the below Update SQL

SYSADM@f89xxxx > update psoprdefn set operpswd = ‘VLmLlgq9QsvCkDvRoqLnvhn8SkM=’ where oprid = ‘NPAI’;

1 row updated.

SYSADM@f89xxxx > commit;

Commit complete.

4.Voilà!! I am now able to log on to F89XXXX using my password that I remember from F89NNNN.

You can follow similar steps to log on using any other operator id. You can update it back to the original value and go undetected unless it’s being audited.

Conclusion

For the DBA:

• Do not provide ANY level of access to PS tools tables to ANY user in ANY environment
• PROTECTPSOPRDEFN and other security tools tables
• Consider HIDING OPERPSWD column using a view or restrict access to PSOPRDEFN using FGAC
• AUDIT all security related tools tables.

For others:

• Do not attempt this in your environment!

Read More About OPRID

Creating OPRID clones at the speed of light!!!

In my development environment, I had a request to create 30 new OPRID’s cloning an existing developer id template. I was not interested in logging into PeopleSoft, navigating to PeopleTools > Security > User Profiles > Copy User Profiles, and creating the clone 30 times. Hmm…

So here is what I did.

1.Create a dummy table

create table hx_clone_ids (oprid varchar2(30));

2.Insert all desired OPRID values. For example,

INSERT INTO HX_CLONE_IDS SELECT ‘HX_NPAI_’||ROWNUM FROM PSOPRDEFN WHERE ROWNUM < 31;

3.Execute below SQL’s (OPRID to be cloned is NPAI).

INSERT INTO PSOPRALIAS

SELECT

HX.OPRID,

OPRALIASTYPE,

OPRALIASVALUE,

SETID,

EMPLID,

CUST_ID,

VENDOR_ID,

APPLID,

CONTACT_ID,

PERSON_ID,

EXT_ORG_ID,

BIDDER_ID,

EOTP_PARTNERID

FROM PSOPRALIAS A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PSOPRDEFN

SELECT

HX.OPRID,

VERSION,

OPRDEFNDESC,

EMPLID,

EMAILID,

OPRCLASS,

ROWSECCLASS,

OPERPSWD,

ENCRYPTED,

SYMBOLICID,

LANGUAGE_CD,

MULTILANG,

CURRENCY_CD,

SYSDATE,

ACCTLOCK,

PRCSPRFLCLS,

DEFAULTNAVHP,

FAILEDLOGINS,

EXPENT,

OPRTYPE,

USERIDALIAS,

LASTSIGNONDTTM,

LASTUPDDTTM,

LASTUPDOPRID,

PTALLOWSWITCHUSER

FROM PSOPRDEFN A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PS_ROLEXLATOPR

SELECT

HX.OPRID,

DESCR,

HX.OPRID,

EMAILID,

FORMID,

WORKLIST_USER_SW,

EMAIL_USER_SW,

FORMS_USER_SW,

EMPLID,

ROLEUSER_ALT,

ROLEUSER_SUPR,

EFFDT_FROM,

EFFDT_TO

FROM PS_ROLEXLATOPR A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PSUSERATTR

SELECT

HX.OPRID,

HINT_QUESTION,

HINT_RESPONSE,

NO_SYMBID_WARN,

LASTUPDDTTM,

LASTUPDOPRID,

MPDEFAULMP

FROM PSUSERATTR A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PSUSEREMAIL

SELECT

HX.OPRID,

EMAILTYPE,

EMAILID,

PRIMARY_EMAIL

FROM PSUSEREMAIL A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;

INSERT INTO PSROLEUSER

SELECT

HX.OPRID,

ROLENAME,

DYNAMIC_SW

FROM PSROLEUSER A, HX_CLONE_IDS HX WHERE ROLEUSER = ‘NPAI’;

 

Read More About OPRID Clones