Starting with Oracle Database 12c, Oracle Database Vault is installed by default but not enabled. Customers can enable it using DBCA or from the command line using SQL*Plus.
This post covers the following topics:
1. DB Vault Enable
2. Realm Creation
3. DB vault testing
4. Secure Applicatin Role Creation
# Step_01: Checking the DB Vault service is enabled or not.
---------------------------------------------------------
SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';
PARAMETER VALUE
---------------------------------------------------------------- -----------------------------
Oracle DATABASE Vault FALSE
----------------------------------------------------------------
# Step_02: Create user dbv_owner and dbv_acctmgr if doesn't exist
----------------------------------------------------------------
SQL> CREATE USER dbv_owner IDENTIFIED BY dbv_owner ;
USER created.
SQL> CREATE USER dbv_acctmgr IDENTIFIED BY dbv_acctmgr;
USER created.
----------------------------------------------------------------------------------------------
# Step_03: Configure DBVAULT for those users
----------------------------------------------------------------------------------------------
SQL> EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');
-----------------------------------------------------------------------------------------------
# Step_4: Enable DBVAULT
-----------------------------------------------------------------------------------------------
SQL> EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');
PL/SQL PROCEDURE successfully completed.
SQL> CONNECT dbv_owner/dbv_owner
Connected.
SQL> EXEC dbms_macadm.enable_dv;
PL/SQL PROCEDURE successfully completed.
-----------------------------------------------------------------------------------------------
# Step_5: Recheck DBVAULT Status
-----------------------------------------------------------------------------------------------
SQL> SET lines 150
SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle DATABASE Vault TRUE
#======== Database Vault 12cR2 testing (Manually REALM Creation) ===========
-----------------------------------------------------------------------------------------------
# Step_1: Creating an object on CMS Schema to apply REALM
-----------------------------------------------------------------------------------------------
SQL> conn cms/cms
Connected.
SQL>
SQL>
SQL> create table cms.app (sl number, host varchar2(20));
Table created.
SQL> insert into app values (10, 'app1.aibl.com');
1 row created.
SQL> commit;
Commit complete.
-----------------------------------------------------------------------------------------------
# Step_2: Creating another user to access object on CMS schema
-----------------------------------------------------------------------------------------------
SQL> conn dbv_acctmgr/dbv_acctmgr
Connected.
SQL>
SQL>
SQL> create user app_read identified by app_read;
User created.
SQL> GRANT CONNECT TO app_read;
Grant succeeded.
SQL> conn sys / as sysdba
Connected.
SQL> GRANT SELECT, UPDATE ON cms.app to app_read;
------------------------------------------------------------------------------------------------------------------------------------------
# Step_3: REALM CREATION:
# In PL/SQL it gives the two below command, parameter realm_type is set to 0 to avoid creation of a mandatory realms which means that
# objects owner (CMS) will still have full access to its objects. I also activate the realms right after its creation (enabled parameter):
--------------------------------------------------------------------------------------------------------------------
SQL> conn dbv_acctmgr/dbv_acctmgr
Connected.
SQL> EXEC dbms_macadm.create_realm(realm_name => 'CMS schema', description => 'Protect CMS Schema ', enabled => 'Y', audit_options => 1, realm_type =>'0' );
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
# Step_4: Add Object to REALM
-----------------------------------------------------------------------------------------------------------------
SQL> EXEC dbms_macadm.add_object_to_realm(realm_name => 'CMS schema', object_owner => 'CMS', object_name => '%', object_type => '%' );
PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------------
# Step_5: Add Authorizaion to REALM:
# If you grant the APP account as a participant to its own realm (can also be done with Cloud Control):
-------------------------------------------------------------------------------------------------------
SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'CMS schema', grantee => 'CMS', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------
# Checking the impact of REALM from SYS/System user who has the sysdba grant:
-------------------------------------------------------------------------------------------------
Step_1:
SQL> conn sys / as sysdba
Connected.
SQL> select * from cms.app;
select * from cms.app
*
ERROR at line 1:
ORA-01031: insufficient privileges
--------------------------------------------------------------------------------------------------
# Step_2: Checking the impact of REALM from a different user who has the right to read the object:
--------------------------------------------------------------------------------------------------
SQL> conn app_read/app_read
Connected.
SQL>
SQL> select * from cms.app;
SL HOST
---------- --------------------
10 app1.aibl.com
------------------------------------------------------------------------------------------------
# Step_3: Checking the impact of REALM from the schema user:
------------------------------------------------------------------------------------------------
SQL> conn cms/cms
Connected.
SQL>
SQL> select * from cms.app;
SL HOST
---------- --------------------
10 app1.aibl.com
## -------------- CHANGING REALM ROLE ------------
SQL> EXEC dbms_macadm.delete_auth_from_realm(realm_name => 'CMS schema', grantee => 'CMS');
PL/SQL PROCEDURE successfully completed.
SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'CMS schema', grantee => 'CMS', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
PL/SQL PROCEDURE successfully completed.
## Note: now owner of the schema can grant it's own objects now:
## ------------- REMOVE REALM -----------------
To remove everything simply execute:
SQL> EXEC dbms_macadm.delete_realm(realm_name => 'CMS schema');
PL/SQL PROCEDURE successfully completed.
===============Create Secure Applicatin Role===============================
Step 1: Create a Rule
begin
DVSYS.DBMS_MACADM.CREATE_RULE(rule_name => 'CMSAPPRULE', rule_expr => 'SYS_CONTEXT('USERENV', 'SESSION_USER') != 'SCOTT'');
end;
Step 2: Create a Rule Set
begin
DECLARE x VARCHAR2(40);
static_option BOOLEAN := FALSE;
BEGIN
x:='N';
IF x = 'Y' THEN
static_option := TRUE;
ELSE static_option := FALSE;
END IF;
DVSYS.DBMS_MACADM.CREATE_RULE_SET(rule_set_name => 'APP_USER_AUTH', description => ' ssss', enabled => 'Y', eval_options => 1, audit_options => 1, fail_options => 1, fail_message => '', fail_code => '', handler_options => 0, handler => '',is_static => static_option);
END;
DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(rule_set_name => 'APP_USER_AUTH', rule_name => 'CMSAPPRULE', rule_order => '1', enabled => 'Y');
end;
Step 3:
Create a Secure Application Role
begin
DVSYS.DBMS_MACADM.CREATE_ROLE(role_name=>DBMS_ASSERT.ENQUOTE_NAME('DV_APP_USR_ROLE'), enabled => 'Y', rule_set_name => 'APP_USER_AUTH');
end;
Step 4: grant to created DV Role
GRANT SELECT, UPDATE ON APP TO DV_APP_USR_ROLE;
GRANT SELECT, UPDATE ON TEST01 TO DV_APP_USR_ROLE;
Step 5:
SQL> EXEC DBMS_MACSEC_ROLES.SET_ROLE('DV_APP_USR_ROLE');
PL/SQL procedure successfully completed.
