Oracle Database Discussions

11.2.0.2

error:




DB1:

userA.table1
userA.table2
userB.table3
userB.table4

grant select on all userB tables to userA

create userB.test_view as select from (4 tables above with appropriate joins)

select from 4 tables and test_view ok as userA

DB2
create db link connect to remote db using userA;
select from all 4 tables@dblink ok
select from test_view@dblink gives

ORA-01031: insufficient privileges
ORA-02063: preceding line from <db link>

Back to DB1
create view userA.test_view as select * from userB.test_view
ORA-01031: insufficient privileges


So I cant create the view on DB1 on the view in userB's schema even though Ive granted explicit select priveliges on the tables that userB test_view is based on. Im pretty sure this is driving the error thats coming up on db2 but I dont understand it as Im only selecting from there, not creating an object. No doubt some database link restrictions apply.

any ideas?