Jump to content
Sharath Thoniyot

SYS_CONTEXT('userenv','MODULE') for Database Vault

Recommended Posts

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; 

Share this post


Link to post
Share on other sites

Hi Sharath,

If you wanna restriction an IP address, use can very well use SYS_CONTEXT('USERENV','IP_ADDRESS') function.

Here are few examples,

http://www.dba-oracle.com/t_adv_plsql_vpd_application_contexts.htm

http://www.dba-oracle.com/t_adv_plsql_dbms_redact_partial.htm

 

Thanks,

Boobal Ganesan

Share this post


Link to post
Share on other sites
On 7/10/2018 at 8:57 AM, Sharath Thoniyot said:

Dear Boobal Ganeshan,

Many thanks for responding to my query. The IP restriction has already been implemented using database vault which works fine for me. But what I would require is to restrict connection to DB only from application(APPS.WINSERVICE.EXE) from the application server 192.168.1.10. 

The situation now is only connections from 192.168.1.10 are allowed to access application schemas. But the problem is any ad-hoc applications installed on the server 192.168.1.10 can connect to DB to access application schemas which I want to restrict it to only APPS.WINSERVICE.EXE application.

Regards,

Sharath

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×