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:
- Enable Database Vault
- Control Privileged User Access
- Create a Realm to Protect Your Data
- 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.