Re: who created the table?

Joe,

There is a script named "audit_ddl.sql" online at http://www.EvDBT.com/tools.htm for enabling only those audits related to CREATE, DROP, ALTER, TRUNCATE, REPLACE, etc commands.  Also, it creates a DBMS_JOB job to delete records from SYS.AUD$ older than 45 days.  Obviously, you may want to make changes to or remove altogether some of this.  Feel free to review it and see if might meet your (future) DDL auditing needs?  Of course, you'll need to bounce the database to set AUDIT_TRAIL to either TRUE (DB), DB, or OS, if it is currently set to NONE...

Hope this helps...
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim@xxxxxxxxx
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt


Dan Norris wrote:
If you want to know in the future, I think the audit events can capture that information for you, but you'd have to enable auditing and then AUDIT CREATE TABLE;

As for hindsight, it may be possible to find out with logminer, but I'm not positive on that. I agree--the data dictionary isn't going to help you at all (except aud$, if enabled).

Dan

On Thu, Nov 20, 2008 at 11:55 AM, Goulet, Richard <Richard.Goulet@xxxxxxxxxxx> wrote:
Joe,
 
    If memory is serving correctly there is no way to find out who created the table.  The data dictionary only has information on the current owner and the object's name.  By passing out the "create any table" system privilege you've lost control of who actually did it unless you have auditing turned on.  In my databases no one other than DBA's have any of the "any" privileges.
 

Dick Goulet
Senior Oracle DBA
PAREXEL International
978.313.3426
 information transmitted in this communication is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please destroy any copies, contact the sender and delete the material from any computer.


 


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Joe Smith
Sent: Thursday, November 20, 2008 11:35 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: who created the table?

How do I determine which user actually created a table?

 

I have a table named ???? under schema A.  I have numerous schemas/users that have CREATE ANY TABLE system privilege.  I have the OBJECT_ID of the table.

 

How do I join USER$, TAB$,  and so on to find the actual user that created the table?

 

Thanks.



Windows Live Hotmail now works up to 70% faster. Sign up today.

-- http://www.freelists.org/webpage/oracle-l

Other related posts: