Re: Role problem: Why doesn't this work?

Everything looks ok when I query the data dictionary to see if the roles and privileges are there, but when I query the table using the synonym it resolves the synonym the owner.table but then says it doesn't exist. I think I'll try recreating it from scratch testing each step like you did to see if I can find out what's not working.

Doug wrote:

> This should work fine...Here's an actual session from personal oracle
> 7... comments of mine preceeded with "---"
>
> SQL> connect demo/demo;
> Connected.
> SQL> select * from user_tab_privs where table_name = 'LOCATION';
>
> no rows selected
>
> --- Establishes that no one has privileges on the LOCATION table...
>
> SQL> connect system/system
> Connected.
> SQL> create public synonym testsynonym for demo.location;
>
> Synonym created.
>
> -- Creates the public synonym as you said
>
> SQL> connect scott/tiger;
> Connected.
> SQL> select * from testsynonym;
> select * from testsynonym
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
> -- Demonstrates that user scott can still not see the table...
>
> SQL> connect system/system;
> Connected.
> SQL> create role testrole;
>
> Role created.
>
> SQL> connect demo/demo
> Connected.
> SQL> grant select on location to testrole;
>
> Grant succeeded.
>
> --- does a grant to the role like you specified...
> SQL> connect scott/tiger;
> Connected.
> SQL> select * from testsynonym;
> select * from testsynonym
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
> --- demonstrates that the user scott can STILL not see the table ..
> --- until we...
>
> SQL> connect system/system
> Connected.
> SQL> grant testrole to public;
>
> Grant succeeded.
>
> SQL> connect scott/tiger
> Connected.
> SQL> select * from testsynonym;
>
> LOCATION_ID REGIONAL_GROUP
> ----------- --------------------
> 122 NEW YORK
> 124 DALLAS
> 123 CHICAGO
> 167 BOSTON
>
> SQL>
>
> ---
> On Fri, 08 Dec 2000 21:31:15 -0600, Douglas Scott <dsscott_at_ev1.net>
> wrote:
>
> >I created some public synonyms to some tables in a particular schema. I
> >created a role and granted table privileges (select, insert, update &
> >delete) on those tables to the role. I then granted the role to
> >PUBLIC. When I log into SQL*Plus as a different user from the schema
> >with the tables I cannot execute any SQLs against any of the tables.
> >Can anyone tell me why this doesn't work?
> >
> >Douglas Scott
>
> "If everything feels like it's under control, you're probably not
> going fast enough" - Mario Andretti
  Received on Sun Dec 10 2000 - 09:51:17 CST