Why you should check the default grants to PUBLIC

Today something Oracle Database Security related: Why you should have a look on the default grants to PUBLIC in an Oracle Database.

Just a small showcase:

• One Application Schema APP_USER which owns some tables
• One Application related Role APP_READ to which grant select on APP_USER.<TABLENAME> were given
• One Enduser named EVIL :-) which got create session and hold the APP_READ role
• One Enduser named FRIENDLY :-) which got create session and hold the APP_READ role

-- First lets create the Application Schema APP_USER
create user APP_USER identified by mysecrectpassword;
grant connect, create table to APP_USER;
create role APP_READ;

-- Secondly create the Enduser EVIL
create user EVIL identified by evilpassword;
grant connect, app_read to EVIL;

-- Thirdly create the Enduser FRIENDLY
create user FRIENDLY identified by friendlypassword;
grant connect, app_read to FRIENDLY;

-- Connect with the Application Schema APP_USER
-- Create one table
-- and asign the select right to the Application Role APP_READ
conn app_user/mysecretpassword
create table t1 as select * from all_objects;
grant select on t1 to APP_READ;

Now we have to open 2 sessions to the Database, one connected with the Application User EVIL and one with the Application User FRIENDLY.

In the session with the user EVIL we execute following SQL statement:

conn evil/evilpassword
exec sys.dbms_snapshot.BEGIN_TABLE_REORGANIZATION('APP_USER','T1');

In the second session with the user FRIENDLY we try now to run select on the table APP_USER.T1:

conn friendly/friendlypassword
select * from APP_USER.T1;

The session from the user FRIENDLY is not responding :-( The session from user FRIENDLY will only responding when we are running following SQL Statement in the session of user EVIL:

-- Session of user EVIL
exec sys.dbms_snapshot.END_TABLE_REORGANIZATION('APP_USER','T1');
-- Some errors will come up, as the used table
-- have not a materialized view log
ERROR at line 1:
ORA-23413: table "APP_USER"."T1" does not have a materialized view log
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_SNAP_INTERNAL", line 703
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2811
ORA-06512: at line 1

In the above example are clearly demonstrated two major problems:

• Even with a create session privilege you have too many privileges through the default grants to PUBLIC
• The Procedure dbms_snapshot.BEGIN_TABLE_REORGANIZATION takes every table on which I got a select right, no matter if its just a normal table
  

The above example shows clearly that we have to be careful with the default grants to PUBLIC in an Oracle Database.

Above shown example were executed under Oracle 11.1.0.7.0 and Oracle 11.2.0.1.0.