Preamble

There are quite a lot of available options to secure your database and they are all about security and confidentiality. All solutions that covert those two distinct subjects are often club in security solution term.

First step is to secure your Operating System and database by applying latest security patches (OS and database) and applying best practices security rules (close unused OS ports, remove unused OS/database accounts, strong passwords, …). Then you can imagine to put your database server in a De Militarized Zone (DMZ), encrypt SQL*Net protocol with Advanced Security option and filter database accesses with Database Firewall.

You must then strictly control who has authorizations to access to your database (different authentications are available) and strictly control database system and objects privileges given to users.

Once done for confidentiality you have following options:

• Virtual Private Database (VPD)
• Oracle Label Security (OLS)
• Oracle Database Vault
• Transparent Data Encryption

Virtual Private Database and Oracle Label Security restrict data that different users can see (rows and columns). They provide fine-grained access control on objects to your sensitive data. If getting lost when to choose one of these products check in 2 Day + Security Guide Oracle at this page.

Oracle Database Vault restricts access to specific areas of your database to users, including users who have administrative access (i.e. DBA and SYSDBA roles), it also provides fine-grained access control. This product is mainly used to separate duties between normal DBAs (that can be from another company) and applicative people that handle secure data (salaries, credit card numbers, …).

Transparent Data Encryption encrypt figures of your database and backup files you generate. It avoids for example OS super users (root) to search at OS level in your datafiles to retrieve confidential information or a thief to use your stolen backup tapes.

To Completely describe Oracle offer it is worth to mention Oracle Database Masking to remove confidential data from copy of production databases and Oracle Audit Vault to manage a secure repository of audit data (forbid, for example, DBAs to remove their own trace of audit).

Please note that all those options are non-free and you must purchase them either as option of Oracle Enterprise Edition (Database Vault, Transparent Data Encryption, …) or as completely separate product (Database Firewall, ..).

Database Vault installation

Normally product should not be installed and you can confirm it with something like:

SQL> SET lines 200
SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
 
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle DATABASE Vault                                            FALSE

To install it, stop database, listener and database console and enable Database Vault link product with:

[oracle@server1 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@server1 lib]$ make -f ins_rdbms.mk dv_on lbac_on ioracle
/usr/bin/ar d /oracle/software/rdbms/lib/libknlopt.a kzvndv.o
/usr/bin/ar cr /oracle/software/rdbms/lib/libknlopt.a /oracle/software/rdbms/lib/kzvidv.o
/usr/bin/ar d /oracle/software/rdbms/lib/libknlopt.a kzlnlbac.o
/usr/bin/ar cr /oracle/software/rdbms/lib/libknlopt.a /oracle/software/rdbms/lib/kzlilbac.o
chmod 755 /oracle/software/bin
 
 - Linking Oracle
rm -f /oracle/software/rdbms/lib/oracle
gcc  -o /oracle/software/rdbms/lib/oracle -m64 -L/oracle/software/rdbms/lib/ -L/oracle/software/lib/ -L/oracle/software/lib/stubs/   -Wl,-E /oracle/software/rdbms/lib/opimai.o /oracle/software/rdbms/lib/ssoraed.o /oracle/software/rdbms/lib/ttcsoi.o  -Wl,--whole-archive -lperfsrv11 -Wl,--no-whole-archive /oracle/software/lib/nautab.o /oracle/software/lib/naeet.o /oracle/software/lib/naect.o /oracle/software/lib/naedhs.o /oracle/software/rdbms/lib/config.o  -lserver11 -lodm11 -lcell11 -lnnet11 -lskgxp11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11
-lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11  -lvsn11 -lcommon11 -lgeneric11 -lknlopt `if /usr/bin/ar tv /oracle/software/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap11" ; fi` -lslax11 -lpls11  -lrt -lplp11 -lserver11 -lclient11  -lvsn11 -lcommon11
-lgeneric11 `if [ -f /oracle/software/lib/libavserver11.a ] ; then echo "-lavserver11" ; else echo "-lavstub11"; fi` `if [ -f /oracle/software/lib/libavclient11.a ] ; then echo "-lavclient11" ; fi` -lknlopt -lslax11 -lpls11  -lrt -lplp11 -ljavavm11 -lserver11  -lwwg  `cat /oracle/software/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /oracle/software/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lztkg11 `cat /oracle/software/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11
-ln11 -lnl11 -lnro11 `cat /oracle/software/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `if /usr/bin/ar tv /oracle/software/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo11"; fi` -L/oracle/software/ctx/lib/ -lctxc11 -lctx11 -lzx11 -lgx11 -lctx11 -lzx11 -lgx11 -lordimt11 -lclsra11 -ldbcfg11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11 -locr11 -locrb11 -locrutl11 -lhasgen11 -lskgxn2 -lnnz11 -lzt11 -lxml11  -loraz -llzopro -lorabz2 -lipp_z
-lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -lippcpemerged -lippcpmerged  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11  -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11 -lcommon11 -lcore11 -laio    `cat /oracle/software/lib/sysliblist` -Wl,-rpath,/oracle/software/lib -lm    `cat /oracle/software/lib/sysliblist` -ldl -lm   -L/oracle/software/lib
test ! -f /oracle/software/bin/oracle ||\
           mv -f /oracle/software/bin/oracle /oracle/software/bin/oracleO
mv /oracle/software/rdbms/lib/oracle /oracle/software/bin/oracle
chmod 6751 /oracle/software/bin/oracle

Then restart database, listener and database console.

Configure Database Vault paid option with Database Configuration Assistant (dbca), make sure you have an entry in /etc/oratab for your database unless option are not accessible. No screen shots here as it is really easy, choose configure database options. Activating Database Vault requires you also activate Label Security.

Then dbca prompts you to choose Database Vault Owner account (suggested account name is DBVOWNER) and Database Vault Account Manager (suggested account name is DBVACCTMGR). Database Vault Account Manager is not mandatory account but more segregate duties between different person. DBVOWNER account will be used to manage access rights (realms, ..) and DBVACCTMGR account will be used to create account. DBAs will not be able to create accounts anymore and will stick on pure database administration tasks.

You will also have to choose connection mode to your database (shared or dedicated). Then Database Configuration Assistant will work for you and configure Database Vault.

Once completed grant SELECT_CATALOG_ROLE and SELECT ANY DICTIONARY to DBVACCTMGR account.

When you login on database again you will see:

[oracle@server1 ~]$ sqlplus / AS sysdba
 
SQL*Plus: RELEASE 11.2.0.2.0 Production ON Tue Aug 30 16:17:14 2011
 
Copyright (c) 1982, 2010, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition RELEASE 11.2.0.2.0 - 64bit Production
WITH the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle DATABASE Vault AND REAL Application Testing options
 
SQL> SET lines 200
SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
 
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle DATABASE Vault                                            TRUE

Database Vault testing

As suggested in documentation let’s try to restrict access to SOE.CUSTOMERS table, with my own account (DBA role) I obviously have access to all objects:

SQL> CONNECT yjaquier
Enter password:
Connected.
SQL> DESC soe.customers
 Name                                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                                        NUMBER(12)
 CUST_FIRST_NAME                           NOT NULL VARCHAR2(30)
 CUST_LAST_NAME                            NOT NULL VARCHAR2(30)
 NLS_LANGUAGE                                       VARCHAR2(3)
 NLS_TERRITORY                                      VARCHAR2(30)
 CREDIT_LIMIT                                       NUMBER(9,2)
 CUST_EMAIL                                         VARCHAR2(100)
 ACCOUNT_MGR_ID                                     NUMBER(6)
 
SQL> SELECT COUNT(*) FROM soe.customers;
 
  COUNT(*)
----------
 101221463

To restrict access to this table (to all, including users with DBA roles and SYS account) we will create a realm called "SOE Customers".

Then either you follow support note 1212124.1 to configure Database Console for DBVOWNER account or you directly access (in any case Database Console is simply launching the below url so no added value in configuring it):

https://hostname:port/dva

In first panel, called administration, select realms hyper link. You see the four default realms that come with product installation:

• Database Vault Account Management
• Oracle Data Dictionary
• Oracle Database Vault
• Oracle Enterprise Manager

Click on create button and create a new one called "SOE Customers" with audit on failure default option (at that stage you cannot add objects on which you want to restrict access).

Once created edit it and add SOE.CUSTOMERS table in Realm Secured Objects section (please note that any wild cards are allowed in this part) and do not grant it to anyone.

Now if you select on SOE.CUSTOMERS table you get (even with SYS account):

SQL> SELECT COUNT(*) FROM soe.customers;
SELECT COUNT(*) FROM soe.customers
                         *
ERROR AT line 1:
ORA-01031: insufficient PRIVILEGES

Remark:
The access violations appear in monitor part of Database Vault graphical interface.

Now let’s grant access to YJAQUIER account on SOE.CUSTOMERS table, if you do it with graphical interface you get something like:

Then the SOE.CUSTOMERS table is accessible again with YJAQUIER account but still not with other account (including SYS).

PeopleSoft Case

Database Vault is certified with few products including PeopleSoft and SAP. Please refer to Oracle web site for latest Database Vault certifications.

What is really interesting is in Oracle Database Vault downloads page, Oracle corporation freely provide the Database Vault PeopleSoft policies. Please note that starting with 11g Database Vault binaries are included in database medias.

The zip you can download contains few SQL scripts that will create all what is needed to secure your PeopleSoft installation.

References

• Delete or Configure Database Options Are Disabled In DBCA [ID 579171.1]
• Accessing Database Vault Through Enterprise Manager Is Failing With “You Must Have Operator Target Privilege” [ID 1212124.1]
• Master Note For Oracle Database Vault [ID 1195205.1]
• Oracle Database Vault reference

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts