Behavior Change: READ privilege for user SYSTEM in Oracle 12.2

Behavior Change: READ privilege for user SYSTEM in Oracle 12.2All 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

2 thoughts on “Behavior Change: READ privilege for user SYSTEM in Oracle 12.2

  1. Mike,
    Thanks for documenting this.

    Another case in point. DBCA fails to update the /etc/oratab file in 12.2 when a database is created. There is no documentation of this behavior. So, I opened a SR and here is what support had to say:

    It is expected. 12.2 GI agent does not update oratab for 12.2 database anymore.
    We want customer to use srvctl to get instance name and oracle home information.

    If the instance is shutdown, how would I get its name and its Oracle home via srvctl? There would be no way of knowing whether the database is even configured on the server.

    Thanks,
    Arun

    • Hi Arun,

      I know that there have been changes for 12.2 GI – and this may be interesting for you as well:
      Applying 12.2.0.1.171017 RU (patch 26737266) To 12.2 Cluster Removes Oratab Entries (Doc ID 2329359.1)

      I see your point. And I brought a similar issue to the attention of the GI folks. But they say “there’s a reason why we are doing this”. My hands are tied but you could insist to get a bug filed.

      Cheers,
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.