Wednesday 6 February 2019

ORA-01031: insufficient privileges DB Vault

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
In  Database Vault enabled databases, SYS user is not able to change certain  parameters using and ALTER SYSTEM command.
The commands are failing :
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
So there are two ways you can solve it
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.
select * from dvsys.DBA_DV_COMMAND_RULE;
COMMAND                        RULE_SET_NAME                                                                              OBJECT_OWNER    OBJECT_NAME                         E PRIVILEGE_SCOPE
—————————— —————————————————————————————— ————— ———————————– – —————
CREATE USER                    Can Maintain Accounts/Profiles                                                             %               %                                   Y
DROP USER                      Can Maintain Accounts/Profiles                                                             %               %                                   Y
CREATE PROFILE                 Can Maintain Accounts/Profiles                                                             %               %                                   Y
ALTER PROFILE                  Can Maintain Accounts/Profiles                                                             %               %                                   Y
DROP PROFILE                   Can Maintain Accounts/Profiles                                                             %               %                                   Y
ALTER USER                     Can Maintain Own Account                                                                   %               %                                   Y
CHANGE PASSWORD                Can Maintain Own Account                                                                   %               %                                   Y
ALTER SYSTEM                   Allow Fine Grained Control of System Parameters                                            %               %                                   Y
begin  DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE
(command=> ‘ALTER SYSTEM’,
rule_set_name   => ‘Allow Fine Grained Control of System Parameters’,
object_owner    => ‘%’,
object_name     => ‘%’,
enabled         => ‘N’);
end;
/
Change the parameter
alter system set max_dump_file_size=10000 scope=spfile;
begin  DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE
(command=> ‘ALTER SYSTEM’,
rule_set_name   => ‘Allow Fine Grained Control of System Parameters’,
object_owner    => ‘%’,
object_name     => ‘%’,
enabled         => ‘Y’);
end;
/
select * from dvsys.DBA_DV_COMMAND_RULE;
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>

No comments:

Post a Comment