Procedure for authorizing and disallowing users in Oracle Database Vault Realm

I must thank my fellow DBA Franky Weber Faust for his publication in his blog.

Introduction: – In this article, we have look at the document the procedure required to authorize a user as a participant in the Oracle Database Vault Realm and then disallow it. All this procedure is possible through Enterprise Manager, but as it is not always available, I will leave it documented here for when someone, including me, needs it.

What is a Database Vault in Oracle 12c?

Oracle Database Vault restricts ingress to specific areas in an Oracle database from any user, including users who have administrative access. For example, you can restrict administrative access to employee salaries, customer medical records, or other impervious information. You configure Oracle Database Vault to manage the security of an individual Oracle Database instance. You can install Oracle Database Vault on standalone Oracle Database installations, in multiple Oracle homes, and in Oracle Real Application Clusters (Oracle RAC) environments. Database Vault is very useful to protect your data from users such as DBA who has access to all tables, But the questions are who is control database vault?  Usually, there are two users to control it, Database Vault owner this user is granted the DV_Owner role and can manage database role and configurations, the username must be minimum 2 and maximum 30 characters, the password for this user should be complex. Another user called: Database Vault manager which is granted DV_ACCTMGR role, and used to manage database user account, this user is created to facilitate separation duties which mean while you install you can only create one user do all these jobs, the username should be minimum 2/maximum 30 character and the password is complex. 

Consult with the DV_MANAGER user in which Realm the protected table is:

SQL> col NAME for a30
SQL> col OWNER for a30
SQL> col OBJECT_NAME for a20
SQL> SELECT r.name,
r.audit_options,
r.realm_type,
r.enabled,
ro.owner,
ro.object_name,
ro.object_type
FROM dvsys.dba_dv_realm r
LEFT OUTER JOIN dvsys.dba_dv_realm_object ro
ON r.name = ro.realm_name
WHERE ro.object_name = 'TABELA_PROTEGIDA';

NAME AUDIT_OPTIONS REALM_TYP E OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------- --------- - ------------------------------ -------------------- -----------
SENSITIVE_DATA_REALM 3 REGULAR Y APP TABELA_PROTEGIDA TABLE

Authorize the SYSTEM user in the REALM in question:

SQL> BEGIN
DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'SENSITIVE_DATA_REALM',
grantee => 'SYSTEM',
auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);
END;
PL/SQL procedure successfully completed.

SQL> delete from APP.TABELA_PROTEGIDA where ID=200;

1 row deleted;

SQL> BEGIN
DBMS_MACADM.DELETE_AUTH_FROM_REALM(
realm_name => 'SENSITIVE_DATA_REALM',
grantee => 'SYSTEM');
END;
PL/SQL procedure successfully completed.