Re: ORACLE & Roles

From: Michael T. Boduch <mboduch_at_interaccess.com>
Date: 1995/11/24
Message-ID: <mboduch.32.008CE120_at_interaccess.com>#1/1


In article <4957qb$6id_at_post.gsfc.nasa.gov> 
Michael_Shapiro_at_ccmail.gsfc.nasa.gov (Michael Shapiro) writes:>From: 
Michael_Shapiro_at_ccmail.gsfc.nasa.gov (Michael Shapiro)>Subject: ORACLE & Roles

>Date: 24 Nov 1995 19:52:43 GMT
 

>Hello world!
 

>We use Roles to allocate privileges to the users.
>At startup our application uses "set role" to allocate
>role/roles to the application user.

Actually (and I'm not trying to split hairs here), since you can only *enable* a role that has been previously granted to the user, no actual "allocation" takes place when you execute the SET ROLE SQL statement (i.e. a GRANT statement allocates the role).

>Is there any way we can disable this role/roles upon
>application termination?

By "application termination," I assume that you mean that you exit the client-side application and break your connection with the database. If that's the case, the previously enabled role is automatically disabled (which is to say that it will not *still* be enabled when you next connect to the instance). The only exception to this would be if said role was made a "default role" with a statement such as:

alter user set default role role_name;

of course, in that case, you wouldn't have needed to issue the "set role" statement on application startup.

However, you may be looking for a way to disable the role on exit from one portion of your application (while maintaining the same database connection.) If that's the case, you might consider issuing an "set role none;" statement which will disable all currently-enabled roles. This solution assumes that you can intercept user requests to exit the application (easily done in Oracle Forms, I don't know about PowerBuilder, etc.)

>The only privilege we assigned to the application role is
>"execute SP"
>DROP ROLE won't work in our case.

Yes, I wouldn't do that (unless the application has only one user...:)

>The application was developed using PowerBuilder or ...
>TIA
Hopefully some of that helps. Best of luck, Michael.

Mike Received on Fri Nov 24 1995 - 00:00:00 CET

Original text of this message