Oracle: Creating view across schemas?

Question

I'm trying to create a view, and have distilled the problem down to the inability to create a view that references tables from a different schema.

For example, I can do:

select count(*) from otherschema.othertable;

and I can do:

create view foo as select count(*) as bar from oneofmytables;

But if I try:

create view foo as select count(*) as bar from otherschema.othertable;

I get an "insufficient privileges" error. What additional privileges do I need?

Answer 1

Do you have the grant to the other user's table directly? Or is it via a role? You will need the privilege to be granted directly in order to create an object (view, procedure, etc.) that references the other table. As a quick test, in SQL*Plus

SQL> set role none;
SQL> select count(*) from otherschema.othertable;

If that fails, then the problem is that you have been granted privileges on the table via a role.

Answer 2

I guess you have been given select right on otherschema.othertable via a role not via a direct grant.

If this is the case, you should connect as otheruser and then do a grant select on othertable to <your-schema>.