Using Tables in Views With a DBLink

I have dealt a lot this week with views. We were trying to create a view from a table on a distributed database joined to a table on the local database. The view creation failed and I assumed it was because we were trying to create a view using a dblink. The error message received during the view creation was ORA-01031 insufficient privileges. I was wrong.

Turns out the problem was in the way the owner of the view was given select privileges to the underlying tables. See document 271587.1 on MetaLink - "Cannot create a view on a table granted via a role". So because the owner didn't have select access to the underlying tables explicitly granted to the view creator and owner, they couldn't create the view. Once select access was granted to the user, the view creation succeeded.

So, the view creation worked. However, the users of the view kept getting an error saying table not found. They didn't have select access to the table that was used to create the view on the remote database (across the dblink). We couldn't grant select on the remote table because the user community couldn't legally see all of the data on the remote table. Some users had direct access to the remote database.

To create a view, you must meet the following requirements (From Oracle 9i Documentation):

• To create a view in your schema, you must have the CREATE VIEW privilege. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege. You can acquire these privileges explicitly or through a role.
• 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 is dependent on the privileges of the view's owner. For example, if the owner of the view has only the INSERT privilege for Scott's emp table, the view can only be used to insert new rows into the emp table, not to SELECT, UPDATE, or DELETE rows.
• If the owner of the view intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the GRANT OPTION or the system privileges with the ADMIN OPTION.

I think the problem was the third one. The owner of the view did not have object privs with the GRANT OPTION or ADMIN OPTION. However, I never got to test it.

Looks like this was a combination problem with views that use DBLINKS to get remote data and permissions. So, we decided to just get around it, by placing the remote table on the local database taking the dblink out of the equation. I will let you know if this worked.