Let me step back as I'm trying to replicate a reported problem and am having
other issues getting in the way now.. btw thanks for your previous reply(ies).

overall I'm trying to recreate a problem.
basically a user is trying to access a table using the dot notation and I'm
getting:
ORA-00903: invalid table name

if I log in as the owner/creator of the schema and tables I can do:
select * from table ok
but logging in as another user (joe) I can't execute select * from
myuser.table;

Can someone tell me what I have left out that will allow using dot notation to
access the tables?

ugly details

I created a user myuser.
I granted myuser these rights:
these roles
.. CONNECT
.. RESOURCE
.. SELECT_CATALOG_ROLE
these system privileges
.. CREATE ANY INDEX
.. CREATE PROCEDURE
.. CREATE SEQUENCE
.. CREATE SESSION
.. CREATE TABLE
.. CREATE TRIGGER
.. EXECUTE ANY PROCEDURE
.. UNLIMITED TABLESPACE

I logged in as myuser and
-created a bunch of tables.
-created a few packages
-put data into the tables

I created a user "JOE"
I logged in as system and ran this to give JOE basic rights:
GRANT "CONNECT" TO "JOE";
GRANT "RESOURCE" TO "JOE";
GRANT "SELECT_CATALOG_ROLE" TO "JOE";
ALTER USER "JOE" DEFAULT ROLE ALL;

GRANT CREATE ANY INDEX TO "JOE";
GRANT CREATE ANY PROCEDURE TO "JOE";
GRANT CREATE ANY SEQUENCE TO "JOE";
GRANT DROP ANY SEQUENCE TO "JOE";
GRANT ALTER ANY SEQUENCE TO "JOE";
GRANT CREATE SESSION TO "JOE";
GRANT CREATE TABLE TO "JOE";
GRANT CREATE TRIGGER TO "JOE";
GRANT CREATE VIEW TO "JOE";
GRANT CREATE SEQUENCE TO "JOE";
GRANT EXECUTE ANY PROCEDURE TO "JOE";
GRANT UNLIMITED TABLESPACE TO "JOE";

I logged in as myuser and ran this to give JOE access to the schema data:

begin
for tab in (select view_name from all_views where owner = 'MYUSER') loop
execute immediate 'grant select,insert,update, delete on  ' || tab.view_name
|| '  to JOE';
commit;
end loop;

for tab in (select table_name from all_tables where owner = 'MYUSER') loop
execute immediate 'grant select,insert,update, delete on  ' || tab.table_name
|| '  to JOE';
commit;
end loop;

for tab in (select sequence_name from all_sequences where sequence_owner =
'MYUSER') loop
execute immediate 'grant select on  ' || tab.sequence_name || '  to JOE';
commit;
end loop;

end;
/

Now when I log in and try things like this:
select * from myuser.table

I get this error message:

ORA-00903: invalid table name

But if I log in as myuser I can do this:

select * from table;

Jeff Kish

-----------------------------------------------Reply-----------------------------------------------