Re: have roles changed in 11g?

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 20 Jan 2010 13:45:21 -0800
Message-ID: <bf46381001201345u1e80f56dr8e56ddc87f724b63_at_mail.gmail.com>

I just performed the test on 11.1.7.0 using SCOTT, and SCOTT was able to see the procedure upon login.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

On Wed, Jan 20, 2010 at 12:02 PM, Dba DBA <oracledbaquestions_at_gmail.com>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
>
>
>

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

Received on Wed Jan 20 2010 - 15:45:21 CST