jump to navigation

Deleting old User Profiles January 10, 2012

Posted by Duncan in Administration, PeopleSoft, Security.
trackback

Vanilla databases often contain 150-200 ‘example’ user profiles. While these can be useful to clone as a starting point early in an implementation, they’re frequently left dormant as the project continues – and sometimes still exist in Production post go-live.

It’s a straightforward task to lock the accounts, but once you have your security setup in place and your own template user profiles to clone then these ‘example’ accounts no longer serve any purpose. Here’s an easy way to delete them.

First of all, a warning. Before you delete anything make sure that you have adequate backups and have performed a trial restore recently.

For a little while now (at least from Tools 8.49) there has been functionality to delete inactive users. It classes an inactive user as one who hasn’t logged on for a set amount of days (using the LASTSIGNONDTTM field on PSOPRDEFN). You can check for user profiles that haven’t logged in for a long time (say, 2 years) using SQL like this:

select OPRID
     , LASTSIGNONDTTM
  from PSOPRDEFN 
 where LASTSIGNONDTTM <= sysdate-730

(note: sysdate-730 is approximately 2 years ago)

You’ll probably find that the list contains none of your users, but a lot of the ‘example’ accounts. These are the accounts that will be purged, so make sure that you’re happy that there are no important accounts in the list.

There are also a lot of example user profiles that have never signed in who therefore have a null LASTSIGNONDTTM. These need to be updated so that the process will also purge them:

update PSOPRDEFN 
   set LASTSIGNONDTTM = '01-JAN-01 00.00.00.000000000' 
 where LASTSIGNONDTTM is null

Once you’re happy with the purge list, we need to setup the process. Go to:

PeopleTools > Security > Password Configuration > Password Controls

Under the Purge User Profiles heading enter the amount of days that you’ve chosen (730 in my example) and click Save.

Then either click the Schedule button, or go to:

PeopleTools > Security > User Profiles > Purge Inactive User Profiles

Select a Run Control ID and run the Purge process. It’s not particularly fast (as it’s done via CI), but if you run the following piece of SQL you’ll be able to see your unneeded OPRIDs slowly disappearing:

select count(OPRID) 
  from PSOPRDEFN 
 where LASTSIGNONDTTM <= sysdate-730
Advertisement

Comments

1. leexx255p - May 28, 2013

Hello! I moved to the PeopleSoft functional side for the past 5 years. Although it is important to keep user profiles pure and clean, I wonder the actual implication of purging inactive profiles. In the organization that I work for, workflow is used in more than 6 modules and inactive user profiles are quickly deleted. The combination leaves no trail of approvals done by employees who left the organization – not possible to link to EMPLIDs of such employees to know who they were (other than just their userids in the transaction tables). Wouldn’t archiving inactive profiles a better feature for Oracle to deliver? Thanks!


Sorry comments are closed for this entry

%d bloggers like this: