Home » SQL & PL/SQL » SQL & PL/SQL » RAISE_APPLICATION_ERROR not invoked by Logon trigger (Oracle 11.2.0.3.0 SE , Oracle 12.1.0.2.0 SE)
RAISE_APPLICATION_ERROR not invoked by Logon trigger [message #680034] Sun, 19 April 2020 23:47 Go to next message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

Hi all,
I am trying to create a simple logon trigger.

I tried to follow the tutorial documented here ==> https://docs.oracle.com/database/121/TDDDG/tdddg_triggers.htm#TDDDG52900

Created a user for my tests:


SQL> create user a identified by a;

User created.

SQL> grant dba to a;

Grant succeeded.

SQL> grant create any trigger to a;

Grant succeeded.


Then created the log table + trigger:


sqlplus a/a

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 20 04:27:55 2020

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


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL>
CREATE TABLE hr_users_log (
  user_name VARCHAR2(30),
  activity VARCHAR2(20),
  event_date DATE
);
SQL>   2    3    4    5
Table created.

SQL>
CREATE OR REPLACE TRIGGER hr_logon_trigger
  AFTER LOGON
SQL>   2    3    ON A.SCHEMA
  4  BEGIN
  5    --
  6    INSERT INTO hr_users_log (user_name, activity, event_date)
  7    VALUES (USER, 'LOGON', SYSDATE);
  8    --
  9    /*RAISE_APPLICATION_ERROR(-20000,'TEST. APPLICATION ERROR FOUND...');*/
 10  END;
 11  /

Trigger created.

SQL> show user
USER is "A"
SQL>



Then I am trying to re-login with user A and see if my trigger fired and have table hr_logon_users_log populated with a row:


SQL>
SQL> conn a/a
Connected.
SQL>
SQL>
SQL> conn a/a
Connected.
SQL>
SQL>
SQL> select * from hr_users_log;

USER_NAME                      ACTIVITY             EVENT_DAT
------------------------------ -------------------- ---------
A                              LOGON                20-APR-20
A                              LOGON                20-APR-20

SQL>


So far so good.

I want to have an error raised whenever I connect to user A, so I change my trigger to:




SQL> CREATE OR REPLACE TRIGGER hr_logon_trigger
  AFTER LOGON
  ON A.SCHEMA
  2    3    4  BEGIN
  5    --
  6    INSERT INTO hr_users_log (user_name, activity, event_date)
  7    VALUES (USER, 'LOGON', SYSDATE);
  8    --
  9    RAISE_APPLICATION_ERROR(-20000,'TEST. APPLICATION ERROR FOUND...');
 10  END;
 11  /

Trigger created.



When I test the RAISE ERROR code without the trigger it seems working fine ( from user A ):


SQL> begin
  2  RAISE_APPLICATION_ERROR(-20000,'TEST. APPLICATION ERROR FOUND...');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-20000: TEST. APPLICATION ERROR FOUND...
ORA-06512: at line 2


But when I try to re-login with user A and see if my trigger fired ( I expect the RAISE_APPLICATION_ERROR to work and display my message ),
Or see if my log table has been added a row, I see that no row is added or message displayed:

SQL> conn a/a
Connected.
SQL>
SQL> select * from hr_users_log;

USER_NAME                      ACTIVITY             EVENT_DAT
------------------------------ -------------------- ---------
A                              LOGON                20-APR-20
A                              LOGON                20-APR-20

SQL>

If I add a commit to the insert to hr_users_log part , I do get the insert statement done OK upon login with A,
So I have an evidence that the trigger does fire:

SQL>
CREATE OR REPLACE TRIGGER hr_logon_trigger
  AFTER LOGON
  ON A.SCHEMA
SQL>   2    3    4  BEGIN
  5    --
  6    INSERT INTO hr_users_log (user_name, activity, event_date)
  7    VALUES (USER, 'LOGON', SYSDATE);
  8    --
  9    COMMIT;
 10    --
 11    RAISE_APPLICATION_ERROR(-20000,'TEST. APPLICATION ERROR FOUND...');
END;
 12   13  /

Trigger created.

SQL> CONN a/a
Connected.
SQL>
SQL> select * from hr_users_log;

USER_NAME                      ACTIVITY             EVENT_DAT
------------------------------ -------------------- ---------
A                              LOGON                20-APR-20
A                              LOGON                20-APR-20
A                              LOGON                20-APR-20

SQL>

I don't understand...Why isn't my RAISE_APPLICATION_ERROR code displaying the error and message I defined ?

Thanks in advance,
Andrey

[Updated on: Mon, 20 April 2020 00:28]

Report message to a moderator

Re: Logon trigger doesn't fire [message #680036 is a reply to message #680034] Mon, 20 April 2020 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As your user A has DBA role, it has ADMINISTER DATABASE TRIGGER, in this any error in the LOGON trigger is ignored, more specifically (Database PL/SQL Language Reference, Chapter 9 PL/SQL Triggers, section Exception Handling in Triggers):

Quote:
In the following cases, the database rolls back only the effects of the trigger, not the effects of the triggering statement (and logs the error in trace files and the alert log):
...
  • The triggering event is AFTER LOGON ON DATABASE and the user has the ADMINISTER DATABASE TRIGGER privilege.
...
Re: Logon trigger doesn't fire [message #680037 is a reply to message #680036] Mon, 20 April 2020 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I ,checked in 11gR2, there is no message, with or without COMMIT:
SQL> create user a identified by a;

User created.

SQL> grant dba to a;

Grant succeeded.

SQL> grant create any trigger to a;

Grant succeeded.

SQL> conn a/a
Connected.
A> CREATE TABLE hr_users_log (
  2    user_name VARCHAR2(30),
  3    activity VARCHAR2(20),
  4    event_date DATE
  5  );

Table created.

A> CREATE OR REPLACE TRIGGER hr_logon_trigger
  2    AFTER LOGON
  3    ON A.SCHEMA
  4  BEGIN
  5    --
  6    INSERT INTO hr_users_log (user_name, activity, event_date)
  7    VALUES (USER, 'LOGON', SYSDATE);
  8    --
  9    COMMIT;
 10    --
 11    RAISE_APPLICATION_ERROR(-20000,'TEST. APPLICATION ERROR FOUND...');
 12  END;
 13  /

Trigger created.

A> conn a/a
Connected.
A> select * from hr_users_log;
USER_NAME                      ACTIVITY             EVENT_DATE
------------------------------ -------------------- -------------------
A                              LOGON                20/04/2020 07:33:30

1 row selected.

A> CREATE OR REPLACE TRIGGER hr_logon_trigger
  2    AFTER LOGON
  3    ON A.SCHEMA
  4  BEGIN
  5    --
  6    INSERT INTO hr_users_log (user_name, activity, event_date)
  7    VALUES (USER, 'LOGON', SYSDATE);
  8    --
  9  --  COMMIT;
 10    --
 11    RAISE_APPLICATION_ERROR(-20000,'TEST. APPLICATION ERROR FOUND...');
 12  END;
 13  /

Trigger created.

A> conn a/a
Connected.
A>  select * from hr_users_log;
USER_NAME                      ACTIVITY             EVENT_DATE
------------------------------ -------------------- -------------------
A                              LOGON                20/04/2020 07:33:30

1 row selected.

A> @v

Oracle version: 11.2.0.4.181016 EE

[Updated on: Mon, 20 April 2020 00:47]

Report message to a moderator

Re: Logon trigger doesn't fire [message #680038 is a reply to message #680036] Mon, 20 April 2020 01:06 Go to previous messageGo to next message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Mon, 20 April 2020 08:45

As your user A has DBA role, it has ADMINISTER DATABASE TRIGGER, in this any error in the LOGON trigger is ignored, more specifically (Database PL/SQL Language Reference, Chapter 9 PL/SQL Triggers, section Exception Handling in Triggers):

Quote:
In the following cases, the database rolls back only the effects of the trigger, not the effects of the triggering statement (and logs the error in trace files and the alert log):
...

  • The triggering event is AFTER LOGON ON DATABASE and the user has the ADMINISTER DATABASE TRIGGER privilege.
...
Does this mean that this example (https://docs.oracle.com/database/121/TDDDG/tdddg_triggers.htm#TDDDG52900) is not valid ?
Or what am I missing, while trying to have a user defined error raised as soon as a user logs in ( I will add more meaningful conditions later on... ) ?

[Updated on: Mon, 20 April 2020 01:06]

Report message to a moderator

Re: Logon trigger doesn't fire [message #680039 is a reply to message #680038] Mon, 20 April 2020 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The example is valid.
You modify it to add an exception and this exception is ignored for user having ADMINISTER DATABASE TRIGGER.
Only (few specific) dba should have this privilege.

In addition, the owner of a LOGON trigger is also exempted of errors raised by its own trigger:
SQL> drop user a cascade;

User dropped.

SQL> create user a identified by a;

User created.

SQL> grant create session to a;

Grant succeeded.

SQL> alter user a quota unlimited on ts_d01;

User altered.

SQL> CREATE TABLE a.hr_users_log (
  2    user_name VARCHAR2(30),
  3    activity VARCHAR2(20),
  4    event_date DATE
  5  )
  6  /

Table created.

SQL> CREATE OR REPLACE TRIGGER a.hr_logon_trigger
  2    AFTER LOGON
  3    ON A.SCHEMA
  4  BEGIN
  5    --
  6    INSERT INTO hr_users_log (user_name, activity, event_date)
  7    VALUES (USER, 'LOGON', SYSDATE);
  8    --
  9    COMMIT;
 10    --
 11    RAISE_APPLICATION_ERROR(-20000,'TEST. APPLICATION ERROR FOUND...');
 12  END;
 13  /

Trigger created.

SQL> conn a/a
Connected.
A> select * from hr_users_log;
USER_NAME                      ACTIVITY             EVENT_DATE
------------------------------ -------------------- -------------------
A                              LOGON                20/04/2020 08:26:38

1 row selected.
If you want to limit the connection of some accounts, you have to create the LOGON trigger in another schema.

Re: Logon trigger doesn't fire [message #680044 is a reply to message #680039] Mon, 20 April 2020 03:15 Go to previous messageGo to next message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Mon, 20 April 2020 09:30

The example is valid.
You modify it to add an exception and this exception is ignored for user having ADMINISTER DATABASE TRIGGER.
Only (few specific) dba should have this privilege.

In addition, the owner of a LOGON trigger is also exempted of errors raised by its own trigger:
SQL> drop user a cascade;

User dropped.

SQL> create user a identified by a;

User created.

SQL> grant create session to a;

Grant succeeded.

SQL> alter user a quota unlimited on ts_d01;

User altered.

SQL> CREATE TABLE a.hr_users_log (
  2    user_name VARCHAR2(30),
  3    activity VARCHAR2(20),
  4    event_date DATE
  5  )
  6  /

Table created.

SQL> CREATE OR REPLACE TRIGGER a.hr_logon_trigger
  2    AFTER LOGON
  3    ON A.SCHEMA
  4  BEGIN
  5    --
  6    INSERT INTO hr_users_log (user_name, activity, event_date)
  7    VALUES (USER, 'LOGON', SYSDATE);
  8    --
  9    COMMIT;
 10    --
 11    RAISE_APPLICATION_ERROR(-20000,'TEST. APPLICATION ERROR FOUND...');
 12  END;
 13  /

Trigger created.

SQL> conn a/a
Connected.
A> select * from hr_users_log;
USER_NAME                      ACTIVITY             EVENT_DATE
------------------------------ -------------------- -------------------
A                              LOGON                20/04/2020 08:26:38

1 row selected.
If you want to limit the connection of some accounts, you have to create the LOGON trigger in another schema.

I tried with *NO DBA privilege* and with the *Trigger on a different schema* ( SYSTEM ) and it worked well:

SQL> show user
USER is "SYS"
SQL>
SQL>
create user a identified by a;SQL>

User created.

SQL>
grant resource , connect to a;
SQL>
Grant succeeded.

SQL>
SQL> conn a/a
Connected.
SQL> show user
USER is "A"
SQL>
SQL>
SQL>
CREATE TABLE a.hr_users_log (
  user_name VARCHAR2(30),
  activity VARCHAR2(20),
  event_date DATE
);
SQL>   2    3    4    5
Table created.

SQL>
SQL>
SQL>
SQL> grant all on a.hr_users_log to system;

Grant succeeded.

SQL>
SQL>
SQL> conn system
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER system.hr_logon_trigger
  2    AFTER LOGON
  3    ON A.SCHEMA
  4  BEGIN
  5    --
  6    INSERT INTO a.hr_users_log (user_name, activity, event_date)
  7    VALUES (USER, 'LOGON', SYSDATE);
  8    --
  9    COMMIT;
 10    --
 11    RAISE_APPLICATION_ERROR(-20000,'TEST. APPLICATION ERROR FOUND...');
END;
 12   13  /

Trigger created.

SQL> conn a/a
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: TEST. APPLICATION ERROR FOUND...
ORA-06512: at line 8


Warning: You are no longer connected to ORACLE.

However, if I grant DBA role to this user, I cannot raise the error for this user upon login.
I cannot revoke the ADMINISTER DATABASE TRIGGER from this user, it's DBA role or nothing..

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 20 08:07:26 2020

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


Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> select * from a.hr_users_log;

USER_NAME                      ACTIVITY             EVENT_DAT
------------------------------ -------------------- ---------
A                              LOGON                20-APR-20
A                              LOGON                20-APR-20

SQL> grant dba to a;

Grant succeeded.

SQL> conn a/a
Connected.
SQL>
Is there a way to do it for a DBA-role granted user ?

[Updated on: Mon, 20 April 2020 03:15]

Report message to a moderator

Re: Logon trigger doesn't fire [message #680045 is a reply to message #680044] Mon, 20 April 2020 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No way by specification.

By the way, you should (almost) never grant DBA role; you should build your own specific dba role(s).
See this post.

Re: Logon trigger doesn't fire [message #680046 is a reply to message #680044] Mon, 20 April 2020 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Create your own DBA role that doesn't include ADMINISTER DATABASE TRIGGER and grant that instead.

You need to realize that this behaviour exists to avoid the possibility of having a DB that no one can log into.
Re: RAISE_APPLICATION_ERROR not invoked by Logon trigger [message #680054 is a reply to message #680034] Mon, 20 April 2020 08:52 Go to previous messageGo to next message
EdStevens
Messages: 1350
Registered: September 2013
Senior Member
Other than as a learning excercise, why are you even doing this at all?
If you want to audit connection requests, the use oracle's ready-built AUDIT.
If you want to prevent logon for certain schemas, simply lock there accounts.
Re: Logon trigger doesn't fire [message #680056 is a reply to message #680045] Mon, 20 April 2020 11:10 Go to previous messageGo to next message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

cookiemonster wrote on Mon, 20 April 2020 11:41
Create your own DBA role that doesn't include ADMINISTER DATABASE TRIGGER and grant that instead.

You need to realize that this behaviour exists to avoid the possibility of having a DB that no one can log into.

I understand. Thank you.



EdStevens wrote on Mon, 20 April 2020 16:52
Other than as a learning excercise, why are you even doing this at all?
If you want to audit connection requests, the use oracle's ready-built AUDIT.
If you want to prevent logon for certain schemas, simply lock there accounts.
I need to protect a concurrent user limit for users that have DBA role





Michel Cadot wrote on Mon, 20 April 2020 11:39

No way by specification.

By the way, you should (almost) never grant DBA role; you should build your own specific dba role(s).
See this post.


I agree. Unfortunately, some things are implemented deep and are a given for most cases.
Nevertheless, knowledge is power and I can use it to workaround the obstacles and handle the task objective.



I do seem to recall witnessing an error raise working with a logon trigger,
I think I made it with a call to a procedure that does the error raising,
However , when I tried to test such scenarios I was always getting the result according to whether ADMINISTER DATABASE TRIGGER is set ( implicitly by DBA role )



Thank you everyone for the advice and time.

Andrey
Re: Logon trigger doesn't fire [message #680057 is a reply to message #680056] Mon, 20 April 2020 11:28 Go to previous message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I need to protect a concurrent user limit for users that have DBA role

In the LOGON trigger, in case of a DBA user, you can count the DBA users and if the limit is reached submit a job that will kill the current session.

Previous Topic: Hierarchy again and again
Next Topic: PL/SQL Stored Procedure Showing Oracle Error 27476 (Job Doesn't Exist)
Goto Forum:
  


Current Time: Tue Nov 30 03:43:50 CST 2021