Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: using set role command in a logon trigger -- got something implemented - now security question

Re: using set role command in a logon trigger -- got something implemented - now security question

From: laura pena <>
Date: Thu, 12 Apr 2007 08:05:07 -0700 (PDT)
Message-ID: <>

Just to provide feedback here, I actually got this implemented using the 10g feature creating application roles via schema packages. Thanks for the link below Sriram.

The user is configured to execute a schema package created as the system user with AUTHID CURRENT_USER.

The schema package uses sys_context to check the users environment module name to determine which application is running. If it's the "approved" module, permission to update,delete, insert into this schema are granted via dbms_session.set_role to the logged in user.

The application itself needed to modified to call this stored procedure on login (it does more checks as well). All users allowed to user this application configured to use this stored procedure.

Works great. Except now I have a security question?

Can a user spoof the sys_context environment module name ? So say the user wants to have super privs within toad? Can he/she change the module toad.exe to the module name of the application. So they may have super privs to the applications tables now?

Any feedback greatly appreciated.

Thanks again,

 On 4/6/07, Paul Drake <> wrote: On 4/6/07, Sriram Kumar <> wrote:
> Hi Jared,

> My mistake. Thanks for pointing out the same.

> My undestanding of the requirement was to allow/disallow a role if the user
> is connected from a particular node/app and this can be implemented using
> roles authenticated using packages and if we use this approach, triggers are
> NOT required

> have not tried myself by example seems to match Laura's requirement

> Refer Creating Secure Application Roles under

> I dont have access to a DB to test by looks like the approach would work

> Best Regards

> Sriram Kumar


I would be careful about its use.
Recent versions of RDA show rows in dba_feature_usage for this, which may or may not be included as part of standard edition or standard one.
This could cause a licensing issue, but I am not a lawyer nor an Oracle sales rep.


Finding fabulous fares is fun.
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains.
Received on Thu Apr 12 2007 - 10:05:07 CDT

Original text of this message