Alter a trigger by a procedure ORA-01031: insufficient privileges

Tried to disable and enable a trigger (which is own by another user)  by a procedure but we are getting error  "ORA-01031: insufficient privileges" event though calling user(current user) have all the neccesary proviliges like "alter any trigger"

Solution:

I hav added  "AUTHID CURRENT_USER" clause in the procedure then it started working as below - 

Just as example-

CREATE OR REPLACE PROCEDURE test
   AUTHID CURRENT_USER  ----set Invoker Rights not definer rights
is

begin

EXECUTE IMMEDIATE 'alter trigger tri_name disable'; 

.......

........

EXECUTE IMMEDIATE 'alter trigger tri_name enable'; 

 end ; 

  For Details

Alter Trigger fails with ORA-01031: insufficient privileges in Oracle

A small thing but if you don't know it might make you confused and need to spend lots of times.

 Today, we are trying to disable a trigger which is own by a differen user but we are keep geting error "ORA-01031: insufficient privileges" even though we are trying with SYS user.  

The Trigger was a "after logon on database"  trigger and database version 19c

Cause:   

Oracle enforced administer database trigger for both Current_User and Trigger Owner (provably from oracle database 12.2 onwards) .

 

Solution:  

Need to provide a direct grant of "administer database trigger" for the trigger owner and may be also for current user.     

In our case below example worked. 

example- 

Trigger owner:  SCOTT     

Then scott needs have below privilage directly (even from a ROLE which has this privilege won't work, need to provide directly)

SQL> grant administer database trigger to SCOTT ;  

 

 

 

 

 

Is APEX installed by default in all latest Oracle Databases.

Just for clearification- I was under impression that, Apex is installed in all latest oracle versions but that's not true.

 Till oracle database 12.1.0.2, Apex schemas were installed by default. But from version 12.2.0.1 onwards they are not installed by default (that's a good idea) but it come with following supplied versions-

 
With Database 12.2.0.1 is Application Express 5.0.4. APEX is not installed by default.
With Database 18.0.0.0 is Application Express 5.1.3. APEX is not installed by default.
With Database 19.0.0.0 is Application Express 18.2. APEX is not installed by default.
With Database 21c is Application Express 20.1. APEX is not installed by default.