CODEWITHSUNDEEP
oracle-database
smackenzie
smackenzie

Reputation: 3022

Unable to create Oracle View accessing another schemas' objects, despite grants

I have 2 schemas:

ARIEL ARIEL_APEX

All the tables in ARIEL are accessible to ARIEL_APEX and the queries run OK from the ARIEL_APEX schema.

For example, 

SELECT * FROM ARIEL.DIM_REGISTRATION_SET

works fine from the ARIEL_APEX schema.

When I try to create a view in ARIEL_APEX:

CREATE VIEW TEST_VIEW AS
SELECT * FROM ARIEL.DIM_REGISTRATION_SET

I get this:

Error at Command Line : 465 Column : 23
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

This works absolutely fine in the test and production environment, this is development. DBA saying all is well at their end.

ARIEL_APEX having below privileges.

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
ARIEL_APEX                     CREATE JOB
ARIEL_APEX                     CREATE MATERIALIZED VIEW
ARIEL_APEX                     CREATE TABLE
ARIEL_APEX                     CREATE OPERATOR
ARIEL_APEX                     CREATE VIEW
ARIEL_APEX                     CREATE TYPE
ARIEL_APEX                     CREATE SYNONYM
ARIEL_APEX                     CREATE CLUSTER
ARIEL_APEX                     CREATE DIMENSION
ARIEL_APEX                     CREATE TRIGGER
ARIEL_APEX                     CREATE SESSION
ARIEL_APEX                     CREATE INDEXTYPE
ARIEL_APEX                     CREATE PROCEDURE
ARIEL_APEX                     CREATE SEQUENCE

And we know the grants are OK on the ARIEL objects to ARIEL_APEX as we can execute the query manually.

This is Oracle 12. Never had the issue before we upgraded, but suspect this is related to accessing objects from another schema within a view.

ARIEL_APEX is a member of the ANALYTICS_ROLE, the ANALYTICS_ROLE grants select on all tables in the ARIEL schema, which can be seen to working below.

The grant is provided to the ANALYTICS_ROLE which grants SELECT access on all ARIEL tables.

The grants work, as can be seen by the direct execution of a query from the ARIEL_APEX schema of an ARIEL table. Error when trying to create a view around the working query.

Upvotes: 1

Views: 2915

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Works in upper environments, only difference is grants provided by a role...in other environments SELECT grants provided directly on objects.

As noted in the documentation (emphasis added):

The owner of the schema containing the view must have the privileges necessary to either select (READ or SELECT privilege), insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

If you only have the select privileged on the underlying table granted through a a role then you cannot create a view against it. Even if you move to role generally, you'll have to keep explicit grant on top for any views you want to create.

I imagine this is to do with how roles work. With a direct grant Oracle knows whether you can see the table in the other schema. If you grant select on your view to someone else then when they query the view Oracle knows that the chain of privileges is there. If your direct grant on the table is revoked then there are mechanisms to invalidate dependent objects. But what should happen the role's select privilege on the table is revoked; or your access to the role is revoked; or just within your own session, what should happen if you disable that role - can you still access the view? It's a bit more complicated that it seems at first glance.

Fortunately creating views should be relatively rare and controllable. Most people accessing the table via the role won't need to create a view on it (I assume!).

Another option here is create the view in the ARIEL schema, and then grant privileges to APEX_ARIEL and/or a role. Whether that is appropriate depends on your real view query and your motivation for creating the view.

Upvotes: 2

Related Questions