FAQ
Hello all,

I am tasked with auditing who accesses the database with what program (equivalent of v$session.program) upon database login. I can get everything I want into the audit trail by using "audit connect", except the equivalent of v$session.program.

(a) Does anyone know of a supported way to push the program information into sys.aud$, and if not,

(b) Does anyone know of a way to accomplish this other than a login trigger? Any sample code available?

Thanks in advance for any hints you can offer.

Mary Elizabeth McNeely

Search Discussions

  • Allen, Brandon at Mar 11, 2008 at 9:05 pm
    I thought for sure this was captured by "audit session", but I looked it
    up and found that I was wrong. I checked both the dba_audit_session
    view and the sys.aud$ table and v$session.program is not captured.
    Sounds like a good enhancement request to me, but in the meantime I
    think you'll have to use a logon trigger. Here is some code to get you
    started (Beware - I haven't tested this, use at your own risk):

    create table user_log
    (

    user_id varchar2(15),
    osuser varchar2(30),
    session_id number(8),
    last_program varchar2(48)

    );

    CREATE OR REPLACE TRIGGER "LOGON_AUDIT_TRIGGER" AFTER

    LOGON ON DATABASE

    DECLARE

    sess number(10);
    s_sid number(10);
    prog varchar2(48);
    s_osuser varchar2(30);
    BEGIN

    sess := sys_context('USERENV','SESSIONID');
    SELECT program,sid,osuser INTO prog,s_sid,s_osuser FROM v$session
    WHERE audsid = sess and rownum<=1;

    INSERT INTO user_log VALUES
    (sys_context('USERENV','session_user'),s_osuser,sys_context('USERENV','S
    ESSIONID'), prog);
    END;

    Regards,
    Brandon


    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Jared Still at Mar 11, 2008 at 11:14 pm
    This information might be good for troubleshooting, or determining how
    legitimate
    users access the database.

    Keep in mind that the value in v$session.program is easily spoofed by any
    user
    with nefarious purposes simply by renaming the executable.

    eg. C:> move toad.exe sqlplus.exe

    Something that anyone with ill intent is likely to know about.

    On Tue, Mar 11, 2008 at 12:41 PM, Mary Elizabeth McNeely <
    mary_mcneely_at_yahoo.com> wrote:
    Hello all,

    I am tasked with auditing who accesses the database with what program
    (equivalent of v$session.program) upon database login. I can get everything
    I want into the audit trail by using "audit connect", except the equivalent
    of v$session.program.

    (a) Does anyone know of a supported way to push the program information
    into sys.aud$, and if not,

    (b) Does anyone know of a way to accomplish this other than a login
    trigger? Any sample code available?

    Thanks in advance for any hints you can offer.

    Mary Elizabeth McNeely
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Yechiel Adar at Mar 16, 2008 at 12:56 pm
    If you use login trigger to insert the values into your table,
    do not forget to use: pragma autonomous transaction.

    Adar Yechiel
    Rechovot, Israel

    Mary Elizabeth McNeely wrote:
    Hello all,

    I am tasked with auditing who accesses the database with what program (equivalent of v$session.program) upon database login. I can get everything I want into the audit trail by using "audit connect", except the equivalent of v$session.program.

    (a) Does anyone know of a supported way to push the program information into sys.aud$, and if not,

    (b) Does anyone know of a way to accomplish this other than a login trigger? Any sample code available?

    Thanks in advance for any hints you can offer.

    Mary Elizabeth McNeely
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Mary Elizabeth McNeely at Mar 16, 2008 at 11:09 pm
    Thanks for all the responses. It looks like I'll have to use a trigger after all ...

    Adar - yes, duly noted about autonomous transaction in a trigger

    Jared - sad but true, the program can be spoofed - risk is understood and accepted

    Brandon - thanks for putting together an initial cut at an answer, wow! I too was surprised calling program wasn't included in the audit stream; in fact, I was so sure it was included, I didn't even bother checking before enabling auditing (oops).

    Mary Elizabeth

    Original Message ----
    From: Yechiel Adar
    Cc: oracle-l_at_freelists.org
    Sent: Sunday, March 16, 2008 7:56:50 AM
    Subject: Re: Audit for program at login time

    If you use login trigger to insert the values into your table,
    do not forget to use: pragma autonomous transaction.

    Adar Yechiel
    Rechovot, Israel

    Mary Elizabeth McNeely wrote:
    Hello all,

    I am tasked with auditing who accesses the database with what program (equivalent of v$session.program) upon database login. I can get everything I want into the audit trail by using "audit connect", except the equivalent of v$session.program.

    (a) Does anyone know of a supported way to push the program information into sys.aud$, and if not,

    (b) Does anyone know of a way to accomplish this other than a login trigger? Any sample code available?

    Thanks in advance for any hints you can offer.

    Mary Elizabeth McNeely
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 11, '08 at 7:41p
activeMar 16, '08 at 11:09p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase