Re: have roles changed in 11g?
Date: Thu, 21 Jan 2010 09:53:14 -0500
Message-ID: <f30139791001210653w22f473dev54ab3f56839431a_at_mail.gmail.com>
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_at_gmail.com>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_at_10046.ch
>
> 10046 Consulting GmbH
> Schwarzackerstrasse 29
> CH-8304 Wallisellen
> Switzerland
>
> Phone +41-(0)8400-10046
> Cell +41 (0) 79 571 36 27
> info_at_10046.ch
> http://www.10046.ch
>
> =========================
>
>
> 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) 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
>>>>
>>>>
>>>
>>>
>>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 21 2010 - 08:53:14 CST