OraFAQ Forum: Security » User without DROP table privilege

Home » RDBMS Server » Security » User without DROP table privilege

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

User without DROP table privilege [message #126525]

Tue, 05 July 2005 06:51

reemagupta18
Messages: 3
Registered: February 2005
Location: Delhi

Junior Member

Hi ,
I am in the process of creating users for the database and due to some configuration issues I need to create users which have all the rights except dropping the table.
Can anybody give me any pointers in this direction.It's urgent.
Thanks
Reema

Report message to a moderator

Re: User without DROP table privilege [message #126548 is a reply to message #126525]

Tue, 05 July 2005 09:41

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

Hi,

The best solution would be to create a schema level trigger to prevent it. Here is a quick example:

Code: [Select all] [Show/ hide]

SQL> CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON scott.SCHEMA
  2  BEGIN
  3     RAISE_APPLICATION_ERROR(-20000, 'You cannot drop anything, ask your DBA.');
  4  END;
  5  /

Trigger created.

SQL> CONNECT scott/tiger
Connected.
SQL>
SQL> DROP TABLE emp;
DROP TABLE emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: You cannot drop anything, ask your DBA.
ORA-06512: at line 2

Best regards.

Frank

Report message to a moderator

Re: User without DROP table privilege [message #126830 is a reply to message #126525]

Wed, 06 July 2005 18:59

Steve Callan
Messages: 44
Registered: January 2004

Member

But what about users who can drop? The trigger impacts them as well. How about creating a role for the non-drop users and revoke drop from users with that "nondrop" role?

Report message to a moderator

Re: User without DROP table privilege [message #128225 is a reply to message #126830]

Fri, 15 July 2005 16:35

srinivas4u2
Messages: 66
Registered: June 2005

Member

Check for the user in the trigger body and fire!

Hope that helps!

-Sri

Report message to a moderator

Previous Topic:	Pushing data securely outside firewall
Next Topic:	i want to get a previllege

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 09:41:47 CDT 2025