Uncategorized
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 ********
$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
Abhihsek
how to restrict the allow the program against IP