Hide view definitions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Hide view definitions

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268

    Hide view definitions

    Hi,
    I am facing a bit of a problem:
    I have User A, which owns a table (T1) and a view on the table (V1).
    I have User B, which has select privilege on A.V1 view.
    If user B queries the ALL_VIEWS view, he can see the view definition.
    I don't want user B to see the view text.
    I tried creating a second view (V2) in User A's schema, that performs
    a SELECT from V1, but the user can still see the query text in
    ALL_VIEWS view.

    How can i hide the view text from user B?

    Thanks,
    R.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Create SYNONYM for the view and grant SELECT priviledge on the SYNONYM to user B

    Code:
    SQL> connect myuser/mypass
    Connect durchgeführt.
    SQL> 
    SQL> create view sam as select * from helloworld where name like 'P%';
    
    View created.
    
    SQL> create synonym samv for sam;
    
    Synonym created.
    
    SQL> grant select on samv to scott;
    
    Grant succeeded.
    SQL> connect scott/tiger
    Connect durchgeführt.
    SQL> 
    SQL> 
    SQL> select * from myuser.samv where rownum = 1;
    NAME
    ------------------------------
    Production
    
    SQL> select * from all_views where view_name = 'SAMV';
    
    Es wurden keine Zeilen ausgewählt
    Last edited by Sameer; 11-27-2002 at 04:33 AM.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Sameer, your solution is not realy a sollution. Couple of remarks:

    1.
    Originally posted by Sameer
    Code:
    SQL> create view sam as select * from helloworld where name like 'P%';
    View created.
    
    SQL> create synonym samv for sam;
    Synonym created.
    
    SQL> grant select on samv to scott;
    Grant succeeded.
    You can't realy grant any privilege on a synonym. Synonyms don't have any method or functionalyty that can be granted. What you actualy executed with your "GRANT SELECT ON samv TO scott" is "GRANT SELECT ON myuser.sam TO scott". Sysnonym is not an object that something can be granted on, it is only an object through which something can be granted on another object.

    2.
    Originally posted by Sameer
    Code:
    SQL> select * from myuser.samv where rownum = 1;
    NAME
    ------------------------------
    Production
    Scott can get the same output by not using that synonym, he can query the view SAM directly:
    Code:
    SQL> select * from myuser.sam where rownum = 1;
    
    NAME
    ----------
    Production
    3.
    Originally posted by Sameer
    Code:
    SQL> select * from all_views where view_name = 'SAMV';
    
    Es wurden keine Zeilen ausgewählt
    But this does not mean that you have hidden the code of the view MYUSER.SAM from scott! Check this:
    Code:
    SQL> connect scott/tiger
    Connected.
    SQL> select * from all_views where view_name = 'SAMV';
    
    no rows selected
    
    SQL> select view_name, text from all_views where view_name = 'SAM';
    VIEW_NAME
    ------------------------------
    TEXT
    --------------------------------------------------
    SAM
    select "NAME" from helloworld where name like 'P%'
    Vhat you realy need to do if you want SCOTT to be able to query the view SAM, yet he will not be able to view the code of the view SAM is the following:

    - create another view SAMV that simply selects form SAM
    - grant select on SAMV to scott
    - (optionaly) create a synonym SAM through which scott will reference the view SAMV, though he will essentialy get the results from view SAM.

    Code:
    SQL> connect myuser/myuser
    Connected.
    SQL> select text from user_views where view_name = 'SAM';
    
    TEXT
    -----------------------------------------------------------
    select "NAME" from helloworld where name like 'P%'
    
    SQL> create view samv as select * from sam;
    
    View created.
    
    SQL> select * from sam;
    
    NAME
    ----------
    Production
    
    SQL> grant select on samv to scott;
    
    Grant succeeded.
    
    SQL> create synonym scott.sam for myuser.samv;
    
    Synonym created.
    
    SQL> connect scott/tiger
    Connected.
    SQL> select * from sam;
    
    NAME
    ----------
    Production
    
    SQL> select view_name, text from all_views where view_name like 'SAM%';
    
    VIEW_NAME
    ------------------------------
    TEXT
    ----------------------------------------
    SAMV
    select "NAME" from sam
    So now SCOTT has the access to the contents of view SAM, yet he is not able to see the business logic incorporated in that view....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi Guys,
    Thanks for your notes.
    I have managed to come up with an idea.
    I have create an object type, created a function the returns the object type wrapped it.
    Then created a view based on the wrapped function.
    The user querys the all_views and gets the select from the function, which he does not have permissions on and is wrapped anyway.

    Thanks for you efforts.
    R.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  5. #5
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    sorry about my typos...
    I have created an object type, created a function that returns the object type wrapped it.


    Cheers.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  6. #6
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by jmodic
    Sameer, your solution is not realy a sollution. Couple of remarks:
    "NAME" from sam
    .
    .
    So now SCOTT has the access to the contents of view SAM, yet he is not able to see the business logic incorporated in that view.... [/B]
    yup!.. I agree, Thanks

    Sameer

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