Re: have roles changed in 11g?
Date: Thu, 21 Jan 2010 15:44:28 +0100
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 P Knecht
CEO & Founder
10046 Consulting GmbH
Cell +41 (0) 79 571 36 27
On Thu, Jan 21, 2010 at 3:40 PM, Dba DBA <oracledbaquestions_at_gmail.com>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_at_gawab.com> wrote:
>> check session_roles , user_tab_privs in (old) 10.1 & (new) 188.8.131.52.1?
>> 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: 184.108.40.206.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.
>>> 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