Let us learn Oracle Database Administration skills: July 2023

Saturday, July 22, 2023

Oracle Database Vault - A walk through

Oracle Database Vault Setup:

Caution: Considering time I couldnt review the complete oracle documentation for this exercise.

Still I have gone through the ASKTom Office Hours video on the database vault.

1 other website and oracle 12.1 database vault doeumentation partly.

Oracle database vault configuration steps:
1. Check if vault is enabled already
2. Create necessary vault admin and vault account manager users
3. Configure the vault
4. Activate the vault
5. Test application object setup -- is already avaible from TDE setup (skipped here)
6. Setup a read only user for the app schema
7. Examing existing realm setup
8. Setup a new realm and add application objects to it
9. Create a rule,ruleset, command rule to restrict the app read only user access to the db.

1. Database vault status:

check if necessary components are installed already:

select comp_id,comp_name,status,version from dba_registry;
COMP_ID ,COMP_NAME ,STATUS ,VERSION
------------------------------,---------------------------------------------,--------------------,------------------------------
DV ,Oracle Database Vault ,VALID ,12.1.0.2.0 <<< Needed
APEX ,Oracle Application Express ,VALID ,4.2.5.00.08
OLS ,Oracle Label Security ,VALID ,12.1.0.2.0 <<< Needed
SDO ,Spatial ,VALID ,12.1.0.2.0
ORDIM ,Oracle Multimedia ,VALID ,12.1.0.2.0
CONTEXT ,Oracle Text ,VALID ,12.1.0.2.0
OWM ,Oracle Workspace Manager ,VALID ,12.1.0.2.0
XDB ,Oracle XML Database ,VALID ,12.1.0.2.0
CATALOG ,Oracle Database Catalog Views ,VALID ,12.1.0.2.0
CATPROC ,Oracle Database Packages and Types ,VALID ,12.1.0.2.0
JAVAVM ,JServer JAVA Virtual Machine ,VALID ,12.1.0.2.0
XML ,Oracle XDK ,VALID ,12.1.0.2.0
CATJAVA ,Oracle Database Java Packages ,VALID ,12.1.0.2.0
APS ,OLAP Analytic Workspace ,VALID ,12.1.0.2.0
XOQ ,Oracle OLAP API ,VALID ,12.1.0.2.0
RAC ,Oracle Real Application Clusters ,OPTION OFF ,12.1.0.2.0
16 rows selected.
SQL>

SET lines 300
set colsep ,
SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';
Output:
SQL> SET lines 300
set colsep ,SQL>
SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';
PARAMETER ,VALUE
----------------------------------------------------------------,----------------------------------------------------------------
Oracle Database Vault ,FALSE
SQL>

2. We need the dbv_owner and dbc_acctmgr users created (this can be any other names as per your standard).

CREATE USER dbv_owner IDENTIFIED BY "dbv_owner";
CREATE USER dbv_acctmgr IDENTIFIED BY "dbv_acctmgr";

Output:

SQL> CREATE USER dbv_owner IDENTIFIED BY "dbv_owner";
User created.

SQL> CREATE USER dbv_acctmgr IDENTIFIED BY "dbv_acctmgr";
User created.

SQL> alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
Session altered.

SQL> select username,account_status,last_login,created from dba_users where username like 'DBV%' order by 1;
USERNAME ,ACCOUNT_STATUS ,LAST_LOGIN,CREATED
--------------------,------------------------------,----------,--------------------
DBV_ACCTMGR ,OPEN , ,22/JUL/2023 13:28:30
DBV_OWNER ,OPEN , ,22/JUL/2023 13:28:24
SQL>

3. Now let us configure the database vault:

EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');

SQL> EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');
PL/SQL procedure successfully completed.
SQL>

Let us check what permissions were granted to these users:

SQL> select * from dba_sys_privs where grantee in ('DBV_OWNER','DBV_ACCTMGR') order by grantee;
GRANTEE ,PRIVILEGE ,ADM,COM
----------,------------------------------,---,---
DBV_OWNER ,ADMINISTER DATABASE TRIGGER ,NO ,NO
DBV_OWNER ,ALTER ANY TRIGGER ,NO ,NO

SQL> col grantee for a15
SQL> select * from dba_role_privs where grantee in ('DBV_OWNER','DBV_ACCTMGR') order by grantee;
GRANTEE ,GRANTED_ROLE ,ADM,DEL,DEF,COM
---------------,--------------------,---,---,---,---
DBV_ACCTMGR ,DV_ACCTMGR ,YES,NO ,YES,NO
DBV_ACCTMGR ,CONNECT ,NO ,NO ,YES,NO
DBV_OWNER ,CONNECT ,NO ,NO ,YES,NO
DBV_OWNER ,DV_OWNER ,YES,NO ,YES,NO

SQL> select * From dba_Tab_privs where grantee in ('DBV_OWNER','DBV_ACCTMGR') order by grantee;
GRANTEE ,OWNER ,TABLE_NAME ,GRANTOR ,PRIVILEGE ,GRA,HIE,COM,TYPE
---------------,--------------------,--------------------,----------,------------------------------,---,---,---,------------------------
DBV_OWNER ,SYS ,DBMS_RLS ,SYS ,EXECUTE ,YES,NO ,NO ,PACKAGE
SQL>

So an extra step to see who else is granted these roles...

SQL> select * from dba_role_privs where GRANTED_ROLE in ('DV_ACCTMGR','DV_OWNER') order by grantee;

GRANTEE ,GRANTED_ROLE ,ADM,DEL,DEF,COM
---------------,--------------------,---,---,---,---
DBV_ACCTMGR ,DV_ACCTMGR ,YES,NO ,YES,NO
DBV_OWNER ,DV_OWNER ,YES,NO ,YES,NO
DVSYS ,DV_OWNER ,YES,NO ,YES,YES <<< the DVSYS user is granted the role of owner
DVSYS ,DV_ACCTMGR ,YES,NO ,YES,YES <<< the DVSYS user is granted the role of account manager
SQL>

4. Activate the database vault

as dv owner:
EXEC dbms_macadm.enable_dv;

Output:

SQL> conn dbv_owner/dbv_owner
Connected.

SQL> show user;
USER is "DBV_OWNER"

SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
SET CONTAINER
ADMINISTER DATABASE TRIGGER
ALTER ANY TRIGGER
GRANT ANY ROLE
CREATE SESSION

SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
CONNECT
DV_SECANALYST
DV_MONITOR
DV_ADMIN
DV_OWNER
DV_PUBLIC
DV_PATCH_ADMIN
DV_STREAMS_ADMIN
DV_GOLDENGATE_ADMIN
DV_XSTREAM_ADMIN
DV_GOLDENGATE_REDO_ACCESS
DV_AUDIT_CLEANUP
DV_DATAPUMP_NETWORK_LINK
13 rows selected.

SQL> set lines 300
SQL> set pages 3000
SQL> set colsep ,
SQL> set time on

13:42:08 SQL> set timing on
13:42:10 SQL> EXEC dbms_macadm.enable_dv;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22

13:42:15 SQL>
13:42:15 SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';
PARAMETER ,VALUE
----------------------------------------------------------------,----------------------------------------------------------------
Oracle Database Vault ,FALSE
Elapsed: 00:00:00.00
13:48:14 SQL>

>> still false

On executing the procedure above.. these objects had a change. But no objects were created new.

13:45:02 SQL> select owner,object_name,object_type,created,last_ddl_Time from dba_objects where last_ddl_Time > sysdate -10/1440 order by 1,2,3;
OWNER ,OBJECT_NAME ,OBJECT_TYPE ,CREATED ,LAST_DDL_TIME
--------------------,------------------------------,-----------------------,--------------------,--------------------
APEX_040200 ,ORACLE_APEX_MAIL_QUEUE ,JOB ,07/JUL/2014 06:29:55,22/JUL/2023 13:45:02
LBACSYS ,LBAC$AFTER_CREATE ,TRIGGER ,07/JUL/2014 06:29:04,22/JUL/2023 13:42:15 << exact time around activation
LBACSYS ,LBAC$AFTER_DROP ,TRIGGER ,07/JUL/2014 06:29:04,22/JUL/2023 13:42:15 << exact time around activation
LBACSYS ,LBAC$BEFORE_ALTER ,TRIGGER ,07/JUL/2014 06:29:04,22/JUL/2023 13:42:15 << exact time around activation
SYS ,FILE_SIZE_UPD ,JOB ,07/JUL/2014 05:51:48,22/JUL/2023 13:44:40
13:45:14 SQL>

13:46:25 SQL> select owner,trigger_name,status from dba_triggers where owner='LBACSYS' order by 2;
OWNER ,TRIGGER_NAME ,STATUS
--------------------,------------------------------,--------------------
LBACSYS ,LBAC$AFTER_CREATE ,ENABLED
LBACSYS ,LBAC$AFTER_DROP ,ENABLED
LBACSYS ,LBAC$BEFORE_ALTER ,ENABLED
13:47:35 SQL>

Let us bounce the db.

13:49:16 SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
13:49:45 SQL> startup;
ORACLE instance started.
Total System Global Area,1442840576,bytes
Fixed Size , 2924448,bytes
Variable Size , 956301408,bytes
Database Buffers , 469762048,bytes
Redo Buffers , 13852672,bytes
Database mounted.
Database opened.

13:49:57 SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';
PARAMETER ,VALUE
----------------------------------------------------------------,----------------------------------------------------------------
Oracle Database Vault ,TRUE
13:50:16 SQL>

Now vault is activated.

5. Test application object setup -- is already avaible from TDE setup (skipped here)

6. Setup a read only user for the app schema & grant permission to the read only

user:
User creation as SYS will fail from now. Since the database vault once setup allow only DV_ACCTMGR roles to create user.

13:54:04 SQL> show user;
USER is "SYS"

13:54:06 SQL> create user ENCVAULT_TEST_RO identified by "encvault_test";
create user ENCVAULT_TEST_RO identified by "encvault_test"
*
ERROR at line 1:
ORA-01031: insufficient privileges

13:54:08 SQL>

Logged in as dbv_acctmgr:

13:55:28 SQL> conn dbv_acctmgr/dbv_acctmgr
Connected.
13:55:43 SQL> create user ENCVAULT_TEST_RO identified by "encvault_test";
User created.
Elapsed: 00:00:00.09

13:55:51 SQL> grant connect,resource to ENCVAULT_TEST_RO;
grant connect,resource to ENCVAULT_TEST_RO
*
ERROR at line 1:
ORA-47410: Realm violation for GRANT on RESOURCE >>> resource role needs to granted as sys.

Elapsed: 00:00:00.05

13:56:07 SQL> grant connect to ENCVAULT_TEST_RO;
Grant succeeded.
Elapsed: 00:00:00.01
13:57:18 SQL>

13:58:09 SQL> show user;
USER is "SYS"
13:58:12 SQL>
13:54:08 SQL> grant resource to ENCVAULT_TEST_RO; >>> permission granted
Grant succeeded.

7. Examining existing realms:

To know more about already created realms:

14:05:40 SQL> show user;
USER is "DBV_OWNER"

14:07:05 SQL>

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
col name for a50
col description for a200
col created_by for a10
col updated_by for a10
set lines 400
set colsep ,

14:05:33 SQL> select * from DVSYS.DV$REALM;
ID#,NAME ,DESCRIPTION ,AUDIT_OPTIONS,REALM_TYPE,E, VERSION,CREATED_BY,CREATE_DATE ,UPDATED_BY,UPDATE_DATE
----------,--------------------------------------------------,--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,-------------,----------,-,----------,----------,--------------------,----------,--------------------
2,Oracle Database Vault ,Defines the realm for the Oracle Database Vault schemas - DVSYS, DVF and LBACSYS where Database Vault access control configuration and roles are contained. , 1, ,Y, 1,SYS ,07/JUL/2014 06:52:01,SYS ,07/JUL/2014 06:52:01
6,Database Vault Account Management ,Defines the realm for administrators who create and manage database accounts and profiles. , 1, ,Y, 1,SYS ,07/JUL/2014 06:52:01,SYS ,07/JUL/2014 06:52:01
7,Oracle Enterprise Manager ,Defines the Enterprise Manager monitoring and management realm. , 1, ,Y, 1,SYS ,07/JUL/2014 06:52:01,SYS ,07/JUL/2014 06:52:01
8,Oracle Default Schema Protection Realm ,Defines the realm for the Oracle Default schemas. , 1, ,Y, 1,SYS ,07/JUL/2014 06:52:01,SYS ,07/JUL/2014 06:52:01
9,Oracle System Privilege and Role Management Realm ,Defines the realm to control granting of system privileges and database administrator roles. , 1, ,Y, 1,SYS ,07/JUL/2014 06:52:01,SYS ,07/JUL/2014 06:52:01
10,Oracle Default Component Protection Realm ,Defines the realm to protect default components of the Oracle database. , 1, ,Y, 1,SYS ,07/JUL/2014 06:52:01,SYS ,07/JUL/2014 06:52:01
6 rows selected.
Elapsed: 00:00:00.00
14:05:40 SQL>

14:07:05 SQL> select * from DVSYS.DBA_DV_REALM order by 1;
NAME ,DESCRIPTION ,AUDIT_OPTIONS,REALM_TYP,E
--------------------------------------------------,--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,-------------,---------,-
Database Vault Account Management ,Defines the realm for administrators who create and manage database accounts and profiles. , 1, ,Y
Oracle Database Vault ,Defines the realm for the Oracle Database Vault schemas - DVSYS, DVF and LBACSYS where Database Vault access control configuration and roles are contained. , 1, ,Y
Oracle Default Component Protection Realm ,Defines the realm to protect default components of the Oracle database. , 1, ,Y
Oracle Default Schema Protection Realm ,Defines the realm for the Oracle Default schemas. , 1, ,Y
Oracle Enterprise Manager ,Defines the Enterprise Manager monitoring and management realm. , 1, ,Y
Oracle System Privilege and Role Management Realm ,Defines the realm to control granting of system privileges and database administrator roles. , 1, ,Y
6 rows selected.
Elapsed: 00:00:00.01
14:16:49 SQL>

REALTM_TYPE is set to NULL for all default realms.

As sys or the app owner:

14:00:51 SQL> grant select on encvault_test.ENCRYPT_TAB1 to ENCVAULT_TEST_RO;
Grant succeeded.

14:07:38 SQL>
col REALM_NAME for a50
col GRANTEE for a20
col AUTH_RULE_SET_NAME for a30
col AUTH_OPTIONS for a30

14:22:27 SQL> select * from DVSYS.DBA_DV_REALM_AUTH order by 1,4,2;
REALM_NAME ,GRANTEE ,AUTH_RULE_SET_NAME ,AUTH_OPTIONS
--------------------------------------------------,--------------------,------------------------------,------------------------------
Database Vault Account Management ,DV_ACCTMGR , ,Owner
Oracle Database Vault ,DVSYS , ,Owner
Oracle Database Vault ,DV_OWNER , ,Owner
Oracle Database Vault ,LBACSYS , ,Owner
Oracle Database Vault ,DV_ADMIN , ,Participant
Oracle Default Component Protection Realm ,SYS , ,Owner
Oracle Default Component Protection Realm ,SYSTEM , ,Owner
Oracle Default Schema Protection Realm ,CTXSYS , ,Owner
Oracle Default Schema Protection Realm ,MDDATA , ,Owner
Oracle Default Schema Protection Realm ,MDSYS , ,Owner
Oracle Default Schema Protection Realm ,SYS , ,Owner
Oracle Enterprise Manager ,DBSNMP , ,Owner
Oracle Enterprise Manager ,SYSTEM , ,Owner
Oracle System Privilege and Role Management Realm ,SYS , ,Owner
14 rows selected.
Elapsed: 00:00:00.15
14:23:04 SQL>

col owner for a20
col object_name for a30
col object_type fpr a30
select * from DVSYS.DBA_DV_REALM_OBJECT order by 1,2,4,3;

select * from DVSYS.DBA_DV_REALM_OBJECT order by 1,2,4,3;

14:25:02 SQL>
REALM_NAME ,OWNER ,OBJECT_NAME ,OBJECT_TYPE
--------------------------------------------------,--------------------,------------------------------,--------------------------------
Database Vault Account Management ,% ,CONNECT ,ROLE
Database Vault Account Management ,% ,DV_ACCTMGR ,ROLE
Oracle Database Vault ,% ,DV_ADMIN ,ROLE
Oracle Database Vault ,% ,DV_AUDIT_CLEANUP ,ROLE
Oracle Database Vault ,% ,DV_DATAPUMP_NETWORK_LINK ,ROLE
Oracle Database Vault ,% ,DV_GOLDENGATE_ADMIN ,ROLE
Oracle Database Vault ,% ,DV_GOLDENGATE_REDO_ACCESS ,ROLE
Oracle Database Vault ,% ,DV_MONITOR ,ROLE
Oracle Database Vault ,% ,DV_OWNER ,ROLE
Oracle Database Vault ,% ,DV_PATCH_ADMIN ,ROLE
Oracle Database Vault ,% ,DV_PUBLIC ,ROLE
Oracle Database Vault ,% ,DV_SECANALYST ,ROLE
Oracle Database Vault ,% ,DV_STREAMS_ADMIN ,ROLE
Oracle Database Vault ,% ,DV_XSTREAM_ADMIN ,ROLE
Oracle Database Vault ,% ,LBAC_DBA ,ROLE
Oracle Database Vault ,DVF ,% ,%
Oracle Database Vault ,DVSYS ,% ,%
Oracle Database Vault ,LBACSYS ,% ,%
Oracle Database Vault ,SYS ,DBMS_RLS ,%
Oracle Default Component Protection Realm ,OUTLN ,% ,%
Oracle Default Component Protection Realm ,SYSTEM ,% ,%
Oracle Default Schema Protection Realm ,% ,CTXAPP ,ROLE
Oracle Default Schema Protection Realm ,% ,EJBCLIENT ,ROLE
Oracle Default Schema Protection Realm ,% ,OLAP_DBA ,ROLE
Oracle Default Schema Protection Realm ,% ,OLAP_USER ,ROLE
Oracle Default Schema Protection Realm ,CTXSYS ,% ,%
Oracle Default Schema Protection Realm ,MDDATA ,% ,%
Oracle Default Schema Protection Realm ,MDSYS ,% ,%
Oracle Enterprise Manager ,% ,OEM_MONITOR ,ROLE
Oracle Enterprise Manager ,DBSNMP ,% ,%
Oracle System Privilege and Role Management Realm ,% ,AQ_ADMINISTRATOR_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,AQ_USER_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,AUDIT_ADMIN ,ROLE
Oracle System Privilege and Role Management Realm ,% ,AUDIT_VIEWER ,ROLE
Oracle System Privilege and Role Management Realm ,% ,DBA ,ROLE
Oracle System Privilege and Role Management Realm ,% ,DELETE_CATALOG_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,DV_REALM_OWNER ,ROLE
Oracle System Privilege and Role Management Realm ,% ,DV_REALM_RESOURCE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,EXECUTE_CATALOG_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,EXP_FULL_DATABASE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,GATHER_SYSTEM_STATISTICS ,ROLE
Oracle System Privilege and Role Management Realm ,% ,GLOBAL_AQ_USER_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,HS_ADMIN_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,IMP_FULL_DATABASE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVADEBUGPRIV ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVAIDPRIV ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVASYSPRIV ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVAUSERPRIV ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVA_ADMIN ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVA_DEPLOY ,ROLE
Oracle System Privilege and Role Management Realm ,% ,LOGSTDBY_ADMINISTRATOR ,ROLE
Oracle System Privilege and Role Management Realm ,% ,OPTIMIZER_PROCESSING_RATE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,RECOVERY_CATALOG_OWNER ,ROLE
Oracle System Privilege and Role Management Realm ,% ,RESOURCE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,SCHEDULER_ADMIN ,ROLE
Oracle System Privilege and Role Management Realm ,% ,SELECT_CATALOG_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,LBACSYS ,DBA_OLS_STATUS ,VIEW
57 rows selected.
Elapsed: 00:00:00.02
14:25:03 SQL>

8. Setting up new realm and adding application objects to it..

EXEC dbms_macadm.create_realm(realm_name => 'APP schema', description => 'Protect APP Schema ', enabled => 'Y', audit_options => 1, realm_type =>'0' );

EXEC dbms_macadm.add_object_to_realm(realm_name => 'APP schema', object_owner => 'ENCVAULT_TEST', object_name => '%', object_type => '%' );

Output:

14:25:03 SQL> EXEC dbms_macadm.create_realm(realm_name => 'APP schema', description => 'Protect APP Schema ', enabled => 'Y', audit_options => 1, realm_type =>'0' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08

14:30:08 SQL> EXEC dbms_macadm.add_object_to_realm(realm_name => 'APP schema', object_owner => 'ENCVAULT_TEST', object_name => '%', object_type => '%' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

14:30:19 SQL> select * from DVSYS.DBA_DV_REALM order by 1;
NAME ,DESCRIPTION ,AUDIT_OPTIONS,REALM_TYP,E
--------------------------------------------------,--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,-------------,---------,-
APP schema ,Protect APP Schema , 1,REGULAR ,Y <<< new realm
Database Vault Account Management ,Defines the realm for administrators who create and manage database accounts and profiles. , 1, ,Y
Oracle Database Vault ,Defines the realm for the Oracle Database Vault schemas - DVSYS, DVF and LBACSYS where Database Vault access control configuration and roles are contained. , 1, ,Y
Oracle Default Component Protection Realm ,Defines the realm to protect default components of the Oracle database. , 1, ,Y
Oracle Default Schema Protection Realm ,Defines the realm for the Oracle Default schemas. , 1, ,Y
Oracle Enterprise Manager ,Defines the Enterprise Manager monitoring and management realm. , 1, ,Y
Oracle System Privilege and Role Management Realm ,Defines the realm to control granting of system privileges and database administrator roles. , 1, ,Y
7 rows selected.
Elapsed: 00:00:00.00

14:30:33 SQL> select * from DVSYS.DBA_DV_REALM_AUTH order by 1,4,2; <<< no change to this
REALM_NAME ,GRANTEE ,AUTH_RULE_SET_NAME ,AUTH_OPTIONS
--------------------------------------------------,--------------------,------------------------------,------------------------------
Database Vault Account Management ,DV_ACCTMGR , ,Owner
Oracle Database Vault ,DVSYS , ,Owner
Oracle Database Vault ,DV_OWNER , ,Owner
Oracle Database Vault ,LBACSYS , ,Owner
Oracle Database Vault ,DV_ADMIN , ,Participant
Oracle Default Component Protection Realm ,SYS , ,Owner
Oracle Default Component Protection Realm ,SYSTEM , ,Owner
Oracle Default Schema Protection Realm ,CTXSYS , ,Owner
Oracle Default Schema Protection Realm ,MDDATA , ,Owner
Oracle Default Schema Protection Realm ,MDSYS , ,Owner
Oracle Default Schema Protection Realm ,SYS , ,Owner
Oracle Enterprise Manager ,DBSNMP , ,Owner
Oracle Enterprise Manager ,SYSTEM , ,Owner
Oracle System Privilege and Role Management Realm ,SYS , ,Owner
14 rows selected.
Elapsed: 00:00:00.15

14:30:46 SQL> select * from DVSYS.DBA_DV_REALM_OBJECT order by 1,2,4,3;
REALM_NAME ,OWNER ,OBJECT_NAME ,OBJECT_TYPE
--------------------------------------------------,--------------------,------------------------------,--------------------------------
APP schema ,ENCVAULT_TEST ,% ,% << Objects in realm
Database Vault Account Management ,% ,CONNECT ,ROLE
Database Vault Account Management ,% ,DV_ACCTMGR ,ROLE
Oracle Database Vault ,% ,DV_ADMIN ,ROLE
Oracle Database Vault ,% ,DV_AUDIT_CLEANUP ,ROLE
Oracle Database Vault ,% ,DV_DATAPUMP_NETWORK_LINK ,ROLE
Oracle Database Vault ,% ,DV_GOLDENGATE_ADMIN ,ROLE
Oracle Database Vault ,% ,DV_GOLDENGATE_REDO_ACCESS ,ROLE
Oracle Database Vault ,% ,DV_MONITOR ,ROLE
Oracle Database Vault ,% ,DV_OWNER ,ROLE
Oracle Database Vault ,% ,DV_PATCH_ADMIN ,ROLE
Oracle Database Vault ,% ,DV_PUBLIC ,ROLE
Oracle Database Vault ,% ,DV_SECANALYST ,ROLE
Oracle Database Vault ,% ,DV_STREAMS_ADMIN ,ROLE
Oracle Database Vault ,% ,DV_XSTREAM_ADMIN ,ROLE
Oracle Database Vault ,% ,LBAC_DBA ,ROLE
Oracle Database Vault ,DVF ,% ,%
Oracle Database Vault ,DVSYS ,% ,%
Oracle Database Vault ,LBACSYS ,% ,%
Oracle Database Vault ,SYS ,DBMS_RLS ,%
Oracle Default Component Protection Realm ,OUTLN ,% ,%
Oracle Default Component Protection Realm ,SYSTEM ,% ,%
Oracle Default Schema Protection Realm ,% ,CTXAPP ,ROLE
Oracle Default Schema Protection Realm ,% ,EJBCLIENT ,ROLE
Oracle Default Schema Protection Realm ,% ,OLAP_DBA ,ROLE
Oracle Default Schema Protection Realm ,% ,OLAP_USER ,ROLE
Oracle Default Schema Protection Realm ,CTXSYS ,% ,%
Oracle Default Schema Protection Realm ,MDDATA ,% ,%
Oracle Default Schema Protection Realm ,MDSYS ,% ,%
Oracle Enterprise Manager ,% ,OEM_MONITOR ,ROLE
Oracle Enterprise Manager ,DBSNMP ,% ,%
Oracle System Privilege and Role Management Realm ,% ,AQ_ADMINISTRATOR_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,AQ_USER_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,AUDIT_ADMIN ,ROLE
Oracle System Privilege and Role Management Realm ,% ,AUDIT_VIEWER ,ROLE
Oracle System Privilege and Role Management Realm ,% ,DBA ,ROLE
Oracle System Privilege and Role Management Realm ,% ,DELETE_CATALOG_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,DV_REALM_OWNER ,ROLE
Oracle System Privilege and Role Management Realm ,% ,DV_REALM_RESOURCE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,EXECUTE_CATALOG_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,EXP_FULL_DATABASE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,GATHER_SYSTEM_STATISTICS ,ROLE
Oracle System Privilege and Role Management Realm ,% ,GLOBAL_AQ_USER_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,HS_ADMIN_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,IMP_FULL_DATABASE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVADEBUGPRIV ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVAIDPRIV ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVASYSPRIV ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVAUSERPRIV ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVA_ADMIN ,ROLE
Oracle System Privilege and Role Management Realm ,% ,JAVA_DEPLOY ,ROLE
Oracle System Privilege and Role Management Realm ,% ,LOGSTDBY_ADMINISTRATOR ,ROLE
Oracle System Privilege and Role Management Realm ,% ,OPTIMIZER_PROCESSING_RATE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,RECOVERY_CATALOG_OWNER ,ROLE
Oracle System Privilege and Role Management Realm ,% ,RESOURCE ,ROLE
Oracle System Privilege and Role Management Realm ,% ,SCHEDULER_ADMIN ,ROLE
Oracle System Privilege and Role Management Realm ,% ,SELECT_CATALOG_ROLE ,ROLE
Oracle System Privilege and Role Management Realm ,LBACSYS ,DBA_OLS_STATUS ,VIEW
58 rows selected.
Elapsed: 00:00:00.01
14:30:56 SQL>

Test the user's access:

--as sys
select count(1) from encvault_test.ENCRYPT_TAB1;
14:33:07 SQL> show user;
USER is "SYS"
14:33:09 SQL>

14:30:04 SQL> select count(1) from encvault_test.ENCRYPT_TAB1;
select count(1) from encvault_test.ENCRYPT_TAB1
*
ERROR at line 1:
ORA-01031: insufficient privileges

--as appln owner
14:33:42 SQL> show user;
USER is "ENCVAULT_TEST"

14:33:47 SQL> select count(1) from encvault_test.ENCRYPT_TAB1;
COUNT(1)
----------
10

--as appln user
14:34:29 SQL> show user;
USER is "ENCVAULT_TEST_RO"

14:34:32 SQL> select count(1) from encvault_test.ENCRYPT_TAB1;
COUNT(1)
----------
10

So the application read only user can still select the data because this is a regular realm, a mandatory in other hand doesnt allow the access for application owner and user.
Users without direct access granted cant access the objects here.

9. Create a ruleset, rule, command rule to restrict the app read only user access to the db..

Rule creation:

BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'Check if Boss Is Logged In',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''PATCH_USER'' and leo_dvowner.check_boss_logged_in = ''TRUE'' ');
END;
/

Actual:

BEGIN
DBMS_MACADM.CREATE_RULE(
rule_name => 'Restrict App RO access',
rule_expr => 'SYS_CONTEXT(''USERENV'',''HOST'') = ''WORKGROUP\WIN-1RJGBCM56VB'' and SYS_CONTEXT(''USERENV'',''OS_USER'') = ''vagrant'' ');
END;
/

col name for a75
col rule_expr for a200
set lines 400
set pages 30000
set colsep ,
select * from DVSYS.DBA_DV_RULE order by 1;
NAME ,RULE_EXPR
---------------------------------------------------------------------------,--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Are Backup Restore Parameters Allowed ,DVSYS.DBMS_MACADM.check_backup_parm_varchar = 'Y'
Are Database File Parameters Allowed ,DVSYS.DBMS_MACADM.check_db_file_parm_varchar = 'Y'
Are Dump or Dest Parameters Allowed ,DVSYS.DBMS_MACADM.check_dump_dest_parm_varchar = 'Y'
Are Optimizer Parameters Allowed ,DVSYS.DBMS_MACADM.check_optimizer_parm_varchar = 'Y'
Are PL-SQL Parameters Allowed ,DVSYS.DBMS_MACADM.check_plsql_parm_varchar = 'Y'
Are Security Parameters Allowed ,DVSYS.DBMS_MACADM.check_security_parm_varchar = 'Y'
Are System Security Parameters Allowed ,DVSYS.DBMS_MACADM.check_sys_sec_parm_varchar = 'Y'
False ,1=0
Is Alter DVSYS Allowed ,DVSYS.DBMS_MACADM.IS_ALTER_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
Is Database Administrator ,DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR('DBA','"'||dvsys.dv_login_user||'"') = 'Y'
Is Drop User Allowed ,DVSYS.DBMS_MACADM.IS_DROP_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
Is First Day of Month ,TO_NUMBER(TO_CHAR(SYSDATE,'DD')) = 1
Is Label Administrator ,DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR('LBAC_DBA','"'||dvsys.dv_login_user||'"') = 'Y'
Is Last Day of Month ,(LAST_DAY(TRUNC(SYSDATE)) - TRUNC(SYSDATE)) = 0
Is SYS or SYSTEM User ,USER IN ('SYS','SYSTEM')
Is Security Administrator ,DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR('DV_ADMIN','"'||dvsys.dv_login_user||'"') = 'Y'
Is Security Owner ,DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR('DV_OWNER','"'||dvsys.dv_login_user||'"') = 'Y'
Is User Manager ,DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR('DV_ACCTMGR', '"'||dvsys.dv_login_user||'"') = 'Y'
Is _dynamic_rls_init Parameters Allowed ,DVSYS.DBMS_MACADM.check_dynrls_parm_varchar = 'Y'
Is _system_trig_enabled Parameters Allowed ,DVSYS.DBMS_MACADM.check_trig_parm_varchar = 'Y'
Is o7_dictionary_accessibility Parameters Allowed ,DVSYS.DBMS_MACADM.check_o7_parm_varchar = 'Y'
Login User Is Object User ,dvsys.dv_login_user = dvsys.dv_dict_obj_name
No Exempt Access Policy Role ,(DVSYS.DBMS_MACUTL.USER_HAS_SYSTEM_PRIV_VARCHAR('EXEMPT ACCESS POLICY','"'||dvsys.dv_login_user||'"') = 'N') OR USER = 'SYS'
Not Export Session , (NVL(DVSYS.DBMS_MACADM.GET_SESSION_INFO('PROGRAM'),'X') NOT LIKE 'exp@%' ) AND (NVL(DVSYS.DBMS_MACADM.GET_SESSION_INFO('PROGRAM'),'X') NOT LIKE 'EXP.EX%')
Restrict App RO access ,SYS_CONTEXT('USERENV','HOST') = 'WORKGROUP\WIN-1RJGBCM56VB' and SYS_CONTEXT('USERENV','OS_USER') = 'vagrant'
True ,1=1

26 rows selected.
Elapsed: 00:00:00.00

16:25:58 SQL> col
col rule_Set_name for a75
col description for a100
col eval_options_meaning for a100
col fail_options_meaning for a100
col faile_message for a40
col handler for a100

select * from DVSYS.DBA_DV_RULE_SET order by 1;

RULE_SET_NAME ,DESCRIPTION ,E,EVAL_OPTIONS_MEANING ,AUDIT_OPTIONS,FAIL_OPTIONS_MEANING ,FAIL_MESSAGE ,FAIL_CODE ,HANDLER_OPTIONS,HANDLER ,IS_ST
---------------------------------------------------------------------------,----------------------------------------------------------------------------------------------------,-,----------------------------------------------------------------------------------------------------,-------------,----------------------------------------------------------------------------------------------------,--------------------------------------------------------------------------------,----------,---------------,----------------------------------------------------------------------------------------------------,-----
Allow Fine Grained Control of System Parameters ,Fine Grained Rule set to control the ability to set system init parameters. ,Y,All True , 1,Show Error Message , , , 0, ,FALSE
Allow Sessions ,Rule set that controls the ability to create a session in the database. ,Y,All True , 1,Show Error Message , , , 0, ,FALSE
Allow System Parameters ,Rule set that controls the ability to set system init parameters. ,Y,All True , 1,Show Error Message , , , 0, ,FALSE
Can Grant VPD Administration ,Rule set that controls the ability to GRANT EXECUTE privileges on the Oracle VPD DBMS_RLS package. ,Y,All True , 1,Show Error Message , , , 0, ,FALSE
Can Maintain Accounts/Profiles ,Rule set that controls the roles that can manage user accounts and profiles. ,Y,All True , 1,Show Error Message , , , 0, ,FALSE
Can Maintain Own Account ,Rule set that controls the roles that can manage user accounts and profiles or your own account. ,Y,Any True , 1,Show Error Message , , , 0, ,FALSE
Disabled ,Convenience rule set to quickly disable system features. ,Y,All True , 0,Show Error Message , , , 0, ,FALSE
Enabled ,Convenience rule set to quickly enable system features. ,Y,All True , 0,Show Error Message , , , 0, ,FALSE
8 rows selected.
Elapsed: 00:00:00.09

16:30:13 SQL>

Ruleset creation:

BEGIN
DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'App Access Rule Set',
description => 'Check if a valid user access the application object',
enabled => DBMS_MACUTL.G_YES,
eval_options => 2,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL,
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
fail_message =>'',
fail_code => NULL,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => ''
);
END;
/

16:30:13 SQL> BEGIN
DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'App Access Rule Set',
description => 'Check if a valid user access the application object',
16:35:00 2 16:35:00 3 16:35:00 4 16:35:00 5 enabled => DBMS_MACUTL.G_YES,
eval_options => 2,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL,
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
fail_message =>'',
fail_code => NULL,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => ''
);
END;
/16:35:00 6 16:35:00 7 16:35:00 8 16:35:00 9 16:35:00 10 16:35:00 11 16:35:00 12 16:35:00 13 16:35:00 14 16:35:00 15
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07

Adding rule to ruleset:

16:35:01 SQL>
BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'App Access Rule Set',
rule_name => 'Restrict App RO access'
);
END;
/

16:35:08 SQL> BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'App Access Rule Set',
rule_name => 'Restrict App RO access'
);
END;
/
16:36:33 2 16:36:33 3 16:36:33 4 16:36:33 5 16:36:33 6 16:36:33 7
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05

16:36:33 SQL>
16:36:33 SQL>

16:35:01 SQL> select * from DVSYS.DBA_DV_RULE_SET order by 1;
RULE_SET_NAME ,DESCRIPTION ,E,EVAL_OPTIONS_MEANING ,AUDIT_OPTIONS,FAIL_OPTIONS_MEANING ,FAIL_MESSAGE ,FAIL_CODE ,HANDLER_OPTIONS,HANDLER ,IS_ST
---------------------------------------------------------------------------,----------------------------------------------------------------------------------------------------,-,----------------------------------------------------------------------------------------------------,-------------,----------------------------------------------------------------------------------------------------,--------------------------------------------------------------------------------,----------,---------------,----------------------------------------------------------------------------------------------------,-----
...
App Access Rule Set ,Check if a valid user access the application object ,Y,Any True , 1,Do Not Show Error Message , , , 0, ,FALSE
..
9 rows selected.
Elapsed: 00:00:00.07
16:35:08 SQL>

16:37:25 SQL> select * from DVSYS.DBA_DV_RULE_SET_RULE order by 1,2;
RULE_SET_NAME ,RULE_NAME ,RULE_EXPR ,E,RULE_ORDER
---------------------------------------------------------------------------,--------------------------------------------------------------------------------------------------------------------------------,--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,-,----------
Allow Fine Grained Control of System Parameters ,Are Backup Restore Parameters Allowed ,DVSYS.DBMS_MACADM.check_backup_parm_varchar = 'Y' ,Y, 1
Allow Fine Grained Control of System Parameters ,Are Database File Parameters Allowed ,DVSYS.DBMS_MACADM.check_db_file_parm_varchar = 'Y' ,Y, 1
Allow Fine Grained Control of System Parameters ,Are Dump or Dest Parameters Allowed ,DVSYS.DBMS_MACADM.check_dump_dest_parm_varchar = 'Y' ,Y, 1
Allow Fine Grained Control of System Parameters ,Are Optimizer Parameters Allowed ,DVSYS.DBMS_MACADM.check_optimizer_parm_varchar = 'Y' ,Y, 1
Allow Fine Grained Control of System Parameters ,Are PL-SQL Parameters Allowed ,DVSYS.DBMS_MACADM.check_plsql_parm_varchar = 'Y' ,Y, 1
Allow Fine Grained Control of System Parameters ,Are Security Parameters Allowed ,DVSYS.DBMS_MACADM.check_security_parm_varchar = 'Y' ,Y, 1
Allow Fine Grained Control of System Parameters ,Are System Security Parameters Allowed ,DVSYS.DBMS_MACADM.check_sys_sec_parm_varchar = 'Y' ,Y, 1
Allow System Parameters ,Is _dynamic_rls_init Parameters Allowed ,DVSYS.DBMS_MACADM.check_dynrls_parm_varchar = 'Y' ,Y, 1
Allow System Parameters ,Is _system_trig_enabled Parameters Allowed ,DVSYS.DBMS_MACADM.check_trig_parm_varchar = 'Y' ,Y, 1
Allow System Parameters ,Is o7_dictionary_accessibility Parameters Allowed ,DVSYS.DBMS_MACADM.check_o7_parm_varchar = 'Y' ,Y, 1
App Access Rule Set ,Restrict App RO access ,SYS_CONTEXT('USERENV','HOST') = 'WORKGROUP\WIN-1RJGBCM56VB' and SYS_CONTEXT('USERENV','OS_USER') = 'vagrant' ,Y, 1 <<<<<<<<<<< our rule
Can Grant VPD Administration ,Is Security Owner ,DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR('DV_OWNER','"'||dvsys.dv_login_user||'"') = 'Y' ,Y, 1
Can Maintain Accounts/Profiles ,Is Drop User Allowed ,DVSYS.DBMS_MACADM.IS_DROP_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y' ,Y, 1
Can Maintain Accounts/Profiles ,Is User Manager ,DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR('DV_ACCTMGR', '"'||dvsys.dv_login_user||'"') = 'Y' ,Y, 1
Can Maintain Own Account ,Is Alter DVSYS Allowed ,DVSYS.DBMS_MACADM.IS_ALTER_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y' ,Y, 1
Can Maintain Own Account ,Login User Is Object User ,dvsys.dv_login_user = dvsys.dv_dict_obj_name ,Y, 1
Disabled ,False ,1=0 ,Y, 1
Enabled ,True ,1=1 ,Y, 1
18 rows selected.
Elapsed: 00:00:00.09

16:37:36 SQL>

Create command rule:

Ex.:

BEGIN
DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'CONNECT',
rule_set_name => 'Dual Connect for Boss and Patch',
object_owner => '%',
object_name => '%',
enabled => DBMS_MACUTL.G_YES);
END;
/
COMMIT;

Actual:

BEGIN
DBMS_MACADM.CREATE_COMMAND_RULE(
command => 'SELECT',
rule_set_name => 'App Access Rule Set',
object_owner => 'ENCVAULT_TEST',
object_name => '%',
enabled => DBMS_MACUTL.G_YES);
END;
/
COMMIT;

col command for a40
16:43:59 SQL> col command for a40
16:44:07 SQL> select * from DVSYS.DBA_DV_COMMAND_RULE order by 1,2;
COMMAND ,RULE_SET_NAME ,OBJECT_OWNER ,OBJECT_NAME ,E,PRIVILEGE_SCOPE
----------------------------------------,---------------------------------------------------------------------------,--------------------------------------------------------------------------------------------------------------------------------,------------------------------,-,---------------
ALTER PROFILE ,Can Maintain Accounts/Profiles ,% ,% ,Y,
ALTER SYSTEM ,Allow Fine Grained Control of System Parameters ,% ,% ,Y,
ALTER USER ,Can Maintain Own Account ,% ,% ,Y,
CHANGE PASSWORD ,Can Maintain Own Account ,% ,% ,Y,
CREATE PROFILE ,Can Maintain Accounts/Profiles ,% ,% ,Y,
CREATE USER ,Can Maintain Accounts/Profiles ,% ,% ,Y,
DROP PROFILE ,Can Maintain Accounts/Profiles ,% ,% ,Y,
DROP USER ,Can Maintain Accounts/Profiles ,% ,% ,Y,
SELECT ,App Access Rule Set ,ENCVAULT_TEST ,% ,Y,
9 rows selected.
Elapsed: 00:00:00.18
16:44:32 SQL>

So now we have schema ENCVAULT_TEST in realm.
Anyone not granted direct access access cant access the objects within them.
Even if the user is given direct access, the users should login from WORKGROUP\WIN-1RJGBCM56VB machine as osuser vagrant to access the record.
If not fail the data selection.

Let us test it..

16:48:25 SQL> conn ENCVAULT_TEST_RO/encvault_test
Connected.

16:50:03 SQL> select sys_context('USERENV','HOST') from dual;
SYS_CONTEXT('USERENV','HOST')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
vcentos79-oracle-ggsrc

16:50:45 SQL>

16:49:54 SQL> select sys_context('USERENV','OS_USER') from dual;
SYS_CONTEXT('USERENV','OS_USER')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle
16:50:03 SQL>

16:48:36 SQL> select count(1) from encvault_test.ENCRYPT_TAB1;
select count(1) from encvault_test.ENCRYPT_TAB1
*
ERROR at line 1:
ORA-01031: insufficient privileges

16:49:08 SQL>

If the same user connects from the correct machine and host:

16:51:44 SQL> show user;
USER is "ENCVAULT_TEST_RO"

16:51:49 SQL> select sys_context('USERENV','HOST') from dual;
SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------------------
WORKGROUP\WIN-1RJGBCM56VB

16:51:52 SQL> select sys_context('USERENV','OS_USER') from dual;
SYS_CONTEXT('USERENV','OS_USER')
--------------------------------------------------------------------------------
vagrant

16:51:55 SQL> select count(1) from encvault_test.ENCRYPT_TAB1;
COUNT(1)
----------
10
16:52:06 SQL>

Same case with app owner as well...

16:52:47 SQL> show user;
USER is "ENCVAULT_TEST"

16:52:58 SQL> select sys_context('USERENV','OS_USER') from dual;
SYS_CONTEXT('USERENV','OS_USER')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oracle

16:53:05 SQL> select sys_context('USERENV','HOST') from dual;
SYS_CONTEXT('USERENV','HOST')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
vcentos79-oracle-ggsrc

16:52:50 SQL> select count(1) from encvault_test.ENCRYPT_TAB1;
select count(1) from encvault_test.ENCRYPT_TAB1
*
ERROR at line 1:
ORA-01031: insufficient privileges

16:53:30 SQL> conn encvault_test/encvault_test@GGSRC01T
Connected.

16:53:39 SQL> select sys_context('USERENV','OS_USER') from dual;
SYS_CONTEXT('USERENV','OS_USER')
--------------------------------------------------------------------------------
vagrant

16:53:42 SQL> select sys_context('USERENV','HOST') from dual;
SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------------------
WORKGROUP\WIN-1RJGBCM56VB

16:53:45 SQL> select count(1) from encvault_test.ENCRYPT_TAB1;
COUNT(1)
----------
10
16:53:52 SQL> exit

>>>>>>>>>>>>>>>>>>> ALLSET with the database vault.

Saturday, July 22, 2023

Oracle Database Vault - A walk through

Flashback data archive steps