Re: have roles changed in 11g?
- From: "Govindan K" <gkatteri@xxxxxxxxx>
- To: oracledbaquestions@xxxxxxxxx
- Date: Fri, 22 Jan 2010 06:43:19 GMT
May be you need to use DBMS_SESSION.IS_ROLE_ENABLED and turn it on in 11g
using logon trigger :-)
HTH
GovindanK
Dba DBA <oracledbaquestions@xxxxxxxxx> wrote on 21 Jan 2010, 06:53 AM:
Subject: Re: have roles changed in 11g?
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@xxxxxxxxx>
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@xxxxxxxx
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info@xxxxxxxx
http://www.10046.ch
=========================
On Thu, Jan 21, 2010 at 3:40 PM, Dba DBA <oracledbaquestions@xxxxxxxxx>
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@xxxxxxxxx> 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
-----------------------------------------------------------------------------------------------------------------------
Send big files for free. Simple steps. No registration.
Visit now http://www.nawelny.com
Other related posts:
