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?

Dennis,

I have been thinking/coding for some time on this problem. My worry is uncontrolled update from tools such as MS-Access. All the roles is my database are currently default roles, so a user with an update capability could update data whether they are in Oracle Forms, Access or whatever.

I considered v$session, but don't trust the connection data in there. I'm not certain how Oracle gets the details of the connected program, but I imagine it relies on the program being well-behaved, and an ill-behaved or malicious program could pass a false identifier.

The approach I'm trying is to set all update roles to non-default and password-protected, and add code to the controlled applications to set the appropriate update roles for the connected user.

I've written the pl/sql to change all the database roles, and the pl/sql to set the roles in the application, but have not yet got the latter to work inside a Forms key-startup trigger (and haven't even considered how I'll do it in PowerBuilder).

I'd be happy to exchange ideas and code via email, if you are interested (I don't want to place code on a newsgroup until it is working correctly).

In case I've not set up my signature file correctly, my email address is glau_at_pcug.org.au.

Geoff Lau Received on Thu Jun 22 1995 - 00:00:00 CEST