-
Title
Running sp_add_triggers script results in error "Error -1031:ORA-01031: insufficient privileges" -
Description
The following errors are observed when the script sp_add_triggers.sql is invoked though the script is able to disable the firing of triggers on the actions of Shareplex user:
-- Oracle error encountered in create_trigger objectid: XDB.XDB$RESCONFIG$xd
Error -1031:ORA-01031: insufficient privileges
when_clause:
-- Oracle error encountered in create_trigger objectid: XDB.XDB$STATS$xd
Error -1031:ORA-01031: insufficient privileges
when_clause:
-- Oracle error encountered in create_trigger objectid: XDB.XDB$ACL$xd
Error -1031:ORA-01031: insufficient privileges
when_clause:
-
Cause
New administrative schema other than SYS or SYSTEM has been introduced in the Oracle version in question and the script is not able to operate on it. -
Resolution
The script sp_add_triggers.sql disables the row level triggers for schema other than SYS, SYSTEM and SCOTT, the first two being administrative users. This is hard coded in script as below:
-- trigger objectid and table objectid
CURSOR cda_trigids IS
(select obj#,
baseobject
from sys.trigger$
where baseobject in
(select obj#
from sys.obj$ ob,sys.user$ usr
where ob.obj# = sys.trigger$.baseobject
and ob.owner# = usr.user#
and usr.name not in ('SYS','SYSTEM','SCOTT'))) ;
The script may result in ORA-01031 if a new administrative user or super user is introduced in an Oracle version (as happened in our case in Oracle 12c with the schema named XDB). The reason being, the script is run as Shareplex user which happens to have DBA privileges but it will not be able to modify the triggers in a newly introduced super user like XDB.
There are 2 ways to deal with the error:
A. The error can simply be ignored since the script is able to modify the triggers for replicating objects and XDB is not a replicating schema.
B. If the error is a distraction, the script can be modified to ignore the problem schema (XDB in our case) as the following example shows:
and usr.name not in ('SYS','SYSTEM','SCOTT', ‘XDB’))) ;
However, when making modification to the script, ensure that the modified script is tested before being deployed in a production environment.