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

How to determine what is being processed?

As a DBA you might be receiving a lot of calls from users asking some of the questions listed below.

- What their process is currently executing?

- How long will it take?

- Should I cancel my process?

In this post, I will cover some steps that will help you quickly respond to the above questions.

I have executed the below SQR Report in my environment and want to know what is happening.

What their process is currently executing?

1.Use the below SQL to correctly identify the Oracle session

SELECT CLIENT_INFO, PROGRAM, SID, SERIAL#, SQL_ADDRESS FROM V$SESSION WHERE CLIENT_INFO LIKE ‘NPAI%’ AND STATUS =‘ACTIVE’;

2.The above results identify the session executing the report; in this case it is SID 32.

3.Use this SID to identify the SQL being executed.

SELECT SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID= 32) ORDER BY PIECE;

SQL_TEXT

—————————————————————-

SELECT A.RECNAME, A.FIELDNAME FROM PSRECFIELD A WHERE A.SUBRECO

RD <> ‘Y’ AND NOT EXISTS (SELECT ‘X’ FROM PSDBFIELD B WHERE B.FI

ELDNAME = A.FIELDNAME)

4.The above results will provide you the SQL that is currently being executed. Equipped with this information you SHOULD be able to dig deeper if the session continues to execute the same SQL for more than expected time. Some of the scenarios could be:

a.Locked rows

b.Bad SQL

c.Statistics not updated

d.Bad DB configuration – SQL waiting on some event

e.Missing index, etc

Note 1
Also, there are scenarios where the session might not be executing any SQL because it is busy executing the code logic within the program. If the program is written using good coding standards then you should be able to look at the log file to decipher the progress.

Note 2
It is possible that there are no Oracle sessions created by the process. Some of the scenarios are:
- Process is Queued
- Process has completed and is Posting, etc
How long will it take?In most scenarios the functional support personnel along with the developer should be able to respond to this question based on the SQL or results in log file that you have identified. If you have knowledge about this process based on its history and/or your ability to interpret the program then you can estimate the completion time.
Should I cancel my process?This decision will require approval from the business + functional support personnel unless you have knowledge about the impact of the process.

Read More About  what is being processed?

Calling Oracle DB Function from PS Query Manager

A few weeks back a developer posed a question“Can I use an Oracle Function in PS Query Manager?”
Yes, you can and here are the steps.

1.Create your function in Oracle DB

CREATE OR REPLACE FUNCTION TEMP_FUNC(VAR1 IN NUMBER)

RETURN VARCHAR2

IS VAR2 VARCHAR2(48);

BEGIN

SELECT NVL(PROGRAM,’NULL’)

INTO VAR2

FROM V$SESSION

WHERE AUDSID=USERENV(‘SESSIONID’);

RETURN(VAR2);

END;

2.Create a view in PeopleSoft

CREATE VIEW PS_TEMP_VW AS SELECT TEMP_FUNC(10) VARIABLE_NAME FROM DUAL;

3.That’s it! Use your view in Query Manager as shown below.

 

Read More About  PS Query Manager

Data Integration Challenge – Understanding Lookup Process – III

In Part II we discussed ‘when to use’ and ‘when not to use’ the particular type of lookup process, the Direct Query lookup, Join based lookup and the Cache file based lookup. Now we shall see what are the points to be considered for better performance of these ‘lookup’ types.

In the case of Direct Query the following points are to be considered

• Index on the lookup condition columns
• Selecting only the required columns

In the case of Join based lookup, the following points are to be considered

• Index on the columns that are used as part of Join conditions
• Selecting only the required columns

In the case of Cache file based lookup, let us first try to understand the process of how these files are built and queried.

The key aspects of a Lookup Process are the

• SQL that pulls the data from lookup table
• Cache memory/files that holds the data
• Lookup Conditions that query the cache memory/file
• Output Columns that are returned back from the cache files

Cache file build process:

Based on the product Informatica or Datastage when a lookup process is being designed we would define the ‘lookup conditions’ or the ‘key fields’ and also define a list of fields that would need to be returned on lookup query. Based on these definitions the required data is pulled from lookup table and the cache file is populated with the data. The cache file structure is optimized for data retrieval assuming that the cache file would be queried based certain set of columns called ‘lookup conditions’ or ‘key fields’.

In the case of Informatica, the cache file is of separate index and data file, the index file has the fields that are part of the ‘lookup condition’ and the data file has the fields that are to be returned. Datastage cache files are called Hash files which are optimized based on the ‘key fields’.

Cache file query process:

Irrespective of the product of choice following would be the steps involved internally when a lookup process is invoked.

Process:

1. Get the Inputs for Lookup Query, Lookup Condition and Columns to be returned
2. Load the cache file to memory
3. 
   
4. Search the record(s) matching the Lookup condition values , in case of Informatica this search happens on the ‘index file’
5. 
   
6. Pull the required columns matching the condition and return, in case of Informatica with the result from ‘index file’ search, the data from the ‘data file’ is located and retrieved
7. 
   

In the search process, based on the memory availability there could be many disk hits and page swapping.

So in terms performance tuning we could look at two levels

1. how to optimize the cache file building process
2. 
   
3. how to optimize cache file query process
4. 
   

The following table lists the points to be considered for the better performance of a cache file based lookup

Category	Points to consider
Optimize Cache file building process	• While retrieving the records to build the cache file, sort the records by the lookup condition, this sorting would speed up the index (file) building process. This is because the search tree of the Index file would be built faster with lesser node realignment • Select only the required fields there by reducing the cache file size • Reusing the same cache file for multiple requirements for same or slightly varied lookup conditions
Optimize Cache file query process	• Sort the records that come from source to query the cache file by the lookup condition columns, this ensures less page swapping and page hits. If the subsequent input source records come in a continuous sorted order then the hits of the required index data in the memory is high and the disk swapping is reduced • Having a dedicated separate disk ensures a reserved space for the lookup cache files and also improves response of writing to the disk and reading from the disk • Avoid querying recurring lookup condition, by sorting the incoming records by the lookup condition

You might want to read these awesome related posts Data Integration Challenge