EPMSxSW

I had a support ticket come in recently where a client couldn’t refresh their Planning security.  That’s always fun when budget has ended and you need people to stop changing data!   After lots of digging, logging searching, hair pulling, we found that there were two users who were once admins/app owners, that were no longer in the company.  These users had been removed from the domain, but were still in Shared Services.  We removed the users from Shared Services, but they were “stuck” in the repository.  If you’re anything like me, you cringe at any issue involving the Planning repository because one wrong move and everything implodes (no pressure).  Disclaimer, I’m giving you the steps to remove a stuck user in the repository, but I strongly suggest you use a professional (or your top notch company DBA) when performing these steps:

** NOTE: PLEASE TAKE A BACKUP OF THE PLANNING APPLICATION SCHEMA BEFORE PERFORMING ANY MODIFICATIONS **
NOTE: These queries need to be executed for each user that needs to be removed from the application schema.
NOTE: If there are any dependencies while running the delete query, delete the entries from the dependency tables.

– Stop Planning Web
– Using your favorite database client, login to the Planning application schema (Oracle), or database (MSSQL Server)

select OBJECT_ID from HSP_OBJECT where OBJECT_NAME = ‘username’;

NOTE: Make a note of the OBJECT_ID (for ‘username’ – it will be different for different users, e.g. ‘admin’ is always 50001. In this example we’ll use 12345 for ‘username’)

Delete the user from the tables. Respect this order or you may run into foreign key constraints:

delete from HSP_MRU_MEMBERS where USER_ID = ‘12345’;
delete from HSP_ANNOTATION where AUTHOR_ID = ‘12345’;
delete from HSP_USERS where USER_ID = ‘12345’;
delete from HSP_ADHOC_OPTS where OBJECT_ID = ‘12345’;
delete from HSP_OBJECT where OBJECT_ID = ‘12345’;
delete from HSP_USER_PREFS where USER_ID=’12345′;
delete from HSP_USERSINGROUP where USER_ID=’12345′;
delete from HSP_ACCESS_CONTROL where USER_ID=’12345′;
The steps above successfully removed the user from the repository.  We refreshed security with no issues (Hallelujah!)

We then went to the budget scenario to change the groups to “read”, and every time we tried to edit the group, we got a blank screen with the words “An error has occurred”.  By the way, those are my favorite errors.  NOT!  Back to the hair pulling.

After working with Oracle support, we found a known bug that prevents  you from editing security if you have more than 25 users or groups.  Luckily, there is a very easy workaround.  When you click on the “+” to add support or click on a group and select the pencil to edit the group, click on the word “Group” at the top of the window, and it will sort the groups and then allow you to change the security.  Weird, I know, but it seriously works.

For more information, you can reference Oracle Doc ID 1984263.1.

Until next time!

~Sum