Written By

Introduction

Oracle database vault provides extreme security to protect the data from unauthorized users or even highly privileged users and which helps organizations address the threats and the same time enabling the flexibility in a cost effective manner. This article purely how to implement or integrate Oracle Database vault with Data Guard 12c.

Oracle Database Vault Software

In order to enable the Oracle Database Vault, we have to download and install the software from OTN for 10g or 9i but from 11g the vault software comes with RDBMS kit, so that you can install whenever you prefer. For 11g or higher versions like 12c we can configure Vault with database by using DBCA, those will be detailed in the later section.  For the prior versions we can download from http://www.oracle.com/technetwork/database/options/database-vault/index-090593.html.

Vault with Data Guard

Integrating the Database Vault with Data Guard is almost same like stand alone database, however there are few actions required depending on the feature we use like Broker. Integrating part is almost similar like how we perform the upgrade of database with Data Guard. More simply we just need to start MRP after changes in parameter and all the changes whatever happened in primary and those will be applied on standby database. We will see how it work now.

Integrating Vault with Data Guard

We will configure the primary database first to enable database vault using DBCA and with few other parameter changes,

Primary Database

1) Ensure that /etc/oratab has valid entries with the current ORACLE_HOME so that DBCA can easily access the home like below.

-bash-3.2$ . oraenv

ORACLE_SID = [mcdb] ?

sqlThe Oracle base remains unchanged with value /u01/app/oracle

bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Nov 29 23:58:43 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

2) If you have Data Guard broker then disable the configuration until we have completed the vault integration with Data Guard.

-bash-3.2$ dgmgrl /

DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected as SYSDG.

DGMGRL> disable configuration

Disabled.

DGMGRL>

3) After disabling Broker we need to stop the processes of the primary database. If RAC database then only database services need to stop but not any cluster services.  While configuring vault DBCA can take care to stop and start the database but again it depends on your requirement on how to do that.

4) Launch DBCA to configure Vault

Select the database operation to "Configure Database Options" 

Select the database to which we want to configure the Vault.

You can see what are the database components and the options are available with the software you have installed and then go for the next page. 

From the "Database Vault Credentials", we can configure the Vault owner, As this database is 12c multitenant we have to use username with "C##xxx" for example "C##DBV" 

Depending on the connection mode we prefer we can either select shared or dedicated. 

Prior to configure, we can go through with the summary what the components are going to configure for the database we have selected. 

As I have mentioned earlier, DBCA can restart the database, if the database already is closed then this step won't be visible, by selecting Finish the configuration of Vault will be initiated. 

The overall configuration from DBCA will not take much time more than 1-2 minutes.(again it depends)

For now the DBCA configuration part for Database Vault is completed.

5) Configure Database parameters.

we have to perform few changes at database parameter level and they are below.

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PRIMARY

SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET OS_ROLES=FALSE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET RECYCLEBIN='OFF' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET SQL92_SECURITY=TRUE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET REMOTE_OS_AUTHENT=FALSE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET REMOTE_OS_ROLES=FALSE SCOPE=SPFILE;

System altered.

SQL>

Because of the static parameters, we have to perform bounce of the database and prior to that we can defer the remote destination.

SQL> alter system set log_archive_dest_state_2='defer';

System altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  730714112 bytes

Fixed Size                  2292672 bytes

Variable Size             654312512 bytes

Database Buffers           71303168 bytes

Redo Buffers                2805760 bytes

Database mounted.

Database opened.

SQL>

After the configuration from DBCA and the parameter level, now we can check whether Vault is enabled or not by the below output from alert log and also from v$option.

SQL> select * from v$option where parameter='Oracle Database Vault';

PARAMETER                 VALUE       CON_ID

------------------------- ------- ----------

Oracle Database Vault     TRUE             0

SQL>

 

Sun Nov 29 15:55:46 2015

Archived Log entry 98 added for thread 1 sequence 61 ID 0xe2211cf8 dest 1:

Sun Nov 29 17:44:35 2015

ALTER SYSTEM SET audit_sys_operations=TRUE SCOPE=SPFILE;

Sun Nov 29 17:44:35 2015

ALTER SYSTEM SET os_roles=FALSE SCOPE=SPFILE;

Sun Nov 29 17:44:35 2015

ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;

Sun Nov 29 17:44:35 2015

ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;

Sun Nov 29 17:44:35 2015

ALTER SYSTEM SET sql92_security=TRUE SCOPE=SPFILE;

..................

Sun Nov 29 17:44:36 2015

Database Vault is enabled.

By that we have performed all the necessary steps related to primary database.

Standby Database

6) As we performed configuration part through DBCA part is not required and not allowed to do on standby database because it is purely physical standby database and no DMLs or allowed. If you have snapshot standby database in place then you can configure vault using DBCA from both primary and snapshot standby database. Ensure you able to connect to standby database and follow below steps.

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Nov 29 23:58:43 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter database open;

Database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL>

7) We are aware that there are no redo is transporting from primary as the standby destination is deferred.  Now cancel the Media Recovery Process and perform the below changes in SPFILE(configuration file) as below.

SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET OS_ROLES=FALSE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET RECYCLEBIN='OFF' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET SQL92_SECURITY=TRUE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET REMOTE_OS_AUTHENT=FALSE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET REMOTE_OS_ROLES=FALSE SCOPE=SPFILE;

System altered.

SQL>

Because of the static parameters, we have to perform bounce of the standby database.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  730714112 bytes

Fixed Size                  2292672 bytes

Variable Size             557843520 bytes

Database Buffers          167772160 bytes

Redo Buffers                2805760 bytes

Database mounted.

Database opened.

SQL> !ps -ef|grep mrp

oracle    9313  9209  0 00:05 pts/4    00:00:00 /bin/bash -c ps -ef|grep mrp

8) From primary database we have deferred destination earlier, now we can enable it as the standby is ready to start Media recovery process.

From Primary

SQL> alter system set log_archive_dest_state_2='enable';

System altered.

SQL>

9) From Standby

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter database recover managed standby database using current logfile through all switchover disconnect;

Database altered.

SQL>

If you can see the standby alert log and you should notice the below line as the Database Vault is enabled and also whenever you connect to database using SQL Plus then the Database Vault option should be appear. This changes happens because of the redo(of vault configuration) from primary is applied on standby database.

Sun Nov 29 23:35:10 2015

Database Vault is enabled.

10) Enable the Broker Configuration

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 30 00:07:10 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,

Oracle Database Vault and Real Application Testing options

SQL>

Now we are finished the configuration of Vault from primary and also standby database, We can enable the broker configuration.

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration;

Configuration - ckpt12c

  Protection Mode: MaxPerformance

  Databases:

  canada - Primary database

    india  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

Test case of Database Vault in Data Guard

To test how powerful is data vault, we will test the users that trying to change roles.  If we see below the DVF user status is expired and locked, If you try to unlock the DVF user from SYS super account, then Vault will not allow to SYS to do such changes even though SYS is an super user and it says insufficient privileges to SYS user.

SQL> select username,account_status from dba_users where username='DVF';

USERNAME        ACCOUNT_STATUS

--------------- --------------------

DVF             EXPIRED & LOCKED

SQL> show user

USER is "SYS"

SQL> alter user dvf account unlock;

alter user dvf account unlock

*

ERROR at line 1:

ORA-01031: insufficient privileges

We have created the Vault user as C##DBV from dbca , now we can test whether this vault user can do the job or not.

SQL> connect c##dbv

Enter password:

Connected.

SQL> show user

USER is "C##DBV"

SQL> alter user dvf account unlock;

User altered.

SQL> select username,account_status from dba_users where username='DVF';

 

USERNAME        ACCOUNT_STATUS

--------------- --------------------

DVF             EXPIRED

SQL>

From the above output if you can see, Vault user have the overall control in terms of authorizations and it can able to unlock or unexpired.

Summary

We've seen the Vault software availability to various versions from 9i and how easy to configure from 11gR2 with the inbuilt RDBMS kit and implementation of the Vault in Data Guard environment in step by step and what the changes to be performed in primary, Finally we have also tested how smart the Database Vault works.

References

http://docs.oracle.com/database/121/DVADM/dvintro.htm#DVADM001