Re: public roles urgent

GG
I think it has to do with the internal recording that Oracle uses. You have granted priv's to a role.
Then granted the role to public.
revoked the role from public.
changed the priv's granted to the role.
granted the role to public.

With the role revoked does it still show up in the user_role_privs ? The changes to the users does not take effect until they log out ond login after you have grante the role to public. The user still maintains the "old" role privs until their user space is updates on login.

Test: as dba create table test1( code number, name char(2)) table created.
insert into test1 values (1,'xx')
commit;
create public synonym test1 for test1;
create role testrole
grant select on test1 to testrole;
grant testrole to public.

as a user login and select from test1;
>1,xx

as a user insert into test1 values (2,'zz') denied insuff privs.
as dba revoke testrole from public
as dba grant insert, delete , update on test1 to testrole; grant testrole to public.
as a user insert into test1 values (2,'zz') denied insuff privs.
as the user logout and login again to update your "user area"

as a user insert into test1 values (2,'zz') 1 row inserted.

Have your users logout and login again to pickup the new permissions granted to the role.
ROR mª¿ªm
as user
>>> GKor_at_rdw.nl 07/23/01 09:20AM >>>

Hi everyone

I have the following situation :
oracle 7.3.4. on windows NT 4.0 sp5

I have a public role with several grants in it.

Then i revoked the role from public today , after that i granted the role to public.

Several users are complaining about the follwing : ORA-00942: table or view does not exist

The user still has the public role (via user_role_privs) but doesn't see the granted tables.
When i drop and create the user everything is ok.

Does anyone has a clue why this happens and how i can eliminate this without dropping the users.

tia

g.g. kor
rdw ict
the netherlands

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: 
  INET: GKor_at_rdw.nl 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).