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:
Code:
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