Home » RDBMS Server » Server Administration » How to protect object from ddl and dml commands (DB:Oracle 9i, Version - 9.2.0.1.0, OS: RHEL 2.1 AS)
How to protect object from ddl and dml commands [message #396271] Sun, 05 April 2009 23:52 Go to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
Hi Friends

I Wold like to know how can i protect my table from dml and ddl commands. To do that i was created one tablespace move that table to that perticular tablespace and then do that tablespace read only. however the tablespace readonly the table can drop. is there any way to protect object from dml and ddl command even owner cant do it.

Chintan

Re: How to protect object from ddl and dml commands [message #396276 is a reply to message #396271] Mon, 06 April 2009 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>is there any way to protect object from dml and ddl command even owner cant do it.
It is easier to trust object owner & allow other schemas read only access.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Mon, 06 April 2009 00:04]

Report message to a moderator

Re: How to protect object from ddl and dml commands [message #396282 is a reply to message #396271] Mon, 06 April 2009 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You did it for DML putting the table in a read-only tablespace but you should also do not grant the privilege to do DML.
For DDL you can
1/ do not grant the privilege to do it
2/ create a ddl trigger

Regards
Michel
Re: How to protect object from ddl and dml commands [message #396286 is a reply to message #396282] Mon, 06 April 2009 01:07 Go to previous messageGo to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks For The Reply

i also like to know the effects of read only tablespace on physical standny database.

Chintan
Re: How to protect object from ddl and dml commands [message #396288 is a reply to message #396286] Mon, 06 April 2009 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None.

Regards
Michel
Re: How to protect object from ddl and dml commands [message #396289 is a reply to message #396288] Mon, 06 April 2009 01:18 Go to previous messageGo to next message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks for instant reply

as per your suggestion i will through it on development server then impliment on live servers.

thanks again

Chintan
Re: How to protect object from ddl and dml commands [message #396323 is a reply to message #396289] Mon, 06 April 2009 02:18 Go to previous message
chintan.patel
Messages: 140
Registered: July 2008
Location: Ahmedabad
Senior Member
now i m facing one more problem is that before dropping my table i was query on dba_segments where tablespace='HISTORYDATA' it gives me belowe result

SEGMENT_NAME SEGMENT_TYPE
------------- ------------
RMSCL122005 TABLE

After droped this table the same query from dba_segments where tablespace_name='HOSTORYDATA'
it gives me below result

SEGMENT_NAME SEGMENT_TYPE
------------- ------------
16.6577 TEMPORARY

Actually i like to know the difference between two results.
however drop this table its occupy the diskspace or not.

Chintan

Previous Topic: User Privileges
Next Topic: Oracle Consume 100 % cpu
Goto Forum:
  


Current Time: Thu Dec 08 20:31:15 CST 2016

Total time taken to generate the page: 0.13475 seconds