SELECT ANY TABLE and VIEWS in different schema

[hacketta1]

Hello All

I'm confident that this a stupid question but I'm rather confused as to what's is going on here. If anyone can shed any light on this it would be much appreciated:

system@DEV> 
system@DEV> SET LINES 132
system@DEV> SET PAGES 9999
system@DEV> 
system@DEV> DROP USER proxy CASCADE;

User dropped.

system@DEV> DROP USER tableuser CASCADE;

User dropped.

system@DEV> DROP USER viewuser CASCADE;

User dropped.

system@DEV> 
system@DEV> define the_pw=&1
Enter value for 1: qwerty+123
system@DEV> 
system@DEV> CREATE USER proxy IDENTIFIED BY "&the_pw"
  2  	DEFAULT TABLESPACE USERS
  3  	TEMPORARY TABLESPACE TEMP;
old   1: CREATE USER proxy IDENTIFIED BY "&the_pw"
new   1: CREATE USER proxy IDENTIFIED BY "qwerty+123"

User created.

system@DEV> 
system@DEV> CREATE USER tableuser IDENTIFIED BY "&the_pw"
  2  	DEFAULT TABLESPACE USERS
  3  	TEMPORARY TABLESPACE TEMP
  4  	QUOTA UNLIMITED ON USERS;
old   1: CREATE USER tableuser IDENTIFIED BY "&the_pw"
new   1: CREATE USER tableuser IDENTIFIED BY "qwerty+123"

User created.

system@DEV> 
system@DEV> CREATE USER viewuser IDENTIFIED BY "&the_pw"
  2  	DEFAULT TABLESPACE USERS
  3  	TEMPORARY TABLESPACE TEMP;
old   1: CREATE USER viewuser IDENTIFIED BY "&the_pw"
new   1: CREATE USER viewuser IDENTIFIED BY "qwerty+123"

User created.

system@DEV> 
system@DEV> GRANT CREATE SESSION TO proxy;

Grant succeeded.

system@DEV> GRANT CREATE SESSION TO viewuser;

Grant succeeded.

system@DEV> 
system@DEV> CREATE TABLE tableuser.t (
  2  	id NUMBER
  3  );

Table created.

system@DEV> 
system@DEV> GRANT SELECT ON tableuser.t TO viewuser;

Grant succeeded.

system@DEV> 
system@DEV> CREATE VIEW viewuser.tv AS
  2  	SELECT * FROM tableuser.t;

View created.

system@DEV> 
system@DEV> GRANT SELECT ON viewuser.tv TO proxy;

Grant succeeded.

system@DEV> 
system@DEV> CONNECT viewuser/&the_pw
Connected.
system@DEV> 
system@DEV> SELECT * FROM tv;

no rows selected

system@DEV> 
system@DEV> CONNECT proxy/&the_pw
Connected.
system@DEV> 
system@DEV> SELECT * FROM viewuser.tv;
SELECT * FROM viewuser.tv
                       *
ERROR at line 1:
ORA-01031: insufficient privileges


system@DEV> 
system@DEV> CONNECT system
Connected.
system@DEV> 
system@DEV> GRANT SELECT ANY TABLE TO viewuser;

Grant succeeded.

system@DEV> 
system@DEV> CONNECT proxy/&the_pw
Connected.
system@DEV> 
system@DEV> SELECT * FROM viewuser.tv;

no rows selected

system@DEV> 
system@DEV> SPOOL OFF

Why can user proxy only select from the view when viewuser has SELECT ANY TABLE privileges? How can I allow PROXY to SELECT from the view without granting SELECT ANY TABLE to VIEWUSER; minimum privileges and all that.

I'm sure this is all basic stuff and I'm embarrased to say I don't really understand what's going on here

[adewri]

Connect as system and do

GRANT SELECT ON tableuser.t TO viewuser with grant option;

In order to grant other users (proxy) access to your (viewuser) view, you must have received object privileges to the base objects (tableusers object) with the GRANT OPTION clause or appropriate system privileges with the ADMIN OPTION clause.

This is one of the additiona requirements in Privileges Required to Create Views

HTH

[hacketta1]

Many Thanks Adweri. That worked.

Note to self: do some reading on this topic