READ Table Privilege

I arrived at work one Monday morning to find an email from our automated job that checks for long transactions in the database:

Transaction for user xxxxxx (sid 678, program SQL Developer) has been open for 44460 seconds

Hmmm… so that would be someone holding a lock in the production system for 12 hours. Thankfully the table where the lock was being held wasn’t frequently subject to DML or this might have caused trouble. I have previously noted how simply having SELECT access allows a user to lock data and that was exactly the scenario that had occurred; a support person had logged into the database, locked a row in a table that they only had SELECT access on without realising what they had done… and then left their session active for a day.

After killing the offending session and sending out a polite email to all support staff requesting that they disconnect from a production database after they have finished whatever needed doing I set about making some changes…

Oracle 12.1.0.2 introduces a new privilege to address this specific problem; READ. According to the documentation READ privilege permits an account to:

Query the table with the SELECT statement. Does not allow SELECT … FOR UPDATE.

Note: This privilege is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Let’s not take Oracle’s word for it; let’s test it using the scenario from the previous post:

CREATE USER data_owner
   IDENTIFIED BY data_owner
   DEFAULT TABLESPACE users
   QUOTA UNLIMITED ON users
/

CREATE USER data_reader
   IDENTIFIED BY data_reader
/

GRANT CREATE SESSION TO data_owner
/
GRANT CREATE TABLE TO data_owner
/
GRANT CREATE SESSION TO data_reader
/

CREATE TABLE data_owner.lock_test
   (id NUMBER(10))
/

INSERT INTO data_owner.lock_test
   VALUES (1)
/

COMMIT
/

Instead of granting SELECT on the DATA_OWNER.LOCK_TEST table to DATA_READER we grant READ:

GRANT READ ON data_owner.lock_test TO data_reader
/

Now to test if we can lock the data in the DATA_OWNER account, we run the following from the DATA_READER account:

DECLARE
   CURSOR cur
   IS
   SELECT *
   FROM   data_owner.lock_test
   FOR UPDATE;
BEGIN
   OPEN cur;
END;
/

The above results in:

SQL>DECLARE
  2     CURSOR cur
  3     IS
  4     SELECT *
  5     FROM   data_owner.lock_test
  6     FOR UPDATE;
  7  BEGIN
  8     OPEN cur;
  9  END;
 10  /
   FROM   data_owner.lock_test
                     *
ERROR at line 5:
ORA-06550: line 5, column 22:
PL/SQL: ORA-01031: insufficient privileges
ORA-06550: line 4, column 4:
PL/SQL: SQL Statement ignored

Exactly what we wanted! Nice one Oracle. READ privilege can be applied to tables, views and materialized views.

For me, some simple changes to the roles assigned to the support users and the unwanted locking problem is resolved.

Unique Constraint Indexes

It’s generally known by developers that when creating unique constraint Oracle will create a unique index to enforce the constraint, as illustrated below:

SQL> CREATE TABLE tab
  2     (col1     NUMBER (10) NOT NULL)
  3  /

Table created.

SQL> ALTER TABLE tab
  2     ADD CONSTRAINT tab_u1
  3     UNIQUE (col1)
  4  /

Table altered.

SQL> SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /


INDEX_NAME           UNIQUENESS
-------------------- --------------------
TAB_U1               UNIQUE

For a unique constraint we can see what index is enforcing the constraint by querying USER_CONSTRAINTS:

SQL>SELECT constraint_name
  2  ,      index_name
  3  FROM   user_constraints
  4  WHERE  constraint_name = 'TAB_U1'
  5  /

CONSTRAINT_NAME      INDEX_NAME
-------------------- --------------------
TAB_U1               TAB_U1

When the index is created as part of adding the constraint then the index will be given the same name as the constraint, as shown in the example above. (As an aside, all these examples have been done on a 12.1.0.2 database but the details will be the same in earlier versions.)

What is less generally known about uniquness constraints (or primary key constraints for that matter) is that the constraint may be enforced by a non-unique index. This is done by creating the index first and then specifying the index to be used when creating the constraint, as shown below:

SQL>CREATE TABLE tab
  2     (col1     NUMBER (10) NOT NULL)
  3  /

Table created.

SQL>CREATE INDEX tab_i1
  2     ON tab (col1)
  3  /

Index created.

SQL>ALTER TABLE tab
  2     ADD CONSTRAINT tab_u1
  3     UNIQUE (col1)
  4     USING INDEX tab_i1
  5  /

Table altered.

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

INDEX_NAME           UNIQUENESS
-------------------- -------------------
TAB_I1               NONUNIQUE

SQL>SELECT constraint_name
  2  ,      index_name
  3  FROM   user_constraints
  4  WHERE  constraint_name = 'TAB_U1'
  5  /

CONSTRAINT_NAME      INDEX_NAME
-------------------- -----------------
TAB_U1               TAB_I1

The index selected to enforce the constraint needs to lead with the same columns as the constraint, although the columns can be specified in any order:

SQL>CREATE TABLE tab
  2     (col1     NUMBER (10) NOT NULL
  3     ,col2     NUMBER (10) NOT NULL
  4     ,col3     NUMBER (10) NOT NULL)
  5  /

Table created.

SQL>
SQL>CREATE INDEX tab_i1
  2     ON tab (col2, col1)
  3  /

Index created.

SQL>
SQL>ALTER TABLE tab
  2     ADD CONSTRAINT tab_u1
  3     UNIQUE (col1, col2)
  4     USING INDEX tab_i1
  5  /

Table altered.

Failure to specify an index that leads with all the columns of the unique constraint results in the error:

ORA-14196: Specified index cannot be used to enforce the constraint.

Knowing the above gives us some opportunity to optimise our applications. We are able to create indexes that support the unique constraint but can also be tweaked to support other processes too. A relatively common scenario involves the need to look up a unique value in a table in order to obtain another attribute from the table. This might be obtaining the description associated with a unqiue ID value or, in a data warehouse scenario, this might be to lookup a dimension surrogate key from the source system unique key. Rather than create two indexes, one to support the unique constraint and another to aid our lookup, we can create just the latter one and use it to enforce the unique constraint too as the following example illustrates:

SQL>CREATE TABLE dim_x
  2     (dim_pk       NUMBER (10) NOT NULL
  3     ,source_pk    NUMBER (10) NOT NULL)
  4  /

Table created.

SQL>CREATE INDEX dim_x_i1
  2     ON dim_x (source_pk, dim_pk)
  3  /

Index created.

SQL>ALTER TABLE dim_x
  2     ADD CONSTRAINT dim_x_u1
  3     UNIQUE (source_pk)
  4     USING INDEX dim_x_i1
  5  /

Table altered.

Rather than needing to perform an index lookup followed by a ROWID access into our table Oracle is able to perform the lookup from the index alone:

SQL>INSERT INTO dim_x
  2  SELECT ROWNUM
  3  ,      ROWNUM+ 99
  4  FROM   dual
  5  CONNECT BY ROWNUM 
SQL>COMMIT
  2  /

Commit complete.

SQL>
SQL>EXEC dbms_stats.gather_table_stats ('','dim_x')

PL/SQL procedure successfully completed.

SQL>EXPLAIN PLAN
  2  FOR
  3  SELECT dim_pk
  4  FROM   dim_x
  5  WHERE  source_pk = :source_pk
  6  /

Explained.

SQL>
SQL>SELECT *
  2  FROM   TABLE(dbms_xplan.display())
  3  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
Plan hash value: 3680186242

------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     8 |     2   (0)|
|*  1 |  INDEX RANGE SCAN| DIM_X_I1 |     1 |     8 |     2   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SOURCE_PK"=TO_NUMBER(:SOURCE_PK))

13 rows selected.

As with many tips, check carefully first as there are trade-offs to consider. The optimiser treats unique and non-unique indexes differently so check that the benefit you’re expecting from the index change is apparent and that hasn’t introduced any side-effects.

Let’s Talk Oracle: Oracle Database 12c for DBAs and Developers – Perth 9 Sept 2015

I spent today attending the “Let’s Talk Oracle: Oracle Database 12c for DBAs and Developers” event in Perth. This purely technical event was presented by three immensely talented and passionate people from Oracle:

• Richard Foote
• Chris Muir
• Connor McDonald

The first half of the day saw Richard Foote covering database 12c features from a DBA perspective. After lunch Chris gave a very informative overview of Oracle REST Data Services followed by Connor with a summary of 12c features for developers. In just one short day I learned about a heap of 12c features and have a rather long list of things to investigate for when I get back to the office.

A very big thank-you to the presenters for their time and effort and also a big thank-you to Dennis Ward from Oracle for arranging the event. Lastly, thanks must go to Oracle Corporation for allowing these people to put on this free event. It was all very, very much appreciated!