How to auto-enable a password protected role
in [Oracle]
|
From: Lothar Armbrüster on 20 Apr 2010 04:20 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
From: Mark D Powell on 20 Apr 2010 08:49 On Apr 20, 4:20 am, Lothar Armbrüster <l.armbrues...(a)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 --
From: Michel Cadot on 20 Apr 2010 11:50 "Mark D Powell" <Mark.Powell2(a)hp.com> a �crit dans le message de news: 9c512a74-d6e2-4759-81ce-d1caa36af3df(a)j12g2000vbl.googlegroups.com... On Apr 20, 4:20 am, Lothar Armbr�ster <l.armbrues...(a)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 -- ---------------------------------------- In addition, logically speaking, what is the purpose of the password on a role if the role is automatically activated on logon? Regards Michel
From: Mark D Powell on 20 Apr 2010 16:25 On Apr 20, 11:50 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "Mark D Powell" <Mark.Powe...(a)hp.com> a écrit dans le message de news: > 9c512a74-d6e2-4759-81ce-d1caa36af...(a)j12g2000vbl.googlegroups.com... > On Apr 20, 4:20 am, Lothar Armbrüster > > > > > > <l.armbrues...(a)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 -- > > ---------------------------------------- > > 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 --
From: Michel Cadot on 21 Apr 2010 11:23
"Mark D Powell" <Mark.Powell2(a)hp.com> a �crit dans le message de news: 80ee7c72-495c-4ce2-a558-7822f193a74c(a)r10g2000vbb.googlegroups.com... On Apr 20, 11:50 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "Mark D Powell" <Mark.Powe...(a)hp.com> a �crit dans le message de news: > 9c512a74-d6e2-4759-81ce-d1caa36af...(a)j12g2000vbl.googlegroups.com... > On Apr 20, 4:20 am, Lothar Armbr�ster > > > > > > <l.armbrues...(a)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 -- > > ---------------------------------------- > > 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 |