Database Vault 12cR1 installation and configuration

Preamble

I have already tested Oracle Database Vault in 11gR2 but as we are starting the study for a new project containing HR figures I wanted to re-test Database Vault 12cR1 (12.1.0.2) to see how things improved. Worth to re-mention that Database Vault is a paid option of Oracle Database Enterprise Edition.

I have two virtual machine to do the testing, one is running my 12cR1 (12.1.0.2) Enterprise edition database under Oracle Linux Server release 7.2. The second virtual machine is a VirtualBox image that I have directly downloaded at:
http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/oem-templates-2767917.html

What this VirtualBox image provides is a complete running Cloud Control 13cR1 (13.1.0.0.0) environment. Even if Cloud Control 13cR2 is available at the time of writing this blog post it is not yet available for download as a running image.

I will also briefly test Privilege Analysis, a new feature introduced with Database Vault 12cR1. Privilege Analysis does not require you enable Database Vault to use it but its licensing is part of Database Vault.

Database Vault 12cR1 installation

Versus 11gR2 Database Vault is already installed but not activated as Oracle says:

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 in a matter of minutes.

Can be confirmed with:

SQL> SET lines 150
SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';
 
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle DATABASE Vault                                            FALSE

Following the official documentation to activate it I executed (the 12cR1 default accounts have moved from dbvxxx to dbv_xxx):

SQL> EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');
BEGIN DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr'); END;
 
*
ERROR AT line 1:
ORA-01918: USER 'dbv_owner' does NOT exist
ORA-06512: AT "DVSYS.DBMS_MACUTL", line 34
ORA-06512: AT "DVSYS.DBMS_MACUTL", line 389
ORA-06512: AT "DVSYS.CONFIGURE_DV", line 126
ORA-06512: AT line 1

Fortunately MOS note How To Enable Database Vault in a 12c database ? (Doc ID 2112167.1) comes to the rescue and accounts must be created first:

SQL> CREATE USER dbv_owner IDENTIFIED BY "secure_password";
 
USER created.
 
SQL> CREATE USER dbv_acctmgr IDENTIFIED BY "secure_password";
 
USER created.

Vault activation is then straightforward:

SQL> EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> CONNECT dbv_owner/"secure_password"
Connected.
SQL> EXEC dbms_macadm.enable_dv;
 
PL/SQL PROCEDURE successfully completed.

Restart the database and you can see it’s there:

SQL> SET lines 150
SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';
 
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle DATABASE Vault                                            TRUE

To be able to use Cloud Control 13cR1 I have granted dv_admin role to my personal DBA account:

SQL> CONNECT dbv_owner/"secure_password"
Connected.
SQL> GRANT dv_admin TO yjaquier;
 
GRANT succeeded.

Database Vault 12cR1 testing

The aim of my testing is to protect objects owned by and OS authenticated account (APP) from high privileges users but to keep the access for accounts to which grants have been given (APP_READ). The data model in itself is simple. I start with accounts creation with dbv_acctmgr account (except RESOURCE role that must be granted with SYS account):

SQL> CONNECT dbv_acctmgr/"secure_password"
Connected.
SQL> CREATE USER app IDENTIFIED externally DEFAULT TABLESPACE users;
 
USER created.
 
SQL> ALTER USER app quota unlimited ON users;
 
USER altered.
 
SQL> GRANT CONNECT TO app;
 
GRANT succeeded.
 
SQL> CREATE USER app_read IDENTIFIED BY secure_password;
 
USER created.
 
SQL> GRANT CONNECT TO app_read;
 
GRANT succeeded.
 
SQL> CONNECT / AS sysdba
Connected.
SQL> GRANT RESOURCE TO app;
 
GRANT succeeded.

Remark
If you have activated Database Vault before creating the data model you start to see the added complexity of the product. The new account to manage accounts is dbv_acctmgr except for few privileges related to the default realms created when implementing Database Vault:

  • RESOURCE role is protected in Oracle System Privilege and Role Management Realm realm and accessible only by SYS
  • CONNECT role is protected in Database Vault Account Management realm and accessible only by DV_ACCTMGR

To see the Oracle defined realms either you use Cloud Control under security/Database Vault menu and check the “Show Oracle defined Realms” checkbox in Administration tab and Realms sub-menu:

database_vault_12cr1_01
database_vault_12cr1_01

Or you use below query (there is no real column to extract only Oracle defined realms):

SQL> SET pages 50
SQL> col name FOR a50
SQL> col description FOR a80 word_wrapped
SQL> SELECT name, description FROM DVSYS.DV$REALM WHERE realm_type IS NULL;
 
NAME                                               DESCRIPTION
-------------------------------------------------- --------------------------------------------------------------------------------
Oracle DATABASE Vault                              Defines the realm FOR the Oracle DATABASE Vault schemas - DVSYS, DVF AND LBACSYS
                                                   WHERE DATABASE Vault ACCESS control configuration AND roles are contained.
 
DATABASE Vault Account Management                  Defines the realm FOR administrators who CREATE AND manage DATABASE accounts AND
                                                   profiles.
 
Oracle Enterprise Manager                          Defines the Enterprise Manager monitoring AND management realm.
Oracle DEFAULT SCHEMA Protection Realm             Defines the realm FOR the Oracle DEFAULT schemas.
Oracle SYSTEM Privilege AND ROLE Management Realm  Defines the realm TO control granting OF SYSTEM PRIVILEGES AND DATABASE
                                                   administrator roles.
 
Oracle DEFAULT Component Protection Realm          Defines the realm TO protect DEFAULT components OF the Oracle DATABASE.
 
6 ROWS selected.

I connect with APP account and create the employees table and grant select and update on it to APP_READ:

SQL> SET lines 150
SQL> CREATE TABLE employees (
     id NUMBER,
     firstname VARCHAR2(50),
     lastname VARCHAR2(50),
     salary NUMBER);
 
TABLE created.
 
SQL> GRANT SELECT, UPDATE ON employees TO app_read;
 
GRANT succeeded.
 
SQL> INSERT INTO employees VALUES(1,'Yannick','Jaquier',10000);
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> SELECT * FROM employees;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier                                                 10000

To create the realm you can use Cloud Control, click on create Administration tab and Realms sub-menu in Security/Database Vault:

database_vault_12cr1_02
database_vault_12cr1_02

Enter a name and a description, its original status and what you wan to audit:

database_vault_12cr1_03
database_vault_12cr1_03

Choose the objects you’d like to protect, let rest by default:

database_vault_12cr1_04
database_vault_12cr1_04

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 (APP) will still have full access to its objects. I also activate the realms right after its creation (enabled parameter):

SQL> EXEC dbms_macadm.create_realm(realm_name => 'APP schema', description => 'Protect APP Schema ', enabled => 'Y', audit_options => 1, realm_type =>'0' );
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC dbms_macadm.add_object_to_realm(realm_name => 'APP schema', object_owner => 'APP', object_name => '%', object_type => '%' );
 
PL/SQL PROCEDURE successfully completed.

Even if I do not add any users to the realms the accounts that have select privileges on APP schema can still perform select while DBA like accounts (including SYS and SYSTEM) cannot see figures anymore:

SQL> SET lines 150
SQL> show USER
USER IS "YJAQUIER"
SQL> SELECT * FROM app.employees;
SELECT * FROM app.employees
                  *
ERROR AT line 1:
ORA-01031: insufficient PRIVILEGES
 
SQL> CONNECT app_read/"secure_password"
SQL> show USER
USER IS "APP_READ"
SQL> SELECT * FROM app.employees;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier                                                 10000

If you make the realms mandatory:

SQL> EXEC dbms_macadm.update_realm(realm_name => 'APP schema', description => 'Protect APP Schema ', enabled => 'Y', realm_type =>'1');
 
PL/SQL PROCEDURE successfully completed.

Then APP_READ schema cannot anymore select on employees table:

SQL> show USER
USER IS "APP_READ"
SQL> SELECT * FROM app.employees;
SELECT * FROM app.employees
                  *
ERROR AT line 1:
ORA-01031: insufficient PRIVILEGES

So the simplest first installation is a non mandatory realm but even if you try to make it simple you will see that APP user cannot create any more objects in its own schema:

SQL> show USER
USER IS "APP"
SQL> CREATE TABLE test01 (val NUMBER);
CREATE TABLE test01 (val NUMBER)
*
ERROR AT line 1:
ORA-47401: Realm violation FOR CREATE TABLE ON APP.TEST01

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 => 'APP schema', grantee => 'APP', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
 
PL/SQL PROCEDURE successfully completed.

Then you can create object but you cannot grant them to other accounts:

SQL> CREATE TABLE test01(val NUMBER);
 
TABLE created.
 
SQL> INSERT INTO test01 VALUES(1);
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.
 
SQL> GRANT SELECT ON test01 TO app_read;
GRANT SELECT ON test01 TO app_read
                *
ERROR AT line 1:
ORA-47401: Realm violation FOR GRANT ON APP.TEST01

If you grant APP to be a owner of its own realm (you need to remove it before re-adding it with another role), can be done with Cloud Control as well:

SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'APP schema', grantee => 'APP', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
BEGIN dbms_macadm.add_auth_to_realm(realm_name => 'APP schema', grantee => 'APP', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER); END;
 
*
ERROR AT line 1:
ORA-47260: Realm Authorization TO APP SCHEMA FOR Realm APP already defined
ORA-06512: AT "DVSYS.DBMS_MACADM", line 1903
ORA-06512: AT "DVSYS.DBMS_MACADM", line 1968
ORA-06512: AT line 1
 
 
SQL> EXEC dbms_macadm.delete_auth_from_realm(realm_name => 'APP schema', grantee => 'APP');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'APP schema', grantee => 'APP', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
 
PL/SQL PROCEDURE successfully completed.

Then APP can grant its own objects to others (and grantee can see figures):

SQL> GRANT SELECT ON test01 TO app_read;
 
GRANT succeeded.
 
SQL> CONNECT app_read/"secure_password"
SQL> SELECT * FROM app.test01;
 
       VAL
----------
         1

To remove everything simply execute:

SQL> EXEC dbms_macadm.delete_realm(realm_name => 'APP schema');
 
PL/SQL PROCEDURE successfully completed.

Database Vault 12cR1 reporting

The two views, well three views, to control what has been changed in Vault or who has tried to violate one of the Vault enforcement are DVSYS.DV$CONFIGURATION_AUDIT and DVSYS.DV$ENFORCEMENT_AUDIT based on DVSYS.AUDIT_TRAIL$:

SQL> col username FOR a10
SQL> col action_name FOR a30
SQL> col action_object_name FOR a20
SQL> SET pages 100
SQL> ALTER SESSION SET nls_date_format="dd-mon-yyyy hh24:mi:ss";
 
SESSION altered.
SQL> SELECT username,TIMESTAMP,action_name,action_object_name FROM dvsys.dv$configuration_audit ORDER BY id# DESC;
 
USERNAME   TIMESTAMP            ACTION_NAME                    ACTION_OBJECT_NAME
---------- -------------------- ------------------------------ --------------------
YJAQUIER   17-oct-2016 17:36:53 ADD Realm Auth AUDIT           APP SCHEMA
YJAQUIER   17-oct-2016 17:36:50 DELETE Realm Auth AUDIT        APP SCHEMA
YJAQUIER   17-oct-2016 17:33:47 ADD Realm Auth AUDIT           APP SCHEMA
YJAQUIER   17-oct-2016 17:30:31 ADD Realm Object AUDIT         APP SCHEMA
YJAQUIER   17-oct-2016 17:30:24 Realm Creation AUDIT           APP SCHEMA
YJAQUIER   17-oct-2016 17:17:31 Realm Deletion AUDIT           APP SCHEMA
YJAQUIER   14-oct-2016 17:55:59 ADD Realm Auth AUDIT           APP SCHEMA
YJAQUIER   14-oct-2016 17:55:59 DELETE Realm Auth AUDIT        APP SCHEMA
YJAQUIER   14-oct-2016 17:48:56 ADD Realm Auth AUDIT           APP SCHEMA
YJAQUIER   14-oct-2016 17:48:56 DELETE Realm Auth AUDIT        APP SCHEMA
YJAQUIER   14-oct-2016 16:51:04 ADD Realm Auth AUDIT           APP SCHEMA
YJAQUIER   14-oct-2016 15:52:19 ADD Realm Object AUDIT         APP SCHEMA
YJAQUIER   14-oct-2016 15:52:14 Realm Creation AUDIT           APP SCHEMA
YJAQUIER   14-oct-2016 15:48:54 Realm Deletion AUDIT           APP SCHEMA
YJAQUIER   14-oct-2016 13:07:04 ADD Realm Object AUDIT         APP SCHEMA
YJAQUIER   14-oct-2016 13:06:59 Realm Creation AUDIT           APP SCHEMA
YJAQUIER   14-oct-2016 13:06:34 Realm Deletion AUDIT           APP SCHEMA
YJAQUIER   14-oct-2016 12:52:40 Realm UPDATE AUDIT             APP SCHEMA
YJAQUIER   14-oct-2016 12:44:01 DELETE Realm Auth AUDIT        APP SCHEMA
YJAQUIER   14-oct-2016 12:30:20 Realm UPDATE AUDIT             APP SCHEMA
YJAQUIER   14-oct-2016 12:30:10 Realm UPDATE AUDIT             APP SCHEMA
YJAQUIER   14-oct-2016 12:23:58 ADD Realm Auth AUDIT           APP SCHEMA
YJAQUIER   14-oct-2016 12:14:33 Realm UPDATE AUDIT             APP SCHEMA
YJAQUIER   14-oct-2016 10:50:13 Realm UPDATE AUDIT             APP SCHEMA
YJAQUIER   14-oct-2016 10:15:03 ADD Realm Object AUDIT         APP SCHEMA
YJAQUIER   14-oct-2016 10:14:55 Realm Creation AUDIT           APP SCHEMA
DBV_OWNER  13-oct-2016 16:40:49 Enable DV enforcement AUDIT
 
27 ROWS selected.
 
SQL> col action_command FOR a60
SQL> SELECT username,TIMESTAMP,action_name,action_object_name,action_command FROM dvsys.dv$enforcement_audit ORDER BY id# DESC;
 
USERNAME   TIMESTAMP            ACTION_NAME                    ACTION_OBJECT_NAME   ACTION_COMMAND
---------- -------------------- ------------------------------ -------------------- ------------------------------------------------------------
APP        17-oct-2016 17:34:31 Realm Violation AUDIT          APP SCHEMA           GRANT SELECT ON TEST01 TO APP_READ
APP        17-oct-2016 17:33:10 Realm Violation AUDIT          APP SCHEMA           CREATE TABLE TEST01 (VAL NUMBER)
APP        17-oct-2016 17:32:19 Realm Violation AUDIT          APP SCHEMA           DROP TABLE TEST1
YJAQUIER   17-oct-2016 17:31:45 Realm Violation AUDIT          APP SCHEMA           SELECT * FROM APP.EMPLOYEES
DBV_OWNER  17-oct-2016 15:44:36 Realm Violation AUDIT          DATABASE Vault Accou GRANT CONNECT TO APP_READ
                                                               nt Management
 
DBV_OWNER  17-oct-2016 15:42:06 Realm Violation AUDIT          Oracle SYSTEM Privil GRANT RESOURCE TO TEST
                                                               ege AND ROLE Managem
                                                               ent Realm
 
DBV_ACCTMG 17-oct-2016 15:41:52 Realm Violation AUDIT          Oracle SYSTEM Privil GRANT RESOURCE TO TEST
R                                                              ege AND ROLE Managem
                                                               ent Realm
 
DBV_OWNER  17-oct-2016 15:41:45 Realm Violation AUDIT          DATABASE Vault Accou GRANT CONNECT TO TEST
                                                               nt Management
 
DBV_OWNER  17-oct-2016 15:41:39 Realm Violation AUDIT          Oracle SYSTEM Privil GRANT RESOURCE TO TEST
                                                               ege AND ROLE Managem
                                                               ent Realm
 
DBV_OWNER  17-oct-2016 15:40:33 Realm Violation AUDIT          DATABASE Vault Accou GRANT CONNECT,RESOURCE TO TEST
                                                               nt Management
 
SYS        17-oct-2016 15:40:19 Realm Violation AUDIT          DATABASE Vault Accou GRANT CONNECT,RESOURCE TO TEST
                                                               nt Management
 
DBV_ACCTMG 17-oct-2016 15:40:02 Realm Violation AUDIT          Oracle SYSTEM Privil GRANT CONNECT,RESOURCE TO TEST
R                                                              ege AND ROLE Managem
                                                               ent Realm
.
.
.

But it cannot compete with the really nice Cloud Control Database Vault reporting. You can also click on each slice of the pie to get further details:

database_vault_12cr1_05
database_vault_12cr1_05

Database Vault 12cR1 errors

When playing with my own account granted and removed to my test realm and when de-activating, re-creating my test realm I reached a strange situation. I was able to select from APP.EMPLOYEES table with my own account while technically I should not have been able to do so. A simple flush shared pool solved the bad situation (and end my half day investigation):

SQL> show USER
USER IS "YJAQUIER"
SQL> SELECT * FROM app.employees;
 
        ID FIRSTNAME                                          LASTNAME                                               SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         1 Yannick                                            Jaquier                                                 10000
 
SQL> ALTER SYSTEM flush shared_pool;
 
SYSTEM altered.
 
SQL> SELECT * FROM app.employees;
SELECT * FROM app.employees
                  *
ERROR AT line 1:
ORA-01031: insufficient PRIVILEGES

Oracle Database Vault 12cR1 Privilege Analysis

Database Vault 12cR1 introduce a new feature called Privilege Analysis. As the name stand for it helps you to analyze the used and unused privileges inside your database. A typical example is an applicative account which you can study to see which privileges (system or objects) it is using and the one it is NOT using. May really helps you to revoke too high privileges in a safer manner.

In Cloud Control in security menu choose Privilege Analysis:

database_vault_12cr1_06
database_vault_12cr1_06

To create a Privilege Analysis policy either you use Cloud Control or PL/SQL. I will create a policy to check privileges APP_READ account uses and dig in the ones it is not using. Remember we have granted update on APP.EMPLOYEES that I will not use in my testing:

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(name => 'APP_READ policy', description => 'Check APP_READ privileges', TYPE => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT, -
> condition => 'SYS_CONTEXT (''USERENV'',''CURRENT_SCHEMA'') = ''APP_READ''' );
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col CONTEXT FOR a60
SQL> SELECT TYPE,enabled,CONTEXT FROM DBA_PRIV_CAPTURES WHERE name='APP_READ policy';
 
TYPE             E CONTEXT
---------------- - ------------------------------------------------------------
CONTEXT          N SYS_CONTEXT ('USERENV','CURRENT_SCHEMA') = 'APP_READ'

Enable the policy with:

SQL> EXEC dbms_privilege_capture.enable_capture(name => 'APP_READ policy');
 
PL/SQL PROCEDURE successfully completed.

Perform connect and few select with APP_READ account but do not update APP.EMPLOYESS table to show APP_READ does not need this privileges ! Once done disable the policy:

SQL> EXEC dbms_privilege_capture.disable_capture(name => 'APP_READ policy');
 
PL/SQL PROCEDURE successfully completed.

With Cloud Control it gives something like:

database_vault_12cr1_07
database_vault_12cr1_07

Generate result report with:

SQL> EXEC dbms_privilege_capture.generate_result(name => 'APP_READ policy');
 
PL/SQL PROCEDURE successfully completed.

And fetch the figures with (refer to Database Vault official documentation for long list of available views), this is extracted and modified from official documentation. List of used privileges:

SQL> col username format a10
SQL> col sys_priv format a16
SQL> col object_owner format a13
SQL> col object_name format a23
SQL> SELECT username, NVL(sys_priv,obj_priv) AS priv, object_owner, object_name FROM dba_used_privs WHERE username='APP_READ';
 
USERNAME   PRIV                                     OBJECT_OWNER  OBJECT_NAME
---------- ---------------------------------------- ------------- -----------------------
APP_READ   SELECT                                   APP           EMPLOYEES
APP_READ   SELECT                                   APP           TEST01
APP_READ   SELECT                                   SYS           DUAL
APP_READ   CREATE SESSION
APP_READ   EXECUTE                                  SYS           DBMS_APPLICATION_INFO
APP_READ   SELECT                                   SYS           DUAL
APP_READ   SELECT                                   SYSTEM        PRODUCT_PRIVS
 
7 ROWS selected.

List of unused privileges, we clearly see that update on app.employees has not been used so might be revoked:

SQL> SELECT username, NVL(sys_priv,obj_priv) AS priv, object_owner, object_name FROM dba_unused_privs WHERE username='APP_READ';
 
USERNAME   PRIV                                     OBJECT_OWNER  OBJECT_NAME
---------- ---------------------------------------- ------------- -----------------------
APP_READ   UPDATE                                   APP           EMPLOYEES
APP_READ   SET CONTAINER

More easily the reports can be generated from Cloud Control:

database_vault_12cr1_08
database_vault_12cr1_08

Clean Privileges Analysis with:

SQL> EXEC dbms_privilege_capture.drop_capture(name => 'APP_READ policy');
 
PL/SQL PROCEDURE successfully completed.

References

  • Master Note For Oracle Database Vault (Doc ID 1195205.1)
  • How To Enable Database Vault in a 12c database ? (Doc ID 2112167.1)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>