OraFAQ Forum: Server Administration » Creating a trigger on SYS.AUD$ or moving SYS.AUD$ or some other way of...

Home » RDBMS Server » Server Administration » Creating a trigger on SYS.AUD$ or moving SYS.AUD$ or some other way of...

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Creating a trigger on SYS.AUD$ or moving SYS.AUD$ or some other way of... [message #51684]

Thu, 06 June 2002 11:04

Teo Laitinen
Messages: 2
Registered: June 2002

Junior Member

Hi!

I'm a computer science student working on the constructional section of my master's thesis. As a part of that I'd like to audit the SELECT-statements (maybe even on column-level) and start a stored procedure if a new entry is inserted in the audit trail. I'm using Oracle 9i Personal Edition.

So, what I thought I needed was a trigger that would have started a procedure after a new audit entry on some SELECT statement was inserted in SYS.AUD$. However the database refuses to allow a trigger created on SYS.AUD$, telling me I shouldn't create triggers on objects owned by SYS. For other INSERT, DELETE and UPDATE statements this is no problem, because I can direct auditing to any table I wish. For SELECT statements I seem to have to use either SYS.AUD$ or the Fine Grained Auditing equivalent for column level auditing. For both the database refuses to allow triggers.

I've thought of moving AUD$ out of SYSTEM tablespace, because on some newsgroup it was said this can be done. However Oracle documentation says this is not supported.

Does anyone have any ideas how I could fire a trigger every time an entry is made to the audit trail table owned by SYS or any opinion on if I could copy audit entries to some other table by a procedure running in the background (can this be done?) and how this would effect the database? Or is it possible to move AUD$?

Thanks in advance,

Teo Laitinen

Computer science student
Department of Computer Science and Information Systems
University of Jyv�skyl�
Finland

Report message to a moderator

Re: Creating a trigger on SYS.AUD$ or moving SYS.AUD$ or some other way of... [message #51685 is a reply to message #51684]

Thu, 06 June 2002 12:30

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

i beleive there is a workaround possible.

1.  First create a local view of the sys.aud$ table
    with you required columns. (say.. view AUD).
    Make sure that u have privs over sys.aud$.

2.  Then you can create a shadow table, or a table
    where you want the audit records to be stored.
    (let it be..Table AUDIT_TABLE).

3.  now you can write an INSTEAD OF  trigger over
    this view AUD so as the records maybe inserted 
    into the shadow table AUDIT_TABLE.

Report message to a moderator

Re: Creating a trigger on SYS.AUD$ or moving SYS.AUD$ or some other way of... [message #51708 is a reply to message #51684]

Sun, 09 June 2002 11:55

Teo Laitinen
Messages: 2
Registered: June 2002

Junior Member

Hi!

Thank you Mahesh for your advice, but I had already tried this method. I even tried installing the Enterprise Edition (Oracle 8i documentation said it this version was necessary) to make INSTEAD OF triggers work, but I can't get them to fire. Here are the codes.

For the local view on SYS.AUD$:

CREATE OR REPLACE VIEW AUDIT_TRAIL ( SESSIONID,
ENTRYID, STATEMENT, TIMESTAMP#, USERID,
USERHOST, TERMINAL, ACTION#, RETURNCODE
) AS SELECT
SESSIONID, ENTRYID, STATEMENT, TIMESTAMP#, USERID, USERHOST, TERMINAL, ACTION#, RETURNCODE
FROM SYS.AUD$

And the trigger:

TRIGGER audit_copy INSTEAD OF INSERT ON AUDIT_TRAIL
BEGIN
INSERT INTO AUDITSTORE (USERNAME, OBJECTNAME) VALUES ('testing','audit_copy');
END;

I've been able to fire such trigger by inserting rows like for example:

INSERT INTO AUDIT_TRAIL (SESSIONID) VALUES (199);

But the trigger doesn't fire when a new row is inserted in the base table (SYS.AUD$). I guess the INSTEAD OF trigger is not meant for this kind of use.

Please correct me if I have understood something wrong or if there is something I can do to get this to work.

If there is no way to override the Oracle limitation of no triggers for SYS owned tables, I guess I'll have to write a stored procedure that will check SYS.AUD$ after certain time interval. Not very efficient but I can't think of anything else.

Best regards,

Teo Laitinen

Report message to a moderator

Previous Topic:	Ideas !!
Next Topic:	Re: OCP Dumps

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 17 20:29:16 CDT 2024