Altering Without Privileges

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.

Create tables with that user under his own schema;

Create tables with that user under HR schema;

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

Test ddl on table ;

He is still altering but can he create 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.

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

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

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.

About these ads