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
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
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
OPTION, then the database invalidates both the view and its containing table or view.
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.
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.
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;
SQL> create view my_view as select * from t;
SQL> create user user1 identified by secret;
SQL> grant create session to user1;
SQL> grant select on my_view to user1;
SQL> connect user1/secret
SQL> select * from ops$oracle.my_view;
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.
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.