DomainKey-Signature: a=rsa-sha1; c=nofws;        d=gmail.com; s=gamma;        h=mime-version:in-reply-to:references:from:date:message-id:subject:to         :cc:content-type;        b=TXPXBW6RlKGZHGP501APGYyEHLVxF2gt8crz6GMZ7YqW//lhfPki3ZMyaot27qGx8i         4hPAsdFMtzIMTj75f5bODClm+ASliXSMWIbgxcB8lEXyz9YW49MVBr88jiE1EWp5I8dy         P2Nh9Ytq1Aq0kQx0N48CIL4KLYK37fhIPMk8s=
MIME-Version: 1.0
In-Reply-To: <f30139791001210640x64f77b39l95d66d38be1c0cc2@mail.gmail.com>
References: <f30139791001201202g643ebba2kb90304f451d978b9@mail.gmail.com> 	<4B579AD3.3010901@gawab.com> <f30139791001210640x64f77b39l95d66d38be1c0cc2@mail.gmail.com>
From: Stefan Knecht <knecht.stefan@gmail.com>
Date: Thu, 21 Jan 2010 15:44:28 +0100
Message-ID: <486b2b611001210644m7339fc2ay99c318e3dea1b84@mail.gmail.com>
Subject: Re: have roles changed in 11g?
To: oracledbaquestions@gmail.com
Cc: oracle-l-freelists <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary=0016e6d7e363bea934047dadbe40
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: knecht.stefan@gmail.com
--0016e6d7e363bea934047dadbe40
Content-Type: text/plain; charset=ISO-8859-1

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@10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info@10046.ch
http://www.10046.ch

=========================


On Thu, Jan 21, 2010 at 3:40 PM, Dba DBA <oracledbaquestions@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@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
>>>
>>>
>>
>>
>>
>

--0016e6d7e363bea934047dadbe40
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

check the column default_role in dba_role_privs for the user / role in ques=
tion. <br><br>Guessing it&#39;s a default role in 10g but not in 11g (did y=
ou re-create the user when testing ? )<br><br>Stefan<br><br><br clear=3D"al=
l">

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
<br><br>Stefan P Knecht<br>CEO &amp; Founder<br><a href=3D"mailto:s@10046.c=
h">s@10046.ch</a><br><br>10046 Consulting GmbH<br>Schwarzackerstrasse 29<br=
>CH-8304 Wallisellen<br>Switzerland<br><br>Phone +41-(0)8400-10046<br>

Cell +41 (0) 79 571 36 27<br><a href=3D"mailto:info@10046.ch">info@10046.ch=
</a><br><a href=3D"http://www.10046.ch">http://www.10046.ch</a><br><br>=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br=
>
<br><br><div class=3D"gmail_quote">On Thu, Jan 21, 2010 at 3:40 PM, Dba DBA=
 <span dir=3D"ltr">&lt;<a href=3D"mailto:oracledbaquestions@gmail.com">orac=
ledbaquestions@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmai=
l_quote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0p=
t 0pt 0.8ex; padding-left: 1ex;">

<div>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. </div>
<div>=A0</div>
<div>do I have to do something different in 11g to make the role &quot;acti=
ve&quot; in some way? I don&#39;t have to do anything in 10.1 ? I=A0 just g=
ranted the role. </div>
<div>=A0</div>
<div>in the past if i need a role I would just</div>
<div>=A0</div>
<div>create role</div>
<div>grant stuff to role</div>
<div>grant role to user</div>
<div>=A0</div>
<div>and then done. I have done it that way for 10 years. <br></div>
<div class=3D"gmail_quote">On Wed, Jan 20, 2010 at 7:07 PM, GovindanK <span=
 dir=3D"ltr">&lt;<a href=3D"mailto:gkatteri@gawab.com" target=3D"_blank">gk=
atteri@gawab.com</a>&gt;</span> wrote:<br>
<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0px=
 0px 0px 0.8ex; padding-left: 1ex;" class=3D"gmail_quote">check session_rol=
es , user_tab_privs in (old) 10.1 &amp; (new) 11.1.0.7.1?<br><br>HTH<br><fo=
nt color=3D"#888888">GovindanK</font><div>

<div></div><div class=3D"h5">=20
<div>
<div></div>
<div><br><br>Dba DBA wrote:<br>
<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0px=
 0px 0px 0.8ex; padding-left: 1ex;" class=3D"gmail_quote">I am not talking =
about the change to either the connect or session view (I can&#39;t remembe=
r which one changed).<br>


I don&#39;t see anything about this in the oracle upgrade doc or in the rel=
ease notes for the upgrade process. I also don&#39;t see anything on google=
.<br><br>We are opening a support ticket, but oracle can be slow to respond=
.<br>


New Version: 11.1.0.7.1<br>Old Version: 10.1.0.3<br>=A0We upgraded one of o=
ur databases to 11.1 for the first time. We have other exact copies of this=
 database in 10.1 so we can compare<br>.<br>Here is a test I just did. In 1=
0g this works.<br>


MySCHEMA:<br><br>=A0create or replace procedure t is<br>=A02 =A0begin<br>=
=A03 =A0null;<br>=A04 =A0end;<br>=A05 =A0/<br>=A0create role test;<br>=A0gr=
ant execute on t to test;<br>=A0grant test to newuser;<br>=A0=A0I then log =
in as NEWUSER<br>I do<br>

desc myuser.t<br>
Table or view does not exist<br>=A0When 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 s=
ee the procedure. We are having this problem with existing objects, so we a=
re running tests.<br>


=A01. The object does not show up in all objects for the user<br>2. if i gr=
ant direct access it works.<br>3. we dont have the same issue with tables<b=
r>4. This WORKS in 10.1.0.3.<br>5. there are no issues with synonyms<br>

6. tried creating a public synonym. I can see the synonym in all_objects, b=
ut can&#39;t describe the object.<br>
I don&#39;t see anything in a quick google search on this.<br><br>ADDITIONA=
L TEST<br>We did this in both 10.1 and 11.1.<br>We did the above test. Then=
 went to the user that we granted the role to and queried all_objects.<br>


In 10.1 we can see the procedure<br>in 11.1 we cannot see the procedure lis=
ted in all_objects<br>=A0=A0<br></blockquote><br><br></div></div></div></di=
v></blockquote></div><br>
</blockquote></div><br>

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