Database Vault RULESET usage examples without GUI.
Note: This code is for test education purposes only.
Ruleset Definition:
As their name denotes, rulesets are a collection of rules that in turn consist of logical statements, which might evaluate to true or false. Because of their capacity for evaluation, rulesets can be associated with command rules, realm authorization, and factor assignment, as well as secure application roles. According to the true and false value of the rule, the value of the rule set itself is either "true" or "false". There are two choices: ALL or ANY. When defining command rules or authorizing a user to access a certain field, a certain set of rules can be set as a condition. When the user executes the SQL command defined in the command rule, Vault first calculates the associated rule set. If the rule set value is "true", the command is allowed to be executed, and if the value of the rule set is "false", the execution is rejected
sqlplus /nolog
conn c##dbowner1/PAkistan123##@BILL_PDB1
Create Ruleset...Reporting from HR Views
BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE_SET(
rule_set_name => 'Reporting from HR Views',
description => 'Reporting from HR Views',
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 report from this view',
fail_code => 20998,
handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
handler => NULL
);
END;
/
PL/SQL procedure successfully completed.
--------------------------------
SQL> conn c##dbmanager1/PAkistan123##@BILL_PDB1
Connected.
SQL> create user hayat identified by PAkistan123##;
User created.
SQL> grant create session,connect to hayat;
Grant succeeded.
--------------------------------
Evaluate HAYAT user
BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE(
rule_name => 'Rule_Hayat',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''HAYAT'''
);
END;
/
PL/SQL procedure successfully completed.
--------------------------------
SQL> conn HR/PAkistan123##@BILL_PDB1
Connected.
SQL>
SQL>
SQL> grant select on emp_details_view to hayat;
Grant succeeded.
--------------------------------
BEGIN
DVSYS.DBMS_MACADM.DELETE_COMMAND_RULE(
command => 'SELECT',
object_owner => 'HR',
object_name => 'EMP_DETAILS_VIEW');
END;
/
--------------------------------
SQL> CONN hayat/PAkistan123##@BILL_PDB1
Connected.
SQL> select first_name,last_name from hr.emp_Details_view where employee_id=100;
FIRST_NAME LAST_NAME
-------------------- -------------------------
Steven King
--------------------------------
SQL> conn scott/PAkistan123##@BILL_PDB1
Connected.
SQL> select first_name,last_name from hr.emp_Details_view where employee_id=100;
select first_name,last_name from hr.emp_Details_view where employee_id=100
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn hr/PAkistan123##@BILL_PDB1
Connected.
SQL> grant select on emp_details_view to scott;
Grant succeeded.
SQL> conn c##dbowner1/PAkistan123##@BILL_PDB1
--if HR_REALM exist for SCOTT user
BEGIN
DBMS_MACADM.DELETE_AUTH_FROM_REALM(
realm_name => 'HR_Realm',
grantee => 'SCOTT');
END;
/
BEGIN
DVSYS.DBMS_MACADM.CREATE_RULE(
rule_name => 'Rule_Scott',
rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SCOTT'''
);
END;
/
SQL> conn scott/PAkistan123##@BILL_PDB1
SQL> select first_name,last_name from hr.emp_Details_view where employee_id=100;
FIRST_NAME LAST_NAME
-------------------- -------------------------
Steven King
SQL> SELECT NAME FROM DVSYS.DBA_DV_REALM;
NAME
--------------------------------------------------------------------------------
Database Vault Account Management
HR_Realm
Oracle Database Vault
Oracle Default Component Protection Realm
Oracle Default Schema Protection Realm
Oracle Enterprise Manager
Oracle System Privilege and Role Management Realm
--Delete Existing REALM Created by SupportPartnet:
EXEC DBMS_MACADM.DELETE_REALM('HR_Realm');
SELECT NAME, AUDIT_OPTIONS, ENABLED FROM DVSYS.DBA_DV_REALM
WHERE AUDIT_OPTIONS = '1';
Enable DROP TABLE command rule 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> conn hr/PAkistan123##@BILL_PDB1
Connected.
SQL> create table test1 (r number);
Table created.
SQL> drop table test1;
Table dropped.
Important Views:
set lines 132 pages 2300
col os_username format a15
col username format a15
col userhost format a15
col ACTION_COMMAND format a45
col audit_option format a10
alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';
select audit_option,os_username,username,userhost,timestamp,action_command from dvsys.audit_trail$;
Part 5 (RULESET Complete end to end example):
https://hayatkhan75.blogspot.com/2022/08/oracle-database-vault-in-12201-part-5.html
Credit Goes to:
No comments:
Post a Comment