So, we get a tree of sorts or three different trees, one for command rules, one for realms and one for secure application roles. Under these are rules in a rule set and under these are factors or code.
If we have a command rule such as ALTER USER then we might want to know what controls this at a Database Vault level. Of course to issue ALTER USER commands a user must also have the ALTER USER system privilege but how can we see the DV structure for the command rule ALTER USER?
First step is to look at the command rule view DBA_DV_COMMAND_RULE in my 21c DV enabled database:
SQL> @sc_print 'select * from dba_dv_command_rule where command =''''ALTER USER'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_dv_command_rule where command =''ALTER USER''','''','''''');
Executing Query [select * from dba_dv_command_rule where command ='ALTER USER']
COMMAND : ALTER USER
CLAUSE_NAME : %
PARAMETER_NAME : %
EVENT_NAME : %
COMPONENT_NAME : %
ACTION_NAME : %
RULE_SET_NAME : Can Maintain Own Account
OBJECT_OWNER : %
OBJECT_NAME : %
ENABLED : Y
PRIVILEGE_SCOPE :
COMMON : NO
INHERITED : NO
ID# : 2
ORACLE_SUPPLIED : YES
PL_SQL_STACK : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
There is only one ALTER USER command rule by default but there could be many command rules for the same command; for instance ALTER SYSTEM has 15 command rules:
SQL> col id# for 999
SQL> col clause_name for a20
SQL> col rule_set_name for a100
SQL> set lines 220
SQL> select id#,clause_name,rule_set_name from dba_dv_command_rule where command='ALTER SYSTEM';
ID# CLAUSE_NAME RULE_SET_NAME
---- -------------------- ----------------------------------------------------------------------------------------------------
24 SET Allow Fine Grained Control for Alter System
15 SET Disabled
19 SET Not allow to set AUDIT_SYS_OPERATIONS to False
20 SET Not allow to turn off AUDIT_TRAIL
13 SET Disabled
29 SET Disabled
28 SET Disabled
17 SET Not allow to set OPTIMIZER_SECURE_VIEW_MERGING to True
22 SET Not allow to set OS_ROLES to True
18 SET Not allow to set PLSQL_DEBUG to True
21 SET Not allow to set REMOTE_OS_ROLES to True
ID# CLAUSE_NAME RULE_SET_NAME
---- -------------------- ----------------------------------------------------------------------------------------------------
23 SET Not allow to set SQL92_SECURITY to False
26 SET Disabled
12 SET Disabled
25 DUMP Allow Dumping Datafile Header
15 rows selected.
SQL>
Notice that some rule sets are "disabled" so that the command rule does not fire. Some also have specific rule sets to control the use of the command rule.
The next step is to see the details of the rule set for the first example for ALTER USER. We can do this as follows:
SQL> @sc_print 'select * from dba_dv_rule_set where rule_set_name=''''Can Maintain Own Account'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_dv_rule_set where rule_set_name=''Can Maintain Own Account''','''','''''');
Executing Query [select * from dba_dv_rule_set where rule_set_name='Can Maintain Own Account']
RULE_SET_NAME : Can Maintain Own Account
DESCRIPTION : Rule set that controls the roles that can manage user accounts and profiles or your own account.
ENABLED : Y
EVAL_OPTIONS_MEANING : Any True
AUDIT_OPTIONS : 1
FAIL_OPTIONS_MEANING : Show Error Message
FAIL_MESSAGE :
FAIL_CODE :
HANDLER_OPTIONS : 0
HANDLER :
IS_STATIC : FALSE
COMMON : NO
INHERITED : NO
ID# : 4
ORACLE_SUPPLIED : YES
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
Next we we need to use the link table to see the rule details for the rule set:
SQL> @sc_print 'select * from dba_dv_rule_set_rule where rule_set_name=''''Can Maintain Own Account'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_dv_rule_set_rule where rule_set_name=''Can Maintain Own Account''','''','''''');
Executing Query [select * from dba_dv_rule_set_rule where rule_set_name='Can Maintain Own Account']
RULE_SET_NAME : Can Maintain Own Account
RULE_NAME : Is Alter DVSYS Allowed
RULE_EXPR : DVSYS.DBMS_MACADM.IS_ALTER_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
ENABLED : Y
RULE_ORDER : 1
COMMON : NO
INHERITED : NO
-------------------------------------------
RULE_SET_NAME : Can Maintain Own Account
RULE_NAME : Login User Is Object User
RULE_EXPR : dvsys.dv_login_user = dvsys.dv_dict_obj_name
ENABLED : Y
RULE_ORDER : 1
COMMON : NO
INHERITED : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
This gives us the expressions for the two rules used in the rule set that is attached to the command rule for ALTER USER. We can see that one rule checks that the user logged in is the object so not a pseudo user such as proxy or RAS or... The other rule calls a DBMS_MACADM function to check if the user is allowed to change their own account including changing their own password.
We can combine these simple checks into one SQL:
-- dv_cmd.sql
-- get command rule details
set lines 225
col command for a15
col clause_name for a8
col rule_set_name for a50
col rule_name for a30
col enabled for a1
col rule_expr_mod for a100 wrap
spool dv_cmd.lis
select c.command,
c.clause_name,
c.rule_set_name,
r.rule_name,
r.enabled,
replace(replace(r.rule_expr, chr(13)||chr(10), ':n'),
chr(13), ':n') as rule_expr_mod
from dba_dv_command_rule c,
dba_dv_rule_set_rule r
where r.rule_set_name=c.rule_set_name
order by c.id#
/
spool off
And sample output is:
SQL> @dv_cmd
COMMAND CLAUSE_N RULE_SET_NAME RULE_NAME E RULE_EXPR_MOD
--------------- -------- -------------------------------------------------- ------------------------------ - ----------------------------------------------------------------------------------------------------
CREATE USER % Can Maintain Accounts/Profiles Is User Manager Y DVSYS.DBMS_MACUTL.ROLE_GRANTED_ENABLED_VARCHAR('DV_ACCTMGR','"'||dvsys.dv_login_user||'"', 1, dvsys.
get_required_scope) = 'Y'
CREATE USER % Can Maintain Accounts/Profiles Is Drop User Allowed Y DVSYS.DBMS_MACADM.IS_DROP_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
ALTER USER % Can Maintain Own Account Login User Is Object User Y dvsys.dv_login_user = dvsys.dv_dict_obj_name
ALTER USER % Can Maintain Own Account Is Alter DVSYS Allowed Y DVSYS.DBMS_MACADM.IS_ALTER_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
DROP USER % Can Maintain Accounts/Profiles Is User Manager Y DVSYS.DBMS_MACUTL.ROLE_GRANTED_ENABLED_VARCHAR('DV_ACCTMGR','"'||dvsys.dv_login_user||'"', 1, dvsys.
get_required_scope) = 'Y'
DROP USER % Can Maintain Accounts/Profiles Is Drop User Allowed Y DVSYS.DBMS_MACADM.IS_DROP_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
CREATE PROFILE % Can Maintain Accounts/Profiles Y DVSYS.DBMS_MACADM.IS_DROP_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
COMMAND CLAUSE_N RULE_SET_NAME RULE_NAME E RULE_EXPR_MOD
--------------- -------- -------------------------------------------------- ------------------------------ - ----------------------------------------------------------------------------------------------------
CREATE PROFILE % Can Maintain Accounts/Profiles Is User Manager Y DVSYS.DBMS_MACUTL.ROLE_GRANTED_ENABLED_VARCHAR('DV_ACCTMGR','"'||dvsys.dv_login_user||'"', 1, dvsys.
get_required_scope) = 'Y'
ALTER PROFILE % Can Maintain Accounts/Profiles Is Drop User Allowed Y DVSYS.DBMS_MACADM.IS_DROP_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
ALTER PROFILE % Can Maintain Accounts/Profiles Is User Manager Y DVSYS.DBMS_MACUTL.ROLE_GRANTED_ENABLED_VARCHAR('DV_ACCTMGR','"'||dvsys.dv_login_user||'"', 1, dvsys.
get_required_scope) = 'Y'
DROP PROFILE % Can Maintain Accounts/Profiles Is Drop User Allowed Y DVSYS.DBMS_MACADM.IS_DROP_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
DROP PROFILE % Can Maintain Accounts/Profiles Is User Manager Y DVSYS.DBMS_MACUTL.ROLE_GRANTED_ENABLED_VARCHAR('DV_ACCTMGR','"'||dvsys.dv_login_user||'"', 1, dvsys.
get_required_scope) = 'Y'
COMMAND CLAUSE_N RULE_SET_NAME RULE_NAME E RULE_EXPR_MOD
--------------- -------- -------------------------------------------------- ------------------------------ - ----------------------------------------------------------------------------------------------------
CHANGE PASSWORD % Can Maintain Own Account Login User Is Object User Y dvsys.dv_login_user = dvsys.dv_dict_obj_name
CHANGE PASSWORD % Can Maintain Own Account Is Alter DVSYS Allowed Y DVSYS.DBMS_MACADM.IS_ALTER_USER_ALLOW_VARCHAR('"'||dvsys.dv_login_user||'"') = 'Y'
ALTER SYSTEM SET Disabled False Y 1=0
ALTER SYSTEM SET Disabled Y 1=0
ALTER SYSTEM SET Disabled Y 1=0
ALTER SYSTEM SET Not allow to set OPTIMIZER_SECURE_VIEW_MERGING to Is Parameter Value Not True Y UPPER(DVSYS.parameter_value) <> 'TRUE'
True
ALTER SYSTEM SET Not allow to set PLSQL_DEBUG to True Y UPPER(DVSYS.parameter_value) <> 'TRUE'
ALTER SYSTEM SET Not allow to set AUDIT_SYS_OPERATIONS to False Is Parameter Value Not False Y UPPER(DVSYS.parameter_value) <> 'FALSE'
ALTER SYSTEM SET Not allow to turn off AUDIT_TRAIL Is Parameter Value Not Off Y UPPER(DVSYS.parameter_value) <> 'OFF'
COMMAND CLAUSE_N RULE_SET_NAME RULE_NAME E RULE_EXPR_MOD
--------------- -------- -------------------------------------------------- ------------------------------ - ----------------------------------------------------------------------------------------------------
ALTER SYSTEM SET Not allow to turn off AUDIT_TRAIL Is Parameter Value Not None Y UPPER(DVSYS.parameter_value) <> 'NONE'
ALTER SYSTEM SET Not allow to set REMOTE_OS_ROLES to True Is Parameter Value Not True Y UPPER(DVSYS.parameter_value) <> 'TRUE'
ALTER SYSTEM SET Not allow to set OS_ROLES to True Y UPPER(DVSYS.parameter_value) <> 'TRUE'
ALTER SYSTEM SET Not allow to set SQL92_SECURITY to False Is Parameter Value Not False Y UPPER(DVSYS.parameter_value) <> 'FALSE'
ALTER SYSTEM SET Allow Fine Grained Control for Alter System Are Dump Parameters Allowed Y DVSYS.parameter_name = 'MAX_DUMP_FILE_SIZE' OR DVSYS.parameter_name = '_LOG_SEGMENT_DUMP_PATCH' OR D
VSYS.parameter_name = '_LOG_SEGMENT_DUMP_PARAMETER' OR DVSYS.parameter_name NOT LIKE '%DUMP%'
ALTER SYSTEM SET Allow Fine Grained Control for Alter System Are Dest Parameters Allowed Y DVSYS.parameter_name = 'STANDBY_ARCHIVE_DEST' OR DVSYS.parameter_name = 'DB_RECOVERY_FILE_DEST_SIZE'
OR DVSYS.parameter_name LIKE '%LOG_ARCHIVE_DEST%' OR DVSYS.parameter_name LIKE '%CURSOR_BIND_CAPTUR
E_DESTINATION%' OR DVSYS.parameter_name NOT LIKE '%_DEST%'
COMMAND CLAUSE_N RULE_SET_NAME RULE_NAME E RULE_EXPR_MOD
--------------- -------- -------------------------------------------------- ------------------------------ - ----------------------------------------------------------------------------------------------------
ALTER SYSTEM DUMP Allow Dumping Datafile Header Is Dump of Block Allowed Y DVSYS.dbms_macutl.alter_system_dump_varchar = 'Y'
ALTER SYSTEM SET Disabled False Y 1=0
ALTER SYSTEM SET Disabled Y 1=0
ALTER SYSTEM SET Disabled Y 1=0
31 rows selected.
SQL>
Database Vault can be sometimes be complex to set up and use and to understand what is controlling what.
#oracleace #oracleacepro #sym_42 #oracle #database #vault #dv #command #rule
