This discussion is archived
12 Replies Latest reply: Jul 31, 2013 7:54 AM by mtefft RSS

Block user access to table

user13045898 Newbie
Currently Being Moderated

Hi,

may be an easy question, but have not managed to find an answer yet.

I have a table MYTABLE and I have a view MYVIEW.

 

MYVIEW is set up as:

 

create or replace force view MYVIEW

as select * from MYTABLE

 

Is it possible to restrict direct access to MYTABLE so that users cannot run a select * from MYTABLE, but they can view the results only if they run select * from MYVIEW?

 

Thanks very mucyh in advance

  • 1. Re: Block user access to table
    Lalit Kumar B Explorer
    Currently Being Moderated

    If you revoke the SELECT object privilege on the containing table or view of a view, whether the privilege was granted with or without the GRANT OPTION, then the database invalidates the view.

    If you revoke the SELECT object privilege on any of the master tables of a view, whether the privilege was granted with or without the GRANT OPTION, then the database invalidates both the view and its containing table or view.

  • 2. Re: Block user access to table
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Sure; give the users privileges on myview, but don't give them privileges on mytable.  The owner of the view (who can be the same as the owner of the table) is the only one who needs privileges on mytable.

  • 3. Re: Block user access to table
    Lalit Kumar B Explorer
    Currently Being Moderated

    It could only be one such case. Rest all cases would end up in invalid objects. I am sure you agree on this.

  • 4. Re: Block user access to table
    user13045898 Newbie
    Currently Being Moderated

    thanks: I have not quite fuilly understand the solution I am afraid.

    I have reveoked the select from MYTABLE, but the view now says "table or view does not exist"...most probably did it wrong....

  • 5. Re: Block user access to table
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Whenever you have a problem, post a complete test script so that the people who want to help you can re-create the problem and test their ideas.  In this case, include CREATE TABLE, CREATE VIEW, GRANT and CONNECT statements.  It's hard for me to see what you're doing wrong if I can't see what you're doing.

     

    The view owner (if different from the table owner) is the only one who needs privileges on the base table.  Those privileges must be granted directly to the owner, not merely to some role that the view owner has, and they should be given WITH GRANT OPTION so that the view owner can give privileges to other users.  If there is only 1 table in the view, then you can probably create the view in the same schema as the table, so privileges for the view owner will not be an issue.

  • 6. Re: Block user access to table
    Brian Bontrager Expert
    Currently Being Moderated

    That sounds like the view and table are in different schemas?

  • 7. Re: Block user access to table
    user13045898 Newbie
    Currently Being Moderated

    Thanks for your answers.

    I apologise if I was not clear enough.

    The owner can see the view and can see that table.

    It is not the owner I want to stop being able to view the content of the table, but another application user (i.e. user1) which only has read privileges on the table: if I remove the read privileges on the table from user1, user1 gets a table or view does not exist when running a query on myview.

     

    The owner has got all the grant options and the owner is not a problem.

  • 8. Re: Block user access to table
    Lalit Kumar B Explorer
    Currently Being Moderated

    Well, that's exactly what me and FrankKulash have explained.

  • 9. Re: Block user access to table
    John Spencer Oracle ACE
    Currently Being Moderated

    Did you actually grant select on myview to user1?  User1 does not need to have any privileges on the base table to be able to select from it.  Unless there is a synonym defined pointing to the view (either a private synonym in user1's schema or a public synonym) user1 will need to qualify the view name with the owner.

     

    What are you doing differently than this?

    SQL> select * from t;

            ID DESCR
    ---------- ----------
             0 One
             0 Two
             0 Three

    SQL> create view my_view as select * from t;

    View created.

    SQL> create user user1 identified by secret;

    User created.

    SQL> grant create session to user1;

    Grant succeeded.

    SQL> grant select on my_view to user1;

    Grant succeeded.

    SQL> connect user1/secret
    Connected.

    SQL> select * from ops$oracle.my_view;

            ID DESCR
    ---------- ----------
             0 One
             0 Two
             0 Three

     

    John

  • 10. Re: Block user access to table
    user13045898 Newbie
    Currently Being Moderated

    Hi,

    thanks all for the replies.

    I have tried the suggestions and it looks like our application uses policies: the no read is apply to the underlined table using policies and by default the same policy applies to the view - I gues that is why I could not see anything in the dba_tabs_priv table and even when I granted select on view and it appeared in dba_tabs_priv, it did not work. Both view and table are in the same schema.

    I guess there is no way around it.

  • 11. Re: Block user access to table
    mtefft Journeyer
    Currently Being Moderated

    Your explanation is not entirely clear...policies (presumably you mean VPD/FGAC policies) can enforce further restrictions on tables or views, but can not make privileges appear out of thin air.

     

    But, realistically, if someone in your shop set up something special with policies, then you are better off discussing your needs with whoever did that. They should know how they intended them to work - we would just be guessing.

  • 12. Re: Block user access to table
    user13045898 Newbie
    Currently Being Moderated

    Thanks all for your time.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points