Re: Application user name in Audit triggers

The 'global variable' idea is on the right track, but it has to be a value available to the audit triggers

you need to create a package with a private persistent (package-level) variable that holds the custom login credentials

it should have a procedure that sets the credentials (tied in with your custom authentication, so that it cannot be spoofed) and a function that returns the login name or id -- call the function in the audit triggers; if it returns null, raise an exception (or raise an exception in the function if the credentials are not set)

here's an example that we use to implement version control of database objects (used in conjunction with schema triggers):

package body versioning
is

    n_contactid contacts.contactid%type;

    procedure init (

        usr in contacts.useraccount%type
        ,pwd in contacts.password%type default null
        )
    is
        begin
        if usr is null
        then
            -- clear context
            n_contactid := null;
        else
            -- verify account
            select contactid
            into n_contactid
            from contacts
            where useraccount = usr
            and password = pwd;
        end if;
        exception
        when no_data_found
            then raise_application_error(-20000,'Invalid username and
password -- Version Control context cleared');
        end init;

        procedure show
        is
        begin
            dbms_output.put_line('Current Version Control Context: ' ||
n_contactid );
        end show;

        function get return contacts.contactid%type
        is
        begin
            return n_contactid;
        end get;

end versioning;

-- 
----------------------------------------
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048

"Anna C. Dent" <anacdent_at_hotmail.com> wrote in message
news:zcjmb.84037$Ms2.23817_at_fed1read03...


> Steve Bell wrote:

> > Good day all,

> >

> > I think this question has been asked before, so please accept my apology

in


> > advance.  I've been unsuccessful searching

> > for previous posts.

> >

> > We have an application (Oracle 8.1.7, Forms 9i, OAS9i) where each user

will


> > have their own login id. This login will be

> > validated by the Application Server.  When they connect to the database,

all


> > users will connect under the same Oracle user.

> >

> > Using update triggers, we populate an audit field called UPDATED_BY when

an


> > existing record is updated.  For development,

> > I just used USER to populate the field.

> >

> > Is their a way that I can get the username that they used originally to

log


> > into the application?  I could then store

> > that in a variable in the update triggers so we could record the exact

> > application user id that performed the update?

> >

> > Any thoughts appreciated.

> >

> > Steve

>


> It all depends.

>


> Might something like the following get you pointed towards a solution.

> I "assume" that there is some sort of initial "login" forms/servlet;

> where the actual username & password are entered before being validated.

> Why can't you just save the username in some global variable and then

> use it in the update triggers?

>


> HTH & YMMV

>