Saturday 11 February 2017

Enable Database Vault on RAC Database

Overview:
In this article we explain the process of  how to register RAC DB in Database Vault

Oracle Database Vault is a security product which is being accessed by privileged database users like DBAs. 
Application data can be protected with policies that control access by setting up the rules.
Environment Details
Hostname
Node1 : db01
Node 2: db02
Database version
11.2.0.4
Database Environment
OMFDB

Register Database in Database Vault.

$export PATH=$PATH:$ORACLE_HOME/bin
$dbca -silent -configureDatabase -sourceDB omfdb -sysDBAUserName sys -sysDBAPassword ******* -addDBOption OMS,DV -dvUserName dbvowner -dvUserPassword ******* -dvAccountManagerName dbvacctmgr -dvAccountManagerPassword ********

Preparing to Configure Database
1% complete
3% complete
18% complete
Adding Oracle Label Security
19% complete
20% complete
21% complete
22% complete
23% complete
24% complete
42% complete
54% complete
Adding Oracle Database Vault
65% complete
90% complete
Completing Database Configuration
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/omfdb/omfdb.log" for further details.

Login to database and change the below parameter to take effect database vault.

$sqlplus / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> set line 200
SQL> set line 200
SQL> set pages 200
SQL> show parameter AUDIT_SYS_OPERATIONS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
SQL> show parameter AUDIT_SYS_OPERATIONS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
SQL> show parameter os_roles

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_roles                             boolean     FALSE
remote_os_roles                      boolean     FALSE
SQL> show parameter RECYCLEBIN

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> show parameter SQL92_SECURITY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql92_security                       boolean     FALSE
SQL> ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE sid='*';

System altered.

sql>ALTER SYSTEM SET OS_ROLES=FALSE SCOPE=SPFILE sid='*';

System altered.

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

System altered.

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

System altered.

SQL>ALTER SYSTEM SET SQL92_SECURITY=TRUE SCOPE=SPFILE sid='*';

System altered.

Check the changes done and DBV Enabled.

SQL> show parameter AUDIT_SYS_OPERATIONS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE

SQL> set pages 200
SQL>  select status from dba_registry where COMP_NAME='Oracle Database Vault';

STATUS
--------------------------------------------
VALID

Bouce database

$ srvctl status database -d omfdb
Instance omfdb1 is running on node dm01
Instance omfdb2 is running on node dm02

$ srvctl stop database -d omfdb

$ srvctl status database -d omfdb
Instance omfdb1 is not running on node dm01
Instance omfdb2 is not running on node dm02

$ srvctl start database -d omfdb

$ srvctl status database -d omfdb
Instance omfdb1 is running on node dm01
Instance omfdb2 is running on node dm02

Re-grant for app specific

SQL> connect / as sysdba
SQL> grant become user to IMP_FULL_DATABASE;

SQL> grant become user to IMP_FULL_DATABASE;

Grant succeeded.

Setting up DBV rules for app

Here in this example we are setting up the DBV rule to allow apps users to access the Database.

$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> set line 200
SQL> set pages 00
SQL> set pages 200
SQL> select name from v$database;

NAME
---------
OMFDB

SQL>  select status from dba_registry where COMP_NAME='Oracle Database Vault';

STATUS
--------------------------------------------
VALID


Login to DBVOWNER to enable the DBV rules.

SQL> conn dbvowner/*******
Connected.


SQL>BEGIN
  DBMS_MACADM.CREATE_RULE (
          rule_name => 'Allow MONITOR_USER Access',
          rule_expr => 'DVF.F$CLIENT_IP in (''10.10.1.1'',''172.10.1.1'',''192.168.1.1'') and DVF.F$SESSION_USER = ''MONITOR_USER''');
END;
/

PL/SQL procedure successfully completed.


SQL>BEGIN
  DBMS_MACADM.CREATE_RULE (
          rule_name => 'Allow non MONITOR_USER Access',
          rule_expr => 'UPPER(DVF.F$CLIENT_IP) NOT in (''10.10.1.1'',''172.10.1.1'',''192.168.1.1'') OR DVF.F$CLIENT_IP is NULL' );
END;
/

PL/SQL procedure successfully completed.

Now Create the Rule set 

SQL>BEGIN
  DBMS_MACADM.CREATE_RULE_SET (
         rule_set_name => 'LIMIT_MONITOR_USER',
         description => 'Limit MONITOR_USER Access to certain IP',
         enabled => DVSYS.DBMS_MACUTL.G_YES,
         eval_options  => DVSYS.DBMS_MACUTL.G_RULESET_EVAL_ANY,
         audit_options => DVSYS.DBMS_MACUTL.G_RULESET_AUDIT_FAIL,
         fail_options => DVSYS.DBMS_MACUTL.G_RULESET_FAIL_SHOW,
         fail_message => 'Rule Set Limit MONITOR_USER Access failed.',
         fail_code => -20001,
         handler_options  => DVSYS.DBMS_MACUTL.G_RULESET_HANDLER_OFF,
         handler         => NULL);
END;
/

PL/SQL procedure successfully completed.


SQL>BEGIN
  DBMS_MACADM.ADD_RULE_TO_RULE_SET (
        rule_set_name => 'LIMIT_MONITOR_USER',
        rule_name => 'Allow non MONITOR_USER Access',
        rule_order => 1);
  DBMS_MACADM.ADD_RULE_TO_RULE_SET (
        rule_set_name => 'LIMIT_MONITOR_USER',
        rule_name => 'Allow MONITOR_USER Access',
        rule_order => 1);
END;
/

PL/SQL procedure successfully completed.


SQL>BEGIN
  DBMS_MACADM.CREATE_COMMAND_RULE (
     command => 'CONNECT',
     rule_set_name => 'LIMIT_MONITOR_USER',
     object_owner => NULL,
     object_name => NULL,
     enabled => DBMS_MACUTL.G_YES);
END;
/

PL/SQL procedure successfully completed.


Verify the DBV rule creation.

SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = 'Allow MONITOR_USER Access';

NAME
------------------------------------------------------------------------------------------
RULE_EXPR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Allow MONITOR_USER Access
DVF.F$CLIENT_IP in ('10.10.1.1','172.10.1.1','192.168.1.1') and DVF.F$SESSION_USER = 'MONITOR_USER'


SQL> SELECT RULE_SET_NAME, RULE_NAME, RULE_EXPR FROM DVSYS.DBA_DV_RULE_SET_RULE
 WHERE RULE_NAME = 'Allow MONITOR_USER Access';  2

RULE_SET_NAME                                                                              RULE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
RULE_EXPR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIMIT_MONITOR_USER_ACCESS                                                              Allow MONITOR_USER Access
DVF.F$CLIENT_IP in ('10.10.1.1','172.10.1.1','192.168.1.1') and DVF.F$SESSION_USER = 'MONITOR_USER'


SQL> conn dbvowner/*********
Connected.
SQL> SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME = 'Allow MONITOR_USER Access';

NAME
------------------------------------------------------------------------------------------
RULE_EXPR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Allow MONITOR_USER Access
DVF.F$CLIENT_IP in ('10.10.1.1','172.10.1.1','192.168.1.1') and DVF.F$SESSION_USER = 'MONITOR_USER'

Conclusion:
In this article we explain the process of  how to register RAC DB in Database Vault



1 comment:

  1. how to restrict the allow the program against IP

    ReplyDelete

Comparing Oracle Database Appliance X8-2 Model Family

September 2019 Oracle announced Oracle Database Appliance X8-2 (Small, Medium and HA). ODA X8-2 comes with more computing resources com...