?
Solved

Oracle Log on/Log off tracking

Posted on 2010-01-12
18
Medium Priority
?
865 Views
Last Modified: 2013-12-07
Hi, I found another experts exchange topic that directed me to this website: http://www.dba-oracle.com/art_builder_sec_audit.htm  to create a custom user log on / log off table in the database.  I thought this was really neat and wanted to use it.

I was able to get the log on trigger created and it works... however, I can't get the log off trigger to work.  

Whenever I try to execute the code to save the trigger it says:
8/35    PL/SQL: ORA-00942: table or view does not exist
5/1     PL/SQL: SQL Statement ignored

And it highlights this part of the code:
BEFORE LOGOFF ON DATABASE

I am running Oracle 11g ... did they remove this key word?  I have verified that my actual table names within the procedure are correct.  Attached is the code I'm trying to execute.
create or replace trigger
   logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
PAYROLL.SYSTEM_USER_LOG
set
last_action = (select action from v$session where     
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
payroll.system_user_log
set
last_program = (select program from v$session where     
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
payroll.system_user_log
set
last_module = (select module from v$session where     
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
   payroll.system_user_log
set
   logoff_day = sysdate
where
   sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
   payroll.system_user_log
set
   logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
   sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
payroll.system_user_log
set
elapsed_minutes =     
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;

Open in new window

0
Comment
Question by:Roxanne25
[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
  • 8
  • 6
  • 3
  • +1
18 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 26297493
You are running the create trigger script as a DBA, correct?
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 400 total points
ID: 26297767
Oh, I see. The problem is, your trigger does not have privilege to select from V$SESSION. DBAs get that via the DBA role, but the PL/SQL proc can't use privs by roles, it must be explic.t

So you can either create the trigger as SYS, or grant explicit SELECT privilege to the DBA user for V$_SESSION.

connect / as sysdba
SQL> grant select on sys.v_$session to system;
SQL> connect system/admin
SQL> -- Then recreate the trigger.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 26297784
Beware, doing logon auditing this way is dangerous. Consider using the builtin audit features for this. If your trigger is messed up, you cannot login to Oracle.

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

Accepted Solution

by:
Mark Geerlings earned 1200 total points
ID: 26304405
Yes, the problem could be security on V$SESSION, and mrjoltcole gave you the solution to that.  And yes, using your own logon/logoff triggers could create problems.  But, if you write and test them carefully, and if you give yourself an alternate way to log in that would allow you to disable or correct the problem, if any, someday, you should be OK.

I really hate to see four separate update statements though for the same row!  That adds a*BIG* performance penalty that you don't need.  One statement could do the job like this:

update
payroll.system_user_log
set
(last_module, logoff_day, logoff_time, elapsed_minutes) =
(select module, trunc(sysdate), to_char(sysdate, 'hh24:mi:ss'),  (sysdate - logon_day)*1440
  from v$session where      
  sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;

And, it may be even faster if you create a local "date" variable in your trigger, set that to sysdate, then refer to that variable instead of making the multiple calls to sysdate.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 400 total points
ID: 26311121
the update given by markgeer should be used instead of that many selects in the trigger. but i think he missed to include two more columns which are present in the trigger code. so the complete update statement would be...

update  payroll.system_user_log
set  (last_action, last_program, last_module, logoff_day, logoff_time, elapsed_minutes) =
(select action, program , module, sysdate, to_char(sysdate, 'hh24:mi:ss'),  (sysdate - logon_day)*1440
from v$session
where  sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;

--> added the missed coulumns last_action and last_program as well to the above update.
--> also removed trunc for the sysdate for the column logoff_day as that was not present in your
      individual statements.

Thanks,
0
 

Author Comment

by:Roxanne25
ID: 26314976
What would be the "giving yourself an alternate way to log on" method?
0
 

Author Comment

by:Roxanne25
ID: 26315063
for the permission granting... I assume instead of granting select to "system" I would need to grant it to the user that I'm trying to create the trigger with.  Or does the grant statement specifically need to be for "system"?
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 1200 total points
ID: 26315126
You are correct.  Select permission on V_$SESSION must be granted by SYS to the user who owns the procedure.

How to give yourself "an alternate way to log on"?  I think if you log in as SYS, you have that, since i think logon/logff triggers are not fired by a SYS login.  The other option would be to add an exclusion in your logon/logoff triggers for yourself or for some other privileged login that you set up for this purpose, so in case there is ever a problem with the logn trigger, you have a way to get in to disable it.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 26315178
Do you understand the point I made about not using multiple update statements, when a single update statement can do the job (*MUCH* more efficiently) for multiple columns?  That can make a huge difference in the size and efficiency of your custom programs.
0
 

Author Comment

by:Roxanne25
ID: 26315252
Hmm, it is logging SYS because in the logon one I'm filtering it out.  I could just put an if statement on the code though right to say don't do the code if the user is SYS?
0
 

Author Comment

by:Roxanne25
ID: 26315262
Oh yes, I do... i will use your refined code for the update!  I hadn't really thought about that since I grabbed that code off the article on the web. :)
0
 

Author Comment

by:Roxanne25
ID: 26315659
Ok, great thank you for everyone's help... I got it to work and I put in an IF statement to allow me to log on as SYS if something goes wrong.  I don't think its capturing for the user that owns the trigger though... because I never see entries in the table for that user.  So, I'm assuming I'd still be able to log on as that user as well.

The only thing I'm slightly annoyed about is it's not populating the last_action field... :(  
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 26315715
Look at the "action" column in v$session for some active sessions.  If your application does not populate that automatically, then your trigger will not invent a value for this column when there is nothing there.
0
 

Author Comment

by:Roxanne25
ID: 26315739
Well yes, I know it won't magically appear...but I was hoping it was capturing that. :)
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 26316431
If you control the source code of your application, you can simply add a call to: dbms_application_info.set_action
to set a value in the "action" column of v$session.
0
 

Author Comment

by:Roxanne25
ID: 26316627
hmmmm....thank you.  I might look into that.  :)  I'm just starting to get into Oracle again ...i've been a sql server person for many years!
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 26316795
In my opinion, there are a *LOT* more differences than similarities between SQL Server and Oracle!  If you know SQL Server pretty well, do *NOT* assume that Oracle works the same way, because in most cases it doesn't.  I'm not saying that either one is better than the other, but they are very different in their underlying approaches to many things, including especially: record locking and read consistency; how dates are handled; how null values are handled; whether temporary tables are needed or not; how disk space is managed in the database; whether stored procedures return result sets or not; whether table, column and procedure names are case-sensitive or not, etc.
0
 

Author Comment

by:Roxanne25
ID: 26316850
Yup Yup... I am in total agreement. :)  Which is why I'm stumbling a bit trying to figure some of this stuff out.  Even the differences from PL/SQL to T-SQL are quite numerous.  I also seem to have way more places to look for stuff than I did in SQL.  I used to do Oracle stuff like 6 years or so ago but never from a DBA standpoint... so I'm kind of getting a crash course in Oracle DBA's for dummies! :)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 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