We help IT Professionals succeed at work.

Creating a Logon trigger

MikeHunt
MikeHunt asked
on
Medium Priority
617 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;
/
Comment
Watch Question

What compilation errors do you get?  Use SHOW ERRORS in sqlplus to display them.

Author

Commented:
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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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?

Author

Commented:
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.

Author

Commented:
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.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

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

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;

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
that looks good. Let me test it. thanks.

Author

Commented:
Thanks. That works and it's a cleaner solution.

Cheers,
Mike.

Author

Commented:
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.
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?

Author

Commented:
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 :-)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.