Can SELECT privilege lock table?

Recent discussion on one forum remind me to share a part of knowledge, which in core, seems that anyone understand well, but I think that many would find them self confused.
Question is: If a user has been granted select privilege on table, is there any way to lock table in any way? You might be surprised, but answer is: Yes. And this is normal behavior on any Oracle database version which have select ... for update syntax.

The problem

Let me show brief example of this problem. I'll create two users a_owner with classic connect, resource roles:

SQL> grant connect, resource to a_owner identified by oracle123;

Grant succeeded.

and user a_locker who will have only create session privilege, privilege that allow only to create session (connect to oracle instance).

SQL> grant create session to a_locker identified by oracle123;

Grant succeeded.

Lets user a_owner create table t1 and put few records in it:

SQL> conn a_owner/oracle123@xe
Connected.
SQL> create table t1 (col1 varchar2(10), col2 varchar2(10));

Table created.

SQL> insert into t1 values ('a','1');

1 row created.

SQL> insert into t1 values ('b','2');

1 row created.

SQL> commit;

Commit complete.

Let user a_owner grant select privilege to a_locker user:

SQL> grant select on a_owner.t1 to a_locker;

Grant succeeded.

Check data from a_locker side of view:

SQL> conn a_locker/oracle123@xe;
Connected.
SQL> select * from a_owner.t1;

COL1       COL2
---------- ----------
a          1
b          2

SQL> 

So far so good. But let us try to lock table t1 from a_locker schema (notice for update part in select statement. This SQL passes without error:

SQL> select * from a_owner.t1 for update;

COL1       COL2
---------- ----------
a          1
b          2

SQL> 

Now let's check if table is really locked (from a_owner side):

SQL> show user;
USER is "A_OWNER"

SQL> update t1 set col1='c' where col1='a';

but SQL hang. Reason is obvious because lock is placed on table t1 from a_locker command!
For checking table lock I'll use mine scripts, run from third session, explained in previous blog postings:

• Blocking session ... show table records that are blocked
• Blocking session ... show table records that are blocked (Part II)

SQL> @sb;
Oracle version: 11.2.0.2.0 (11.1.0.0.0)      
        
Blocker          Inst    SID  Serial     [sec]        Lock Type/Req. lock       Status                   Module
----------------------------------------------------------------------------------------------------------------
1.A_LOCKER          1    220      21        41                Transaction     INACTIVE                 SQL*Plus
  A_OWNER             1    189      17        11                  Exclusive       ACTIVE                 SQL*Plus

SQL> @sbr
        
Oracle version: 11.2.0.2.0 (11.1.0.0.0)
        
A_LOCKER (1 '220,21')
  A_OWNER (1 189,17)     147 sec       Exclusive          ACTIVE        SQL*Plus
        SELECT COL1 , COL2 FROM A_OWNER.T1 WHERE rowid = 'AAAY2QAAGAAAAFjAAA' ;
        (update t1 set col1=:"SYS_B_0" where col1=:"SYS_B_1")

PL/SQL procedure successfully completed.

SQL> SELECT COL1 , COL2 FROM A_OWNER.T1 WHERE rowid = 'AAAY2QAAGAAAAFjAAA' ;

COL1       COL2
---------- ----------
a          1

1 row selected.

SQL> 

So indeed a_locker has placed transactional table lock on one record.
When a_locker execute rollback, then in a_owner session update was successful. Notice Elapsed column, which shows time to perform update statement (10 minutes 18 seconds is block wait):

SQL> update t1 set col1='c' where col1='a';

1 row updated.

Elapsed: 00:10:18.00
SQL> 

Solution

So after confirmation that select privilege can lock table, is there anything to be done to prevent such a situation?
The only answer that I know so far is yes-create a view with dummy column.
To be able to execute that, first add "create view" privilege to a_owner:

SQL> grant create view to a_owner;

Grant succeeded.

Create view t1_vw in a a little specific way:

SQL> CREATE OR REPLACE VIEW a_owner.t1_vw AS 
  2    SELECT * 
  3      FROM (SELECT a_owner.t1.*,
  4                   count(1) over (partition by 1) xxx FROM a_owner.t1
  5            )
  6  ;

View created.

SQL> 

As you'll see later columny xxx will prevent any DML on t1. To be consistent, I have to revoke previously granted select privilege on table t1 and grant select on newly create view t1_vw:

SQL> revoke select on a_owner.t1 from a_locker;

Revoke succeeded.

SQL> grant select on a_owner.t1_vw to a_locker;

Grant succeeded.

SQL> 

Now with user a_locker, locking is no longer possible:

SQL> select col1, col2 from a_owner.t1_vw for update;
select col1, col2 from a_owner.t1_vw for update
                               *
ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

SQL> 

As you see ORA-02014 is raised!

The End

This prove another old advice to expose data through views only and avoid direct grant through real oracle tables.

Cheers!