X

All Things Database: Education, Best Practices,
Use Cases & More

Using IP rules to access Autonomous Database-Shared

Goutam Pal
Oracle PreSales Cloud Engineer

Overview:

There may be cases where we do not want application servers having access to unauthorized data lying in the same database in a multi application to single Autonomous Database architecture.

One of the many security capabilities built into Autonomous Database is Database Vault. Database Vault implements powerful, unique security controls that restrict access to application data by privileged database users and enforce context-aware policies for data access by any user. Database Vault reduces the risk of insider and outside threats and addresses common compliance requirements.

Implementation:

Database Vault enables separation of duties by implementing two dedicated database roles – DV_OWNER (used to create and manage security policies enforced by Database Vault) and DV_ACCTMGR (used to separate the duty of database user management – including password resets – from the DBA role). By default the ADMIN user has both the DV_OWNER and DV_ACCTMGR role. Ideally ADMIN user should only be used to provision other users with required privileges and roles and not used for day to day operations.  If you want to set up separate users for DV_OWNER and DV_ACCTMGR accounts

 

For DV_OWNER:

 

   CREATE USER ADV_OWNER IDENTIFIED BY ######

   DEFAULT TABLESPACE "DATA"

   TEMPORARY TABLESPACE "TEMP";

GRANT "DV_OWNER" TO "ADV_OWNER" WITH ADMIN OPTION;

ALTER USER "ADV_OWNER" DEFAULT ROLE ALL;

GRANT CREATE SESSION TO "ADV_OWNER";

 

 

 

For DV_ACCTMGR 

   CREATE USER "ADV_ACCT_ADMIN" IDENTIFIED BY #######

      DEFAULT TABLESPACE "DATA"

      TEMPORARY TABLESPACE "TEMP";

GRANT "DV_ACCTMGR" TO "ADV_ACCT_ADMIN" WITH ADMIN OPTION;

ALTER USER "ADV_ACCT_ADMIN" DEFAULT ROLE ALL;

 GRANT CREATE SESSION TO "ADV_ACCT_ADMIN";

 

 

 

Enable Oracle Database Vault on Autonomous Database-Shared

Shows the steps to enable Oracle Database Vault on Autonomous Database-Shared.

Oracle Database Vault is disabled by default on Autonomous Database. To configure and enable Oracle Database Vault on Autonomous Database, do the following:

  1. Configure Oracle Database Vault using the following command using the user and role created in above step:

 EXEC DBMS_CLOUD_MACADM.CONFIGURE_DATABASE_VAULT('ADV_OWNER', 'ADV_ACCT_ADMIN');

 

  1. Enable Oracle Database Vault:

EXEC DBMS_CLOUD_MACADM.ENABLE_DATABASE_VAULT;

  1. Restart the Autonomous Transaction Processing instance from OCI console

Use the following command to check if Oracle Database Vault is enabled or disabled:

SELECT * FROM DBA_DV_STATUS;

Output similar to the following appears:

CopyNAME                 STATUS
-------------------- -----------
DV_CONFIGURE_STATUS  TRUE
DV_ENABLE_STATUS     TRUE

The DV_ENABLE_STATUS value TRUE indicates Oracle Database Vault is enabled.

 

In order to execute this command successfully, you must do it as a user with DV_ACCTMGR role. PDB_DBA is not sufficient

Now we have schema HR created in ATP as below:

 

create user hr identified by *********;

grant create session, create table to hr;

grant unlimited tablespace to hr;

create table hr.employees (id number, name varchar2 (20), salary number);

insert into hr.employees values (10,'Larry',20000);

commit;

 

For this scenario I have two servers which access the HR schema in ATP instance.

 

Server 1 IP: 132.145.75.77

Server 2 IP: 132.145.53.205

 

And I want only Server 2 to have access to HR schema and block all other incoming connections from any server.

In such case we will have to setup the below Database vault policy from ‘ADMIN’ account or 'ADV_OWNER' account

A Database Vault policy is made up of a few different components:

  • Rule – describe conditions that will be checked before an action is allowed or blocked
  • Rule set – a group of one or more rules that can be assigned to an action. You can associate the rule set with a realm authorization, factor assignment, command rule, or secure application role.
  • Command rule – connects a rule set to a specific action and object or schema. A command rule ALWAYS references a rule set. Command rules are commonly used to implement context-sensitive access policies
  • Realm – a logical grouping of objects or schemas. Realms automatically override DBA and * ANY privileges. Realms are commonly used to block privileged user access to data

A simple example of these concepts working together might be:

  • Create a rule that checks the client IP address and ensures it is 132.145.53.205
  • Create a rule set that includes the rule, and specified when Database Vault will create an audit records (success, failure, or both)
  • Create a command rule that enforces the IP address restriction for the CONNECT operation

 

  • Create Rule Set:

BEGIN

 

DVSYS.DBMS_MACADM.CREATE_RULE_SET(

rule_set_name => 'RULE_SET_HR',

description => 'Rule Set enabled for HR',

enabled => DVSYS.DBMS_MACUTL.G_YES,

eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL, -- all rules must be true,

audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL, -- no audit

fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,

fail_message => '',

fail_code => NULL,

handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,

handler => NULL

);

 

END;

/

  • Create Rule

BEGIN

 DBMS_MACADM.CREATE_RULE(

  rule_name => 'Check Client IP Address',

  rule_expr => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') IN (''132.145.53.205'')');

END;

/

  • Add Rule to Rule Set

BEGIN

    DBMS_MACADM.ADD_RULE_TO_RULE_SET(

     rule_set_name     => 'RULE_SET_HR',

     rule_name         => 'Check Client IP Address'

   );

END;

/

  • Create command rule for Schema HR

BEGIN

   DBMS_MACADM.CREATE_COMMAND_RULE(

    command            => 'CONNECT',

    rule_set_name      => 'RULE_SET_HR',

    object_owner       => 'HR',

    object_name        => '%',

    enabled            => DBMS_MACUTL.G_YES);

END;

/

COMMIT;

 

 

  • Check the rule has been implemented

SELECT * FROM DVSYS.DBA_DV_RULE where name like '%IP%'

 

 

 

 

 

 

 

Validation

 

Let’s connect to HR schema from Server 2 (132.145.53.205) and we can see its connecting.

 

 

And when we try to connect from Server 1 (132.145.75.77) , we will see connection denied.

 

And it will deny access for any unauthorized IP. Below is a snap of trying to connect from my local SQL Developer

 

 

Note: If we want to add a list of authorized IPs, update the rule as shown below

BEGIN

 DBMS_MACADM.UPDATE_RULE(

  rule_name => 'Check Client IP Address',

  rule_expr => 'SYS_CONTEXT(''USERENV'',''IP_ADDRESS'') IN (''132.145.53.205'',''132.145.75.77'')');

END;

/

 

References:

https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/autonomous-database-vault.html#GUID-70F233AF-458F-44B7-9418-F9AF70CFDD15

https://docs.oracle.com/en/database/oracle/oracle-database/19/dvadm/oracle-database-vault-rule-set-apis.html#GUID-1B24D370-46E3-445E-91CC-8E621F5E94CB

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.