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
SELECT from it:
GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;
GRANT is executed during deploy while logged in as
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?