?
Solved

oracle logon trigger

Posted on 2011-09-19
6
Medium Priority
?
972 Views
Last Modified: 2013-12-19
Hello All,

I would like to track the users who are connecting to one schema (say "Scott") and I am NOT interested in tracking the users who are connecting to other schemas in the database. So can I execute this trigger in "scott" schema (and not in sys or system)? I am new to the system level triggers and I would like to know your expert comments if there is any downside or server impact due to this system level trigger? Do I need to restart the DB server for the trigger to work?

create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats_user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
COMMIT;
END;
0
Comment
Question by:sivi_3883
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 752 total points
ID: 36560998
system triggers execute at the system level.  You need to add an IF statement to check the username and only perform the insert if user='SCOTT'.
0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 248 total points
ID: 36561021
As indicated by @slightwv, an If statement will work.
Impact on performance will be minimal for this system trigger unless you have a lot of user traffic.  There is always some impact of adding a trigger, but the impact will mainly come from the Insert statement in this case, which is adding one record for each logon.  Not a lot of overhead.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 752 total points
ID: 36561151
I suppose I could have provided the information for the trigger.

I removed the commit because I cannot remember if this is allowed in a trigger.

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/triggers.htm#LNPLS794

create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN

if ora_login_user = 'SCOTT' then
insert into stats_user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
end if;
END;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:johnsone
ID: 36561269
COMMIT is not allowed in a trigger and will produce an error.  I believe it is an ORA-04092.
0
 

Author Comment

by:sivi_3883
ID: 36561451
All,
Thanks for your valuable comments.
I agree COMMIT is not allowed in trigger. Since I am performing a DML statement(INSERT) inside the trigger, I would like to confirm this. I dont use COMMIT inside Row/statement level triggers since the procedure(which will trigger the event on the respective table) will have the COMMIT.
In this case, will DML statement(insert) be committed automotically by a system level trigger?
Does it need a server restart for this trigger to work?

0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 752 total points
ID: 36561469
I came across a commit in an after logon trigger from an old script I had laying around so it must have been allowed at one point in the past.

>>Does it need a server restart for this trigger to work?

No.


>>be committed automotically by a system level trigger?

Check the doc link I provided.

For AFTER LOGIN:
Starts separate transaction and commits it after firing triggers.
 
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question