Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1021
  • Last Modified:

oracle logon trigger

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
sivi_3883
Asked:
sivi_3883
4 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
JacobfwCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
Technology Partners: 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!

 
johnsoneSenior Oracle DBACommented:
COMMIT is not allowed in a trigger and will produce an error.  I believe it is an ORA-04092.
0
 
sivi_3883Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now