Results 1 to 4 of 4

Thread: Audit Trigger Fails

  1. #1

    Audit Trigger Fails

    Greetings,

    We recently added CFR audit for 20 tables on ur XE 7333 installation.

    One of the tables F3112 has an issue when R31422 runs, the logs show that it happens during the CFR Update trigger execution.
    The pointed line is the following query:

    select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);

    THis does not happen when user submits the job, it happens on scheduled runs only.

    Does anyone have an insight into how the Sessions are generated in the DB that could cause this problem?
    Looking at the query, audsid shouldl be unique, and there is no reason it would pickup more than 1 record. So, somehow AUDSID duplicates.
    (error is, indeed, that the query fetches more than 1 line. trigger does not have exception handling).

  2. #2
    Senior Member peterbruce's Avatar
    Join Date
    Jan 2004
    Location
    NSW Australia
    Posts
    2,147
    erodr154,

    It might be handy to know what your database is.

    Do you run run two or more jobs on the scheduler at the same time, using the same user ID?
    Thanks, Peter

    "Give a person a fish, feed them for a day, teach them how to fish, feed them for a lifetime."

    E9.1 TR9.1.2.1, Enterprise Server: Sun, Database Server: Sun, Oracle DB: 11g, Weblogic.
    Create!form 7

  3. #3
    Member
    Join Date
    Dec 2000
    Location
    Australia
    Posts
    584
    Hi,

    Based on the query you have posted you are running on Oracle. I was surprised you could get two records for a query based on userenv('sessionid') so I did a bit of research. It seems that AUDSID in V$SESSION can be 0 when connecting as an internally generated SYS session or on some older Oracle releases when AUDIT TRAIL is not turned on. But by older version I mean Oracle 7 and below which even with XE I don't think was ever supported. Further, I would not expect JDE to be establishing a connect internal session unless there is a script running with CONNECT / AS SYSDBA or similar. A UBE would certainly not be doing that. (?)

    See "How Sessions get Their AUDSID Identifier (Doc ID 122230.1)" on Oracle support. AUDSID's should be unique for the internal connections which will be 0 or UB4MAX(4294967295) for direct SYS connections. The sequence that the AUDSID is taken from is SYS.AUDSES$ which wraps at 2,000,000,000 but that also couldn't account for duplicated AUDSID.

    So unless the scheduled job is somehow connecting as SYSDBA I don't see how there could be duplicate rows for that query. If this is reproducible in your non-production environment I would probably modify the trigger to issue a query like:

    select sid, audsid, user from v$session where audsid = userenv('sessionid') and user in (select user from dual);

    and write out the results through a separate function running an AUTONOMOUS TRANSACTION so that you can log into a table what records are actually coming back without that logging insert being rolled back when the trigger errors.
    Justin Miller

  4. #4
    Thanks for the replies.

    I was not able to replicate the issue in other environments.
    It is kind of hard to understand if it is running as SYS, i would assume that in that case, every execution would error out for any UBE that touches a table being audited.

    I did read about the Sessions in the database, and i agree there should never be duplicated except 0 or UB4MAX.

    I would belive that:
    A) something is wrong on a database level
    B) A setup from JDE is not properly done for auditing F3112 specifically

    DBA have not been especially helpful and they keep saying it is an expected error given that query, and that query should be changed

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.