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 <knecht.stefan@xxxxxxxxx>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 > > > ========================= > > 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 >>>> >>>> >>> >>> >>> >> >