Solved

oracle logon trigger

Posted on 2011-09-19
6
939 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
6 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 188 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 62 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 188 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 188 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now