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 ========================= Stefan P Knecht CEO & Founder s@xxxxxxxx 10046 Consulting GmbH Schwarzackerstrasse 29 CH-8304 Wallisellen Switzerland Phone +41-(0)8400-10046 Cell +41 (0) 79 571 36 27 info@xxxxxxxx http://www.10046.ch ========================= On Thu, Jan 21, 2010 at 3:40 PM, Dba DBA <oracledbaquestions@xxxxxxxxx>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 <gkatteri@xxxxxxxxx> wrote: > >> check session_roles , user_tab_privs in (old) 10.1 & (new) 11.1.0.7.1? >> >> HTH >> GovindanK >> >> >> 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: 11.1.0.7.1 >>> Old Version: 10.1.0.3 >>> 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 10.1.0.3. >>> 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 >>> >>> >> >> >> >