have roles changed in 11g?

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Wed, 20 Jan 2010 15:02:43 -0500
Message-ID: <f30139791001201202g643ebba2kb90304f451d978b9_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 20 2010 - 14:02:43 CST