Showing posts with label Database Vault. Show all posts
Showing posts with label Database Vault. Show all posts

Monday, February 1, 2010

Time based Access to Database Using Database Vault

Database Vault -
Scenario Description- Restrict three dba's - dba1,dba2 and dba3 with command rule connect, so that they canconnect only at a specific time in the day.dba1 - First 8 hours of the day dba2- 2nd 8 hours of the day dba3 - 3rd 8 hours of the day
The challenge -The problem is whenever a rule is written to achieve the above mentioned scenario, no user in the database is able to connect to anything andone has to disable the database vault to disable the ruleset attached with connect command rule.
The solution - It is imperative to write the rule expression absolutely correctly so that only the three users in the scenario are affected based on the time of the day. It is important to create a bypass, in the rule exprression, for the rest of the users of the database so that they can connect anytime and they arenot affected by the rule set.
So the expression is written this way -
to_char(sysdate,'hh24') in ('08','09','10','11','12','13','14','15') and sys_context('userenv','session_user')='DBA2' ) OR (SYS_CONTEXT('USERENV','SESSION_USER')='DBA1' AND TO_CHAR(SYSDATE,'HH24') IN ('00','01','02','03','04','05','06','07')) OR (to_char(sysdate,'hh24') in ('17','18','19','20','21','22','23','16') AND SYS_CONTEXT('USERENV','SESSION_USER') = 'DBA3') OR (SYS_CONTEXT('USERENV','SESSION_USER') not IN ('DBA1','DBA2','DBA3'))
Note: Remember not to put a semicolon at the end of the rule expression.
Here the last line of the rule-expression acts as the bypass and allows all other users (except dba1, dba2 and dba3) to connect to the database withoutany problem.

Enable/Disable Database Vault

Database Vault - Used to protect objects in a database from access by other users (even by privileged users like sys).
Disable Database Vault-
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off
relink oracle
Enable Database Vault
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_on
relink oracle
Now to Ensure that Database Vault has really been enabled, do this-
sys> select * from v$option where lower(parameter) like '%vault%';
If the output is true then database vault has been enabled successfully.
If false then one has to check why it has not been enabled.

Note:-

In case of a Rac Setup -

srvctl stop database -d db_name

note: do this from all nodes:
cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk dv_off ioracle