Table of contents
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:
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:
Enter a name and a description, its original status and what you wan to audit:
[
Choose the objects you’d like to protect, let rest by default:
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 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:
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:
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:
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)
