Error granting SELECT on view that uses ALL_OBJECTS

Question

I'm in the middle of testing Oracle 12c for our application. I have something similar to this view in a particular schema, which we'll call MY_OWNER_SCHEMA for this question:

CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER 
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
    OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
;

The view is a sort of "metadata" view, used to verify (programmatically) that all tables and views have a comment. Since MY_OWNER_SCHEMA is locked except during deployment, we grant permissions to MY_LOGIN_USER to SELECT from it:

GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;

(This GRANT is executed during deploy while logged in as MY_OWNER_SCHEMA.)

This worked just fine in Oracle 11g, and creating it works in 12c as well. However, in 12c only, the GRANT fails with the following message:

SQL Error: ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view or a view was being replaced
           and the grant option was not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view or
           revoke existing grants on the view.

This is fairly odd, since ALL_OBJECTS is a public interface accessible to all users, and it only shows objects to which the user already has some access. Did something change with the permissions model or default permissions settings in Oracle 12c? Is it possible that the database was installed differently? Is there a way to get around this error without giving MY_OWNER_SCHEMA explicit permission to GRANT to this global system view?

Answer 1

Yes, use READ privilege (introduced in 12c) instead of SELECT.

SQL> create user MY_OWNER_SCHEMA identified by MY_OWNER_SCHEMA;

User created.

SQL> create user MY_LOGIN_USER identified by MY_LOGIN_USER;

User created.

SQL> grant create session, create view to MY_OWNER_SCHEMA;

Grant succeeded.

SQL> conn MY_OWNER_SCHEMA/MY_OWNER_SCHEMA
Connected.
SQL> CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
  2    3    4    5    6    7    8    9      OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
; 10   11   12

View created.

SQL> GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;
GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'

Above fails, but:

SQL> GRANT READ ON OBJECT_COMMENTS TO MY_LOGIN_USER;

Grant succeeded.