Tracing users by after logon trigger

mikys67

Hi.

I have to trace some users application to find the source of problems.
Oracle8i Enterprise Edition Release 8.1.7.0.0

Expand|Select|Wrap|Line Numbers

  
connect system/manager@testdb
 
create or replace trigger login_trigger

after logon on database

  begin

     if (USER in ('BLAKE','SCOTT')) then

execute immediate

    'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

     end if;

  end;

/

show error;
 
SQL> connect scott/tiger@testdb

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01031: insufficient privileges

ORA-06512: at line 3
 
OK, I'll grant a priv.
 
SQL> connect system/manager@testdb

SQL> GRANT administer DATABASE TRIGGER TO "SCOTT";
 
SQL> connect scott/tiger@testdb

Connected.

Good, only *.trc file is empty after that and there is no trace information for analyse.

Could you please give me a solution?

Mikhail

amitpatel66

After the privilege is GRANT to scott, were you able to create a after logon trigger without any error under user scott?

mikys67

Quote:

Originally Posted by amitpatel66

After the privilege is GRANT to scott, were you able to create a after logon trigger without any error under user scott?

I've done it. The empty *.trc file again
ERROR: no valuable data in trace file (not from 10046 event?)
and Why should I create scott owner trigger. I have to use one trigger to trace muliple users

mikys67

Quote:

Originally Posted by amitpatel66

After the privilege is GRANT to scott, were you able to create a after logon trigger without any error under user scott?

Sorry for the trouble I'm giving you. The reason was my misunderstanding the necessary privileges and owner to create trigger. The correct sequence to prepare trace rule must be:
1 - SYSTEM user grants ADMINISTER DATABASE TRIGGER privilege to SCHEMA OWNER (e.g.SCOTT)
2 - having such privilege SCHEMA OWNER (e.g.SCOTT) should create TRIGGER AFTER LOGON to force system trace event 10046.
3 - SYSTEM or any privileged owner controls TRIGGER using DISABLE\ENABLE instruction

amitpatel66

Quote:

Originally Posted by mikys67

Sorry for the trouble I'm giving you. The reason was my misunderstanding the necessary privileges and owner to create trigger. The correct sequence to prepare trace rule must be:
1 - SYSTEM user grants ADMINISTER DATABASE TRIGGER privilege to SCHEMA OWNER (e.g.SCOTT)
2 - having such privilege SCHEMA OWNER (e.g.SCOTT) should create TRIGGER AFTER LOGON to force system trace event 10046.
3 - SYSTEM or any privileged owner controls TRIGGER using DISABLE\ENABLE instruction

Error Resolved?? .

fcdfreitas

The logon trigger must be created by SYS AS SYSDBA.

Even if you create a DBA user or another user with the ADMINISTER DATABASE TRIGGERS privilege, it won't work.

Regards,
Flavio

Quote:

Originally Posted by mikys67

Hi.

I have to trace some users application to find the source of problems.
Oracle8i Enterprise Edition Release 8.1.7.0.0

Expand|Select|Wrap|Line Numbers

  
connect system/manager@testdb
 
create or replace trigger login_trigger

after logon on database

  begin

     if (USER in ('BLAKE','SCOTT')) then

execute immediate

    'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';

     end if;

  end;

/

show error;
 
SQL> connect scott/tiger@testdb

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01031: insufficient privileges

ORA-06512: at line 3
 
OK, I'll grant a priv.
 
SQL> connect system/manager@testdb

SQL> GRANT administer DATABASE TRIGGER TO "SCOTT";
 
SQL> connect scott/tiger@testdb

Connected.

Good, only *.trc file is empty after that and there is no trace information for analyse.

Could you please give me a solution?

Mikhail

Tracing users by after logon trigger

re: Tracing users by after logon trigger

re: Tracing users by after logon trigger

re: Tracing users by after logon trigger

re: Tracing users by after logon trigger

re: Tracing users by after logon trigger

Similar Oracle Database bytes

/bytes/development

/bytes/it

/bytes/about