Hello Tom,
I have implemented DB Vault on a 12.2.0.1.0 Oracle database. I created a Vault policy to block adhoc access to application schema using DB tools like Toad etc. The policy should allow only application connection to DB from application server with IP 192.168.1.10 and restrict connection to the APPS schema from anywhere else. But here the MODULE factor does not seem to work, as it is allowing connection from ad-hoc tools from 192.168.1.10 server. Is there any alternative I can use to achieve this apart from logon triggers ?
The below provided piece of code is what has been used to implement the vault policy.
BEGIN
DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'Limit_SQL_Plus_Access',
description => 'Limits access to SQL*Plus for Apps Schemas',
enabled => DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_OFF,
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SHOW,
fail_message => 'ad-hoc access denied for Apps Schemas',
fail_code => 20461,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => NULL);
END;
/
BEGIN
DBMS_MACADM.CREATE_FACTOR(
factor_name => 'MODULE',
factor_type_name => 'Application',
description => 'Stores client program name that connects to database',
rule_set_name => 'Limit_SQL_Plus_Access',
validate_expr => NULL,
get_expr => 'UPPER(SYS_CONTEXT(''USERENV'',''MODULE''))',
identify_by => DBMS_MACUTL.G_IDENTIFY_BY_METHOD,
labeled_by => 0,
eval_options => DBMS_MACUTL.G_EVAL_ON_SESSION,
audit_options => DBMS_MACUTL.G_AUDIT_OFF,
fail_options => DBMS_MACUTL.G_FAIL_WITH_MESSAGE);
END;
/
BEGIN
DBMS_MACADM.CREATE_FACTOR(
factor_name => 'PROGRAM',
factor_type_name => 'Application',
description => 'Stores client program name that connects to database',
rule_set_name => 'Limit_SQL_Plus_Access',
validate_expr => NULL,
get_expr => 'UPPER(SYS_CONTEXT(''USERENV'',''CLIENT_PROGRAM_NAME''))',
identify_by => DBMS_MACUTL.G_IDENTIFY_BY_METHOD,
labeled_by => 0,
eval_options => DBMS_MACUTL.G_EVAL_ON_ACCESS,
audit_options => DBMS_MACUTL.G_AUDIT_OFF,
fail_options => DBMS_MACUTL.G_FAIL_WITH_MESSAGE);
END;
/
BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'Rule_Connect',
rule_expr => 'UPPER(DVF.F$MODULE) in (''APPS.WINSERVICE.EXE'') AND DVF.F$SESSION_USER IN (''APPS'') AND DVF.F$CLIENT_IP IN (''192.168.1.10') AND UPPER(DVF.F$PROGRAM) in (''APPS.WINSERVICE.EXE'')');
END;
/
BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'Limit_SQL_Plus_Access',
rule_name => 'Rule_Connect'
);
END;
/
BEGIN
DBMS_MACADM.CREATE_CONNECT_COMMAND_RULE(
rule_set_name => 'Limit_SQL_Plus_Access',
user_name => 'APPS'
enabled => DBMS_MACUTIL.G_YES,
scope => DBMS_MACUTIL.G_SCOPE_LOCAL);
END;
I'm not familiar with Database Vault. So I reached out to Alan Williams, PM for Database Vault about this. He had this to say:
I notice they are using factors – that’s not necessary and I recommend just using rules to access context directly. Factors adds another layer of complication and since it’s rarely (if at all used), may not be as well understood.
I recommend creating the connection command rule/rule set and then taking it step by step
- Create rule set (as below)
- Create command rule (as below)
- Use the out of the box ‘enabled’ and ‘disabled’ rules to make sure the command rule is working as anticipated
- Then replace the OOTB rule with the IP rule and test
- Then test with another rule (setting aside the IP rule) and test the new rule independently
- In fact, test with each rule independently to make sure each rule is working as anticipated
- Then start concatenating rules together
If they aren’t sure if the parameter values are correct, they can use the new 12.2 simulation mode to capture the factors first
- Create rule set/command rule/enabled command rule – set to simulation mode
- Connect as the user from the application
- Review the simulation log and look into the factor column to review the factors that were used