Re: Revoke Delete
You can't revoke the ability to delete from the schema owner. You could revoke CREATE SESSION from the schema owner, but that doesn't solve the problem of DBA-privileged accounts being able to delete. I'm guessing that this is a perfect opportunity to use an INSTEAD OF trigger. --- Aldi Barco [EMAIL PROTECTED] wrote: Hi Listers, How can we revoke 'delete privilege' from the schema owner of the table and also from DBA ? If it is not possible, can we set through trigger ? Thanks. Aldi _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Aldi Barco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Revoke Delete
set what, precisely, through a trigger? Geez, if you have a DBA and/or a schema owner that can't/shouldn't be deleting from a table, what you have is NOT a database problem, it's an HR problem. sheesh. yea, how about this? an BEFORE DELETE trigger on the table, saving and repopulating each row the Evil DBA deleted, and logging his Evil Actions in the Military Audit table, so that the Evildoer can be brought to justice? Yea, that's the ticket, that's printable in Dilbert. In peace, love, data, and triggers, - The Evil One -Original Message- Hi Listers, How can we revoke 'delete privilege' from the schema owner of the table and also from DBA ? If it is not possible, can we set through trigger ? Thanks. Aldi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Revoke Delete
But of course a delete trigger is not called on a truncate... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] MohanR@STARS- SMI.com To: [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: RE: Revoke Delete com 11/27/01 02:40 PM Please respond to ORACLE-L set what, precisely, through a trigger? Geez, if you have a DBA and/or a schema owner that can't/shouldn't be deleting from a table, what you have is NOT a database problem, it's an HR problem. sheesh. yea, how about this? an BEFORE DELETE trigger on the table, saving and repopulating each row the Evil DBA deleted, and logging his Evil Actions in the Military Audit table, so that the Evildoer can be brought to justice? Yea, that's the ticket, that's printable in Dilbert. In peace, love, data, and triggers, - The Evil One -Original Message- Hi Listers, How can we revoke 'delete privilege' from the schema owner of the table and also from DBA ? If it is not possible, can we set through trigger ? Thanks. Aldi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Revoke Delete
Actually, you can. Use FGAC (Fine-Grained Access Control) and you can put a Policy in place on a table that even the table owner can't bypass - even System can't bypass. Only Sys can bypass FGAC policies - and the owner of the security schema in which you place the Policy functions. I've used FGAC and Application Context successfully to enforce complex security, but the more I think about it, you could really do some fiendish tricks with it - if you were the fiendish kind. ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Baumgartel Sent: Tuesday, November 27, 2001 3:55 PM To: Multiple recipients of list ORACLE-L You can't revoke the ability to delete from the schema owner. You could revoke CREATE SESSION from the schema owner, but that doesn't solve the problem of DBA-privileged accounts being able to delete. I'm guessing that this is a perfect opportunity to use an INSTEAD OF trigger. --- Aldi Barco [EMAIL PROTECTED] wrote: Hi Listers, How can we revoke 'delete privilege' from the schema owner of the table and also from DBA ? If it is not possible, can we set through trigger ? Thanks. Aldi _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Aldi Barco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Revoke Delete
i.e. unless u dont have a truncate trigger instead .. lol Create or Replace Trigger StopTruncatesAndDrops Before TRUNCATE or DROP on database WHEN (ora_dict_obj_name = 'MTRX_GAME_SESSION_FACTS' AND ora_dict_obj_type = 'TABLE') declare begin raise_application_error(-20211, 'Cannot Drop or Truncate Fact Table!!'); end; / intrestin stuff aint it .. especially when the fact table holds millions .. of records i mean ;) Deepak --- Ron Thomas [EMAIL PROTECTED] wrote: But of course a delete trigger is not called on a truncate... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] MohanR@STARS- SMI.com To: [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: RE: Revoke Delete com 11/27/01 02:40 PM Please respond to ORACLE-L set what, precisely, through a trigger? Geez, if you have a DBA and/or a schema owner that can't/shouldn't be deleting from a table, what you have is NOT a database problem, it's an HR problem. sheesh. yea, how about this? an BEFORE DELETE trigger on the table, saving and repopulating each row the Evil DBA deleted, and logging his Evil Actions in the Military Audit table, so that the Evildoer can be brought to justice? Yea, that's the ticket, that's printable in Dilbert. In peace, love, data, and triggers, - The Evil One -Original Message- Hi Listers, How can we revoke 'delete privilege' from the schema owner of the table and also from DBA ? If it is not possible, can we set through trigger ? Thanks. Aldi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message
RE: Revoke Delete
Like I said, an HR problem. Or a training problem. :-) With Treacly Love, and Unctuous Peace, etc. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 11/27/2001 5:45 PM But of course a delete trigger is not called on a truncate... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] MohanR@STARS- SMI.com To: [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: RE: Revoke Delete com 11/27/01 02:40 PM Please respond to ORACLE-L set what, precisely, through a trigger? Geez, if you have a DBA and/or a schema owner that can't/shouldn't be deleting from a table, what you have is NOT a database problem, it's an HR problem. sheesh. yea, how about this? an BEFORE DELETE trigger on the table, saving and repopulating each row the Evil DBA deleted, and logging his Evil Actions in the Military Audit table, so that the Evildoer can be brought to justice? Yea, that's the ticket, that's printable in Dilbert. In peace, love, data, and triggers, - The Evil One -Original Message- Hi Listers, How can we revoke 'delete privilege' from the schema owner of the table and also from DBA ? If it is not possible, can we set through trigger ? Thanks. Aldi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Revoke Delete
Yea, but he didn't specify Oracle version. -Original Message- To: Multiple recipients of list ORACLE-L Sent: 11/27/2001 5:55 PM Actually, you can. Use FGAC (Fine-Grained Access Control) and you can put a Policy in place on a table that even the table owner can't bypass - even System can't bypass. Only Sys can bypass FGAC policies - and the owner of the security schema in which you place the Policy functions. I've used FGAC and Application Context successfully to enforce complex security, but the more I think about it, you could really do some fiendish tricks with it - if you were the fiendish kind. ;-) Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Baumgartel Sent: Tuesday, November 27, 2001 3:55 PM To: Multiple recipients of list ORACLE-L You can't revoke the ability to delete from the schema owner. You could revoke CREATE SESSION from the schema owner, but that doesn't solve the problem of DBA-privileged accounts being able to delete. I'm guessing that this is a perfect opportunity to use an INSTEAD OF trigger. --- Aldi Barco [EMAIL PROTECTED] wrote: Hi Listers, How can we revoke 'delete privilege' from the schema owner of the table and also from DBA ? If it is not possible, can we set through trigger ? Thanks. Aldi _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Aldi Barco INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
