alter system set参数 报权限不足 ORA-01031 DB Vault

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>