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 EXCLUSIVE
ly 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
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