?
Solved

Creating a Logon trigger

Posted on 2009-02-10
13
Medium Priority
?
598 Views
Last Modified: 2013-12-18
Hi,

I'm trying to implement a login trigger (see below). The code works except for the last part of the create trigger statement. I need to insert the profile for the user into the user_log table and I can only get this from the sys.dba_users table .. not sys_context. Unfotunately, when I add in the update sql cmd at the bottom the trigger has compilation problems. Any advice?

btw.  If I use the commented out update statement it works .. so it's just the way I've written the statement that's the problem.

create table
   audit_log
(
   user_name           varchar2(30),
   session_id           number(8),
   host              varchar2(30),
   logon_day                 date,
   logon_time        varchar2(10),
   elapsed_minutes       number(8),
   profile           varchar2(20)
)
;


create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into audit_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null
);
 --update audit_log set profile='DEFAULT';
update stats$user_log set profile=(select profile FROM DBA_USERS WHERE USERNAME = USER) where USERNAME = USER;
END;
/
0
Comment
Question by:MikeHunt
  • 7
  • 3
  • 3
13 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 23601159
What compilation errors do you get?  Use SHOW ERRORS in sqlplus to display them.
0
 
LVL 1

Author Comment

by:MikeHunt
ID: 23601180
Actually ...

changing the last line to

 update stats$user_log set profile=(select profile FROM DBA_USERS WHERE USERNAME = USER);

seems to eliminate the compilation errors. Let me check the results to make sure that it's working.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23601216
what is the username column in stats$user_log.

perhaps it should be USER_NAME instead of USERNAME.  which would be why the where clause would fail, assuming that's the problem.

If you post the error it will help a lot



also, why  aren't you populating the profile column of your audit_log?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:MikeHunt
ID: 23601280
Actually it should have been ...

update stats$user_log set profile=(select profile FROM DBA_USERS WHERE USERNAME = USER) where user_name=USER;

.. now it works.
0
 
LVL 1

Author Comment

by:MikeHunt
ID: 23601306
HI sdstuber: .. I was using the update statement to update that col. If you can see another way of updating it as part of the first statement let me know .. that would be better.

Thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23601349
ok, I thought it was probably a column name misspelling.

you have two different tables.  audit_log and stats$user_log
Do you need to update the profile in both of them?

if so,  you can put the same subquery in the insert too.
insert into audit_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   (select profile FROM DBA_USERS WHERE USERNAME = USER)
);

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23601366
or, select the profile into a variable and use it in both the insert to audit_log and the update of stats$user_log.

0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 200 total points
ID: 23601370
You could try:

insert into audit_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   (select profile FROM DBA_USERS WHERE USERNAME = USER)
);

Can't remember if this is valid syntax in an insert statement.  If not, use a select statement to create the row instead:

insert into audit_log    
select  user,
           sys_context('USERENV','SESSIONID'),
          sys_context('USERENV','HOST'),
          sysdate,
          to_char(sysdate, 'hh24:mi:ss'),
          null,
         profile
FROM DBA_USERS WHERE USERNAME = USER;

0
 
LVL 1

Author Comment

by:MikeHunt
ID: 23601390
that looks good. Let me test it. thanks.
0
 
LVL 1

Author Closing Comment

by:MikeHunt
ID: 31545056
Thanks. That works and it's a cleaner solution.

Cheers,
Mike.
0
 
LVL 1

Author Comment

by:MikeHunt
ID: 23601873
I just saw that sdstuber submitted what I think is the same code for that solution. Sorry mate .. I only saw the last post when I checked back. Don't think I can do anything about that now.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 23602032
sdstuber gets enough points so I'm sure he won't mind losing a few to me ;-)

Out of interest, which solution did you go for; the straight insert statement or the insert select statement.

Is Mike Hunt your real name or are you a Porkies fan?
0
 
LVL 1

Author Comment

by:MikeHunt
ID: 23602298
I used the straight insert:

insert into audit_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   (select profile FROM DBA_USERS WHERE USERNAME = USER)
);

as part of my create trigger statement and it worked. And yes I did like Porkys when I first saw it. Luckily for me my real name isn't Mike Hunt or I would have had a very difficult childhood :-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

749 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