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.

About these ads

5 Comments »

  1. Thanks for the credit Coskan.

    Comment by Yas — December 7, 2007 @ 4:11 pm

  2. Just for completeness sake, this wasn’t always the case. Back in day, the CONNECT role used to contain the CREATE TABLE privilege, amongst others (and in the above case, the example to create table T3 would then have worked). I never used CONNECT or RESOURCE for that very reason but rather implemented custom roles/quotas for application schemas and users to explicitly define their privileges independent of whatever would be in the current (or next) Oracle release. It’s nice to see that Oracle (at least in 10g rel. 2) has taken the least permissive/privileged principle to heart.

    Comment by Marvin — April 30, 2008 @ 4:38 pm

  3. It’s a good tutorial Mr Coskan. Hopefully Oracle is always easy to be learned by beginners.

    regards

    tiftazani.wordpress.com

    Comment by tiftazani — July 27, 2008 @ 6:51 pm

  4. This tutorial helped me a lotb .It was very useful , Thanks to Mr Coskan.
    regards vel

    Comment by murugavel — October 17, 2008 @ 6:10 pm

  5. This tutorial helped me a lot .It was very useful , Thanks to Mr Coskan.
    regards vel

    Comment by murugavel — October 17, 2008 @ 6:11 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers

%d bloggers like this: