LATEST TOPICS

Oracle 12c (12.1.0.2): Consider extending READ privilege to public dictionary views

While working on my last article, I had the opportunity to explore another real time scenario, where a non privileged user can completely cause application outage without having any privileged access to the database. By default any valid database user has access to the USER_ and ALL_ dictionary views. We don’t need to explicitly grant access on these views to a database user as the access is granted in PUBLIC mode and hence any database user would be having SELECT access on these set of dictionary views.

We have seen in the previous article, how a user can block DML on a table just by having SELECT access and misusing the SELECT..FOR UPDATE command even though it doesn’t have DML privilege on the underlying table.

Considering this vulnerability, I have tried few experiments with a non privileged database user. Here is a test case, where a non privileged user by just having default access can cause a complete outage to application.

Lets create a user with just connect privilege.

----//
----// create a user (test_ro) //----
----//
SQL> create user test_ro identified by test_ro;

User created.

----//
----// grant connect to test_ro user //----
----//
SQL> grant connect to test_ro;

Grant succeeded.

Now, lets login as the TEST_RO user and see what kind of mess it can create in the database.

----//
----// login as test_ro user  //----
----//
SQL> conn test_ro/test_ro@mypdb_01
Connected.
SQL> show user
USER is "TEST_RO"

SQL> show con_name

CON_NAME
------------------------------
MYPDB_01

----//
----// make a note of session ID //----
----//
SQL> SELECT sys_context('USERENV', 'SID') SID  FROM DUAL;

SID
--------------------
102

----//
----// select records from all_users with for update option //-----
----// any user has access to all_ views due to public grants //----
----//
SQL> select * from all_users for update;

USERNAME                USER_ID CREATED   COM O
-------------------- ---------- --------- --- -
XS$NULL              2147483638 13-SEP-15 YES Y
SYSKM                2147483619 13-SEP-15 YES Y
SYSDG                2147483618 13-SEP-15 YES Y
SYSBACKUP            2147483617 13-SEP-15 YES Y
TEST_RO                      71 11-OCT-15 NO  N
C##ADMIN                     64 17-SEP-15 YES N
MYAPP                        63 17-SEP-15 NO  N
PDBADMIN                     62 13-SEP-15 NO  N
GSMCATUSER                   61 13-SEP-15 YES Y
ANONYMOUS                    51 13-SEP-15 YES Y
XDB                          50 13-SEP-15 YES Y
APPQOSSYS                    49 13-SEP-15 YES Y
DBSNMP                       48 13-SEP-15 YES Y
ORACLE_OCM                   36 13-SEP-15 YES Y
DIP                          23 13-SEP-15 YES Y
GSMUSER                      22 13-SEP-15 YES Y
GSMADMIN_INTERNAL            21 13-SEP-15 YES Y
OUTLN                        13 13-SEP-15 YES Y
SYSTEM                        8 13-SEP-15 YES Y
AUDSYS                        7 13-SEP-15 YES Y
SYS                           0 13-SEP-15 YES Y

21 rows selected.

At this point, we have logged in as a database user TEST_RO which has only connect privilege. However, it was able to lock the dictionary view ALL_USERS by means of SELECT..FOR UPDATE command due to public access granted on this view

Lets check the impact of this lock in the database. Lets login to the database from another session.

[oracle@mylab-02 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 11 16:20:07 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: myapp/myapp@mypdb_01
----// 
----// connection is not established and is in hang state //----
----//

What the heck?? Any new connection to the database just hangs.
Lets try if we can login as SYSDBA

[oracle@mylab-02 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 11 16:12:34 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: sys/oracle@mypdb_01 as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> show con_name

CON_NAME
------------------------------
MYPDB_01

SQL> show user
USER is "SYS"

Luckily, we are able to login as SYSDBA ( As we have the option of authentication by OS or password file in case of a SYSDBA user)

----//
----// query the v$session_wait to determine why the new session in hange state //----
----//
SQL> select sid,event,state,seconds_in_wait from v$session_wait where sid in (select sid from v$session where username='MYAPP');

       SID EVENT                                    STATE               SECONDS_IN_WAIT
---------- ---------------------------------------- ------------------- ---------------
       102 SQL*Net message from client              WAITING                         619
       129 enq: TX - row lock contention            WAITING                         132

As per the session_wait report, looks like the new session is waiting to acquire a lock and facing contention there. This is due to the dictionary view ALL_USERS being locked by TEST_RO user.

We can further query the v$locked_object and v$lock views to determine the type of lock obtained by the non privileged user.

SQL> select session_id,v$locked_object.object_id,object_name,locked_mode
  2  from v$locked_object, dba_objects
  3  where v$locked_object.object_id=dba_objects.object_id;

SESSION_ID  OBJECT_ID OBJECT_NAME               LOCKED_MODE
---------- ---------- ------------------------- -----------
       129         22 USER$                               3
       102         22 USER$                               3
       102         16 TS$                                 3

	   

SQL>  select sid,type,id1,id2,lmode,request,block from v$lock where sid in (102,129) order by sid;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       102 AE        133          0          4          0          0
       102 TX     262146       1063          6          0          1
       102 TM         22          0          3          0          0
       102 TM         16          0          3          0          0
       129 TM         22          0          3          0          0
       129 TX     262146       1063          0          6          0
       129 AE        133          0          4          0          0

7 rows selected.

As we can see from this output, the non privileged user (SID:102) is holding exclusive lock on USER$ (OBJECT_ID:22) table (by means of SELECT..FOR UPDATE on ALL_USERS view). Since the base table USER$ is locked in exclusive mode, Oracle is not able to acquire a lock on that and hence not able to authenticate the connecting users.

This type of vulnerability is very dangerous and has a potential to cause a complete outage to the application. I was in the impression that Oracle might have already fixed these type of vulnerabilities with the introduction of new READ object privilege. However, we could still observe the same behaviour in Oracle 12 release 12.1.0.2 (where the new READ privilege was introduced).

The fix to these vulnerabilities is to provide only READ access to the dictionary views which are having public access. I am hoping, Oracle will fix this in the upcoming release and replace the SELECT with READ access for all the dictionary views.

In the meantime, we can probably fix it manually by replacing SELECT with READ privilege for all the public dictionary views with the following set of queries.

----//
----// query to generate SQLs for granting READ on public dictionary views //----
----//
select 'grant READ on '||owner||'.'||table_name||' to '||grantee||';' 
from dba_tab_privs where table_name like 'ALL_%' and grantee='PUBLIC' and privilege='SELECT';

select 'grant READ on '||owner||'.'||table_name||' to '||grantee||';' 
from dba_tab_privs where table_name like 'USER_%' and grantee='PUBLIC' and privilege='SELECT';

select 'grant READ on '||owner||'.'||table_name||' to '||grantee||';' 
from dba_tab_privs where table_name like 'DBA%' and grantee='PUBLIC' and privilege='SELECT';

Once we grant the READ access to public dictionary views by executing SQLs generated from above queries, we can revoke the SELECT privilege from public dictionary views by running SQLs generated from following queries.

----//
----// query to generate SQLs for revoking SELECT from public dictionary views //----
----//
select 'revoke SELECT on '||owner||'.'||table_name||' from '||grantee||';' 
from dba_tab_privs where table_name like 'ALL_%' and grantee='PUBLIC' and privilege='SELECT';

select 'revoke SELECT on '||owner||'.'||table_name||' from '||grantee||';' 
from dba_tab_privs where table_name like 'USER_%' and grantee='PUBLIC' and privilege='SELECT';

select 'revoke SELECT on '||owner||'.'||table_name||' from '||grantee||';' 
from dba_tab_privs where table_name like 'DBA_%' and grantee='PUBLIC' and privilege='SELECT';

Lets now validate if the non privileged user still has the ability to impact database availability.

[oracle@mylab-02 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 11 16:52:06 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: test_ro/test_ro@mypdb_01
Last Successful login time: Sun Oct 11 2015 16:50:02 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> show user
USER is "TEST_RO"
SQL> show con_name

CON_NAME
------------------------------
MYPDB_01

----//
----// try to lock the public dictionary view using SELECT..FOR UPDATE //----
----//
SQL> select * from all_users for update;
select * from all_users for update
              *
ERROR at line 1:
ORA-01031: insufficient privileges


----//
----// select from public dictionary view //----
----//
SQL> select * from all_users;

USERNAME                USER_ID CREATED   COM O
-------------------- ---------- --------- --- -
XS$NULL              2147483638 13-SEP-15 YES Y
SYSKM                2147483619 13-SEP-15 YES Y
SYSDG                2147483618 13-SEP-15 YES Y
SYSBACKUP            2147483617 13-SEP-15 YES Y
TEST_RO                      71 11-OCT-15 NO  N
C##ADMIN                     64 17-SEP-15 YES N
MYAPP                        63 17-SEP-15 NO  N
PDBADMIN                     62 13-SEP-15 NO  N
GSMCATUSER                   61 13-SEP-15 YES Y
ANONYMOUS                    51 13-SEP-15 YES Y
XDB                          50 13-SEP-15 YES Y
APPQOSSYS                    49 13-SEP-15 YES Y
DBSNMP                       48 13-SEP-15 YES Y
ORACLE_OCM                   36 13-SEP-15 YES Y
DIP                          23 13-SEP-15 YES Y
GSMUSER                      22 13-SEP-15 YES Y
GSMADMIN_INTERNAL            21 13-SEP-15 YES Y
OUTLN                        13 13-SEP-15 YES Y
SYSTEM                        8 13-SEP-15 YES Y
AUDSYS                        7 13-SEP-15 YES Y
SYS                           0 13-SEP-15 YES Y

21 rows selected.

As we can see, the non privileged user is no longer able to lock the public dictionary views as we have replaced the SELECT with READ access for all the public dictionary views.

However, a user with DBA/SELECT_CATALOG_ROLE privilege would still be able to lock the dictionary views as even a SYS user can’t revoke the privileges assigned to these predefined internal roles as shown below.

----//
----// trying to revoke select from select_catalog_role //----
----//
SQL> show user
USER is "SYS"
SQL> revoke select on all_users from select_catalog_role;
revoke select on all_users from select_catalog_role
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

This is just one test case, a non privileged Oracle user has the ability to lock number of other dictionary views (tables) and hence has the potential to cause a complete service outage. Fortunately, we have a pure READ privilege available with Oracle 12c (12.1.0.2) which can be used to address this threat.

In my opinion, Oracle should consider implementing this fix (enhancement) in the upcoming release (or patch) to change all the read access to dictionary views from SELECT to READ as it has a more restrictive privilege (READ) now available at its disposal.

Note: In this article, I have modified default access on dictionary views. All though I have tested that this modification doesn’t have any side effect on the database, I would still recommend to consult with Oracle before modifying any default database behaviour.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed