People Soft re sending the content for bulk processes after they run to not posted

Certain times, we find distribution status for a huge number of processes is Not Posted, which could happen for many reasons. In this case, resending the content from PIA process monitor page for each single process is not efficient and is very time consuming.

Solution:

We can update few tables playing role in sending the output reports to report repository .Run the below update statements on the database : 

UPDATE PSPRCSRQST SET DISTSTATUS=7 where DISTSTATUS=4;
UPDATE PSPRCSQUE SET DISTSTATUS=7 where DISTSTATUS=4;
UPDATE PS_CDM_LIST SET DISTSTATUS = '8',TRANSFERINSTANCE = 0 WHERE DISTSTATUS=4;

After updating the distribution status in respective tables, distribution agent takes up the request and posts the files to report repository.

People Soft Troubleshooting Initiating User error handler

Hi All,

Very often while booting peoplesoft application server, we get following error and app server is not started :

-- error initiating user error handler .

and no more information is available in logs .

Now it clearly states the reason that it has a problem with any user credentials .The possibilities could be :

1) Wrong, Locked or Expired Connect ID/Password
2) Wrong, Locked or Expired PeopleSoft User ID/Password
3) Wrong, Locked or Expired Access ID and Password
4) Wrong symbolic ID specified in PSOPRDEFN against that particular userid.
5) Owner ID could be wrong in PSSTATUS table
6) Entry in PSDBOWNER table could be wrong.

There are over ten reasons here.

In cases when you have done any instance refresh from a prod box which uses a different owner id or symbolic id, you would not have any idea whats wrong since it never has been started before otherwise atleast few things you could be sure are correct.

So what approach do you follow in this case ?

I am giving my approach here to exactly know what is wrong or locked or expired. I take advantage of application designer usnig the same mechanism to start as app server.

In configuration manager, give the same connect id and password you have specified in app server config file. Set trace by checking sql statements .

Now login to application designer with the same Peoplesoft user id and password as given in app server config file. It would not login to database but generate the trace.
Now just open the trace file with notepad and look at what step and table its stuck to move ahead.Now troubleshooting takes a minute only, if its stuck at dbowner, check the entry in that . If stuck at initial step while logging in from connect id, try logging to database with that id . if stuck at psstatus, check entry in that . And we are done , correct the thing and restart the app server ..it will boot !!!!


Note : In case of process scheduler, in addition to the above stated causes it might also occur due to incorrect settings in report nodes .see the distagent.log for details , if no errors are found there then above technique should work.

People Soft Security Hardening from PIA

Hi All,

In this post, i'll brief how to revoke access from users to add/modify/delete any permission lists, roles or user profiles ?
This is vital to the PeopleSoft application, if not done, users can change any profiles abd get any access you have emplyed security upon .

I'll discuss how to revoke access so that users will have display only access while opening any permission list, role or user profile. Also they wont see delete or copy permission list/role and user profiles .

The security related pages are added to Maintain_Security menu. Our aim is to give display only access on these pages.
Approach:

1) We need to identify which permission lists give access to these pages.The name and number of PL's differ from one application to other.Navigate to PeopleTools->Portal->Structure and Content.
2) Navigate to any page contained in Maintain_Security eg. delete user profile, click on security tab and see what all permission lists are added over there.
3)oracle delivers the applications in such a way that access to all these pages are given to few permission lists. So now our aim is to revoke access from these permission list.
4) There can be multiple approaches from here,one could be to find through which roles users are having access to these permission lists and remove the actual permission list/Roles from users and give a duplicate of these permission lists with pages under Maintain_Security made display only. Other way is to change these delivered permission list and make the pages display only. If taken second approach, read the point 5.
5) Then create a new permission list, give full access on these pages and attach it to a new created role. Add this role to admins user profile.

After doing this, users will have read only access on these pages maintaining security of People soft system while admins will get full access.


Write me for any query/confusion ....

People Soft Schema level application creation in a single database

Hi All,
We all know that a PeopleSoft applicaton resides in a single schema in a database on Oracle platform . In this post i'll brief how can we create multiple applications running in a single oracle database with separated schemas holding data of respective applications .

This approach saves us atleast 50% on memory consumption and hard disk required on database server(if we create atleast two instances) since we have to allocate memory target to a single database. Also while application creation, data files of some sizes are created on disk but not that much data is contained on the physical disks .Therefore when we create multiple applications in a single database, same empty space is used for other applications.

This architecture is ideal for non production environments.
To create multiple applications in a single Oracle database, following steps are to be taken:

1) Create an Oracle database with some general db name  such as PSDB01.
2) Run the initial scripts utlspace, psdbowner, application specific script(if creating different applications, run each application specific scripts.We get error if second script tries to create the same tablespaces--this is normal and no harm is done), psrole, psadmin(run as many times as the number of applications to be created with different owner id and passwords such as HRM91, CRM91) and connect.sql.
3) Import the Peoplesoft schema data into each of the respective schemas created with impdp utility, use Remap_Schema parameter if required.

Now, the major hurdle come to understand how application servers will be booted on each specific application.To get a hold of this, lets walk through the PeopleSoft login process:

1) When we login, we specify database name, userid-password and connectid-password. First connection is made using connect id and password and a query is executed on database:
    SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME='DB NAME WHICH WE GAVE';
The schema name should be returned here in which all the peoplesoft data resides.
2) A second query is run on database from the table <schema fetched>.PSSTATUS where version of the tool is matched and again the owner is selected, which should match as the one specified in PS.PSDBOWNER table.
3) Using this owner, password for the user id specified is matched from <owner>.PSOPRDEFN .
4) If successful, access id and password is fetched from <owner>.PSACCESSPRFL . Which is the schema name and password where all the data for an application resides.Once fetched, connection is made from this id and application server is booted.


Now, i'll consider an example. Here lets say i created a database PSDB01. Inside this, i created two schemas HRM91 and CRM91 and imported the respective applicaton data from other databases.

In PS.PSDBOWNER table, i need to make two entries :

Column :    DBNAME            OWNER
Values :    HRM91            HRM91
        CRM91            CRM91

Update the ownerid in HRM91.PSSTATUS to HRM91 and in CRM91.PSSTATUS to CRM91.

Now i need to make two tns entries on server from where application server will be booted as :


HRM91= ...
    Host=<IP> Port=<listener port>
    Service=PSDB01

CRM91= ...
    Host=<IP> Port=<listener port>
    Service=PSDB01

Please note that both the connect identifiers are pointing to the same database PSDB01 .

We are all set to boot app servers. Here is what happens:

Booting HRM91 app server :
Parameters : dbname-HRM91
1) select owner from ps.psdbowner where dbname='HRM91'; -- output- HRM91
2) select owner, toolsrel from HRM91.PSSTATUS;        ---- output - HRM91
3) select operpswd, symbolicid ... from HRM91.PSOPRDEFN;
4)select accessid, accesspswd .. from HRM91.PSACCESSPRFL; ----output -HRM91/PASSWORD

This way it boots on hrm91 applications.

Same happens while booting the app server for CRM91 application.

For any confusion/doubt email me ...



   

People Soft Report Posting - process scheduler or web server

Hi All,

In this post i'll discuss the role played by web server and batch server while transferring the reports to report repository specified. I'll be assuming http/https transfer throughout the post.xcopy/ftp i'll discuss in another post.

I find in many documentation that distribution agent process in process scheduler server transfers the output files generated by a process to report repository and deletes the file from log/output directory.

Well i find this a very rough idea as it fails to mention the role played by web server. In fact files are posted to repository and removed from log/output directory completely by web server !!!!!

Did it ever occur to you where is the repository path picked from ? Consider a situation, i have two batch servers configured on two different machines. and a web server running on third machine .Now the report repository path given in web profile is c:\psreports.
Now if transferring the files was the job of process schedulers, they will read the repository path as local directory since nowhere else do we specify the server address and they should post the files on the respective physical server those are running .

But this does not happen, reports are posted on the local drive where web server is running. Now lets understand the process how it happens :

On web servers two servlets, SchedulerTransfer and psreports owning the responsibility to transfer the reports and to view the reports from front end. All distribution agent does is send a request to web server with the details of processes for whom reports are to be sent. Report node setting plays a crucial role in this.

Report Node Settings :

In report node, URI host URI Port is the address of web server where SchedulerTransfer servlet is present .in URI Resource we give SchedulerTransfer/<site name>.Site name is used to pick the web profile it uses and from there report repository path is taken (if nothing given then configuration.properties it take value from). A request to transfer the files is sent to this servlet on this web server configured which picks the repository path from web profile/configuration.properties and transfers the files.

psreports servlet is used to view the reports from front end which we mention in the URL field of report node .Again here site name is mentioned as a hint to tell the repository path from where report has to be picked to view .

Understood this concept, you can setup two report repository such that output of process run by one process scheduler are posted on one server and reports of processes run by other batch server are posted on another server.However, reauthentication is required in that case. check my other post on re authentication .

Try these settings !!!!

People Soft Reauthenticaton required while viewing reports

Hi All,

Did you ever come across a situation where, while viewing the reports after a process is run and reports are posted, you are prompted to login again for authentication .


By default there is no such thing employed by Oracle to re authenticate the user while opening the reports.

This could occur for two reasons :
1) If you have modified the signon peoplecode and changed something which prevents the authentication to be passed when opening the report.
2)If the URL that you are using to open the application and the one given in URL field of report node,which your batch server is using,do not match but point to same server.

The first point varies from customer to customer as they employ changes. I'll explain the second reason in this post.

While setting up the report node,there is a field URL where we give a value like :

    http://IP:Port/psreports/Sitename

Here IP and port is the address of web server which fetches the report to view. psreports is the servlet that does this job and site name is used to locate report repository where output files are placed(from its web profile or configuration.properties).When we view the report, the URL at browser changes to this IP and port.

In cases where the URL which we used to login to application and the one specified here do not match (it happens if over one place we use intranet IP while at other place Internet IP or DNS is given.In case of RPS also it happens). Since the cookies are passed only to urls with same ip and port or domain names, here authentication information is not passed when opening the report . This is the reason we need to pass our credentials again. Notice, once we give the id and password, until we log off we are not asked to re authenticate ourselves. Since now two credentials are stored in cookies and passed on to respective URLs.

Post your questions and comments. !!

People Soft PSCipher Utility Loopholes

Hi ,

lets decipher the password encrypted by PSCipher in PeopleSoft !!!!

Well, we know PSCipher utility is used to encrypt the password of Peoplesoft user ID given in web server configuration.properties and in the components of Integration. There is a bat file PSCipher.bat given under web server domain which is used in the process , from cmd also we can encrypt any string with this algorithm and generate new versions of key if required. If you match a password given in configuration.properties with the one you see in PSOPRDEFN for the same user, they are different and encryption in PSOPRDEFN is one way (means passwords are never decrypted ,while authentication its encrypted with the same algorithm and matched with one stored in PSOPRDEFN).

This was hint enough for me to guess out that password in configuration.properties would have to decrypted and brought back to original string for authentication against PSOPRDEFN table.
The curiosity kept on accumulating, and one day i opened the PSCipher.bat file, it calls a java class file PSCipher.class to encrypt the plain strings and passes that string to that java code as default argument to main function.I then gave a try to de compile the class file and guess what i found --

There is a function which accepts that string as argument and uses the key stored in psvault file to encrypt the strings. There is also a decrypt function which is used to decrypt the encrypted strings.

Rest was a cake walk... opened my eclipse , wrote a java program and called the decrypt method of the class file and passed the encrypted string and yesss got the same plain string which was encrypted!!!!

I then wrote another bat file decode.bat in same way as pscipher.bat ,which now i use from cmd . Just run decode.bat from cmd with encrypted string as an argument and we get the original plain string !!! :):)

All the security freaks, give this a try it was fun .If you dont know java programming, you can use the peoplecode to instantiate the same class file and use the decrypt method .Then insert into any page and there you go ....P

People Soft Last Updated UserID in Target after migration

Hi All,

In this post i am gonna talk about the audit details of a peoplesoft object in target system after it has been migrated from a source database.

For any peoplesoft object we can open in app d, we can view the user id it was last modified by and on which date.When we migrate an object from source to target, we use a valid userid and password to login to the target database to copy the objects.
After the object is moved to target instance, what would we see in the fields last updated by and last updated on ?Would this be the userid that we used to login to target database? Well the answer is no .

When we copy any object from source to target database, the last updated on field is updated to current date and last updated by value is just taken from the source database.So what we see in the last modified by field in source database for that particular object will be the value of last updated by in target database.

Impact : Therefore while looking at the last updated by value, we need to consider if that user explicitly changed the object or it has been migrated from some other database and the user did not make any changes.

Same case happens, if you find last updated by values as PPLSOFT and date is very recent its not mandatory that a patch has been applied, it could simply have been migrated from another database.

People Soft Integration gateway.properties file

Hi All,

In this post, i'll just brief about the integration gateway.properties file present under web server domain and what happens when we access it from PIA under gateway properties.

Did you ever wonder when you access the gateway.properties file from PIA, where is it loaded from ?is it fetched from database or read in from web server domain ?
Also did you encounter that the content you see from PIA and what you see in web server domain are different ?Ever noticed what could be the reason ?

Well here you go :

There is only one integration gateway.properties file per web server domain. It contains the node details that we have configured from gateway properties page, logging details and other information. When we access the nodes listed in gateway properties page, the entries are populated from this file only. likewise, if we make any changes in node entries, same is recorded in gateway.properties file.

The same file is read and loaded when we access it from PIA .Did you find the content is different ?
This could happen only in the case when the webserver given in gateway URL and the one which you are accessing physically are different.

The file is read through a web service and loaded from under the web server which is mentioned in the gateway URL.

In cases it happens that while integration, you have given the higher instances gateway URL which is different from the host instance. In this case when accessed from PIA, gateway.properties file is read from the other web server domain only.So when you compare, the file that you see from PIA and the one you look in the host web domain would certainly be different !!!!

Therefore lets say, you need to change the logging level, go to the web server domain which is given in the gateway URL, find the file and make changes, not in the host web server domain in case these are different.

Implementing Change Control

Hi All,

In this post, i am gonna discuss the approach while implementing change control in any of your PeopleSoft application.

Enabling change control from App D is not a big task. However,after enabling this,restrict that no one should be having supervisor access except the admin which is the main security to control.Otherwise, lets say you enable the change control and any developer having supervisor access can disable the settings !!!

Change control, as name indicates , is a way to control the changes done by several developer in the development landscapes.It employs locking and unlocking of objects before modifying them and prevent overwriting by another developer when already one technical is making changes to it.

Change control authority is controlled by permission lists given to a user through roles.There are three levels of authority :

1) Restricted Access : User with this access only, cannot lock an object or change the setting of change control.
2) Developer Access  : User with this access can lock the unlock objects and make changes. They cannot lock or                               unlock objects that are locked by others.Can not change settings.
3) Supervisor Access : User with this can control the settings and lock/unlock any objects.

Here is the approach you should follow :

1) Enable the change control from App D.
2) Note that users could be having tens of permission lists which gives them the supervisor access. Find the list
   of permission lists having supervisor access to change control,which are added to users roles:
  
   SELECT CLASSID FROM PSAUTHITEM WHERE MENUNAME='APPLICATION_DESIGNER' AND BARNAME='CHANGE_CONTROL' AND    AUTHORIZEDACTIONS=4;

3)Revoke the supervisor access from all permission lists and give restricted access:

  UPDATE PSAUTHITEM SET AUTHORIZEDACTIONS=2 WHERE MENUNAME='APPLICATION_DESIGNER' AND BARNAME='CHANGE_CONTROL';
  Note : update authorizedactions as 3 if you want to give developer access to everyone.

4)Now that no permission lists are having supervisor access, create a new permission list and just give     supervisor access to it on change control .No other permissions to be given to this PL.

5)Create a new role, assign the created PL to this role and attach this role to admins user profile .

Result : except admins, no one would be having access to make modifications to change control settings. They can          just lock the definition if given developer access.

People Soft Definition Security by securing definition type

Hi All,

In my last post on securing object access through definition groups in prod boxes, i presented a major limitation.
This post, i'll describe an approach to achieve the same in production instances by going one level up and securing definition type from users.

Definition type access is controlled by permission lists given to users through roles.Open any permission list, navigate to Peopletools and see if Application designer box is checked. If yes, then a link gets enabled named definition permissions. From here we can restrict access on a particular type of object from users.

Approach :

1) We will update the access as readonly to a particular type of object for all permission list.After doing this, no user will have access on that particular type of object.
For example, to restrict access on all fields, run this query

UPDATE PSAUTHITEM SET AUTHORIZEDACTIONS=2 WHERE MENUNAME='APPLICATION_DESIGNER' AND BARNAME='DBFIELD';

2) Create a new permission list now, and give full access on the same type of object.
3) Create a new role and add that permission list to this role.
4) Add this role to user profiles of admins.


This way, all users lose their modify access to all def types and have readonly access.But the admins will have full access.

Unlike, definition groups, we need not take any addition care of new objects created after this security is employed.

Write me for any queries or confusions.!!!

People Soft Definition Security by definition group

Hi All,
In this post, i'll brief about levels of restricting object definition when opened from Application designer, also very critical loophole of implementing this in projects.

Using definition groups to restrict access to objects :

1) A user gets access to objects from primary permission list added to his profile, not through roles .Definition groups are added to primary permission lists.
2) Create a definition group, add to it the objects that you want  a developer/user to access.
3)Add this definition group to that user's primary permission list. A precaution to take here is, if the same primary permission list is given to another user, he gets access to those objects as well.
4) If you want to give read only access on the objects included in a definition group only access.If you want a developer not to have access to any object, just dont include it in definition group added to his primary permission list.

The best approach is to categorize the objects as per the modules on which developers would be working.Then create individual def groups and add respective category of objects to the definition groups.And attach one or more to primary permission lists of users you want to have access on those objects.

Approach in production databases:
Since in production databases, no one except admins need to have write access on objects, you can limit every user to have display only access on objects. For this, simply create a definition group, add all objects to that .Then in the permission lists of admins and normal users, add this definition group , only difference being while adding it to the primary permission list of normal users, have display only checked.

Major Limitation :

If you read in peoplebooks, there are 3 rules using which definition access is determined for any user.

The first among that is, if any object is not added to any definition group then every user in the application has full access to it !!!!
the point is, if a new object is created in any environment, by default its not added to any def group and everyone gets access to it untill you manually add it to a definition group atleast ...not a feasible option !!

Therefore definition security through def groups servers no purpose in prod databases ... have you applied this mechanism in your databases ???
Read my post on securing definition by limiting user access on definition types instead of def names ...

Checking version of different component in peoplesoft

PeopleTools Version :

From PIA you could press ctrl+J and check the tools version.
From file server, you can check the version at the top left when you open the psadmin utility.We can also see the details from App d or data mover on signin page .Also you can check in peopletools.properties file. This should be updated when you install a patch to show the correct patching detail.


Application version :

From PIA press ctrl+J and check the application version.
From database query the table PSRELEASE and check the latest updated row.

Tuxedo version :

From cmd , change directory to the bin folder of where you installed the tuxedo. then run this command :

        tmadmin -v

WebLogic Version :

From cmd change directory to WL_HOME/server/lib and run this command :
        java -cp weblogic.jar weblogic.version

Database Version :
When you connect to database, it shows which version are you connected to.
Also you can query v$instance .

Verity Version :

Navigate from file server to Navigate to verity\winx86\_nti40\bin
 and run the command : Rcvdk.exe

Check your People Soft Application Security

Hi All,

In this post, i am gonna take you all to do an audit of your current PeopleSoft applications for security loopholes.

Check the following things and please post the count of points given here your application conforms to:

1) The user booting the application and batch server should not have access to do anything else in the application.

2) Except the user booting the two servers, no one should have privilege to start the app, batch server. Otherwise miscreants can have their own app server running against your database ..a major security threat !!!!

3) Have you emplyed change control ? did you remove supervisor access from every other users so as not to allow them to turn the change control on/off ? Else it could happen that you enabled the change control and someone disable it !!!

4) Did you employ security to the objects accessed from app designer ?Do you use definition groups to maintain objects security ? When a new object is added to database, by default its not added to any def group and every user gets update access to it !! did you take care of this ?

5) Did you revoke access from end users so as not to allow them have permission to add/modify/delete any permission list/roles/user profiles ?? if not, your every other security measure is at major risk !!!

6) Did you generate the new key for your PSCipher utility and have it protected ?? if not, anyone can use the decrypt method written in PSCipher class and decrypt your web profile and integration broker passwords !!!

7) Did you hide the address of application server,database name and other sensitive information while someone hits ctrl+j from PIA ?? no ?? yes you can hide certain info while leaving users to see the component name, page name etc...

8) Did you remove delivered users with any peoplesoft application ?? I have seen miscreants using those ids to get unauthorized access to the applications ...

9) Do you have your connect id-password and userid-password envrypted in configuration files ?

10) Do you have enough security at database level ? I have seen people copied the encrypted password from psoprdefn table to a text file, changed the password of a user, did some transactions and restored the password as before and updated the last updated userid as before ?? !!!



stay tuned for more info ....

Calling an oracle script from within batch file and vice versa

Hi All,

Well cmd commands, sql scripts and task scheduler on windows are great tools when used with sharp thinking to automate literally anything that you perform manually in any project .

Recently i was automating a process on windows operating system, and created a bat file start.bat containing the code:

    sqlplus
    sysadm/sysadm@mydb
    @d:\script.sql
    f:
    cd f:\afolder
    second.bat

When i run the batch file, it executed the first two lines and stuck there . It never run the sql script and the other bat file .I wondered what went wrong .

After a min of brainstorming i could find the reason. cmd and sqlplus are two different programs. after two lines i was connected to database with sqlplus, control was with sqlplus program not with cmd.exe. And the other lines were supposed to be run by the cmd program  since bat file was given to it to execute. Therefore until sqlplus does not return the control to cmd, it will not go further two lines and wait for you to enter any sql command on sqlplus . If we enter exit, that program terminates and control is returned  to cmd and it treats the third line as cmd command and opens the file in notepad .

So if we are to run a script from within a bat file, it has to be passed to sqlplus as parameter on single line like :

    sqlplus sysadm/sysadm@mydb @d:\script.sql
    f:
    cd f:\afolder
    second.bat

and make sure the sql script has an exit command so that after the script is run, control is returned to cmd to execute rest of the code it was given in bat file .


*************
if you are to run a cmd command in a sql script , you can use the host/ho command before the command. Its treated like cmd command if preceded by host command. like in a sql script you might have :

    insert into ...
    ho d:
    ho cd d:\afolder
    ho cd start.bat
    update table ...

and have an exit written at the end of bat to return the control to sqlplus to further execute the script lines.

Automating People Soft Instance Refreshes

Hi All,

Do you still do the PeopleSoft instance refreshes manually ?Configure the report nodes, integration settings, restore the database user and PeopleSoft application user manually after every refresh with production data ?
Well, no more ...In this post, i'll describe an approach to automate the instance refreshes ..

Here i assume you have the responsibility to restore the oracle database/schema backup in addition to taking care of people soft application.

I'll divide the steps in two categories :

1) Before Dropping the Old Database :
   
   i) Create an sql script, which backups the existing database users(with their       original passwords);
   ii) Create a data mover script(command line form) which takes backup of existing        application users       (passwords,roles, permission list, all other accesses including        every table related to people soft       security) and report node settings.
   iii)Now create a batch script which calls the sql script first, then invokes the data        mover from command   line to take security and report node tables backup and finally   include the expdp command to take a  backup of existing old database.

This way you just have to run the batch script and it does all the task you have to do before dropping the old database.

2) Drop the old database and create a new database with the same name .

3) After new database has been created :

    Create a batch file and include the following calls in that script line by line :

  i) Create a consolidated sql script with the 6 scripts, creating the psdbowner table, access id, creating           tablespaces and data files , connect id , creating required roles.
  ii)set oracle sid and import the data from dump file with impdp command .

  iii) Create a post refresh script which updates the tables with data from production to what it should be in      dev/test environment including updates to mask the emails, update the gateway url and other integration      broker tables, creating synonym for all the tables, creating the db links if any, clearing the message and      process request tables and other updates as per your project requirements .

  iv) Call data mover from command line and import the security and report node table backup(.dat file) that we took before dropping  the table.

   v) call a sql script which gathers the statistics for the tables.

   vi) Write cmd commands to zip the log folder, where logs for each and every step are created, and email to a configured mail id which you can verify from anywhere.

And this is all ... you have to call a batch file, drop and create database and call another batch file and you refresh is done !!!!

Note : If you have enough competency you can automate the drop and create of database as well and then you just have to start a batch file and wait until you receive an email of all the logs !!!

---**--- If you get stuck anywhere or need the scripts to complete the task, email me ...

Also there are other approaches to do the database refresh where drop of database is not require ..i'll explain this in another post ...