Hi
Oracle 10.2.0.4
We have a number of base tables <TABLE_NAME> in schema source. I hace created views on certain tables as table_view <TABLE_NAME_VIEW>
DBA has created a new user called view_user and a role called view_user_role.
SELECT permissions on these views are granted to view_user_role like GRANT SELECT ON <TABLE_NAME_VIEW> to view_user_role
If I log in as user view_user I can do SELECT from source.table_view and it works SELECT * from source.<TABLE_NAME_VIEW>
I can also create (as source schema owner) public synonym with the same name as table name to make selection easier
CREATE PUBLIC SYNONYM <TABLE_NAME> FOR <TABLE_NAME>_VIEW
This works and view_user can select from <TABLE_NAME>
However, I like to create private views for this user only. When I try to do (as source schema owner)
CREATE OR REPLACE SYSNONYM view_user.<TABLE_NAME> FOR source.<TABLE_NAME_VIEW>
I get insufficient privileges!
Any idea how I can overcome this problem? What advantage is there to create private views given that a public view will work as no other user has view_user_role
Thanks