Skip to Main Content
  • Questions

  • DDL Auditing - automatically including sql alt script file names

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, David.

Asked: November 08, 2002 - 10:28 pm UTC

Last updated: December 25, 2005 - 8:54 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,
I am trying to implement ddl auditing using new 8i schema level event triggers on creates drops and alters. I have read your example trigger and found it to be very useful. see -'Frederic -- Thanks for the question regarding "DDL triggers in 8.1.7", version 8.1.7
originally submitted on 4-feb-2001 15pm eastern time, lasted updated 11-apr-2002 7am'
My question specifically concerns documenting alters. On our database, changes to schema objects are done via 'alt scripts' run in sqlplus. A script to add/drop a column to/from a table might be called "customer.1.1.alt" where customer is the table name and the two digits represent a product release level and alt script sequence number respectively. Is there any way to get the actual script name from an Oracle data dictionary table, system variable or context variable, so that every time an alter script is run, the name of the script can be included as one of the values inserted into the ddl_audit table? This would also be useful for creates and drops of course but these script names are easy to reconstruct from the values in ora_sysevent, ora_dict_obj_name, and ora_dict_obj_type.
I have been able to put the alt script name in
embedded comments in the alt script and then extracted them from the sql_text field of v$open_cursor, however, the sql_text field is only 60 characters long so I lose desired sql_text and I have to embed comments, which is inelegant.
e.g. 'alter table customer /*customer.1.1.alt*/
drop column obsolete_column;'
What I would really like to be able to do is elegantly extract whatever follows the '@' symbol - e.g. @'customer.1.1.alt' into a variable in the trigger for whichever scripts are run in sqlplus that cause the schema level event trigger to fire.

Thanks very much,

David Wilson.



and Tom said...

As long as you use sqlplus to install -- "elegant r us" = true.

sqlplus stuffs the name and even the depth of nesting of the current script in the MODULE column of v$session. Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table log
2 ( operation varchar2(25),
3 script varchar2(255),
4 owner varchar2(25),
5 name varchar2(25),
6 extra varchar2(4000) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger ddl_trigger
2 after create or alter or drop on SCHEMA
3 declare
4 l_sysevent varchar2(25);
5 l_extra varchar2(4000);
6 begin
7 select ora_sysevent into l_sysevent from dual;
8
9 if ( l_sysevent in ('DROP','CREATE') )
10 then
11 if l_sysevent = 'CREATE'
12 then
13 begin
14 select 'storage ( initial ' || initial_extent ||
15 ' next ' || next_extent || ' .... )'
16 into l_extra
17 from all_tables
18 where table_name = ora_dict_obj_name
19 and owner = user;
20 exception
21 when no_data_found then null;
22 end;
23 end if;
24
25 insert into log
26 select ora_sysevent, module, ora_dict_obj_owner,
27 ora_dict_obj_name, l_extra
28 from v$session
29 where sid = (select sid from v$mystat where rownum=1);
30 elsif ( l_sysevent = 'ALTER' )
31 then
32 insert into log
33 select ora_sysevent, module, ora_dict_obj_owner,
34 ora_dict_obj_name, sql_text
35 from v$open_cursor,
36 (select module
37 from v$session
38 where sid = (select sid from v$mystat where rownum=1))
39 where upper(sql_text) like 'ALTER%' ||
40 ora_dict_obj_name || '%'
41 and sid = ( select sid
42 from v$session
43 where audsid=userenv('sessionid') );
44 end if;
45 end;
46 /

Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table cust ( x int primary key );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> @customer.1.1.alt
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table cust add new_column number;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> column script format a25
ops$tkyte@ORA817DEV.US.ORACLE.COM> column extra format a50
ops$tkyte@ORA817DEV.US.ORACLE.COM> select script, extra from log;

SCRIPT EXTRA
------------------------- --------------------------------------------------
01@ test.sql
01@ test.sql storage ( initial 524288 next 524288 .... )
02@ customer.1.1.alt alter table cust add new_column number



Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A most elegant solution indeed!

David Wilson, November 09, 2002 - 12:27 pm UTC

Hi Tom,
That is very useful. If you don't mind a followup question, where is the best place to look up what information the columns in the data dictionary contain? I would like to study them. I have found the oracle documentation descriptions to be somewhat terse, although I might be looking in the wrong place.
Thanks again for your help and for providing such a useful and informative site.


Tom Kyte
November 09, 2002 - 3:02 pm UTC

This is what I've always used:

</code> http://docs.oracle.com/docs/cd/B10501_01/nav/docindex.htm#index-REF <code>

however a tidbit such as

"oh by the way the MODULE column in v$session will have the sqlplus script name in it when you look at it and the session is using sqlplus"

won't be found pretty much anywhere. EG: the reference guide cannot say this as this is a feature/function of SQLPlus. The module column contains a value that the application that is connected to the database just happens to have stuffed in there. I know from observation that sqlplus does this. You won't "find" this factoid anywhere.


Great Info

Jeremy Smith, November 10, 2002 - 1:57 am UTC

You know, it's surprising to me that of all of the Oracle books out there, no one has ever written a reference that specifically just goes through the whole data dictionary, V$ views, the few X$ views people occasionally use, and explains not just what each one contains, but useful purposes it can be used for, what each value really means, etc. Right now there's the reference you posted, but factoids like this, what "good" and "bad" values are for various parameters, which one is applicable in which situation, etc. Between your book, the Oracle docs, JPL's book, Steve Adams' website, whatever, there's a lot of information out there.

Ah well. Can't have everything. :)

Any pre-built mechanism in 9i

Subrata, November 11, 2002 - 11:11 am UTC

Do you have any pre built mechanism to do the same in 9i?

Tom Kyte
November 11, 2002 - 6:17 pm UTC 

this works in 9i, you might have to add

SQL> set appinfo on

to your script as the default value seems to have changed.

wrong log table create statement

Edward Hayrabedian, May 17, 2003 - 5:16 am UTC

Hi Tom,

I have found a little mistake in your script, which can lead to a problem, which is not easy to investigate.

The "owner" and "name" columns in the "log" table could be more than 25 chars long. The "log" table's definition should be:

create table log
( operation varchar2(25),
script varchar2(255),
owner varchar2(30),
name varchar2(30),
extra varchar2(4000) );

Thanks for your help!

Best regards,
Eddie


Auditing procedures, functions and packages

A reader, November 14, 2003 - 2:58 pm UTC

Hi Tom, after reading your book I got your idea to enable audit, for all DLL activity.

So I'm enabling but I can't enable auditing in all functions, procedures, and packages with one command.
Is there a trick for it?

Thanks.

PD.
I enabled this auditing
AUDIT CREATE ON DEFAULT;
AUDIT ALTER ON DEFAULT;

AUDIT ALTER DATABASE;
AUDIT SYSTEM AUDIT;
AUDIT ALTER SYSTEM;

AUDIT ALTER TABLESPACE;
AUDIT DROP TABLESPACE;
AUDIT CREATE TABLESPACE;

AUDIT CREATE USER WHENEVER SUCCESSFUL;
AUDIT ALTER USER WHENEVER SUCCESSFUL;
AUDIT DROP USER WHENEVER SUCCESSFUL;

AUDIT CREATE ROLE WHENEVER SUCCESSFUL;
AUDIT ALTER ANY ROLE WHENEVER SUCCESSFUL;
AUDIT DROP ANY ROLE WHENEVER SUCCESSFUL;

AUDIT DROP ANY TABLE WHENEVER SUCCESSFUL;
AUDIT CREATE ANY TABLE WHENEVER SUCCESSFUL;
AUDIT ALTER ANY TABLE WHENEVER SUCCESSFUL;

AUDIT CREATE ANY SEQUENCE WHENEVER SUCCESSFUL;
AUDIT ALTER ANY SEQUENCE WHENEVER SUCCESSFUL;
AUDIT DROP ANY SEQUENCE WHENEVER SUCCESSFUL;

AUDIT CREATE DATABASE LINK WHENEVER SUCCESSFUL;
AUDIT CREATE PUBLIC DATABASE LINK WHENEVER SUCCESSFUL;

AUDIT CREATE OR REPLACE PROCEDURE;

AUDIT DROP ANY VIEW WHENEVER SUCCESSFUL;
AUDIT CREATE ANY VIEW WHENEVER SUCCESSFUL;

AUDIT DROP ANY INDEX WHENEVER SUCCESSFUL;
AUDIT CREATE ANY INDEX WHENEVER SUCCESSFUL;
AUDIT ALTER ANY INDEX WHENEVER SUCCESSFUL;

AUDIT DROP ANY SYNONYM WHENEVER SUCCESSFUL;
AUDIT CREATE ANY SYNONYM WHENEVER SUCCESSFUL;

AUDIT DROP ANY TRIGGER WHENEVER SUCCESSFUL;
AUDIT CREATE ANY TRIGGER WHENEVER SUCCESSFUL;
AUDIT ALTER ANY TRIGGER WHENEVER SUCCESSFUL;

AUDIT DROP ANY CLUSTER WHENEVER SUCCESSFUL;
AUDIT CREATE ANY CLUSTER WHENEVER SUCCESSFUL;
AUDIT ALTER ANY CLUSTER WHENEVER SUCCESSFUL;

AUDIT DROP ANY CONTEXT WHENEVER SUCCESSFUL;
AUDIT CREATE ANY CONTEXT WHENEVER SUCCESSFUL;

AUDIT DROP ANY DIMENSION WHENEVER SUCCESSFUL;
AUDIT CREATE ANY DIMENSION WHENEVER SUCCESSFUL;
AUDIT ALTER ANY DIMENSION WHENEVER SUCCESSFUL;

AUDIT DROP ANY DIRECTORY WHENEVER SUCCESSFUL;
AUDIT CREATE ANY DIRECTORY WHENEVER SUCCESSFUL;



Tom Kyte
November 15, 2003 - 8:52 am UTC 

 
  1  SELECT * FROM STMT_AUDIT_OPTION_MAP WHERE NAME LIKE '%ANY%'
  2* order by name
ops$tkyte@ORA920>
ops$tkyte@ORA920> /

       192 EXECUTE ANY LIBRARY                     0
       144 EXECUTE ANY PROCEDURE                   0
       184 EXECUTE ANY TYPE                        0

Sorry

A reader, November 14, 2003 - 3:16 pm UTC

Sorry, my mistake, it was a problem with a letter.
Any way you can see in this table
all the %ANY% privileges you can give

SELECT * FROM STMT_AUDIT_OPTION_MAP WHERE NAME LIKE '%ANY%'



OK

Stella, March 14, 2004 - 9:10 am UTC

Hi Tom,
Till I am not able to understand the concepts "application info and setting context namespaces".
I see things like
sql > set appinfo on
and other related information.They seem to be scary for me.Could you please explain them if you have time?
Please do write a followup.



Tom Kyte
March 14, 2004 - 10:04 am UTC

why are they "scary"?

application info (a very useful tool for instrumenting your code) and "context namespaces" (a very useful tool for supplying bind variables to queries when you cannot otherwise bind) are really "not related"

set appinfo on

just lets sqlplus tell other sessions what your session is doing. appinfo on is a sqlplus command to tell sqlplus "please use dbms_application_info to set the client_info/action/module columns in v$session so others can see what script we are running and so on". It is useful so you can monitor the execution of a script from another session.


Things are less scary if you read about them -- suggest you start with the Oracle Concepts guide -- if you read that, retain 10% of it, you'll already know 90% more than most people do!

Any Example Please

Amir Ali, March 16, 2004 - 1:22 am UTC

Dear Sir,
Do you have any simple demos for "dbms_application_info.
set_client_info".Can it affect other sessions? * We don't
have access to your book *.Kindly bear with us.
Please do write a followup.

Tom Kyte
March 16, 2004 - 7:37 am UTC

sure you have *access to my book*. its for sale.

But anyway, you certainly have access *to the documentation*, otn.oracle.com has it and we document stuff like this.

search this site for set_client_info, you'll see examples. it sets the value only in your session.

see the supplied packages guide (otn.oracle.com -> documentation) for all of the details on dbms_application_info.

ddl_trigger

Ghulam Moinuddin, December 25, 2005 - 8:13 am UTC

I found it very much useful for auditing DDL, This trigger only working with system. When I tried to test it using scott it was not firng, appreciate if you kindly let me know what are the reason for this

Tom Kyte
December 25, 2005 - 8:54 am UTC

give us a "for example"

show me what you did.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library