We help IT Professionals succeed at work.

Getting a LOGON trigger to work

myerpz
myerpz asked
on
Oracle 8.1.6 on an NT4 PC
I have got a database logon procedure as follows :-

   CREATE OR REPLACE TRIGGER logontrig
   AFTER LOGON ON DATABASE
   declare
      query_str   varchar2(1000) := '';
   begin
      query_str := 'alter session set current_schema = user1';
      EXECUTE IMMEDIATE query_str;
   exception
      when others then null;
   end;
   /

The trigger is owned by user1.

When I log in as user2, and run the following :-

   set serveroutput on size 1000000;
   begin
    dbms_output.put_line(SYS_CONTEXT('USERENV','CURRENT_SCHEMA'));
   end;
   /

It shows me that the current schema is still user2, so it looks like the trigger has not run.

However when I run the trigger body as an anonymous PL/SQL block, and then run :-

   set serveroutput on size 1000000;
   begin
    dbms_output.put_line(SYS_CONTEXT('USERENV','CURRENT_SCHEMA'));
   end;
   /

... it works ok.

It looks like I need to grant some privilege to user2 to allow him to run the login trigger I defined earlier ( which is owned by user1, by the way). But I cannot grant him access to a trigger, only to a procedure etc.

Does anyone know what user2 needs to be granted in order for him to be able to run this trigger ?
I tried "GRANT ADMINISTER DATABASE TRIGGER TO user2;" but it didn't work.


Many thanks
Comment
Watch Question

If you see the grant statement there is no provision to grant execute on trigger to other users.

grant execute applies only to procedure/function/package.

Thus trigger owned by user1 can not be executed by other users. However if a procedure is created for this functionality the same can be executed by public.

This means that trigger has to be created for each user.
I subscribe to the fact that a trigger for logon of a user gets executed only when that user logs on and can't, and even shouldn't execute when some other user logs on. Would it make any sense to be otherwise?
So, you should have an after-logon trigger on each user that you wish to apply an action after logon.
This might be somewhat simplified if in the trigger you call a stored proc to which the user has the execute granted (you won't need several copies of the procedure code in different schemas).
Commented:
You should write your code as below. In your case database trigger should be after logon on database.

IN the following example when your scott will connect to the database , his current schema would be samdba.

CREATE OR REPLACE TRIGGER logontrig
  AFTER LOGON ON scott.schema
  declare
     query_str   varchar2(1000) := '';
  begin
     query_str := 'alter session set current_schema = SAMDBA';
     EXECUTE IMMEDIATE query_str;
  exception
     when others then null;
  end;
/

The above trugger should be created in sys user account. after creating the trigger , Do not forget to  restart the instance after creation of the trigger.

Hope this will solve your problem.

Goodluck
Sam

Author

Commented:
Many thanks to the first two respondents, but the 3rd one helped me the most.
After re-starting the database my original trigger fired for every user who connected.
But using the restriction "AFTER LOGON ON user1.schema" means that I could avoid having to use an IF block such as
   IF   USER = 'user2'
   then EXECUTE IMMEDIATE query_str;
   endif;

... in my original trigger.

Thanks again.


Explore More ContentExplore courses, solutions, and other research materials related to this topic.