Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01031: insufficient privileges | Database level trigger (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
ORA-01031: insufficient privileges | Database level trigger [message #678662] Wed, 25 December 2019 23:43 Go to next message
vippysharma
Messages: 60
Registered: May 2013
Location: www
Member
While trying to create database level trigger (see below) i am getting Insufficient privileges exception. I tried to create via SYSTEM, SYSDBA as well as MY_SCHEMA but every time getting same error.

CREATE OR REPLACE TRIGGER MY_SCHEMA.ENTLOGON_TR AFTER LOGON ON DATABASE
BEGIN
              MY_SCHEMA.SESSION_PKG.LOGON_PROC();
END;
Any idea why Confused ? do i need to grant something here ?
Re: ORA-01031: insufficient privileges | Database level trigger [message #678663 is a reply to message #678662] Thu, 26 December 2019 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 66861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't tell, show!
Use SQL*Plus and copy and paste your session, the WHOLE session.

Re: ORA-01031: insufficient privileges | Database level trigger [message #678664 is a reply to message #678663] Thu, 26 December 2019 02:24 Go to previous messageGo to next message
vippysharma
Messages: 60
Registered: May 2013
Location: www
Member
SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 26 13:42:14 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: system/system@VIPSTC
Last Successful login time: Thu Dec 26 2019 09:45:22 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE OR REPLACE TRIGGER NETWORK_PLANNING.ENTLOGOFF_TR
  2      BEFORE LOGOFF
  3      ON DATABASE
  4  BEGIN
  5      DELETE FROM ENT_ACTIVELOGON_DATA
  6            WHERE SESSIONID = DBMS_SESSION.UNIQUE_SESSION_ID ();
  7
  8      COMMIT;
  9  END;
 10  /
CREATE OR REPLACE TRIGGER NETWORK_PLANNING.ENTLOGOFF_TR
                          *
ERROR at line 1:
ORA-01031: insufficient privileges
PS: This is another trigger having same problem. And SYSTEM has all DML rights against table ENT_ACTIVELOGON_DATA.
Re: ORA-01031: insufficient privileges | Database level trigger [message #678665 is a reply to message #678664] Thu, 26 December 2019 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not SYSTEM you should have the rights but NETWORK_PLANNING as it is the owner of the trigger.

Re: ORA-01031: insufficient privileges | Database level trigger [message #678673 is a reply to message #678665] Fri, 27 December 2019 01:20 Go to previous messageGo to next message
vippysharma
Messages: 60
Registered: May 2013
Location: www
Member
Not able to login NETWORK_PLANNING from oracle client (another m/c) but successfully login from direct DB and created trigger successfully.
Re: ORA-01031: insufficient privileges | Database level trigger [message #678690 is a reply to message #678664] Fri, 27 December 2019 08:49 Go to previous messageGo to next message
EdStevens
Messages: 1178
Registered: September 2013
Senior Member
I'd re-think that COMMIT inside a trigger ....
Re: ORA-01031: insufficient privileges | Database level trigger [message #678692 is a reply to message #678690] Fri, 27 December 2019 09:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2914
Registered: January 2010
Location: Connecticut, USA
Senior Member
This is database level trigger, so commit is allowed. But here it isn't not needed since it is last statement in the trigger and login itself commits at the end. But in other cases it might be helpful when used in the middle - e.g. to run DBMS_JOB or to force on commit MV refresh...

SY.



Re: ORA-01031: insufficient privileges | Database level trigger [message #678695 is a reply to message #678692] Fri, 27 December 2019 10:48 Go to previous messageGo to next message
EdStevens
Messages: 1178
Registered: September 2013
Senior Member
Solomon Yakobson wrote on Fri, 27 December 2019 09:25
This is database level trigger, so commit is allowed. But here it isn't not needed since it is last statement in the trigger and login itself commits at the end. But in other cases it might be helpful when used in the middle - e.g. to run DBMS_JOB or to force on commit MV refresh...

SY.



point taken.
Re: ORA-01031: insufficient privileges | Database level trigger [message #678705 is a reply to message #678695] Mon, 30 December 2019 05:51 Go to previous messageGo to next message
Bill B
Messages: 1936
Registered: December 2004
Senior Member
To create a database level trigger such as after login or before logoff you must have the privilege "ADMINISTER DATABASE TRIGGER" granted to the user. SYSTEM is not granted this privilege by default, but SYS and the DBA role does have the privilege

[Updated on: Mon, 30 December 2019 06:21]

Report message to a moderator

Re: ORA-01031: insufficient privileges | Database level trigger [message #678709 is a reply to message #678705] Mon, 30 December 2019 23:16 Go to previous messageGo to next message
vippysharma
Messages: 60
Registered: May 2013
Location: www
Member
Quote:
I tried to create via SYSTEM, SYSDBA as well as MY_SCHEMA but every time getting same error.
Re: ORA-01031: insufficient privileges | Database level trigger [message #678741 is a reply to message #678709] Mon, 06 January 2020 04:17 Go to previous message
cookiemonster
Messages: 13815
Registered: September 2008
Location: Rainy Manchester
Senior Member
However you logged onto sys you did it wrong.
Sys has privileges for everything.
Previous Topic: Raise exception in case clause PL/SQL function
Next Topic: ORA-03001 while using bulk collect
Goto Forum:
  


Current Time: Tue Feb 25 04:32:08 CST 2020