creating a role to update a view
Dan AFeb 9 2010 — edited Feb 9 2010Hi.
Oracle 10.2.04. Linux 4.
I have been reading about updating views. The Oracle documentation http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/views001.htm#i1006887 states that:
The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition. The owner cannot have obtained these privileges through roles. Also, the functionality of the view depends on the privileges of the view owner. For example, if the owner of the view has only the INSERT privilege for Scott's emp table, then the view can be used only to insert new rows into the emp table, not to SELECT, UPDATE, or DELETE rows.
This is helpful, but doesn't resolve my issue.
A role has been created to allow access to a view called SALES_RESULTS:
create role update NOT IDENTIFIED;
GRANT INSERT, SELECT, UPDATE ON SALES_RESULTS TO update;
GRANT update to user;
When user tries to update the view however, an error is returned. (Sorry, I dont know the error just yet!!)
In essence, my question is: in order for the updates to work, does the user 'user' need explicitly granted priviliges on the underlying objects, as stated in the Oracle doc extract above - which was discussing the owner of the view?
Thanks.
DA