Re: Application userid security

Here's a thought ...

create or replace trigger logon_security after logon on database declare

    l_program v$session.program%type;

begin

    select program
    into l_program
    from v$session
    where audsid = sys_context('userenv', 'sessionid');

    if user = 'APPUSER' and l_program not like 'superapp%' then

        raise_application_error(-20000, 'get lost');     end if;

end;
/

That still doesn't prevent them from building their own executable with program name 'superapp' and connecting with it. Anybody have any other ideas about how you could get around this?

Note: I tested this in 9i but it should work the same in 8.

Richard

Ed Stevens wrote:
>
> Subject: Application userid security
>
> Platform: Oracle SE 8.0.5 and 8.1.7 on NT
>
> I'm pretty sure others must have addressed this issue (and solved it more
> elegantly than our shop) but a search of the ng archives didn't turn anything
> up.
>
> Requirement is to prevent development staff from being able to connect to
> production databases. Individual users do not have Oracle userids. The
> application connects with its own id.
>
> Current solution: Applications have no knowledge of connect string or
> credentials. All apps call a common routine that returns to the app the userid,
> pswd, and connect string. This prevents the developers from having a *need* to
> know what the connect credentials are, but of course they can still find out if
> they want to by simply stepping through their pgms in debug mode and seeing what
> comes back from the called routine. For their own work "outside" of the apps,
> they are provided with a userid that does not exist in the production DBs.
>
> The mechanics of the system work fine and is being used by over a dozen
> applications written in Powerbuilder, VB, and COBOL. But of course it doesn't
> really provide the security of preventing the developers from knowing how to get
> into a production DB.
>
> Surely (???) others have dealt with this problem. Everything I've looked at
> regarding Oracle security and user authentication still brings me back to a
> solution that allows the developers to be able to easily discover the keys to
> the production DB. Perhaps I've been staring at the solution and not seeing it.
Received on Wed Apr 10 2002 - 18:49:27 CDT