From: Lothar =?utf-8?Q?Armbr=C3=BCster?= on
"Michel Cadot" <micadot{at}altern{dot}org> writes:

> "Mark D Powell" <Mark.Powell2(a)> a écrit dans le message de news:
> 80ee7c72-495c-4ce2-a558-7822f193a74c(a)
> On Apr 20, 11:50 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>> "Mark D Powell" <Mark.Powe...(a)> a écrit dans le message de news:
>> 9c512a74-d6e2-4759-81ce-d1caa36af...(a)
>> On Apr 20, 4:20 am, Lothar Armbrüster
>> <l.armbrues...(a)> wrote:
>> > Hello out there,
>> > I'm currently testing the migration of our application from Oracle
>> > to
>> > 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 --
>> ----------------------------------------
>> In addition, logically speaking, what is the purpose of the password
>> on a role if the role is automatically activated on logon?
>> Regards
>> Michel- Hide quoted text -
>> - Show quoted text -
> But I can understand wanting to share the one role both with usernames
> used in batch and via the application. We build all out rules using
> password even though the roles are default roles. With 11gR2 that is
> going to be an issue. Mostly I think we can drop and re-create most
> of our roles without passwords.
> In fact this may be the best solution. Grant the non-password role to
> selected usernames as a default role and to others as a non-default
> role which the application can still set.
> HTH -- Mark D Powell --
> ------------------------
> I think the best solution is to use Secure Application Role.
> Regards
> Michel
Secure Application Role was one of my first fields of research, but it
requires Enterprise Edition. (Or at least more than SE1)
The idea was to enable the secure application role for selected users in
a logon trigger, but it didn't work on my system.
So the solution I'm using now is creating a separate developer role
which get the same grants as the protected role. I transferred the
grants via PL/SQL. That seems to work.

many thanks for your advices.


Lothar Armbrüster | lothar.armbruester(a)
Hauptstr. 26 |
65346 Eltville |