Home » RDBMS Server » Security » User without DROP table privilege
User without DROP table privilege [message #126525] Tue, 05 July 2005 06:51 Go to next message
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
Re: User without DROP table privilege [message #126548 is a reply to message #126525] Tue, 05 July 2005 09:41 Go to previous messageGo to next message
Frank Naude
Messages: 4569
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:

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
Re: User without DROP table privilege [message #126830 is a reply to message #126525] Wed, 06 July 2005 18:59 Go to previous messageGo to next message
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?
Re: User without DROP table privilege [message #128225 is a reply to message #126830] Fri, 15 July 2005 16:35 Go to previous message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Check for the user in the trigger body and fire!

Hope that helps!

-Sri
Previous Topic: Pushing data securely outside firewall
Next Topic: i want to get a previllege
Goto Forum:
  


Current Time: Sun Sep 26 17:40:08 CDT 2021