Oracle Database Vault on Autonomous Database: Quickly and Easily

June 4, 2020 | 7 minute read
Can Tuzla
Principal Product Manager
Text Size 100%:

We often talk about how to protect our data from external threats by implementing various practices such as encrypting our data, only allowing connections via TCPS authentication or even further, deploying our databases in a private network. However, these security practices might not be sufficient to eliminate the threats that can originate from unauthorized privileged user access or unauthorized database changes. Good news is that Oracle Database Vault offers comprehensive access control capabilities to prevent those internal threats. What’s better is that you can use it in your Oracle Autonomous Database Serverless (ADB-S) as well!

In this blog post, we are going to focus on how to take advantage of Database Vault in ADB-S by just following a few simple steps:

  1. Enable Database Vault
  2. Control Privileged User Access
  3. Create a Realm to Protect Your Data
  4. Disable Database Vault

Enable Database Vault

To configure and enable Oracle Database Vault on Autonomous Database, we will follow the steps described in our documentation:

  • Create two users, one to be the Database Vault owner while the other one to be the Database Vault account manager. This is the first step towards implementing separation of duties.
ctuzla$ ./sqlplus ADMIN/************@tuzladv_low

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 3 21:07:29 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Jun 03 2020 20:55:08 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> create user mydvowner identified by ************;

User created.

SQL> create user mydvacctmgr identified by ************;

User created.
  • Configure Database Vault:
SQL> EXEC DBMS_CLOUD_MACADM.CONFIGURE_DATABASE_VAULT('mydvowner', 'mydvacctmgr');

PL/SQL procedure successfully completed.
  • Enable Database Vault:
SQL> EXEC DBMS_CLOUD_MACADM.ENABLE_DATABASE_VAULT;

PL/SQL procedure successfully completed.
  • Restart your ADB-s instance.
  • Confirm Database Vault is enabled:
SQL> SELECT * FROM DBA_DV_STATUS;

NAME		                                 STATUS
------------------   -------------------
DV_APP_PROTECTION    NOT CONFIGURED
DV_CONFIGURE_STATUS  TRUE
DV_ENABLE_STATUS     TRUE

Control Privileged User Access

In the previous section, we have seen how to set up separate users to be the designated Database Vault owner and account manager. This was an important step in order to achieve separation of duties; however, we are not done yet. In Autonomous Database, ADMIN user has DV_OWNER and DV_ACCTMGR roles by default. What this means is that even after configuring and enabling Database Vault with different users, ADMIN user can still perform certain operations such user management thanks to these roles. Even though ADMIN can be considered as a backup account for both Database Vault owner and account manager, it’s fairly easy to revoke these roles from ADMIN as we will see below.

  • Connect to your ADB-S instance as the designated Database Vault account manager and revoke DV_ACCTMGR role from ADMIN:
ctuzla$ ./sqlplus MYDVACCTMGR/************@tuzladv_low

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 3 22:42:00 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Jun 03 2020 21:18:48 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> revoke dv_acctmgr from ADMIN;

Revoke succeeded.
  • Connect to your ADB-S instance as the designated Database Vault owner and revoke DV_OWNER role from ADMIN:
ctuzla$ ./sqlplus MYDVOWNER/************@tuzladv_low

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 3 22:43:30 2020
Version 19.3.0.0.0

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

Last Successful login time: Sun May 31 2020 21:38:21 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> revoke dv_owner from ADMIN;

Revoke succeeded.
  • Confirm ADMIN user cannot perform any user operations:
ctuzla$ ./sqlplus ADMIN/************@tuzladv_low

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 3 23:00:05 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Jun 03 2020 21:30:55 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> create user testuser identified by ************;
create user testuser identified by ************
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> alter user dvdemo identified by ************;
alter user dvdemo identified by ************
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> drop user dvdemo;
drop user dvdemo
*
ERROR at line 1:
ORA-01031: insufficient privileges

Create a Realm to Protect Your Data

In this section, we will explore how to create a realm to protect tables of a schema. For this demonstration, I will be using my DVDEMO schema that contains a table named PRODUCTS.

  • Check if ADMIN can access the DVDEMO.PRODUCTS table:
ctuzla$ ./sqlplus ADMIN/************@tuzladv_low

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 3 23:00:05 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Jun 03 2020 21:30:55 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> select count(*) from dvdemo.products;

  COUNT(*)
----------
	72

As you can see, ADMIN can access the data that belongs to another schema since it’s not protected by a realm yet.

  • Create a realm for all tables of the DVDEMO schema as Database Vault owner:
SQL> begin
  DBMS_MACADM.CREATE_REALM (
    realm_name => 'DVDEMO Realm',
    description => 'Realm for DVDEMO schema',
    enabled => DBMS_MACUTL.G_YES,
    audit_options => DBMS_MACUTL.G_REALM_AUDIT_OFF,
    realm_type => 1);
end;
/ 

PL/SQL procedure successfully completed. 

SQL> begin
  DBMS_MACADM.ADD_OBJECT_TO_REALM(
    realm_name => 'DVDEMO Realm',
    object_owner => 'DVDEMO',
    object_name => '%',
    object_type => 'TABLE');
end;
/  

PL/SQL procedure successfully completed.
  • Authorize DVDEMO to access the realm:
SQL> begin
  DBMS_MACADM.ADD_AUTH_TO_REALM(
    realm_name => 'DVDEMO Realm',
    grantee => 'DVDEMO',
    auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
end;
/ 

PL/SQL procedure successfully completed.
  • Try to access PRODUCTS table as ADMIN and DVDEMO users:
ctuzla$ ./sqlplus ADMIN/************@tuzladv_low

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 4 00:04:48 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Jun 03 2020 23:00:06 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> select count(*) from dvdemo.products;
select count(*) from dvdemo.products
                            *
ERROR at line 1:
ORA-01031: insufficient privileges

ctuzla$ ./sqlplus DVDEMO/************@tuzladv_low

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 4 00:05:44 2020
Version 19.3.0.0.0

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

Last Successful login time: Sun May 31 2020 20:57:16 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> select count(*) from dvdemo.products;

  COUNT(*)
----------
	72

As expected, only DVDEMO can access the PRODUCTS table.

Disable Database Vault

As the final step, let’s take a look at how to disable Database Vault.

  • Disable Database Vault using the following API:
ctuzla$ ./sqlplus MYDVOWNER/************@tuzladv_low

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 4 00:13:10 2020
Version 19.3.0.0.0

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

Last Successful login time: Thu Jun 04 2020 00:08:31 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> EXEC DBMS_CLOUD_MACADM.DISABLE_DATABASE_VAULT;

PL/SQL procedure successfully completed.
  • Restart your ADB-s instance.
  • Confirm Database Vault is disabled:
ctuzla$ ./sqlplus ADMIN/************@tuzladv_low

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 4 00:16:50 2020
Version 19.3.0.0.0

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

Last Successful login time: Thu Jun 04 2020 00:04:48 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> SELECT * FROM DBA_DV_STATUS;

NAME		                               STATUS
------------------- --------------------------
DV_APP_PROTECTION   NOT CONFIGURED
DV_CONFIGURE_STATUS TRUE
DV_ENABLE_STATUS    FALSE
  • Check if ADMIN can access the DVDEMO.PRODUCTS table now:
SQL> select count(*) from dvdemo.products;

  COUNT(*)
----------
	72

It is pretty simple, isn’t it? In just a few steps, we have gone through how to enable Database Vault, how to limit privileged user access via a realm and how to revert everything back to square one by disabling Database Vault. It’s important to note that this is just a small subset of all the capabilities Database Vault offers. If you’d like to learn more about Database Vault, please check out Database Vault Administrator’s Guide.

Can Tuzla

Principal Product Manager

Can is a Principal Product Manager for Oracle Autonomous Database (ADB-S) and has been with the company since 2014. Prior to joining the ADB-S team, he worked on the Oracle Multitenant and Oracle Query Optimizer teams. Can holds a MS (Computer Science) from Case Western Reserve University and a BS (Computer Engineering) from Bilkent University.


Previous Post

Autonomous Database Newsletter - June 4-2020

Keith Laker | 8 min read

Next Post


Query ORC files and complex data types in Object Storage with Autonomous Database

Marty Gubar | 3 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider