Re: How to auto-enable a password protected role

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 20 Apr 2010 05:49:26 -0700 (PDT)
Message-ID: <9c512a74-d6e2-4759-81ce-d1caa36af3df_at_j12g2000vbl.googlegroups.com>

On Apr 20, 4:20 am, Lothar Armbrüster
<l.armbrues..._at_vertriebsunion.de> wrote:
> Hello out there,
> I'm currently testing the migration of our application from Oracle
> 10.2.0.4 to 11.2.0.1.
> All tables of the application belong to one schema. Access to these
> tables is granted to a password protected role which is enabled at
> application start using the command
>
> set role <role> identified by <pwd>
>
> Now there are some users used for batch processes and a few developers
> that have this role set as default.
> In Oracle 10 the default role is enabled despite of its password. In
> Oracle 11 it is not.
>
> I tried setting the role for these users in a logon trigger but get
> ORA-06565 saying set role cannot be invoked from within a stored
> procedure. Secure application roles are not an option for me since I'm
> on Standard Edition One.
>
> Now the question is, is there a way to get a password protected role
> enabled automatically for some users?
>
> Many thanks in advance,
> Lothar

Lothar, I was thinking there was a way to do what you wanted but when I checked the Oracle Security manual I found the following, "You cannot authenticate a password-authenticated role on logon, even if you add it to the list of default roles. You must explicitly enable it with the SET ROLE statement using the required password."

You might consider creating a new non-password authenicated role with the same privileges and granting this new role as a default role to the target usernames as a workaround. I do not have an 11g system anymore that I can test with but perhaps you can grant the existing role to the new role to keep maintaining the roles simple.

HTH -- Mark D Powell -- Received on Tue Apr 20 2010 - 07:49:26 CDT