Configuration and administration of Oracle Database Vault 12c in a multitenant environment using OEM Cloud Control 12c

Version 2
Created by LaserSoft on Sep 1, 2016 10:33 PM. Last modified by LaserSoft on Sep 28, 2017 3:04 AM.

    Introduction

    Oracle Database Vault is a declarative security framework built within the Oracle kernel. By using Oracle Database Vault, you can impose restriction over very sensitive data for the DBA, SYS and SYSTEM users or any user with SELECT ANY TABLE privilege. It was mainly developed to ensure the separation of duties (Sod) and make the database security compliant. Oracle Database Vault includes specific components in addition to the main security mechanisms provided by Oracle database such as factors, rules, rule sets, realms, command rules and secure application roles. In this article we will not demonstrate how to implement security using these components; rather, we will focus in how to configure the OEMCC Database interface. More about Oracle Vault components can be found at (https://docs.oracle.com/database/121/DVADM/dvintro.htm#DVADM70110) on the main Oracle Vault documentation page.

     

    Starting with Oracle Database version 12.1.0.1 Oracle Vault is no longer integrated with OEM. So two options are available: one is to use the PL/SQL API (DBMS_MACADM package) and one is to use Oracle Manager Cloud Control 12c/13c. In the following article we will demonstrate how to configure Cloud Control 12c Oracle Vault interface in a multitenant environment and demonstrate some operations related to Database operation authorizations such as data pump and oradebug.

     

    Environmental setup

    • Oracle Virtual Box with Oracle Enterprise Linux 6.5
    • Oracle Database Ver. 12.1.0.2.0 Enterprise Edition
    • Oracle Enterprise Manager Cloud Control 12c

     

     

    Installation and configuration

    • Launch dbca, check create database and click the Next button.
    • Check advanced mode at the creation mode step and click the Next button.
    • Check General purpose or transaction processing at the database template step and click the Next button.

     

     

    • At the Database identification step, Global Database Name: ORCL, SID: ORCL, Select the option “Create as Container Database” and choose the option “Create a Container Database with one or more PDBs”, as number of PDBs add 1, and PDB Name will be ORCL1_1 and click next as follows:

     

    ConfigDBVault12cfig1.jpg

     

     

     

    • When we are the Database Options step, click on the Database Vault & Label Security tab. Select the option “Configure Database vault”. As database Vault owner add c##dv_admin, as password we used Next1234#. Select the option “Create a Separate Account Manager” and input the values for Account Manager, c##dv_acctmgr, password Next1234# and click the Next button as follows:

     

    ConfigDBVault12cFig2.jpg

     

    • The Oracle database vault management users must be created at container level.
    • The c##dv_admin user is the administrative user. It is used for creating Database Vault Policies.
    • The c##dv_acctmgr is used for managing user accounts.

     

    Click Next and go through the wizard until the database is created.

     

    The management users have been created at root container level, to be able to connect with these users at any other container level we should add the create session privilege to c##dv_admin and c##dv_acctmgr as follows:

     

    SQL> connect / as sysdba

    Connected.

     

    SQL> alter session set container=ORCL1_1;

    Session altered.

     

    SQL> grant create session to c##dv_admin,c##dv_acctmgr;

    Grant succeeded.

     

    Configure the dbsnmp user for Database Vault monitoring

     

    To be able to monitor Orale Database Vault with OEM Clound Control 12c, dbsmnp must have the DV_MONITOR privilege granted. Connect as Database Vault account manager to ORCL database, change the password and unlock the dbsnmp account as follows: 

     

    [oracle@emnode bin]$ $ORACLE_HOME/bin/sqlplus c##dv_acctmgr/Next1234#

     

    SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 26 16:04:27 2016

    Copyright (c) 1982, 2014, Oracle.  All rights reserved.

     

    Connected to:

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,

    Oracle Database Vault and Real Application Testing options

     

    SQL> alter user dbsnmp identified by "db%1345T" account unlock;

    User altered.

     

    Connect as Oracle Vault administrator and grant the DV_MONITOR privilege to dbsnmp as follows:

     

    SQL> conn c##dv_admin/Next1234#

    1. Connected.

     

    SQL> grant dv_monitor to dbsnmp;

    Grant succeeded.

     

    Add the ORCL database and configure the OEMCC 12c Database Vault Interface

     

    Login to Oracle Enterprise Manager Cloud Control 12c and discover the database using the manual guided process. Navigate to Setup menu, Add targets, choose Add targets Manually. Next, as target type choose Oracle database, listener and Automatic Storage Management and click on the Next button as follows:

     

    ConfigDBVault12cFig3.jpg

     

    Next from the Host Pop-up choose the host name and click the Select button as follows:

     


    ConfigDBVault12cFig4.jpg

     

    The host will be completed and push the Next button:

     

    ConfigDBVault12cFig5.jpg

    Next, check the database called ORCL which is the root container database and click the Configure button as follows:

     

    ConfigDBVault12cFig6.jpg

    Enter the dbsnmp user password ( db%1345T) and click the Test Connection button. If the connection is successful click on the Save button as follows:

     

     

    ConfigDBVault12cFig7.jpg

     

     

     


    The final page of the discovery process is the Review page, here we can observe that the pluggable database ORCL_ORCL1_1 was discovered along with the root container database ORCL as follows:

     

    ConfigDBVault12cFig8.jpg

     

    The discovery process finalize and the database is added as monitored target:

     

    ConfigDBVault12cFig9.jpg

       

    Use the Oracle Vault Integrated Interface

     

    From the Target menu, click on Database submenu, next drill down to pluggable database ORCL1_1 and click on it.


    ConfigDBVault12cFig10.jpg

     

    From security menu, click on Database Vault as follows:

     

    ConfigDBVault12cFig11.jpg

     


    As username enter the database vault administrator defined at the database creation, c##dv_admin and as password type Next1234#. You can set this as preferred credential, but this is optional. Next, click on the Login button.

     

    ConfigDBVault12cFig12.jpg

     

     

    The Oracle Database Vault Interface will be opened. In the home page briefly, we have the option to enable or disable oracle vault also we can see links to various rule violation reports in the right part of the page.


    ConfigDBVault12cFig13.jpg

     

    In the Administration tab we have the possibility to define and configure the oracle vault components such as realms, command rules, rule sets, factors, factor types, secure application roles, OLS integration, Database Vault Roles.

     

    ConfigDBVault12cFig14.jpg

    In the Database operation Authorizations tab we can define rules regarding data pump, scheduler, Streams and GoldenGate, database patching, initialization parameters and oradebug. In the next paragraphs we will demonstrate how to define rules of data pump and disable oradebug. The Oradebug control is a new feature introduced in 12.1.0.2.


    ConfigDBVault12cFig15.jpg

     

    Configure Database Operation Authorization – Data Pump

     

    Create a network service name ORCL1_1. Next, try to perform a full database export with the SYSTEM user :

     

    expdp system/sys@orcl1_1 dumpfile=export:export_full.dmp logfile=export:export_full.log full=y

     

    Export: Release 12.1.0.2.0 - Production on Sun Jun 26 16:45:49 2016

    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

     

    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,

    Oracle Database Vault and Real Application Testing options

    Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@orcl1_1 dumpfile=export:export_full.dmp logfile=export:export_full.log full=y

    ORA-39327: Oracle Database Vault data is being stored unencrypted in dump file set.

    Estimate in progress using BLOCKS method...

    Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

    Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

    Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

    Total estimation using BLOCKS method: 4.265 MB

    Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

    Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

    ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [MARKER]

    ORA-01031: insufficient privileges

     

    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

    ORA-06512: at "SYS.KUPW$WORKER", line 11259

     

    ----- PL/SQL Call Stack -----

      object      line object

      handle    number name

    0x124772758 27116  package body SYS.KUPW$WORKER

    0x124772758 11286  package body SYS.KUPW$WORKER

    0x124772758 13515  package body SYS.KUPW$WORKER

    0x124772758 3173  package body SYS.KUPW$WORKER

     

    The operation will fail.

     

    To authorize SYSTEM user to perform full export with Oracle Vault enabled navigate to the Database Operations Authorizations tab click on Data Pump. In the left panel click on Add button as follows:


    ConfigDBVault12cFig16.jpg

     

     

    Choose SYSYEM user, select the option “Allow Full database” and grant also DV_OWNER role. A full database export obviously will include the database vault users and schemas (DVSYS and DVF), without having the DV_OWNER role granted the full export will fail. Click the OK button.


    ConfigDBVault12cFig17.jpg

     

    Try the full export again with SYSTEM user. This time the export will succeed.

     

    expdp system/sys@orcl1_1 dumpfile=export:export_full.dmp logfile=export:export_full.log full=y

     

    Export: Release 12.1.0.2.0 - Production on Sun Jun 26 17:09:14 2016

    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

     

    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,

    Oracle Database Vault and Real Application Testing options

    Starting "SYSTEM"."SYS_EXPORT_FULL_03":  system/********@orcl1_1 dumpfile=export:export_full.dmp logfile=export:export_full.log full=y

    ORA-39327: Oracle Database Vault data is being stored unencrypted in dump file set.

    Estimate in progress using BLOCKS method...

    Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

    Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

    Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

    Total estimation using BLOCKS method: 5.578 MB

    Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

    Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

    Processing object type DATABASE_EXPORT/TABLESPACE

    Processing object type DATABASE_EXPORT/PROFILE

    Processing object type DATABASE_EXPORT/SYS_USER/USER

    Processing object type DATABASE_EXPORT/SCHEMA/USER

    Processing object type DATABASE_EXPORT/RADM_FPTM

    Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

    Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

    Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

    Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

    Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT

    Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

    Processing object type DATABASE_EXPORT/RESOURCE_COST

    Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

    Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY

    Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT

    Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM

     

    ….............................................................................................................................................

    Master table "SYSTEM"."SYS_EXPORT_FULL_03" successfully loaded/unloaded

    ******************************************************************************

    Dump file set for SYSTEM.SYS_EXPORT_FULL_03 is:

      /kit/export_full.dmp

    Job "SYSTEM"."SYS_EXPORT_FULL_03" completed with 1 error(s) at Sun Jun 26 17:12:01 2016 elapsed 0 00:02:46

     

    Configure Database Operation Authorization – Oradebug

    The oradebug utility is mostly used for “deep” debug of various problems. It directly interacts with the oracle kernel and can be used for generation of traces and dump, it can be used also for suspending processes.  As you probably guessed it may turn into a handy tool in some experienced malicious hands. From a security point of view is better to have it disabled and enabled only when we really need it. From version 12.1.0.2 we can disable/enable oradebug from Oracle Vault.

     

    In Database Operation Authorization tab navigate to ORADEBUG.. In the left panel click on the disable button. A confirmation window will pop up, click on OK.


    ConfigDBVault12cFig18.jpg

     

    Connect to ORCL1_1 pluggable database as sysdba and try to use oradebug as follows:

     

    [oracle@emnode bin]$ sqlplus sys/sys@orcl1_1 as sysdba

     

    SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 14:01:31 2016

    Copyright (c) 1982, 2014, Oracle.  All rights reserved.

     

    Connected to:

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,

    Oracle Database Vault and Real Application Testing options

     

    SQL> oradebug setmypid

    ORA-47411: Cannot use ORADEBUG due to Oracle Database Vault policy.

     

    As we can see oradebug is now disabled by Oracle Vault.

     

    Enable/Disable Oracle Vault

    Before Oracle Database 12c, to disable Oracle Vault we have to shutdown everything and relink the database executables using the dv_off option. In Oracle Database 12c there is a procedure called DBMS_MACADM.DISABLE_DV to disable Oracle Vault at container level. This can be performed also from Oracle Enterprise Manager Cloud Control Oracle Vault's interface.

    Navigate to home tab and click on the Disable button, a confirmation window will pop up asking us to continue or to cancel, press continue as follows:


    ConfigDBVault12cFig19.jpg

     

    The next step is to restart the Pluggable Database (ORCL1_1) database to complete the disable of Oracle Vault.

     

    Connect to ORCL database as sysdba and issue the following commands:

     

    SQL> alter pluggable database orcl1_1 close;

    Pluggable database altered.

     

    SQL> alter pluggable database orcl1_1 open;

    Pluggable database altered.

     

    To check that Oracle Vault was really disabled we can try again to use oradebug which was previously disabled by Oracle Vault as follows:

     

    [oracle@emnode bin]$ sqlplus sys/sys@orcl1_1 as sysdba

     

    SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 14:12:38 2016

    Copyright (c) 1982, 2014, Oracle.  All rights reserved.

     

    Connected to:

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

    and Real Application Testing options

     

    SQL> oradebug setmypid

    Statement processed.

     

    [oracle@emnode bin]$ sqlplus sys/sys@orcl1_1 as sysdba

     

    SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 14:12:38 2016

    Copyright (c) 1982, 2014, Oracle.  All rights reserved.

     

    Note: If you disable Oracle Vault at root level it will be disabled for all containers

     

    Enable the Oracle Vault back by using the DBMS_MACADM  PL/SQL API this time. Connect as Oracle Vault owner and execute the following commands:

     

    SQL> conn c##dv_Admin/Next1234#@orcl1_1

    1. Connected.

     

    SQL> exec DBMS_MACADM.ENABLE_DV;

    PL/SQL procedure successfully completed.

     

    Restart the database and try to use oradebug again

     

    [oracle@emnode bin]$ sqlplus sys/sys@orcl1_1 as sysdba

     

    SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 27 14:22:15 2016

    Copyright (c) 1982, 2014, Oracle.  All rights reserved.

     

    Connected to:

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,

    Oracle Database Vault and Real Application Testing options

     

    SQL> oradebug setmypid;

    ORA-47411: Cannot use ORADEBUG due to Oracle Database Vault policy.

     

    Oracle vault enabled back with all the policies in place.

     

    Summary

    In the current article we tried to demonstrate how to integrate the administration of Oracle Vault 12c with OEMCC 12c used in multitier environment. We created a database called ORCL and a Pluggable Database ORCL1_1 and configured Database Vault using the DBCA option. In the remaining part of the article we demonstrated how to use two Oracle Database Authorization components namely DataPump and the new Oradebug feature introduced in 12.1.0.2. The last operation demonstrated was how to disable Oracle Database Vault from the Oracle Vault Console and by using the Oracle Database Vault API.