Re: have roles changed in 11g?

From: Govindan K <>
Date: Fri, 22 Jan 2010 06:43:19 GMT
Message-ID: <>

May be you need to use DBMS_SESSION.IS_ROLE_ENABLED and turn it on in 11g using logon trigger :-)   

Dba DBA <> wrote on 21 Jan 2010, 06:53 AM: Subject: Re: have roles changed in 11g?
thanks. looks like how they handle default roles changed in 11.1. This only about half solves my problem. I did this test because I had another problem. I have a package that has execute granted to a role, that is granted to this user.   

The user was not dropped and re-created. This works as expected in 11g. Now that I go to session_roles, I see that this role is not a session_role, but in dba_role_privs it is listed as a default role.   

so why would a role be a default role and not be listed in session_roles? MYDB> select *
  2 from dba_role_privs
  3 where grantee = 'MYNEWUSER';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MYNEWUSER          MYOLDROLE                        NO             YES 

But when I log into MYNEWUSER and do   

select * from session_roles it is not listed. It is listed in the 10.1 database. This role was not re-created and neither was the user.   

On Thu, Jan 21, 2010 at 9:44 AM, Stefan Knecht <> wrote:
check the column default_role in dba_role_privs for the user / role in question.
Guessing it's a default role in 10g but not in 11g (did you re-create the user when testing ? )

Stefan P Knecht
CEO & Founder
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27

On Thu, Jan 21, 2010 at 3:40 PM, Dba DBA <> wrote:
thanks. This is interesting. Test role is listed in sessions_roles in 10g, but not in 11g. I did the exact same thing in both databases and both databases are exact copies. Just that one was upgraded.   

do I have to do something different in 11g to make the role "active" in some way? I don't have to do anything in 10.1 ? I just granted the role.   

in the past if i need a role I would just   

create role
grant stuff to role
grant role to user   

and then done. I have done it that way for 10 years. On Wed, Jan 20, 2010 at 7:07 PM, GovindanK <> wrote: check session_roles , user_tab_privs in (old) 10.1 & (new) HTH
Dba DBA wrote:
I am not talking about the change to either the connect or session view (I can't remember which one changed).
I don't see anything about this in the oracle upgrade doc or in the release notes for the upgrade process. I also don't see anything on google. We are opening a support ticket, but oracle can be slow to respond. New Version:
Old Version:
 We upgraded one of our databases to 11.1 for the first time. We have other exact copies of this database in 10.1 so we can compare .
Here is a test I just did. In 10g this works. MySCHEMA:
 create or replace procedure t is
 2 begin
 3 null;
 4 end;
 5 /
 create role test;
 grant execute on t to test;
 grant test to newuser;
  I then log in as NEWUSER
I do
desc myuser.t
Table or view does not exist
 When I do this EXACT test from the same schema to the same schema in an exact copy of this database in 10.1 I can see the procedure. We are having this problem with existing objects, so we are running tests.

  1. The object does not show up in all objects for the user
  2. if i grant direct access it works.
  3. we dont have the same issue with tables
  4. This WORKS in
  5. there are no issues with synonyms
  6. tried creating a public synonym. I can see the synonym in all_objects, but can't describe the object. I don't see anything in a quick google search on this. ADDITIONAL TEST We did this in both 10.1 and 11.1. We did the above test. Then went to the user that we granted the role to and queried all_objects. In 10.1 we can see the procedure in 11.1 we cannot see the procedure listed in all_objects

Send big files for free. Simple steps. No registration. Visit now
Received on Fri Jan 22 2010 - 00:43:19 CST

Original text of this message