Oracle Database Vault in 12.2.0.1 - Part 3

 

Database Vault COMMAND RULE usage examples without GUI.

Note: This code is for test education purposes only.

User Case#1 [Restrict access to DROP TABLE in any schema]

SQL> Connect c##dbowner1/PAkistan123##@BILL_PDB1

SQL> SHOW USER

USER is "C##DBOWNER1"

SQL> begin

dbms_macadm.create_command_rule(command=>'DROP TABLE',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'%',enabled=>dbms_macutl.g_yes);

end;  

/

PL/SQL procedure successfully completed.

Command=>DROP TABLE

Rule Set=>Disable

Object Owner=>%

Object Name"=>%

Status=>Enabled

---Now try and create table and than drop it

Conn HR/Password:::::@BILL_PDB1

SQL> create table test (empno number, ename varchar2(30));

Table created.

SQL> drop table test;

drop table test

*

ERROR at line 1:

ORA-47400: Command Rule violation for DROP TABLE on HR.TEST

User Case#2  [Restrict access to View  EMP_DETAILS_VIEW ]

Conn HR/Password:::::

create or replace view test_Command_rule as select first_name,last_name from employees;

SQL> Connect c##dbowner1/PAkistan123##@BILL_PDB1

SQL> begin

dbms_macadm.create_command_rule(command=>'SELECT',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'EMP_DETAILS_VIEW',enabled=>dbms_macutl.g_yes);

end;  /

PL/SQL procedure successfully completed.

Conn HR/Password:::::

SQL> select first_name,last_name from emp_details_view where employee_id=100;

select first_name,last_name from emp_details_view where employee_id=100

                                 *

ERROR at line 1:

ORA-01031: insufficient privileges

---- DROP the command rule and verify 

SQL> BEGIN

 DVSYS.DBMS_MACADM.DELETE_COMMAND_RULE(

  command      => 'SELECT',

  object_owner => 'HR',

  object_name  => 'EMP_DETAILS_VIEW');

END;

/

PL/SQL procedure successfully completed.

Conn HR/Password:::::

SQL> select first_name,last_name from emp_details_view where employee_id=100;

FIRST_NAME           LAST_NAME

-------------------- -------------------------

Steven               King

User Case#3 [Restrict access to CREATE TABLE command]

SQL> Connect c##dbowner1/PAkistan123##@BILL_PDB1

begin

dbms_macadm.create_command_rule(command=>'CREATE TABLE',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'%',enabled=>dbms_macutl.g_yes);

end;

/

Conn HR/Password:::::

SQL> CREATE TABLE TEST2 (StudentID number);

CREATE TABLE TEST2 (StudentID number)

*

ERROR at line 1:

ORA-47400: Command Rule violation for CREATE TABLE on HR.TEST2

Disable Command Rule and Allow Table Creation

SQL> BEGIN

 DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(

  command         => 'CREATE TABLE',

  rule_set_name   => 'Disabled',

  object_owner    => 'HR',

  object_name     => '%',

  enabled         => DBMS_MACUTL.G_NO);

END;

/  

PL/SQL procedure successfully completed.

Conn HR/Password:::::

SQL>  CREATE TABLE TEST2 (StudentID number);

Table created.

Disable Command Rule and Allow Table Drop Activity at Database Level

SQL>  conn c##dbowner1/PAkistan123##@BILL_PDB1

Connected.

SQL> begin

dbms_macadm.update_command_rule(command=>'DROP TABLE',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'%',

enabled=>dbms_macutl.g_no);

end;

/  

PL/SQL procedure successfully completed.

SQL>

SQL> conn hr/PAkistan123##@BILL_PDB1

Connected.

SQL>  create table test1 (r number);

Table created.

SQL> drop table test1;

Table dropped.

SQL>

Enable Create Table:

begin

dbms_macadm.update_command_rule(command=>'CREATE TABLE',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'%',

enabled=>dbms_macutl.g_no);

end;  

/

---Important Views:

SELECT command,rule_set_name ,enabled,object_owner,object_name from DVSYS.DBA_DV_COMMAND_RULE

WHERE COMMAND IN ('CREATE TABLE','DROP TABLE');

SELECT RULE_SET_NAME, HANDLER_OPTIONS, HANDLER FROM DVSYS.DBA_DV_RULE_SET

WHERE RULE_SET_NAME = 'Disabled';

 

SELECT RULE_SET_NAME, RULE_NAME, RULE_EXPR FROM DVSYS.DBA_DV_RULE_SET_RULE

WHERE RULE_NAME = 'Disabled';

RULESET can be studied in part 4: https://hayatkhan75.blogspot.com/2022/08/oracle-database-vault-in-12201-part-4.html

Credit Goes:

http://oradbakar.blogspot.com/2011/12/database-vault-step-by-step-example.html

https://docs.oracle.com/cd/E18283_01/server.112/e16544/apis_dbms_macadm.htm

https://blog.actorsfit.com/a?ID=01450-3eca16ce-66a2-47e5-8671-1a072f790062

https://www.oreilly.com/library/view/oracle-11g-anti-hackers/9781849685269/ch07s02.html