How to auto-enable a password protected role

From: Lothar Armbrüster <l.armbruester_at_vertriebsunion.de>
Date: Tue, 20 Apr 2010 01:20:42 -0700 (PDT)
Message-ID: <73872654-b0b0-4d4a-8fd3-6577a22b6b1e_at_c27g2000vbb.googlegroups.com>

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 Received on Tue Apr 20 2010 - 03:20:42 CDT