Re: ORACLE & Roles

Michael_Shapiro_at_ccmail.gsfc.nasa.gov (Michael Shapiro) wrote:
> We use Roles to allocate privileges to the users. At startup our
> application uses "set role" to allocate role/roles to the application
> user. Is there any way we can disable this role/roles upon
> application termination? The only privilege we assigned to the
> application role is "execute SP". DROP ROLE won't work in our case.

If the termination of your application also terminates the current Oracle session, you do not need to take any action. The SET ROLE command enables the designated roles for the current Oracle session. SET ROLE does not permanently grant roles to a user, it enables previously granted roles for the duration of a session or until another SET ROLE is executed.

To understand roles the following commands should be understood:

   CREATE ROLE - creates a new role which can then be granted

                 to users or other roles.
   DROP ROLE -   permanently drops a role from the database so it may
                 no longer be used by any user.
   GRANT ROLE -  grants the use of a role to a user or to another role.
                 Roles granted as "default" roles to a given user, are
                 automatically "enabled" when the user starts an Oracle
                 session.  Roles which are not granted as default roles,
                 must be enabled using SET ROLE.
   SET ROLE -    Enables one or more roles for the current Oracle 
                 session.  SET ROLE disables any roles that are not
                 explicitly set.  SET ROLE is not cumulative.  

Roles must be CREATED before they can be granted to any user or other role. Once a role is CREATED, it may then be GRANTED to a user or role. The SET ROLE command can only be used to ENABLE roles that have already been granted to a user. The command SET ROLE NONE can be used to disable all roles for the current session. I hope this answers your question.

• Andy Zitelli