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
No comments:
Post a Comment