Posts Tagged ‘PCI Credit Cards’

R12 Security and the Auditors from Mars

October 9, 2010

Let’s face it auditors are not from this planet. Rather like the famous movie War of the Worlds, they bring fear and terror wherever they go. Are they from Mars? Probably, although the one thing certain about auditors is the uncertainty they bring with them, together with the general fear and terror they instill.

I thought the picture below was a rather appropriate one of a hand reaching out with the entire planet in its grip…..when the auditors are around you sometimes get the feeling that your entire company is under the grip of these terrifying Auditors from Mars…..


Now this column seems almost like a recommendation for movies these days, so if you’ve not seen War of the Worlds, get it on DVD. It’s a great movie.

Now as someone that had not one, not two, not three but FOUR different companies looking at my R12 Upgrade Project (AND FINDING NOTHING!!!!!) I have to say I’ve learned to deal with them pretty well over time. Our first R12 audit was from our internal audit guys, who wanted to learn R12. It’s important for internal staff to learn, so we were happy to help. We then had an audit from E&Y as our R12 Upgrade was part of a large Program…….we then had a pre-Attestation audit from PWC to make sure the project was OK from an attestation viewpoint. Finally we had another guy from a large Consulting Organization looking at it and never quite figured out why. (Guess they also wanted to learn how to do an R12 Upgrade). Amusing that these audits cost 30% of the actual cost of the upgrade and ended up commending the R12 Upgrade 🙂 One audit surveyed random users with 20 out of 21 commending the team, with the other neutral. It was a lot of work providing the auditors everything they needed, but I have to ask – 4 audits???????

Now here’s a question for you. If you saw an auditor drowning in a river, would you run to get the life belt 30 feet away from the river, or run to your car 50 feet away to get your video camera? Alternatively if you’re a Project Management Professional (PMP) you’d have planned ahead and kept a bag with bricks in your car beside your video camera. As you throw the bag of bricks the auditor is thinking “What a nice guy throwing a bag to keep me afloat”, whilst you are thinking “What a sucker, that’ll help him sink……”


I have to point out you should not take this blog too seriously, as the above may get you into some serious trouble, although with a good lawyer you’ll probably get off with a small fine as it could be seen as justifiable or reasonable……. You may be able to even make some money back to pay for your legal fees from the video on America’s favorite home movies show.

There is no question that Auditors are from Mars, causing fear and terror wherever they go………well recently they struck on an area that probably too many people just don’t take seriously enough or don’t even realize is their responsibility. This area is Data Security within your ERP regarding access of your Support Team. Now what is worse is that when auditors do raise this they generally have a very good point and your best interests at heart.

So we’re almost going back to our Koan stuff (see the Zen column). How do you do support Oracle ERP Production when your support team can’t access Production due to information and data security concerns?

That’s a difficult question and I’ve yet to see any really effective, comprehensive solution either in a blog, white paper or from Oracle themselves (at a reasonable price – I had a discussion with Oracle last week and almost fell off my chair – the Oracle Sales guy might as well have had a mask and a gun…….).

So you’ve got your auditors on the one hand, a whole raft of legislation on data security (your legally obliged to follow – it’s not a matter of choice) and a very limited IT budget. So how do you square the circle to keep every party happy?

So look at your typical ERP these days. It’s a monolithic, super system:

  • Financial performance – if you’re a listed company that causes major data security issues to prevent internal staff dealing in shares, because they know you had a record quarter looking at your General Ledger before it was announced or worse short your shares if they know there is bad news coming. That’s real company loyalty for you.
  • It probably has all your HR information in there, including performance appraisals (some none too pleasant quotes on how a staff was involved in some questionable activity), potentially medical information, salary of course and a whole lot more.
  • You might have Procurement information, so a huge amount of confidential contracts also.
  • And most ERP sites run Payables and Payments. Do you know the potential fines you can get if you lose bank information? Or worse if you lost your Supplier bank information and the damage that could do to your company’s reputation, not to mention legal liability.
  • Ever worked out that your Accounts Receivables is a very good indication of a customer’s credit rating? After all, you kind of know who’s got cash flow problems……And the Bank Account information is everywhere. Are you really looking after that securely? Your customers won’t be too impressed if you lost that and would probably never do business with you again.
  • Or are you capturing credit card information as part of say your Order Management/Accounts Receivable process…….Have a look here if you are.

Data Security is an absolute minefield for any IT department responsible for Oracle ERP.

There is just so much legislation on this area that most are blissfully unaware of:

  • Sarbanes Oxley
  • PCI
  • European Directives
  • Individual country legislation
  • And a whole lot more…..

This is serious business and can really hit you hard if things go wrong – blissful ignorance is no legal defense. And yet I bet you haven’t reviewed anything from a legal viewpoint on this……

There’s a couple of interesting links at the bottom. Choicepoint had 163,000 customer records compromised. That cost $15 million US Dollars, not to mention reputational damage and cost of implementing proper data security. The data involved? Names, Social Security Numbers, Birth Dates, Employment Information – sounds pretty much like Oracle HRMS right – they got fined for not protecting exactly the type of information you’re holding…..Or how about the California hospital that got hit with $250,000 penalties just for reporting a breach late……

Not in the US, so don’t really care? Have a look at this link. The UK has put in place legislation for fines up to GBP500,000 (equivalent to around US800,000) for data security breaches. Or HSBC, one of the largest banks in the world, that got hit with a fine of almost 5,000,000 US Dollars.

So not in the US, not in the UK, still don’t care. How about the following:

A German company fined 137,500 Euro for asking employees why they were sick and storing the information. If you run absence management, are you potentially collecting scanned medical records and are they properly secured?

Still not convinced? Forrester estimates a cost of $155 Per record for data security breaches. Lose 100,000 records and you’ll be looking at $15.5 Million US Dollars legal liability and probably looking for another job…….I guess that’s finally caught your attention? Legislation has been enacted or is being enacted worldwide. Sooner or later, it’s going to hit your country too.

Now I have to state from the outset this article does not provide all the answers. This is a highly specialized area which goes way beyond my own personal capabilities in this area. So if you’re looking for all the answers, hire a specialist. Actually read hire multiple specialists over a number of years. Security is a massive task to do well and therefore effectively.

The aim of this article is simply to show what we are doing and have done to implement a more robust security around our R12 Oracle ERP that allows us to support the Business, whilst still trying to do our best to be secure.

This purely looks at using roles to restrict database access for support people. This article does nothing more and that has to be stressed at the outset.

Hopefully it can get you at least part of the way there, giving some additional protection without costing a fortune.


Step 1 – Find out what access was out there historically

Our starting point was to figure out from an Applications side what access our IT guys had to Production (and note when I am talking IT I am talking ERP Support only in this column. Other areas such as DBA, System Admin, etc are beyond the scope of this article). Luckily we were already very heavily locked down. We don’t have any access to modify any part of Oracle ERP and I’d say that’s a very good thing. It makes me sleep easier at night knowing that there is no way my staff can log in to Production, “cause there’s a really easy way to fix something………”. Every change we do to Production is through a formal turnover to separate groups, fully approved, appropriately segregated and audited. That keeps our Production system safe. (All code is also in source control using Subversion just in case we ever need to rollback a change).

The next step was to see what read-only access was given to Support Teams. Now over the years, many IT systems just grow and grow, access is given for support purposes and no-one really thinks too much about it.  I was relieved to see that the Support Team had no access to Oracle ERP through the front end applications.

I thought I’d throw in a useful note at this point. We have a custom program that reports quarterly on all menus/functions/applications by person. It replaces the standard Oracle System Administrator reports that generate listings the size of War and Peace. This report is reviewed both in IT and Business to ensure that each user has the appropriate access for their job. We also tied this to staff movements within our HRMS System, so that when someone moves, this is highlighted as a potential access control violation and can be assessed before it even happens (using date-tracking in HRMS). The other useful point here is that this quarterly check also ensures complete segregation of duties. Finally by using a custom report and showing dates menus/functions were changed, it vastly reduces the work to check each menu against each data item. Actually a single maximum date at the top of the report means the users only check this and can thereby can confirm no changes from the last review. Not perfect, but keeps our auditors happy. This is a cheap and cheerful approach without buying Oracle’s GRC Suite although I think the GRC Suite looks very interesting indeed.

With our front end applications secure from our Support Team, we then started to look at the database access. Now I know some people say IT Support should have no access, especially the evil DBA Vogons from Vogsphere, who according to the book The Hitchhikers Guide to the Galaxy have “as much sex appeal as a road accident”. On the other hand others say “With no access, I can’t see anything, therefore no support’. We’re back to Koan (See R12 Patching and the Art of Zen) here – How can you fix something you cannot see? (To our DBA friends I heard the new 11G Database Release 2 is self-managing, so exactly what do you guys do???? – that should provoke a few comments 🙂  )

Our support team had read-only access using database roles to Production, but when we looked at the roles created we concluded that these were too broad and needed some serious work. Roles are simply access to database tables/views or other objects given to a specific support person by the evil DBA Vogon group.

Note: For those interested in Vogons, it’s from a movie called Hitchhikers Guide to the Galaxy. Here’s a movie recommendation. Don’t see this as it sucks big-time (although the original TV series and radio show and book were amazing).

Now our next step was pretty simple. Monthly we review database access to Production and with the count of accesses by support staff, we found that many actually just didn’t need access to Production, because the support volumes regarding database queries by support staff were low (or zero). We cut these immediately.

Again doing a review monthly (only takes ten minutes) ensures that as people leave, move job, etc your access controls remain updated and correct. Again this keeps the auditors happy.

At the end of this step, we defined a Security Access Matrix, showing each Database Role against each Support Staff, shown below. This was our first baseline.



Step 2 – Defining Your Database Security Architecture

Ignore the fancy title. What this means is simple. You need to look at:

  1. Your Support Team Structure
  2. Your Applications
  3. Your Required Access

So let’s take an example. We run a fairly large ERP footprint:

  • HRMS Suite (HRMS, Payroll, Learning, Time & Labor, Self Service, Advanced Benefits, iRecruitment)
  • Financials (General Ledger, Payables, Payments, Fixed Assets, SLA, EBiz Tax, Accounts Receivables)
  • Procurement (Purchasing,  Services, Inventory, iSupplier)
  • Projects
  • A large number of custom modules
  • We’re going to add CRM (hopefully Fusion) to that in 2011.

Our Support Team is organized along classic line of business model (Procurement, Finance, HRMS). It is further segregated so that certain support people provide support for say HRMS/Payroll, others do Self Service, etc.

Now the problem we had was two-fold:

  1. Too many Support people had too much access
  2. The data protection needed to be much stronger

Our approach was simple, but effective. Each role would be along the lines of module or modules required for logical support. So we would set up roles for Procurement, AP/Payments, General Ledger, HRMS, etc.

Now this for us still didn’t get us to where we wanted to be. What was going to be in these roles database object wise?

Our next task was to then define exactly what that role would have access to table and view wise. Your average ERP Module can have several hundred tables (or even more….). Now you can give a blanket access if you want, but I think that’s overkill. It’s hard to audit and hard to police. Your average support person needs access to only a limited number of tables. That is what you SHOULD define.

In addition, obviously there is some cross-cutting of access between modules and tables, so for instance you may have to grant GL_CODE_COMBINATIONS across various roles.

In our final step, to the horror of many, we decided to NOT give access to standard sensitive tables, but instead to build views WITHOUT KEY ELEMENTS. Now a lot of support people are going to scream at this, but I need to ask, if you have a support call, do you really need access to the guy’s date of birth or performance or disciplinary records or even Full Name? I doubt it for 99.99% of cases.

Here are a couple of examples of securing tables into masked views:

Instead of giving access on PER_ALL_PEOPLE_F we removed all information except the Employee Number. No access to Social Security, Performance, Home Phone Numbers or anything else.

Instead of giving access on the Bank Tables, we removed all information and masked the bank account.

Instead of giving access of PO_VENDORS, we removed all information and simply gave the VENDOR_NUMBER.

What you need to be careful of is the other tables that you really wouldn’t expect to hold sensitive information. A prime example of this is the workflow tables.

To secure workflow tables we ensured that each role only had access to relevant workflow ITEM_TYPES. This was a very simple, yet highly effective way to secure this by role.

The great thing about this is that we got our support people to define these roles in a simple spreadsheet. This gets you your buy-in from the very people who would be screaming about cutting access. After all they are now the ones tasked with defining it (obviously subject to reviews within IT and approvals by Business).

We then decided that we’ll make this formalized. This has a lot of benefits:

  1. It provides an agreed document of what a role has access to data-wise
  2. It provides an agreed document between Business and IT on that role/data
  3. It provides a document that can be agreed and signed by all parties
  4. It makes you think about your data and protection of said data in a disciplined manner
  5. It provides the basis for all future access to sensitive data by IT Support Personnel
  6. It provides a clear statement of who has access to what in a transparent, controlled manner
  7. It provides protection from your auditors

We decided to record other details in this spreadsheet. (Each role has a separate spreadsheet).

The spreadsheets had the following columns:

  1. Object Name
  2. Database Owner
  3. Object Type
  4. Business Owner
  5. Description
  6. Object Classification (Custom/Standard or Secure. A Secure Object was a restricted, masked view of a key table)
  7. Data Security Risk (Critical, High, Medium, Low)
  8. IT Review Date

A separate Tab (Security Details) on the Excel Spreadsheet held additional details

  1. Object Name
  2. Base Table
  3. Masked Columns (.e.g. vendor name, bank account, social security number, etc)
  4. Date Approved

The Spreadsheet was split into two sections – data objects and reference objects. (Reference objects are the pre-seeded or setup tables within R12). A further tab showing review actions was included to show that this was a serious, considered exercise to the auditors.


 Suddenly, without even realizing it, you’ve just created a very precise, auditable and comprehensive technical security framework for your R12 Oracle ERP that will vastly improve your security of data, whilst still allowing effective support. This also provides a very powerful data classification framework for both you and your users. Even more interesting, by putting a risk score on each object, you can actually see the overall risk access rating of each role, allowing you to make informed decisions on how widely this role is given to support personnel (or not).

If you have knowledgeable, motivated people, this doesn’t take a huge amount of time. I am extremely lucky to have such a great team of people.

Once the roles were defined, signed off and agreed, the DBA Team created the roles/access very easily.

Note that once you have these documents in place and have implemented the framework within your database, these documents (and hence the roles within the database) should be strictly controlled otherwise all your work will have been for nothing……

So after not a huge amount of work we had roles for most applications that were nicely segregated, secured and with all key information masked appropriately. This can be seen in the Security Matrix below:


(Note how the General Database Access role was entirely removed once we worked out roles/access. The above is only a sample, not the real resources/roles).

So in a couple of weeks we went to very fine grained security on our Production System, without spending vast sums of either cash or time.

Our overall security had vastly improved for our Support access to Production. Indeed the data had really been masked very effectively yet we still could do effective support, without disclosing any critical data to our IT Support Staff.


Step 3 – Defining Your Access Security Processes

Without policies and procedures any security framework will fall to bits pretty quickly. Now this article isn’t really about processes, but here are the key aspects to consider. Security is not a one-time process. It’s a critical ongoing process that needs to have strong controls to keep you secure going forward:

  • When people join our company, we ensure that non-disclosure clauses are signed the very minute they step in the door.
  • Security is not just about securing your database or applications or data once.
  • The Database Security Architecture showing each module/access to tables/views should be agreed and signed off by the Business. It is important that Business understand the access IT has to data in a transparent manner. Too many IT organizations think they own the data. They don’t, they are merely custodians.
  • When new access is required for a Support person, or additional access is required, there must be a strong process to seek authorization from the Business for that access. This process should be formalized, ideally automated and fully auditable. We are currently implementing Oracle’s Identity Management product and intend to integrate the process into this.
  • When a person changes role or position, it is important that any access is immediately discontinued. Linking your process to the HRMS System gets you part of the way there and avoids any embarrassing moments when the auditor asks why a person that was fired still has access to your HRMS database details……..
  • When the Database Security Architecture documents need adjusted to add a new table/view, this must go through a formal change control process, with appropriate sign off by the Business users.
  • The DBA Team should not update any role to add/modify any table/view unless the change has been formally signed off in the Database Security Architecture documents. All requests for changes in the database should go through a formal turnover process which is fully auditable and approved by IT Management and Business Users.
  • A monthly review of Support Personnel/Access/Roles should be done by the person managing the Team. This doesn’t take long and ensures that only those needed access have access. Documenting this process protects you when the auditors role in. (Note the Oracle Database can be configured to automatically email you the roles/people/number of accesses)
  • There is one other interesting area. If you formalize the Data Architecture documents and make that part of every project before go-live, you’ll also ensure that support for go-live is properly thought through and adding new systems does not compromise security going forward.

 Step 4 – Encrypt Non-Production Databases

I’ll cover this in another article (working title is Securing your R12 using Captured Alien Technology from Roswell), but the key point here is that there is no point in protecting your production database, if you then clone and have all the same access in copies of production……..Security is about all your databases, not just Production.

 Step 5 – Securing all Layers

Again, this is beyond the scope of this article, but keep in mind that the best security is a layered approach. Your Oracle ERP security framework must involve all of the following:

  1. Network
  2. Servers (Linux, Unix, etc)
  3. Oracle ERP (across the modules)
  4. Individual Modules (each with their own security models)
  5. Database
  6. Security Patch Policy across all areas

Security is only as strong as the weakest layer…….A couple of the best articles that take a holistically approach are found in the Related Articles Section at the end of this article.

 Step 6 – Future Directions

Our goal in this article was to make others aware of simple steps to improve security for Support Staff supporting Production systems by using role based access at the database level.

However, as we looked more and more on the security, and as we became more aware of the challenges, but also consequences of getting it wrong, we see other opportunities that remain very interesting future initiatives that we will seriously look at.

We’re interested to lock down database access to restricted IP addresses. This will stop password sharing, but also ensure that even if an account is compromised, unless you sit in the person’s chair, you’re still going to have difficulty accessing. Not bullet proof for sure, but it adds another layer in our security.

We’re interested in auditing the database statements of support personnel and perhaps moving database roles/custom built to a more secure and robust solution. It looks like Oracle’s Audit Vault may get us there, but at a heavy price. This can implement roles in a more secure manner, provide full auditing, reporting, restrict data by IP address and a whole lot more.

We do already have data scrambling and encryption on our non-Production databases, along with formalized controls and processes. However as one article points out, the days of custom scripts are coming to an end (or is it just an Oracle sales pitch to get you to buy the products?) and we are seriously looking at a couple of options:

  1. The Oracle Enterprise Manager with Data Masking Pack. This allows you to setup and execute data masking. With Grid Control you can then have central data masking across all Non-Production databases. So yes they are trying to sell you even more, but then again, it’s better and cheaper than a 15M US Dollar fine for non-compliance…..
  2. Oracle Application Management Pack for ERP – This is a pretty broad release that provides functionality for Configuration Management, Application Performance Management and Service Level Management (including related to this article obfuscating sensitive data). This will allow you to clone and scramble data in a single process.


 One area we are interested in looking at in the respect of functional changes, setup, etc is the GRC (Governance, Risk and Compliance) module. This provides a lot of the audit capturing and reporting capability as a by-product of the general setup process. More details can be found at the end of this column. We haven’t fully evaluated this product but it does look interesting. Again Oracle has added analytics that could potentially make this a complete, all round solution (subject to our evaluation).

Oracle Identity Management. We are so interested in this product that we actually went out and bought it. This will once fully operational, control access to all our key systems, allowing us to comply with key controls from both auditors and legislation. We intend to use this directly linked to our HRMS system to basically provide all provisioning of user accounts, access, etc. This is linked not only to the triggering HR Process, but also directly to the Business owners who grant access to the modules, all using Oracle workflow technology. We’re also having a close look at the Identity Management analytics, although have not bought this yet. The two products combined can not only provide very strong security, but save a huge amount on the cost of our compliance, which becomes more and more every year……



Our final exercise is to get every Support Person off Production completely. We’re working on nightly, automated clones with data scrambling. At that point we expect to remove almost all access to Production. After all you have everything you need to do your job as a support person, bar critical calls. This is where we really want to be, but whether we get to that point remains to be seen, but we’re certainly going to try.

Of course, we’ll have all the usual security exercises on database hardening, ERP hardening, Linux hardening, segregation of duties, access control, etc going to ensure we have continuous improvement across these key areas.

I hope you’ve enjoyed this article and hopefully it provided some useful insights into how we are improving security and easing our compliance burden overall. This article has mainly been on securing data in Production from support, but I guess I’ve gone off on a tangent into some other closely connected areas also……

 I’d be very interested also to hear comments from others, as I think security is such a vast topic that really others views (please do comment) would be very helpful.

So what are you doing on security of data encryption/access to production data?

I’d very much appreciate anyone to comment. (I have written this column in the hope of actually getting feedback and learning what others are doing to improve what I also do 🙂  )

And now, if you’ll excuse me, I’m off to apply a security patches to Production before the PWC Auditors from Hong Kong (awesome city) role in for a Penetration Test in October……..I like to call them my Loser friends (nothing like poking fun at the auditors….the loser buys lunch……), but they are immensely smart guys (as most PWC folks seem to be) and security is always a moving target requiring continuous improvement, so it’ll be interesting to see if I put one over on the auditors yet again (where they find nothing) or they finally put one over me and I need to buy them lunch…….

Of course either way, our company will be a safer and better place, security wise.


Related Articles

I hope that as you start to consider security in more depth, you may find the other articles relevant and useful reading also. My thanks go to the generous authors on a number of the articles below for sharing their knowledge for the wider Oracle ERP (and also Oracle Database) community.

Oracle Audit Vault

Oracle Governance, Risk and Compliance

Best Practices for Securing Oracle E-Business Suite Release 12 (Note 403537.1)

Oracle Identity Management

Oracle Identity Management Analytics

Oracle ERP Management Tools and Solutions

Oracle Applications Management Pack (with Data Scrambling)

Oracle Enterprise Manager 11G

Oracle Enterprise Manager 11G Data Masking Pack

Security Breaches – US Company Penalty

Security Breaches – UK Data Protection Penalties 1

Security Breaches – UK Data Protection Penalties 2

Security Breaches – California hospital Penalties

Security Breaches – HSBC Penalties

Germany Company Penalties

Data Masking – Strengthening Data Privacy and Security

Solution Beacon Security Column

Solution Beacon Security Best Practices

Project Lockdown – Securing Your Database

Recommendations for Leveraging the Critical Patch Updates

Oracle Security Column – Technology Network

Mask your Secrets using Oracle Enterprise Manager

Oracle Security Column – Documentation and Best Practices

Oracle Security Column – Critical Patch Updates and Security Alerts