Solved

oracle logon trigger

Posted on 2011-09-19
6
950 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 66
make null the repeated levels 2 31
Oracle collections 15 27
Email query results in HTML 6 29
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

839 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