Ultimate script to automate system administrator access

Most Oracle developers and analysts don’t have system administrator access in Oracle Applications production instances due to company policy or SOX/PCI compliance. That lack of access trickles down during every clone process, and each fresh copy requires setting up user accounts all over again. Most of us have to log into Oracle Applications and manually add all of the system administrator access by choosing the different responsibilities we need.

At my current job, I have access to a reporting instance. It is a nightly refresh of all production data. So, it’s extremely useful for testing code and new setups before moving into production. But, because it is refreshed daily I had to automate system administrator access in Oracle Applications.

The reporting instance I often used has forced me to rethink this process of adding system administrator access. I didn’t want to manually update my user account each time. I’m a techno-functional-developer, so I have very few responsibilities granted to my account in production (I’m way too dangerous), but may need a variety of responsibilities depending on my current project.

So, I wrote a script to automate system administrator access in Oracle Applications. It does the following:

  1. identifies the username being changed
  2. creates a list of responsibilities to be added
  3. creates a table of profile options to be set
    • Java Color Scheme
    • Diagnostics
    • Personalizations
  4. updates profile options from table in #3
  5. adds several hardcoded roles
  6. adds responsibilities based on the list from #2

 

This script could be used to:

  • add system admin access following a clone
  • learn some PL/SQL basic code structures
  • onboard a new analyst or developer or consultant (just adapt the list of responsibilities)
  • automate granting responsibilities to a type of user (for example, if your accounting group is always bringing on new GL accountants who always need a specific set of GL responsibilities in different ledger sets)

One thought on “Ultimate script to automate system administrator access”

Leave a Reply

Your email address will not be published. Required fields are marked *