Monthly Archives: October 2010

Software. Hardware. Complete.

So recently I was browsing Walmart.com ‘s Electronics section and was amazed at the selection they have.

You want to buy a computer? They’ve got it.

You want an operating system for that computer? They’ve got it.

You want to buy a network switch and cables to link multiple computers together? They’ve got it.

You want to buy 4TB of NAS storage? They’ve got it.

You can get them all from one vendor. The switches say their certified with the OS. The computer says its certified with the OS. Your storage is certified with your OS.

You can even install Oracle database on the hardware and be fully supported by Oracle (thought not certified by Oracle because Oracle doesn’t certify 3rd party hardware).

Have you ever bought a wireless Microsoft keyboard and mouse that didn’t work right with your Microsoft Windows OS running on a PC with a sticker on it that said “Designed for Windows” ? It’s all from one vendor. Just one throat to choke, right?

So why isn’t most of your data center running off of what’s at Walmart?

Because those products might not be leaders in their category.

Because the technical support backing those products might be crappy.

Because the software might not be enterprise ready .

Just because you can buy everything from one company doesn’t mean you should.

Webinars of interest to Oracle Apps DBAs on RedHat Linux with VMware

So recently I’ve been getting notifications about a number of various interesting Webinars. Since they’re coming from all sorts of random sources, I’m sharing the links as a service to my readers.

I’m receiving no consideration or such from the companies mentioned, these are things I thought would be of interest to me, and perhaps my readers

Upcoming (all dates / times are based on Central US time zone)

Get The Facts: Oracle’s Unbreakable Enterprise Kernel for Linux Oct 26th 11am, put on by Oracle

EBS Workflow Purging – Best Practices Nov 10th 11am, put on by Oracle

How to use My Oracle Support for ATG issues Oct 27th 11am, put on by Oracle

E-Business Suite using a DB-Tier with RAC Oct 28th 11am, put on by Oracle

Top 10 Virtual Storage Mistakes Oct 28th 1pm, put on by Quest Software

On-Demand (already happened)

Oracle Virtualization Webcast put on by Embarcadero and VMware

RHCE Virtual Loopback: Unlocking the value of the Cloud put on by RedHat (pdf)

RHCE Virtual Loopback: Performance and Scalability RHEL5 -> RHEL6 put on by RedHat (pdf)

Managing Red Hat Enterprise Linux in an Increasingly Virtual World put on by RedHat

State of “Btrfs” File System for Linux put on by Oracle

Lower Your Storage Costs with Oracle Database 11g and Compression put on by Oracle

Linux Configuration and Diagnostic Tips & Tricks put on by Oracle

Feel free to share any other Seminars you think may be of use in the comments!

Oracle VM Security: Sometimes you need hip waders

Have you ever read something and thought, “what a load of crap. I had better get my hip waders out.”

Well, as a cynical jaded DBA, I have that experience regularly.

Take this Oracle.com blog post on Oracle VM where Rene Kundersma who is a Technical Architect with Oracle explains Oracle’s reasons for NOT shipping Oracle VM with a “fancy Gnome X-Window” environment:

“Oracle has it reasons to NOT ship Oracle VM with all the bells and whistles of a fancy Gnome X-Window environment. This has to do with vulnerabilities, not tested situations of software combination’s and whatever reason that makes Oracle VM not to behave as tested and intended.”

Vulnerabilities as the reason for Oracle VM not having a “fancy X-Window environment”. Vulnerabilities… really? But isn’t Oracle VM running on a special version of Oracle Unbreakable Linux (hint: yes – they’re both based off of RedHat Enterprise Linux)?

Want to get to the console of a VM running under Oracle VM? It uses VNC. Sure, you need to know the password to connect to the VNC Desktop, but guess what, the VNC traffic isn’t encrypted. The password is sent in cleartext.

Unbreakable indeed.

I find this all the more contradictory when one of Oracle’s talking points for why to use Oracle VM is Secure Live Migration which SSL encrypts the live migration (aka vMotion) traffic. My favorite line: “No need to purchase special hardware or deploy special secure networks. “

No need to deploy special secure networks! VLANs? Who needs them? We’ve got encrypted live migration!

Oh wait, in Oracle’s own Oracle Real Application Clusters in Oracle VM Environments guide, there’s this tidbit

“While Secure Live Migration is enabled by default, it should be considered that a secure connection to the remote guest (using –ssl) adds overhead to the Live Migration operation. It is therefore recommended to avoid such secure connections, if permitted. In general, a secure connection can be avoided, if the Live Migration network is inherently secure. “

Seriously Oracle, which is it?

But let’s get back to the main point Rene was trying to get across – that Oracle VM doesn’t come with a GUI to reduce vulnerabilities. Oracle’s October 2010 CPU (Critical Patch Update) was released on October 12th, 2010 and for the current version of Oracle VM (2.2.1) it lists 4 vulnerabilities, 3 of which have a base score of 9.0 (the scale is from 0.0 to 10.0, with 10.0 representing the highest severity of vulnerability). All 3 of those 9/10 severity vulnerabilities have a low access complexity (they’re easy to do) and result in complete access.

Oracle, thank you for not including a “fancy Gnome X-Window” with Oracle VM so as to reduce vulnerabilities. Given how insecure your product appears without a GUI, I shudder to think what things would be like with a GUI.

An alternative to scrambling data: Restricting access with Virtual Private Database (VPD)

Back in June, I wrote a blog post on scrambling HR data in our EBS instances . Although effective, it was a bit of a kludge – it involved an excel spreadsheet, and giving everyone the same salary and banking info.

As we went with this solution in our development and test environments, we ran into some issues where the salary data would totally screw up the benefits data as it’s calculated as a percentage of salary. The solution was effective at keeping the data secure, but it wasn’t optimal. After some investigation, we turned to Oracle VPD – Virtual Private Database – functionality. With this we are able to restrict access to certain columns (such as salary or national identifiers) to all but necessary users. With an EBS database, where every connection is connect as APPS, this poses special considerations.

I’ll cover the technical details of implementing VPD in an EBS environment. Then I’ll talk about the changes you need to make to keep things functional for your business analysts and yet keep the data secure.

First it was necessary to create a policy function. In our case this is very generic, basically just returning the predicate.

CREATE OR REPLACE FUNCTION “APPS”.”LUM_HIDE_HR_COLS” (schema in varchar2, tab in varchar2)

return varchar2 as predicate varchar2(8) default ‘1=2’;

begin

return predicate;

end;

/

Next we add a policy on the column we want to restrict access to point it at the policy function we created

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PER_ALL_PEOPLE_F’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘NATIONAL_IDENTIFIER,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;

/

In this case we’re ADDing a policy and ENABLing it, applying the LUM_HIDE_HR_COLS function on the NATIONAL_IDENTIFIER column of table PER_ALL_PEOPLE_F in the HR schema, preventing users from SELECTing data and stating that this is for ALL_ROWS.

Once we issued that, all users (besides SYS and SYSTEM) will get NULLs when they select NATIONAL_IDENTIFIER from that table. That took care of our social security number concern.

We also set up additional policies on other data:

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PER_PAY_PROPOSALS’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘PROPOSED_SALARY_N,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;

/

The above policy restricts access to PROPOSED_SALARY_N column of HR.PER_PAY_PROPOSALS. That took care of our salary data concern.

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PAY_EXTERNAL_ACCOUNTS’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘SEGMENT3,SEGMENT4,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;
/

The above policy restricts access to the SEGMENT3 and SEGMENT4 columns of HR.PAY_EXTERNAL_ACCOUNTS. That took care of our banking data concern.

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PER_ADDRESSES’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘ADDRESS_LINE1,ADDRESS_LINE2,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;
/
The above policy restricts access to ADDRESS_LINE1 and ADDRESS_LINE2 columns of HR.PER_ADDRESSES. That took care of our concern of employees looking up addresses of other employees.

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PER_ALL_ASSIGNMENTS_F’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘GRADE_ID,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;
/

The above policy restricts access to the GRADE_ID column of HR.PER_ALL_ASSIGNMENTS_F. That took care of our concern about employees looking up the pay grade of other employees.

begin dbms_rls.add_policy(object_schema => ‘HR’, object_name => ‘PAY_ELEMENT_ENTRY_VALUES_F’, policy_name => ‘LUM_HIDE_HR_COLS’, function_schema => ‘APPS’, policy_function => ‘LUM_HIDE_HR_COLS’, statement_types => ‘select,’, update_check => FALSE , enable => TRUE , static_policy => FALSE , policy_type => dbms_rls.STATIC , long_predicate => FALSE , sec_relevant_cols => ‘SCREEN_ENTRY_VALUE,’ , sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS ); end;
/

The above policy restricts access to the SCREEN_ENTRY_VALUE column of HR.PAY_ELEMENT_ENTRY_VALUES_F. That took care of our figuring out salary based on insurance coverage concern.

That’s all there is to it. You can just issue the code above in an Apps 11i instance and at that point no user besides SYS and SYSTEM can see that data.
Now that you’ve handled the technical details, there’s the issue of cloning the instances and being able to test HR functionality in development environments while still restricting the data. Here’s what we do.
In PROD, in addition to the function and policies listed above, we create a read-only user calls APPSHR:

create user APPSHR identified by XXXXXXXXXXXXX;
GRANT ALTER SESSION TO “APPSHR”;
GRANT CREATE DATABASE LINK TO “APPSHR”;
GRANT CREATE PUBLIC SYNONYM TO “APPSHR”;
GRANT CREATE SESSION TO “APPSHR”;
GRANT CREATE SYNONYM TO “APPSHR”;
GRANT EXECUTE ANY PROCEDURE TO “APPSHR”;
GRANT SELECT ANY DICTIONARY TO “APPSHR”;
GRANT SELECT ANY SEQUENCE TO “APPSHR”;
GRANT SELECT ANY TABLE TO “APPSHR”;
GRANT UNLIMITED TABLESPACE TO “APPSHR”;
GRANT “RESOURCE” TO “APPSHR”;
ALTER USER “APPSHR” DEFAULT ROLE ALL;
That APPSHR user now has the ability to select any data in the system but it’s read only (no updating).
We then exempt the APPS and APPSHR user in PROD ONLY from the policies we created:

GRANT EXEMPT ACCESS POLICY to APPS;
GRANT EXEMPT ACCESS POLICY to APPSHR;

At this point only users who connect at the database level as APPS (that would be all forms based users) and APPSHR (our HR analysts) can see the restricted data. The APPS password in our PROD environment is known only to the DBAs. The APPSHR password is known only to the HR Business Analysts / HR Developers / DBAs. All other business analysts have access to another read-only account called APPSXXXX that is NOT exempt from the security policies. With that APPSXXXX account, the regular business analysts and developers can query the database directly for all but the restricted data and can access any data via the forms that their forms responsibilities allow.
When we clone an instance, we don’t have to do any scrambling. For all of our DEV, TEST and PSUP (Production Support) instances we merely have to change the APPS password to a commonly known password and issue

REVOKE EXEMPT ACCESS POLICY FROM APPS;

Now everyone can read and write data as APPS, but because APPS now is restricted by the policies we put in place, APPS can’t see the sensitive data. The only time this causes a problem is for the HR Business Analyst or HR Developers who need a non-PROD place to work issues or develop code. For them we created a special HR cloned instance with the same security setup as PROD but with the APPS password known to the HR Analysts and HR Developers.

This solution has worked out much better for us than the scrambling. Give it a shot and let me know how it goes for you in the feedback!

It’s been too long

Wow, it’s really been over a month since I posted last. Since then I’ve attended VMware VMworld, went on a cycling and camping vacation for a week, attended Oracle Openworld, was at work a week, and dealt with a distant relative dying.

It’s been a bit busy.

During all that however, I’ve just had tons of blog post ideas floating around in my head that need to get out.

First, a housekeeping point. As Paul wrote in the comments of my previous post on Advanced Compression, using the compression and de-duplication aspects of Oracle SecureFiles DOES require licensing advanced compression. This is putting a big crimp in my plans for an end of year conversion to SecureFiles that was going to reduce our space usage, but you’ve got to stay legal.

On to other matters. VMware VMworld was awesome.  It was my third VMworld, and the best I’ve attended. Major changes took place with scheduling sessions (now first come, first serve) and the labs (all now on demand and there were 38 of them compromising about 50 hours of training). It was extremely impressive just to see the 480 on demand lab stations and performance overall was good to excellent. I attended one session on the architecture of the lab systems and I was blown away. Super super impressive.

Oracle Openworld was huge this year – 41,000 people. It’s funny how just different the two conferences feel. VMworld feels to me much more about the underlying technology. How to do cool things with the products, how to make things work faster or better… Openworld… well, it just has that big business feel to it. The sessions although interesting, weren’t generally covering that much new, at least with regards to E-Business Suite.

More on these topics later, I need to get back to providing good technical data.