Re: Dynamic Default Roles ?

dbrewer_at_nosc.mil (Dennis Brewer) wrote:

>Fact1: Every user that logs in is assigned a defult role.
>Fact2: Some users wear different hats when logging onto the database. This requires
> different role assignments bases on the current application and session.
>Fact3: Each time a user logs on a session is started with an entry in the v$session table.
 

>Question1: Does it put an entry in the v$session table, before the default role is enabled?
>Question2: Can a trigger be placed on the v$session table?

No - you can't put triggers on system tables. Even if you could, you couldn't put it on v$session because it really isn't a table. Oracle makes it look like a table but it is an in-memory structure that doesn't behave much like a table.

> If the answers to both questions are yes, we plan to write a procedure to dynamically
>assign default roles based on the current users application. Some users may be
>the division head in their own department during the first session/application. The same
>user may later use a different applicaton with very limited priviledges in another division.
>The trigger on the v$session will assign the default role dynamically using a procedure
>which detects which application is in use.
 

> If the answers to the above questions is no. Does anyone out there have another method,
>procedure or sugestions on how this may be accomplished.

I'm assuming that you are planning on checking the PROGRAM column in the v$session table. Some problems: This usually only works if the user is logged onto the host where the database is running. Windows applications tend to show up as "MS-WINDOW" or "USER.EXE". Also, it is not hard for a user to spoof what appears in this column - just rename "my-spoof-program.exe" to "privileged-app.exe", or worse yet: rename "SQLPLUS.EXE to "privileged-app.exe". Oracle makes no claims of providing any kind of security or authentication outside of the database - which is exactly what you are looking for: some secure method of identifying what application the user is running - it's a pipe dream.

Dave Trahan
dtrahan_at_ultranet.com Received on Fri Jun 16 1995 - 00:00:00 CEST