Database Vault RULESET (Complete) Example without GUI.
Note: This code is for test education purposes only and the idea is taken from the book "Oracle 11g Anti-hacker's Cookbook" Chapter 7.
Create RULESET for "end of the month reporting". It will only allow creating a view in the HR schema on the last day of every month only.
sqlplus c##dbowner1/PAkistan123##@BILL_PDB1
--- Create Rule: Evaluate to HR user sessions only
BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE(
rule_name => 'Rule_Evaluate_HR_user',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''HR'''
);
END;
/
--- Create Rule: Evaluate to Last Month of the day
BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE(
rule_name => 'Rule_Evaluate_Is_lastdayofmonth',
rule_expr =>'(LAST_DAY(TRUNC(SYSDATE)) - TRUNC(SYSDATE)) = 0'
);
END;
/
------ Create Ruleset as Ruleset_Month_End
----- This ruleset restrict VIEW creation in HR schema only due to sub-rule "Rule_Evaluate_HR_user"
BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'Ruleset_Month_End',
description => 'Create views for end of the month reporting',
enabled => DVSYS.DBMS_MACUTL.G_YES,
eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL, -- all rules must be true,
audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL, -- no audit
fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,
fail_message => 'You are not allowed to create reports until the end of the month',
fail_code => 20999,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => NULL
);
END;
/
--- Add Rules created above to to Ruleset
BEGIN
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'Ruleset_Month_End',
rule_name => 'Rule_Evaluate_HR_user',
rule_order => 1
);
DBMS_MACADM.ADD_RULE_TO_RULE_SET(
rule_set_name => 'Ruleset_Month_End',
rule_name => 'Rule_Evaluate_Is_lastdayofmonth',
rule_order => 1
);
END;
/
--- Add Command to Ruleset "Ruleset_Month_End" to restrict CREATE View command
BEGIN
DBMS_MACADM.CREATE_COMMAND_RULE (
command => 'CREATE VIEW',
rule_set_name => 'Ruleset_Month_End',
object_owner => 'HR',
object_name => '%',
enabled => DBMS_MACUTL.G_YES);
END;
/
--- Logon as HR and Verify if HR user allowed to create View
sqlplus HR/PAkistan123##@BILL_PDB1
SQL> create or replace view salaries_and_commissions as select
first_name,last_name,salary,commission_pct from employees where
commission_pct is not null; 2 3
first_name,last_name,salary,commission_pct from employees where
*
ERROR at line 2:
ORA-47306: 20999: You are not allowed to create reports until the end of the
month
SQL> select sysdate from dual;
SYSDATE
---------
12-AUG-22 >>>>> It is not month end
---------------------------------
For other users like scott etc
---------------------------------
SQL> show user
USER is "SCOTT"
SQL> select sysdate from dual;
SYSDATE
---------
12-AUG-22
SQL> create view vtst2 as select * from stest;
View created.
--- Connect as SYS user, and Change date to Last month
SQL> CONN SYS/PAkistan123##@BILL_PDB1 AS SYSDBA
Connected.
SQL> ALTER SYSTEM SET FIXED_DATE='31-AUG-2022';
System altered.
--- Logon as HR again, and verify if View creation is allowed
sqlplus hr/PAkistan123##@BILL_PDB1
SQL> select sysdate from dual;
SYSDATE
---------
31-AUG-22
SQL> create or replace view salaries_and_commissions as select
first_name,last_name,salary,commission_pct from employees where
commission_pct is not null; 2 3
View created.
==============================
--- The user with the DBV_ACCTMGR role can only grant the connect role
SQL> conn sys/PAkistan123##@BILL_PDB1 as sysdba
Connected.
SQL> grant create table to scott;
Grant succeeded.
SQL>
Views to verify:
select * from DVSYS.DBA_DV_RULE_SET WHERE RULE_SET_NAME IN ('Ruleset_Month_End')
SELECT * FROM DVSYS.DBA_DV_COMMAND_RULE WHERE RULE_SET_NAME IN ('Ruleset_Month_End');
SELECT * FROM DVSYS.DBA_DV_COMMAND_RULE WHERE COMMAND IN ('CREATE TABLE');
SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME in ('Rule_Evaluate_HR_user','Rule_Evaluate_Is_lastdayofmonth')
select * from DVSYS.DBA_DV_REALM;
select * from DVSYS.DBA_DV_REALM_AUTH;
select * from DVSYS.DBA_DV_REALM_OBJECT;
Credit Goes to:
https://netsoftmate.com/enable-database-vault-on-rac-database/
https://www.oreilly.com/library/view/oracle-11g-anti-hackers/9781849685269/ch07s02.html
No comments:
Post a Comment