Written By Nassyam Basha
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.
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.
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.
We will configure the primary database first to enable database vault using DBCA and with few other parameter changes,
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 18.104.22.168.0 Production on Sun Nov 29 23:58:43 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
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 126.96.36.199.0 - 64bit Production
Copyright (c) 2000, 2012, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> disable configuration
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;
SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET OS_ROLES=FALSE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET RECYCLEBIN='OFF' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET SQL92_SECURITY=TRUE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET REMOTE_OS_AUTHENT=FALSE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET REMOTE_OS_ROLES=FALSE SCOPE=SPFILE;
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';
SQL> shut immediate
ORACLE instance shut down.
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
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
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;
ALTER SYSTEM SET os_roles=FALSE SCOPE=SPFILE;
ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;
ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;
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.
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> alter database open;
SQL> alter pluggable database all open;
Pluggable database altered.
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.
Because of the static parameters, we have to perform bounce of the standby database.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
Variable Size 557843520 bytes
Database Buffers 167772160 bytes
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.
SQL> alter system set log_archive_dest_state_2='enable';
9) From Standby
SQL> alter database recover managed standby database using current logfile through all switchover disconnect;
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
10) Enable the Broker Configuration
SQL*Plus: Release 188.8.131.52.0 Production on Mon Nov 30 00:07:10 2015
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
Oracle Database Vault and Real Application Testing options
Now we are finished the configuration of Vault from primary and also standby database, We can enable the broker configuration.
DGMGRL> enable configuration;
DGMGRL> show configuration;
Configuration - ckpt12c
Protection Mode: MaxPerformance
canada - Primary database
india - Physical standby database
Fast-Start Failover: DISABLED
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';
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
USER is "C##DBV"
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.
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.