Oracle Database Vault provides powerful security controls to help protect application data from unauthorized access and comply with privacy and regulatory requirements. Recently, we had a customer purchase the Database Vault option for their environment and was looking for a demonstration of the product to help them get started. This blog entry is a brief introduction on Database Vault that demonstrates how to protect sensitive data from abuse by privileged users like the DBA.
Database Vault protects sensitive data by using Realms. A database Realm is created and contains one or more objects (Tables, Indexes,…) from one or more schemas. Users are authorized directly by the DV_OWNER and the schema owner by granting access. Mandatory Realms only allow users who are directly authorized by DV_OWNER to access the protected objects (not schema owners or anyone granted access by the schema owner). Administrative users can not access objects unless specifically authorized.
(Courtesy Oracle America)
In this post, I will demonstrate how to configure Database Vault and
demonstrate protecting data from the SYSDBA user. For this post, I’m
using Enterprise Edition Database 12c (12.1.0.2) with Oracle
Multi-tenancy on a Windows machine.Oracle Database Vault Configuration
Database Vault is installed when you perform a default installation of Oracle Database. After the database is installed, you must register Oracle Database Vault and then enable the Oracle Database Vault Account Manager user account. If Oracle Label Security is not enabled, then the registration process enables it as well as Database Vault.Register Database Vault:
- Log in as SYS user with SYSDBA privileges
- Check if Oracle Database Vault has already been enabled. The PARAMETER column is case sensitive.SELECT value FROM V$OPTION WHERE parameter = ‘Oracle Database Vault’; If TRUE, then Database Vault is registered. If FALSE, then Database Vault registration is required as follow.
Create accounts for the Database Vault Owner and Account Manager (DV_OWNER and DV_ACCTMGR Roles)
sys@ORCL> grant create session, set container to c##dbv_owner_root identified by oracle_4U container=all; Grant succeeded. sys@ORCL> grant create session, set container to c##dbv_acctmgr_root identified by oracle_4U container=all; Grant succeeded.Configure Database Vault user accounts
sys@ORCL> begin 2 dvsys.configure_dv ( 3 dvowner_uname => 'c##dbv_owner_root', 4 dvacctmgr_uname => 'c##dbv_acctmgr_root'); 5 end; 6 / PL/SQL procedure successfully completed.Recompile Invalid Database Objects
sys@ORCL> @?/rdbms/admin/utlrp.sql -- -- Cut for brevity -- PL/SQL procedure successfully completed. sys@ORCL>Just a note – Oracle recommends creating Backup Accounts for the DV Owner and Account Manager Roles. (e.g. c##dbv_owner_root_bkup and c##dbv_acctmgr_root_bkup)
Connect to Root Container as Database Vault Owner and Enable Database Vault
sys@ORCL> connect c##dbv_owner_root/oracle_4U Connected. c##dbv_owner_root@ORCL> exec dbms_macadm.enable_dv; PL/SQL procedure successfully completed. c##dbv_owner_root@ORCL>Connect to Root Container as SYSDBA and Bounce the Database.
c##dbv_owner_root@ORCL> connect / as sysdba Connected. ys@ORCL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. sys@ORCL> startup ORACLE instance started. Total System Global Area 2550136832 bytes Fixed Size 3048872 bytes Variable Size 671091288 bytes Database Buffers 1862270976 bytes Redo Buffers 13725696 bytes Database mounted. Database opened. sys@ORCL>Register Database Vault common user to manage specific PDBs
Grant Privileges for Database Vault Owner and Account Manager in the Pluggable Database (PDB1)
sys@ORCL> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba Connected. sys@PDB1> grant create session, set container to c##dbv_owner_root container = current; Grant succeeded. sys@PDB1> grant create session, set container to c##dbv_acctmgr_root container = current; Grant succeeded.Configure Database Vault user accounts for PDB
sys@PDB1> begin 2 dvsys.configure_dv ( 3 dvowner_uname => 'c##dbv_owner_root', 4 dvacctmgr_uname => 'c##dbv_acctmgr_root'); 5 end; 6 / PL/SQL procedure successfully completed.Recompile Invalid Database Objects in PDB
sys@PDB1> @?/rdbms/admin/utlrp.sql -- -- Cut for brevity -- PL/SQL procedure successfully completed.Note: In a multitenant environment, you must register Oracle Database Vault in the root first, then in the PDBs. If you try to register in a PDB first, then an ORA-47503: Database Vault is not enabled on CDB$ROOT error appears.
Connect to PDB Container as Database Vault Owner and Enable Database Vault
sys@PDB1> connect c##dbv_owner_root/oracle_4U@//localhost:1521/pdb1.us.oracle.com Connected. c##dbv_owner_root@PDB1> exec dbms_macadm.enable_dv; PL/SQL procedure successfully completed.Connect to Root Container as SYSDBA and Bounce the Pluggable Database
c##dbv_owner_root@PDB1> connect / as sysdba Connected. sys@ORCL> alter pluggable database pdb1 close immediate; Pluggable database altered. sys@ORCL> alter pluggable database pdb1 open; Pluggable database altered. sys@ORCL>
Verify Database Vault is Configured and Enabled
The V$OPTION view and the DVSYS.DBA_DV_STATUS and DBA_OLS_STATUS data dictionary views verify if Oracle Database Vault is configured and enable.Query the V$OPTION view to verify Database Vault and Label Security are configured
sys@ORCL> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba Connected. sys@PDB1> select value from v$option where parameter = 'Oracle Database Vault'; VALUE ---------------------------------------------------------------- TRUE sys@PDB1> select value from v$option where parameter = 'Oracle Label Security'; VALUE ---------------------------------------------------------------- TRUEQuery the DVSYS.DBA_DV_STATUS view to verify Database Vault status
sys@PDB1> select * from dvsys.dba_dv_status; NAME STATUS ------------------- --------------------------------- DV_CONFIGURE_STATUS TRUE DV_ENABLE_STATUS TRUE
Database Vault Demonstration:
The V$OPTION view and the DVSYS.DBA_DV_STATUS and DBA_OLS_STATUS data dictionary views verify if Oracle Database Vault is configured and enabled.Enable the Scott account and grant access to the Order Entry Customer’s table
idle> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba Connected. sys@PDB1> alter user scott identified by oracle_4U account unlock; User altered. sys@PDB1> alter user oe identified by oracle_4U account unlock; User altered. sys@PDB1> connect oe/oracle_4U@//localhost:1521/pdb1.us.oracle.com Connected. oe@PDB1> grant select on customers to scott; Grant succeeded.Query the OE.CUSTOMERS table as SYS and SCOTT
At this stage, both SYS and SCOTT have select privileges on the CUSTOMERS table because SYS has Administrative privileges and SCOTT has a direct grant.
oe@PDB1> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba Connected. sys@PDB1> select count(*) from oe.customers; COUNT(*) ---------- 319 sys@PDB1> connect scott/oracle_4U@//localhost:1521/pdb1.us.oracle.com Connected. scott@PDB1> select count(*) from oe.customers; COUNT(*) ---------- 319Create a Realm to Protect the OE.CUSTOMERS Table
I’m sorry – We have to do this the hard way, I don’t have Enterprise Manager Cloud Control on my Laptop
scott@PDB1> connect c##dbv_owner_root/oracle_4U@//localhost:1521/pdb1.us.oracle.com Connected. c##dbv_owner_root@PDB1> begin 2 DBMS_MACADM.CREATE_REALM ( 3 realm_name => 'OE Protections', 4 description => 'Realm to protect OE schema', 5 enabled => DBMS_MACUTL.G_YES, 6 audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL, 7 realm_type => 1); 8 end; 9 / PL/SQL procedure successfully completed.Add OE tables to Realm for protection
c##dbv_owner_root@PDB1> begin 2 DBMS_MACADM.ADD_OBJECT_TO_REALM( 3 realm_name => 'OE Protections', 4 object_owner => 'OE', 5 object_name => '%', 6 object_type => 'TABLE'); 7 end; 8 / PL/SQL procedure successfully completed. c##dbv_owner_root@PDB1>Create Realm Authorizations for OE and Scott
c##dbv_owner_root@PDB1> begin 2 DBMS_MACADM.ADD_AUTH_TO_REALM( 3 realm_name => 'OE Protections', 4 grantee => 'OE', 5 auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER); 6 end; 7 / c##dbv_owner_root@PDB1> begin 2 dbms_macadm.add_auth_to_realm( 3 realm_name => 'OE Protections', 4 grantee => 'SCOTT', 5 auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT); 6 end; 7 / PL/SQL procedure successfully completed.Test our Database Vault protections for the OE Tables
scott@PDB1> connect oe/oracle_4U@//localhost:1521/pdb1.us.oracle.com Connected. oe@PDB1> select table_name from user_tables; TABLE_NAME ---------------------------------------------------------------------------------------------------- CUSTOMERS WAREHOUSES ORDER_ITEMS ORDERS INVENTORIES PRODUCT_INFORMATION PRODUCT_DESCRIPTIONS PROMOTIONS PRODUCT_REF_LIST_NESTEDTAB SUBCATEGORY_REF_LIST_NESTEDTAB 10 rows selected. oe@PDB1> select count(*) from customers; COUNT(*) ---------- 319 oe@PDB1> select count(*) from warehouses; COUNT(*) ---------- 9This just shows that the Owner of the Realm has access to their tables.
idle> connect scott/oracle_4U@//localhost:1521/pdb1.us.oracle.com Connected. scott@PDB1> select count(*) from oe.customers; COUNT(*) ---------- 319 scott@PDB1> select count(*) from oe.warehouses; select count(*) from oe.warehouses * ERROR at line 1: ORA-00942: table or view does not existThis shows that Scott has access to the customers table in the Realm but not the warehouses table. While the Realm we configured contains all of OE’s tables, any user in the Realm like Scott still needs to have appropriate privileges to the objects in the Realm.
scott@PDB1> connect sys/oracle_4U@//localhost:1521/pdb1.us.oracle.com as sysdba Connected. sys@PDB1> select count(*) from oe.customers; select count(*) from oe.customers * ERROR at line 1: ORA-01031: insufficient privileges sys@PDB1> select count(*) from oe.warehouses; select count(*) from oe.warehouses * ERROR at line 1: ORA-01031: insufficient privilegesFinally, this shows that SYS with SYSDBA privileges does not have authority to view data that is in the Realm! As you can see, using Database Vault helps prevent the “insider attack”.
References:
Database Vault Administrator’s Guide – https://docs.oracle.com/database/121/DVADM/toc.htmDatabase 2 Day + Security Guide – https://docs.oracle.com/database/121/TDPSG/toc.htm
Oracle Sample Schemas on GitHub – https://github.com/oracle/db-sample-schemas
No comments:
Post a Comment