RE: Simple auditing question
- From: Don Granaman <DonGranaman@xxxxxxxxxxxxxxx>
- To: "oracledba.williams@xxxxxxxxx" <oracledba.williams@xxxxxxxxx>, "Mayen.Shah@xxxxxxxxxx" <Mayen.Shah@xxxxxxxxxx>
- Date: Mon, 6 Dec 2010 15:06:20 -0600
Indeed I have a suggestion - standard auditing may be insufficient. (In 9i it
certainly is.) I use a custom method - with trigger, a procedure, a sequence
and a table. (Granted, there is room for improvement here. This has evolved
over a long time and is now likely quite "suboptimal". It does have the
advantage of actually working though.) The trick is in the permissions needed
by the owner, but I have to leave for an appointment, so will let you (and the
rest of Oracle-L) work that out.
create sequence DDL_AUDIT_SEQ;
create table DDL_AUDIT (
ID number(38.0) not null,
DB_NAME varchar2(30) null,
ACTION_TIME date not null,
LOGON_TIME date null,
SQL_TEXT clob null,
CLIENT_IP varchar2(20) null,
ORA_SYSEVENT varchar2(20) null,
OBJ_OWNER varchar2(30) null,
OBJ_TYPE varchar2(30) null,
OBJ_NAME varchar2(30) null,
INSTANCE_NUM number(2) null,
LOGON_USER varchar2(30) null,
OSUSER varchar2(30) null,
SESSIONID number null,
MACHINE varchar2(64) null,
TERMINAL varchar2(30) null,
PROGRAM varchar2(48) null,
MODULE varchar2(48) null,
CLIENT_INFO varchar2(64) null,
HOST varchar2(54) null,
AUTHENTICATION_TYPE varchar2(30) null,
SCHEMANAME varchar2(30) null,
EXTERNAL_NAME varchar2(256) null
);
create or replace procedure GET_SESSION_INFO (
sessionid in number,
schemaname out varchar2,
osuser out varchar2,
process out varchar2,
machine out varchar2,
terminal out varchar2,
program out varchar2,
module out varchar2,
action out varchar2,
logon_time out date,
client_identifier out varchar2,
external_name out varchar2
) as
BEGIN
select SCHEMANAME,
OSUSER,
PROCESS,
MACHINE,
TERMINAL,
PROGRAM,
MODULE,
ACTION,
LOGON_TIME,
CLIENT_IDENTIFIER,
EXTERNAL_NAME
into schemaname,
osuser,
process,
machine,
terminal,
program,
module,
action,
logon_time,
client_identifier,
external_name
from V$SESSION S
where S.AUDSID = sessionid
and rownum = 1;
EXCEPTION
when OTHERS then
raise;
END;
/
create or replace trigger BEFORE_DDL
before ddl on database
DECLARE
id number;
sql_text ora_name_list_t;
n number;
stmt clob;
client_ip varchar2(20);
session_id number;
host varchar2(54);
external_name varchar2(256);
os_user varchar2(30);
sessionid number;
l_command number;
l_db_name varchar2(30);
l_schemaname varchar2(30);
l_osuser varchar2(30);
l_process varchar2(12);
l_machine varchar2(64);
l_terminal varchar2(30);
l_program varchar2(48);
l_module varchar2(48);
l_action varchar2(32);
l_client_info varchar2(64);
l_logon_time date;
l_client_identifier varchar2(64);
l_external_name varchar2(256);
l_authentication_type varchar2(30);
BEGIN
-- Note: This will generate audit records only if the target is in the SCOTT
schema or the BIGAPP schema.
IF ora_dict_obj_owner IN ('SCOTT','BIGAPP')
then
stmt := null;
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
select sys_context('USERENV','HOST'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','EXTERNAL_NAME'),
sys_context('USERENV','DB_NAME')
into host, os_user, external_name, l_db_name
from dual;
sessionid := userenv('SESSIONID');
get_session_info (
sessionid,
l_schemaname,
l_osuser,
l_process,
l_machine,
l_terminal,
l_program,
l_module,
l_action,
l_logon_time,
l_client_identifier,
l_external_name);
INSERT INTO DDL_AUDIT
(ID,
DB_NAME,
ACTION_TIME,
LOGON_TIME,
SQL_TEXT,
CLIENT_IP,
ORA_SYSEVENT,
OBJ_OWNER,
OBJ_TYPE,
OBJ_NAME,
INSTANCE_NUM,
LOGON_USER,
OSUSER,
SESSIONID,
MACHINE,
TERMINAL,
PROGRAM,
MODULE,
CLIENT_INFO,
HOST,
AUTHENTICATION_TYPE,
SCHEMANAME,
EXTERNAL_NAME)
values (
DDL_AUDIT_SEQ.NEXTVAL,
l_db_name,
sysdate,
l_logon_time,
stmt,
ora_client_ip_address,
ora_sysevent,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_instance_num,
ora_login_user,
os_user,
sessionid,
NULL, /* machine */
userenv('TERMINAL'),
l_program,
l_module,
userenv('CLIENT_INFO'),
host,
l_authentication_type,
l_schemaname,
l_external_name
);
end if;
END;
/
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Dennis Williams
Sent: Monday, December 06, 2010 12:48 PM
To: Mayen.Shah@xxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
Subject: Re: Simple auditing question
Thanks everyone for you great replies. As Mayen suggested, I checked further,
and the requirement is to audit DDL statements, but not DML statements.
Specifically we need to know how to audit DDL. I.E. ALTER TABLE, CREATE TABLE,
DROP TABLE, CREATE INDEX, DROP INDEX, ALTER INDEX. Basically we need to audit
all ALTERs, CREATEs, and DROPs on all objects in the database.
Any thoughts on that score?
Dennis Williams
On Fri, Dec 3, 2010 at 2:06 PM, <Mayen.Shah@xxxxxxxxxx> wrote:
Dennis,
Rather than what you do not want to know (or what not to audit) you should come
up with the requirements of what you want to know or what you want to audit.
Best
- Mayen
From: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: 12/03/2010 02:45 PM
Subject: Simple auditing question
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
________________________________________
List,
I haven't used Oracle Auditing much, so this is probably a newbie
question. Need to turn on auditing for a high-volume OLTP database. My
understanding is that if I turn on AUDIT TABLE, it gets everything. I don't
need to know all the inserts, updates on these tables. Is there a simpler audit
setting? I probably am missing a concept.
Thanks,
Dennis Williams
--
//www.freelists.org/webpage/oracle-l
Other related posts:
