Re: EXECUTE ANY PROCEDURE

From: Jeff Kish <jeff.kish_at_mro.com>
Date: Mon, 14 May 2007 14:19:40 -0400
Message-ID: <979h439k13f11j02hm3tmpnol3t507spqr@4ax.com>

<snip>
>
>David Fitzjarrell
sorry. obviously my message was sloppy, and I'm assuming the problem is being caused by my own sloppiness.

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 Received on Mon May 14 2007 - 13:19:40 CDT