When DV Is Enabled SYS User Is Not Able To Change Certain Parameters - ORA-01031 (Doc ID 758327.1)SYMPTOMS
In Database Vault enabled databases, SYS user is not able to change certain parameters using and ALTER SYSTEM command. The commands are failing :
SQL> alter system set max_dump_file_size=10000 scope=spfile;
alter system set max_dump_file_size=10000 scope=spfile
*
ERROR at line 1:
ORA-01031: insufficient privileges
CAUSE
Database Vault prohibits the dynamic change of certain parameters. The dynamic change of the following parameters is blocked intentionally:
dump datafile
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_1
db_recovery_file_dest
LOG_ARCHIVE_DEST_%
log_archive_dest_state_%
background_dump_dest
core_dump_dest
user_dump_dest
audit_file_dest
db_recovery_file_dest
DB_RECOVERY_FILE_DEST_SIZE
standby_archive_dest
recyclebin=on
control_files
optimizer_secure_view_merging = true
utl_file_dir
plsql_debug=true
audit_sys_operations = false
audit_trail
remote_os_roles
os_roles
job_queue_processes
sql92_security
SOLUTION
Workaround I:
Disable the ALTER SYSTEM command rule.
Login to the Database Vault Console -> select command rule -> select ALTER SYSTEM command -> Edit and set it disable.
If console is not accessible the following code block can be used to disable the command rule
############### Disable the command rule from the command line ###########
As DV Owner:
=============
select * from DVSYS.DBA_DV_COMMAND_RULE;
select * from dvsys.DBA_DV_POLICY;
Save output of above queries
Disable the Policies -
exec dbms_macadm.update_policy_state('Oracle System Protection Controls',dbms_macadm.g_partial);
BEGIN
DBMS_MACADM.UPDATE_COMMAND_RULE(
command => 'ALTER SYSTEM',
rule_set_name => 'Allow Fine Grained Control for Alter System',
object_owner => '%',
object_name => '%',
enabled => 'N',
clause_name => 'SET',
parameter_name => '%',
event_name => '%',
component_name => '%',
scope => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/
As DBA:
===========
SQL> alter system set audit_file_dest='/oracle19c/app/audit/misdb1' scope=spfile;
Now, enable the policies again.
As DV Owner:
=============
BEGIN
DBMS_MACADM.UPDATE_COMMAND_RULE(
command => 'ALTER SYSTEM',
rule_set_name => 'Allow Fine Grained Control for Alter System',
object_owner => '%',
object_name => '%',
enabled => 'y',
clause_name => 'SET',
parameter_name => '%',
event_name => '%',
component_name => '%',
scope => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/
exec dbms_macadm.update_policy_state('Oracle System Protection Controls',DBMS_MACADM.G_ENABLED );
Validate using below queries -
select * from DVSYS.DBA_DV_COMMAND_RULE;
select * from dvsys.DBA_DV_POLICY;
Workaround II:
Manually edit init.ora and change the parameters. And restart the database with this init.ora file with the command STARTUP PFILE=<path/name of the pfile>