Bypassing Privileges with Oracle Database Express Edition 11g Release 2

by Chris Rucker, Data Scientist

The basis of this project was to create a link between two disparate database servers in order to cross-validate row counts.

Oracle Database Express Edition 11g Release 2 is described as an "entry-level, small footprint RDBMS" based on its big brother Oracle Database Edition 11g Release 2.

Oracle Database Express Edition (XE) addressed the immediate need by myself to compare the record counts of like tables loaded from different networks where I did not have sufficient privileges to build database links.

The idea was to introduce an SQL minus script into XE subtracting the rows of one table in Server A from its counterpart in Server B or vice versa.

Upon completion of the project, I found that I could bypass certain DBA-enforced rules simply because I was sitting behind the network and using a free third-party database server, namely XE.

Firstly, I downloaded and installed XE from Oracle's website.

You may need an account, but that is pretty easy to obtain.  And installation is similar to installing other databases in terms of the "tnsnames.ora" file, etc.

I used SQL*Plus to access XE from a Windows CLI with a system name password similar:

sqlplus system@xe/oraclexe

Secondly, I established links to the two disparate databases once XE was up and running similar:

create public database link <alias A> connect to <owner A> identified by <password A> using `<server A>`; create public database link <alias B> connect to <owner B> identified by <password B> using `<server B>`;

So XE now sits between Server A and Server B which are linked together by database links.

And now I can run my minus scripts and spool them similar:

select <columns> from <tables> where <conditions> minus select <columns> from <tables> where <conditions>

I received an "ORA-01031: insufficient privileges" error upon trying to establish a public database link in Server A to Server B which made it impossible to run the minus script and perform cross validation.

So, installing XE and creating the links on XE bypassed any database administrator privileges that I might normally need to make the two databases shake hands.

The second unexpected benefit of running XE was the ability to run subroutines like procedures, functions, and triggers after creating copies of tables from Server A/B on XE similar:

create table <table> as (select * from <schema><table>@<dblink>);

Lastly, Oracle Database Express Edition 11g Release 2 allowed me to create database links, run my scripts, and create procedural language (i.e., PL/SQL) where I ordinarily would have encountered "insufficient privileges" errors.

Not bad at all for a free database.