Oracle 12c New Security Features – 01, Use READ Privilige for Inquiry Users.

I would like to start a new series to introduce Oracle 12c new security features.

I want to start with READ privilege first. This privilege solves many problems at Oracle security. Before Oracle 12c you can give SELECT privilege to a user to SELECT any table. But with this privilege the user not only SELECT any table but also she or he can LOCK the table with SELECT for UPDATE or direct LOCK TABLE statement . This is an unwanted situation for inquiry users; because any inquiry user with  SELECT privilege can lock your table and stop your application. For this reason administrators would create views and then would grant these views to  inquiry users. It was an long operation if you had more than hundred tables and It was difficult to manage all views and tables.

To solve this problem; Oracle introduced a new privilege – READ privilege. With this privilege you can give SELECT grant to a user. But the user can only SELECT the table and he cannot lock it anymore.This enhancement is very important for inquiry users.

I will show you with examples.

Let’s start with Oracle 11g

SQL> connect scott/oracle

Connected.
SQL> grant SELECT on EMP to USER01;

Grant succeeded.

SQL> connect USER01/oracle

Connected.

SQL> select empno,ename from scott.emp;

EMPNO ENAME
———- ———-
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER

14 rows selected.

And now the tricky point

SQL> select * from scott.emp for update of ename;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

……

And now nobody at the database can update this table till USER01 commit the transaction.

Try it. Connect with Scott user at the same time

sqlplus scott/oracle

SQL> update emp set ename=’XXX’;

This statement hangs forever till USER01 commits its transaction. Simply a SELECT statement hangs you your database and your application. This is unwanted sitiuation

You can lock the EMP table with Lock table command  with USERB01 as well.

SQL> Lock table scott.emp in exclusive mode;

Table(s) Locked.

Therefore At Oracle 11g It is very dangerous to give SELECT grant table on any tables to a inquiry user. Instead , you should create views and then you should grant these views to inquiry users.

Let’s look at Oracle 12c version now.

SQL> connect appuser/oracle;
Connected.
SQL> grant READ on test to appuser03;

Grant succeeded.

SQL> connect appuser03/oracle
Connected.
SQL> select * from appuser.test;

X
———-
1
2
3

SQL> lock table appuser.test in exclusive mode;
lock table appuser.test in exclusive mode
*
ERROR at line 1:
ORA-01031: insufficient privileges

As you see; you cannot lock with READ privilege

And then check for Select for Update command;
SQL> select * from appuser.test for update of x;
select * from appuser.test for update of x
*
ERROR at line 1:
ORA-01031: insufficient privileges

Simply; After upgrade your database to Oracle 12c, recheck the privileges of  inquiry users

and use READ privileges for them.

 

Anıl Akduygu

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s