Coskan’s Approach to Oracle

December 6, 2007

Altering Without Privileges

Filed under: Basics — coskan @ 4:54 pm

There was a mail in Turkish Oracle Users group mailing list about “why a users can still alter a table despite all privileges has been revoked (only connect privilege is left). My answer was to double check if there is any privilege was left by selecting DBA_TAB_PRIVS for that user, but the best answer send by Yasin Baskan (Oracle Today). He claimed and proved that, if the objects are created/owned by that user he can still alter the objects without any privilege. Below is how he proved his claim and a little addition from me (privileges on different schema test).

First create a user with connect and dba privileges.

usercreate

Create tables with that user under his own schema;

create table

Create tables with that user under HR schema;

create table on hr

Revoke DBA privilage from user; (the costst user is must disconnect after revoke to connect with new privs)

revoke

Test ddl on table ;

ddl

He is still altering but can he create new ?

new

What about the table he created on HR schema, can he alter it ?He couldn’t as I expected because he doesn’t have access to that schema.

hrddl

What privileges user has ? (Checked by find_all_privs.sql script written by Pete Finnegan ). CONNECT and CREATE SESSION

privs

What if other one creates a table on coststs schema. He still has access  as you might guess

otheruser

The privileges comes from schema owning and this is the expected behaviour. You can not disable this option, so  you must think different ways  such as creating different user or moving tables under another schema, to prevent a user from  accessing tablesunder its schem.

Special thanks goes to Yasin  for this entry.

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers