I recently worked with a client who did not like to give out additional EBS responsibilities without going through a convoluted approvals process.  Whilst I commend them for this approach to system security, they were let down in two key ways.

Firstly, this was a development system.  I appreciate that getting into practice with your development system is important, BUT employing a consultant on a daily rate, and then having them wait for four days before giving them access doesn’t make much sense to me!

Secondly, and most importantly, they gave me the APPS password for the database in ALL the environments! So, I had a database login for all the environments, but a query only
user for EBS access – quite how they intended for me to access the concurrent requests to change them was beyond me!

So, I started to look at how the responsibilities were assigned to the users in the database, which is defined within the Workflow directory service, specifically in WF_LOCAL_USER_ROLES.  So, on my own system (I can’t emphasize that enough – I would NEVER advocate running this on a system you shouldn’t), I put together an experiment to see if I could give any user ‘System Administrator’ responsibility.  The result of my experiment can be found in the attached files with this post.

Based on an inbound user ID (and the user needs to be setup within the Workflow Directory Service first), the code derives the originating system and system ID for the record (user, person or party) that you want to grant access.  The code then inserts a new record in the local user roles table for the system administrator role.  Checking the contents of the table afterwards shows that the user now has the appropriate responsibility.

Once the code has run, you will need to commit the transaction before continuing.  Logging into the system as that user will now show that they have System Administrator responsibility.

As mentioned above, NEVER run this in an environment that you shouldn’t.  I’m a little wary about releasing the code here, but not only does it highlight how to give yourself responsibilities through the database, I’m hoping that it will also help (or at least flag a warning) to DBAs and other system administrators – by giving out a database password, it opens up the whole system in this way.  If you are a DBA or responsible for an EBS implementation, it’s probably worthwhile checking on a regular basis who is a system administrator, and what other roles are being given out within your system.

As ever, comments always welcome!

SQL script

This entry was posted on Thursday, November 13th, 2008 at 4:20 pm and is filed under Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.