Tracing users by after logon trigger 
March 29th, 2008, 10:51 AM
| | Newbie | | Join Date: Mar 2008
Posts: 3
| |
Hi.
I have to trace some users application to find the source of problems.
Oracle8i Enterprise Edition Release 8.1.7.0.0 -
-
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
Last edited by amitpatel66; March 29th, 2008 at 12:21 PM.
Reason: code tags
|
March 29th, 2008, 12:22 PM
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,188
| | | re: Tracing users by after logon trigger
After the privilege is GRANT to scott, were you able to create a after logon trigger without any error under user scott?
|
March 29th, 2008, 12:55 PM
| | Newbie | | Join Date: Mar 2008
Posts: 3
| | | re: Tracing users by after logon trigger
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
|
March 30th, 2008, 05:05 PM
| | Newbie | | Join Date: Mar 2008
Posts: 3
| | | re: Tracing users by after logon trigger
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
|
March 31st, 2008, 01:38 PM
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,188
| | | re: Tracing users by after logon trigger
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?? .
|
June 18th, 2009, 03:51 PM
| | Newbie | | Join Date: Jun 2009
Posts: 1
| | | re: Tracing users by after logon trigger
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 -
-
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 | |  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 225,662 network members.
|
