Oracle-L: Re:Audit Drop Table

From: <Chaim.Katz_at_Completions.Bombardier.com>
Date: Tue, 19 Mar 2002 13:52:41 -0800
Message-ID: <F001.0042DD8F.20020319135241@fatcity.com>

Raj,
Audit table will create an audit trail for DELETE as well as TRUNCATE (as well as CREATE), even in 7.3 (I just tried) Maybe you were logged in as SYS or maybe it's one of those things that you have to log out and back it for it to be effective?
Chaim

Rajesh.Rao_at_jpmchase.com_at_fatcity.com on 03/19/2002 03:13:45 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

Thanks Doug. But doesn't the product_user_profile only work for connections using SQL*Plus? Anyway, they figured out it was a temporary table, which was dropped and recreated by a batch job being run daily.

But I just figured out there's no way to audit drop and truncate statements executed against specific tables, using the 'AUDIT' command.

Thanks
Raj

                    dgoulet_at_vicr.
                    com                  To:     Rajesh Rao/CHASE_at_CHASE,
Multiple recipients of list ORACLE-L
                                         <ORACLE-L_at_fatcity.com>
                    March 19,            cc:
                    2002 02:55 PM        Subject:     Re:Audit Drop Table

Raj,

From Metalink:

The PRODUCT_USER_PROFILE table will only allow you to enable or disable certain
sql*plus commands (Not logins). "Connect" is one such command.

For example, the command.

INSERT INTO PRODUCT_USER_PROFILE(product, userid, attribute, char_value) VALUES('SQL*Plus','TEST','CONNECT','DISABLED');

Will ensure that the user test can not execute the "connect" command within
his session. This includes attempts to 'connect' as himself or any other user.

Refer Note: 98549.947 for more information.

You should be able to insert a record to prevent the user from dropping tables
after which it's just a matter of time till someone complains. Then you will
have your culprit.

Also, have you considered giving people their own accounts if they have a need
to create objects? That would also fix your problem, unless the user allows
others to use his/her account. In that case it's their problem, not yours.

Dick Goulet

____________________Reply Separator____________________
Author: Rajesh.Rao_at_jpmchase.com
Date:       3/19/2002 11:04 AM

One of the application users has been complaining that someone keeps dropping one of his tables. He imports it back, and after a few hours, its gone again. I tried to turn on auditing for this table when its dropped. I thought this was possible but looks like its not. I turned on full auditing for the table but it does not audit the drop in sys.aud$. I understand that there's no such system privilege as 'drop table'.

How do I turn this auditing on? Something like 'Audit drop on scott.emp'. Is it possible? I am thinking, not. There are hundreds of users in the database, and I dont wish to turn full auditing on for all these users. And no drop statement exists in v$sqlarea currently. And damn !!! This is on 7.3.4.

Raj

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Rajesh.Rao_at_jpmchase.com

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: ListGuru_at_fatcity.com (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:
INET: Rajesh.Rao_at_jpmchase.com

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Chaim.Katz_at_Completions.Bombardier.com

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