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

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 Fri Dec 08 2000 - 22:17:56 CST