Behavior Change: READ privilege for user SYSTEM in Oracle 12.2

All credits here go to Marcel Pils from Logicalis, a German Oracle partner. Thanks Marcel! In Oracle 12.2 there’s an interesting behavior change: READ privilege for user SYSTEM in Oracle 12.2.

Some Background Information

In Oracle 12.1 the READ privilege has been introduced. Please find more information in the Oracle 12.1 Security Guide: New READ Object Privilege and READ ANY TABLE System Privilege for SELECT Operations. The idea behind the READ object and the READ ANY TABLE system privileges is that you can enable users query database tables, views, materialized views, and synonyms. But they can’t lock rows of the table. The privileges to LOCK TABLE table_name IN EXCLUSIVE MODE; and SELECT ... FROM table_name FOR UPDATE; are not included in the READ privilege whereas you get these two privileges when one has granted you the SELECT object privilege instead.

Please find more information here: READ and SELECT object privileges for a comparison of both.

Simple example:

grant read any table to HUGO;

doesn’t allow HUGO to LOCK a table EXCLUSIVEly or issue a SELECT ...FOR UPDATE.

Behavior Change for user SYSTEM in Oracle 12.2

In Oracle 12.1 and Oracle 12.2 the user SYSTEM has the same privileges out-of-the-box. If you query SESSION_PRIVS it lists the privileges that are currently available to the user.

SQL> connect system/oracle

SQL> select * from session_privs where privilege like 'GRANT%';

PRIVILEGE
----------------------------------------
GRANT ANY ROLE
GRANT ANY PRIVILEGE
GRANT ANY OBJECT PRIVILEGE

The privilege GRANT ANY OBJECT PRIVILEGE allows SYSTEM to grant access on every object to everybody – in theory.

Let me compare Oracle 12.1 now with Oracle 12.2 with a simple example:

-- Oracle 12.1.0.2

sqlplus / as sysdba

SQL> grant connect, resource, dba to HUGO identified by HUGO;

SQL> connect system/oracle

SQL> grant SELECT on all_synonyms to hugo with grant option;
Grant succeeded.

Repeating the same test with Oracle 12.2. differs a bit:

-- Oracle 12.2.0.1

sqlplus / as sysdba

SQL> grant connect, resource, dba to HUGO identified by HUGO;

SQL> connect system/oracle

SQL> grant SELECT on all_synonyms to hugo with grant option;
grant SELECT on all_synonyms to hugo with grant option
                *
ERROR at line 1:
ORA-01031: insufficient privileges

Let’s dig a bit deeper and compare:

-- Oracle 12.1.0.2

SQL> select grantee, privilege, grantable from dba_tab_privs where table_name='ALL_SYNONYMS'

GRANTEE      PRIVILEGE	     GRA
------------ --------------- ---
PUBLIC	     SELECT	     YES
HUGO	     SELECT	     YES

And now the same query in Oracle 12.2:

-- Oracle 12.2.0.1

SQL> select grantee, privilege, grantable from dba_tab_privs where table_name='ALL_SYNONYMS';

GRANTEE      PRIVILEGE	     GRA
------------ --------------- ---
PUBLIC	     READ	     YES

So there’s a change in Oracle 12.2.0.1. I couldn’t find a hint in the documentation explaining it.

Workaround

I don’t see switching O7_DICTIONARY_ACCESSIBILITY=TRUE as a valid and useful workaround as it will turn off dictionary object protection. But the correct way to grant the privilege would be:

SQL> grant READ on ALL_SYNONYMS to HUGO with GRANT OPTION;
Grant succeeded.

SQL> select grantee, privilege, grantable from dba_tab_privs where table_name='ALL_SYNONYMS';

GRANTEE      PRIVILEGE	     GRA
------------ --------------- ---
PUBLIC	     READ	     YES
HUGO	     READ	     YES

This will work in Oracle 12.1. as well.

Just don’t forget to revoke the SELECT privilege in case you add the READ privilege afterwards.

SQL> select grantee, privilege, grantable from dba_tab_privs where table_name='ALL_SYNONYMS';

GRANTEE      PRIVILEGE	     GRA
------------ --------------- ---
PUBLIC	     SELECT	     YES
HUGO	     SELECT	     YES
HUGO	     READ	     YES

Summary and Further Information

In Oracle 12.2 a change got introduced allowing to grant READ to any other user but not SELECT anymore. This change was done to prevent users from inadvertently locking objects with SELECT FOR UPDATE clauses.

Marcel has blogged about this in English as well on his own blog with a much more detailed example:
http://marcelpils.de/grant-any-object-privilege-gaop-seams-to-be-not-working-in-12-2/

On a related note, please see my blog post from 2015:
Behavior Change: SELECT AND DICTIONARY with reduced privilege set in Oracle 11.2.0.4 and 12.1.0.2

–Mike