Re: have roles changed in 11g?

From: Dba DBA <>
Date: Thu, 21 Jan 2010 09:40:15 -0500
Message-ID: <>

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)
> 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:
>> 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.
>> 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.
>> 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

Received on Thu Jan 21 2010 - 08:40:15 CST

Original text of this message