Oracle: Creating a view across schemas using admin account

Question

I want to create a view in SCHEMA_A using columns from a table LICENSE in SCHEMA_B.

create view SCHEMA_B.V_TEST as
SELECT  LICENSE_NUMBER  FROM     SCHEMA_A.LICENSE L

then

select * from  SCHEMA_B.V_TEST

This is giving me "ORA-01031: insufficient privileges" when I try to select from the view as the admin account. (Edit: admin account has SELECT ANY TABLE privileges.) What am I missing?

There is a similar question but the solution has to do with one user granting select to another. Since I'm admin I should have select privilege on both schemas already? However I have tried adding the line "GRANT SELECT ON SCHEMA_A.LICENSE TO admin_account WITH GRANT OPTION" and get the same error.

What does "look at the view as the admin account" mean? Are you saying that you get an error when you create the view in schema_a? Are you saying that some third account does not have privileges to query the view that you have created in schema_a? Or are you saying something else? What is the statement that causes the error to be raised? — Justin Cave
Justin Cave, sorry yeah that was unclear. Edited to clarify. — user3234309
You're getting the ORA-01031 selecting from, not creating the view, right? That implies that schema_b does have select privileges on schema_a.license, which is necessary. An error from the select suggests your 'admin account' does not have select any table privileges, so you'd need to look at what privileges you do have and need. Why do you think you have select privileges on both schemas already? You might have explicit permissions for existing objects, but not automatically be able to see something new. (It isn't obvious that you need to be able to query the view from that account?). — Alex Poole
Yes, I get it selecting from the view. I've added "select any table" privileges and continue getting the error. What else could be missing? — user3234309
@user3234309 - who did you give that privilege to, the schema_b admin user, or the admin user? Does the admin user have its privileges granted directly or through roles? Can you also confirm whether you can select from the view when actually connected as schema_b? — Alex Poole

Michal Pravda Michal Pravda · Accepted Answer · 2014-06-16T14:59:24

I think that the creation of the view fails, because schema_b doesn't have select privilege to schema_a.licence. Since you're creating an object in the schema_b, schema_b must have the necessary privilege.

Grant it:

as schema_a (or an DBA - "admin account")

grant select on schema_a.licence to schema_b

then you should be able to create the view and then select from it.

Oracle: Creating a view across schemas using admin account

1 Answers