Jump to content
Sign in to follow this  
richchen65

Prevent Oracle users to drop their own objects

Recommended Posts

Dear all,

 Recently we were asked to find out a way to prevent Oracle db users to drop their own tables.

AFAIK, Oracle users can always  drop their own tables by default.

Possible workaround is to create a trigger to raise error to prevent dropping a table by the user and the code works fine.

CREATE or replace TRIGGER rich.trigger_prevent_drop BEFORE DROP ON rich.SCHEMA

DECLARE

v_username varchar2(30);

BEGIN

SELECT user INTO v_username FROM dual;

IF  ora_dict_obj_type = 'TABLE'

    AND ora_dict_obj_owner = v_username

    AND ora_login_user = v_username

THEN

    raise_application_error (-20000, 'YOU CAN NOT DROP YOUR OWN TABLE!');

END IF;

END;

 

HOWEVER, Orale users can easily drop the trigger.

(PS: The code can not prevent oracle users to drop their triggers.)

CREATE or replace TRIGGER rich.prevent_drop_trigger BEFORE DROP ON rich.SCHEMA

DECLARE

v_username varchar2(30);

BEGIN

SELECT user INTO v_username FROM dual;

IF  ora_dict_obj_type = 'TRIGGER'

    AND ora_dict_obj_owner = v_username

    AND ora_login_user = v_username

THEN

    raise_application_error (-20000, 'YOU CAN NOT DROP YOUR OWN TRIGGER!');

END IF;

END;

 

Is there any way i can prevent a oracle user from dropping triggers even if it is the owner ?

 

Rich

Share this post


Link to post
Share on other sites

Hello Rich,

There are different ways I could think of for your questions,

1) You can very well use ALTER TABLE TABLE_NAME DISABLE TABLE LOCK; to disable all the DDL operations on that table, yet the user can issue a similar ENABLE command for him.

2) A user will always have access to drop tables from his schema. If you don't want him to do that, why don't you just create those tables in a different schema and grant only appropriate privileges to him?

3) Create a DDL trigger as mentioned but in a different schemas?

Thank you,

Boobal Ganesan

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×