IMP_FULL_DATABASE role/privilege Can Be Dangerous At Times

Recently faced an issue where an Application developer as per one request asked to revert dml permissions from the table; they just wanted "select only privilege"(Only for read purpose.).

Did the same; removed the dml privileges from the public role as well as from the schema which was using that table via synonym; but still they complained that they are able to update the table from other schema.

Checked all privileges and roles but it was select only; anyhow still they were able to update the table.

Table name was TEMP

Select Privilege from dba_tab_privs where table_name='TEMP';

PRIVILEGE

----------------------------------------

SELECT

So why to go by theory; going ahead tested one scenario

SQL> create user part1 identified by part1;

User created.

SQL> create user part identified by part ;

User created.

SQL> grant connect, resource to part1, part;

Created table TEMP in part schema and created the synonym in schema part1.

SQL> conn / as sysdba

Connected.

SQL> grant CREATE SYNONYM to part1;

Grant succeeded.

SQL> conn part1/part1

Connected.

SQL> create or replace synonym temp for part.temp;

SQL> conn part/part

Connected.

SQL> grant select on temp to part1;

SQL> conn part1/part1

SQL> update temp

set code = 'ABCD'

where emp_no = '1020';  

update temp

       *

ERROR at line 1:

ORA-01031: insufficient privileges

And above is usual behavior and expected one; that with select privilege one cannot fire dml; so curiosity takes me to other side that I checked all the roles provided to the original schema(which tester tested) and they were fine too except ”IMP_FULL_DATABASE” ;  during troubleshooting I found out that IMP_FULL_ DATABASE contains( INSERT ANY TABLE/ UPDATE ANY TABLE) kind of privileges.

So not going by the theory

Logged in as sys and granted synonym user part1 with IMP_FULL_DATABASE

SQL> conn / as sysdba

Connected.

SQL> grant imp_full_database to part1;

Grant succeeded.

SQL> conn part1/part1

Connected.

SQL> update temp

set code = 'ABCD'

where emp_no = '1020';

1 row updated.

SQL> roll;

Rollback complete.

So it is clear here that having imp_full_database one can easily update the table.

Let’s revert it again

QL> conn / as sysdba

Connected.

SQL> revoke imp_full_database from part1;

Revoke succeeded.

SQL> conn part1/part1

Connected.

SQL> update temp

set code = 'ABCD'

where emp_no = '1020';

update temp

       *

ERROR at line 1:

ORA-01031: insufficient privileges

So please make sure that you have to be very much aware while providing these kinds of privileges/roles to the user.