Home -> Mailing Lists -> Oracle-L -> Re: have roles changed in 11g?

Re: have roles changed in 11g?

From: Govindan K <gkatteri_at_gawab.com>
Date: Fri, 22 Jan 2010 06:43:19 GMT
Message-ID: <20100122064319.23460.qmail_at_info9.gawab.com> 






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_at_gmail.com> 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_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
  



    





Send big files for free. Simple steps. No registration.
Visit now http://www.nawelny.com


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


Received on Fri Jan 22 2010 - 00:43:19 CST












Original text of this message