Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Unable to get the sql command in Audit table

Re: Unable to get the sql command in Audit table

From: <daoudamjad_at_emailmiser.com>
Date: Thu, 18 Oct 2007 05:07:53 -0700
Message-ID: <1192709273.253239.33050@q5g2000prf.googlegroups.com>


On Oct 17, 6:16 pm, DA Morgan <damor..._at_psoug.org> wrote:
> nitesh wrote:
> > Hi,
>
> > I need to create a trigger, which should record any ddl changes in a
> > schema. The output should be sent to an audit table in which it should
> > show the sqlstatement, user and program. So i created an audit table
> > and a trigger. In the audit table it is showing the user and program
> > but not the sqlstatement. Here i am giving my approach..
>
> > CREATE TABLE ddl_log (
>
> > operation VARCHAR2(30),
>
> > obj_owner VARCHAR2(30),
>
> > object_name VARCHAR2(30),
>
> > sql_text VARCHAR2(64),
>
> > attempt_by VARCHAR2(30),
>
> > attempt_dt DATE);
>
> > CREATE OR REPLACE TRIGGER ddl_trigger
>
> > BEFORE CREATE OR ALTER OR DROP
>
> > ON SCHEMA
>
> > DECLARE
>
> > oper ddl_log.operation%TYPE;
>
> > sql_text ora_name_list_t;
>
> > i PLS_INTEGER;
>
> > BEGIN
>
> > SELECT ora_sysevent
>
> > INTO oper
>
> > FROM dual;
>
> > i := sql_txt(sql_text);
>
> > IF oper IN ('CREATE', 'DROP') THEN
>
> > INSERT INTO ddl_log
>
> > SELECT ora_sysevent, ora_dict_obj_owner,
>
> > ora_dict_obj_name,NULL, USER, SYSDATE
>
> > FROM dual;
>
> > ELSIF oper = 'ALTER' THEN
>
> > INSERT INTO ddl_log
>
> > SELECT ora_sysevent, ora_dict_obj_owner,
>
> > ora_dict_obj_name, sql_text, USER, SYSDATE
>
> > FROM gv$sqltext
>
> > WHERE UPPER(sql_text) LIKE 'ALTER%'
>
> > AND UPPER(sql_text) LIKE '%NEW_TABLE%';
>
> > END IF;
>
> > END ddl_trigger;
>
> > /
>
> > please suggest.
>
> > Thanks in advance.
>
> A schema level trigger will not do what it appears you are trying to do.
> Schema level doesn't trap anyone touching a named schema ... rather it
> affects anyone logged in as that schema owner only.
>
> Look at the demos here:http://www.psoug.org/reference/ddl_trigger.html
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Rainbow Tables for CAIN:

parameters are given below:

oracle_oracle#1-8_0_2400x40000000_system#000.rt
oracle_oracle#1-8_0_2400x40000000_system#001.rt
oracle_oracle#1-8_0_2400x40000000_system#002.rt
...........................

oracle_oracle#1-8_0_2400x40000000_system#099.rt

email for details, demo, and downloading url's!

(Only system account) oracle hashes challenges are welcome! please make the payment FIRST!

happy crackin' Received on Thu Oct 18 2007 - 07:07:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US