SELECT ANY TABLE and VIEWS in different schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SELECT ANY TABLE and VIEWS in different schema

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367

    SELECT ANY TABLE and VIEWS in different schema

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Connect as system and do

    Code:
    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
    Last edited by adewri; 07-01-2005 at 08:36 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Many Thanks Adweri. That worked.

    Note to self: do some reading on this topic

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width