Drop Table Access for Table Owner

[fatcodeguy]

Hi,

I have a user (with CONNECT and RESOURCE roles) that creates a set of tables. If I revoke CONNECT and RESOURCE, and grant instead only CREATE SESSION and ALTER SESSION, the user can still drop a table he created (but not create new ones).

Is there a way to avoid this?

Thanks!

 

[SantaMufasa]

Fat,

What, specifically, is the "this" that you want to avoid? What do you want the user to be able to do and what do you want the user to not be able to do?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[fatcodeguy]

Well, here's the entire scenario.

I have an application that creates & populates a database upon installation (via a user APP_USER with RESOURCE and CONNECT roles). However, after the installation, APP_USER does not require create table/drop table rights anymore (only select/delete/update/insert), and we want to ensure that any users who do discover, for whatever reason, the APP_USER password doesn't connect to Oracle and create or drop tables.

So, after installation, i've revoked RESOURCE and CONNECT roles from APP_USER and granted CREATE SESSION and ALTER SESSION and select/update/insert/delete privileges on his tables (not sure if i even need to do this). After this, i tried dropping a table and it worked.

So: Is there a way to prevent a user who created (and i suppose owns) a table from being able to drop it?

Does this make sense?

Thanks again!!

 

[sem]

You may disable APP_USER account and then grant all privileges you want to another user. You may also write a database trigger to prevent from dropping APP_USER's objects.

Regards, Dima

http://seminihin.narod.ru

 

[fatcodeguy]

Hi

Unfortunately something in the application prevents us from granting access to another user and user that user instead, so i'll have to use a trigger (but i think i'll use a SCHEMA trigger instead).

Thanks!!

 

[sem]

I suppose that that "something" may be overridden by creating synonyms

Regards, Dima

http://seminihin.narod.ru