JDBC Event Log - unwanted loopback


I'm trying to create an event log entry for the connected database to
process. For example, the staging table is updated with changes from the
Identity Vault and an event log entry is created as a pointer to the
changed row.
The same event log is being used to publish data from a staging table
into the Identity Vault.
The document says two things about this.
If you use the "driver's database username" in the perpetrator column,
1) the driver will not process the event log entry via the Publisher
channel unless 2) the "Allow Loopback" parameter is set to true.

Unfortunately, even though I've used what I think is the username, and
"Allow Loopback" is false, the driver Publisher sets the status to 'S'
and deletes the entry.

Any suggestions?
By "driver database username" I presume it means the username in the
"authentication" section of the driver config?

Database is Oracle 11g. IDM Driver version is 3.5.9 with latest Oracle
JDBC. IDM is 4.0.2


--
SFDavies
------------------------------------------------------------------------
SFDavies's Profile: https://forums.netiq.com/member.php?userid=866
View this thread: https://forums.netiq.com/showthread.php?t=55787

  • On 4/28/2016 11:44 AM, SFDavies wrote:
    >
    > I'm trying to create an event log entry for the connected database to
    > process. For example, the staging table is updated with changes from the
    > Identity Vault and an event log entry is created as a pointer to the
    > changed row.
    > The same event log is being used to publish data from a staging table
    > into the Identity Vault.
    > The document says two things about this.
    > If you use the "driver's database username" in the perpetrator column,
    > 1) the driver will not process the event log entry via the Publisher
    > channel unless 2) the "Allow Loopback" parameter is set to true.
    >
    > Unfortunately, even though I've used what I think is the username, and
    > "Allow Loopback" is false, the driver Publisher sets the status to 'S'
    > and deletes the entry.
    >
    > Any suggestions?
    > By "driver database username" I presume it means the username in the
    > "authentication" section of the driver config?
    >
    > Database is Oracle 11g. IDM Driver version is 3.5.9 with latest Oracle
    > JDBC. IDM is 4.0.2
    >
    >


    Your understanding is correct. I'm not aware of any bugs related to that feature (ever).
    It must be a mismatch in the configs. Set the trace level to 5 on the driver, stop the driver, delete the trace, and then
    start it up so you have a nice clean start.

    You should see the driver initialization parameters that include the configured database admin name. That string needs to
    match exactly to what is in your perpetrator column. Looking at the trace as opposed to the config should clear up any
    confusion. Keep in mind that depending on how your Oracle was installed the name MAY be case sensitive. I believe when the
    driver does the event log query it is actually using a where clause to exclude that perp name so it needs to be exactly the same.

    If that still doesn't do it, post the driver startup and then post a level 5 trace of the polling event.
    We can pursue it further from there.

    --
    -----------------------------------------------------------------------
    Will Schneider
    Knowledge Partner http://forums.netiq.com

    If you find this post helpful, please click on the star below.

  • I've obtained some new information. Setting Trace to 5 I got this in the
    startup:

    [04/29/16 08:38:30.944]:BODSDEV PT:BEGIN Get database properties.
    [04/29/16 08:38:30.944]:BODSDEV PT: Database name: Oracle
    [04/29/16 08:38:30.944]:BODSDEV PT: Database version: Oracle Database
    11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    [04/29/16 08:38:30.944]:BODSDEV PT: JDBC driver name: Oracle JDBC
    driver
    [04/29/16 08:38:30.944]:BODSDEV PT: JDBC driver version: 11.2.0.4.0
    [04/29/16 08:38:30.944]:BODSDEV PT: JDBC driver major version: 11
    [04/29/16 08:38:30.944]:BODSDEV PT: JDBC driver minor version: 2
    [04/29/16 08:38:30.944]:BODSDEV PT: Case sensitive: false
    [04/29/16 08:38:30.944]:BODSDEV PT: Stored case: upper
    [04/29/16 08:38:30.944]:BODSDEV PT: Association case: upper
    [04/29/16 08:38:30.944]:BODSDEV PT: Username: 'LBU_STAGE_ADMIN'

    Then I found this in the trace:

    [04/29/16 09:09:04.398]:BODSDEV PT:SELECT * FROM IDVAULT_EVENTLOG WHERE
    STATUS = 'N' AND ROWNUM <= 1 ORDER BY RECORD_ID ASC
    [04/29/16 09:09:04.413]:BODSDEV PT: Allocated result set 56e58587 from
    statement 3797d6f9.
    [04/29/16 09:09:04.413]:BODSDEV PT:END Query for events.
    [04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 1, field 'RECORD_ID',
    value = 43279
    [04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 2, field 'TABLE_KEY',
    length: 9, value = 'ID=519925'
    [04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 3, field 'STATUS',
    length: 1, value = 'N'
    [04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 4, field 'EVENT_TYPE',
    value = 5
    [04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 6, field 'PERPETRATOR',
    length: 15, value = 'LBU_STAGE_ADMIN'
    [04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 7, field 'TABLE_NAME',
    length: 12, value = 'IDVAULT_DATA'
    [04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 8, field 'COLUMN_NAME',
    value = null
    [04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 9, field 'OLD_VALUE',
    value = null
    [04/29/16 09:09:04.413]:BODSDEV PT: RS @ index 10, field 'NEW_VALUE',
    value = null
    [04/29/16 09:09:04.413]:BODSDEV PT:BEGIN Row.
    [04/29/16 09:09:04.413]:BODSDEV PT: RECORD_ID: 43279
    [04/29/16 09:09:04.413]:BODSDEV PT: TABLE_KEY: 'ID=519925'
    [04/29/16 09:09:04.413]:BODSDEV PT: STATUS: 'N'
    [04/29/16 09:09:04.413]:BODSDEV PT: EVENT_TYPE: 5; insert row
    (query-back)
    [04/29/16 09:09:04.413]:BODSDEV PT: PERPETRATOR: 'LBU_STAGE_ADMIN'
    [04/29/16 09:09:04.413]:BODSDEV PT: TABLE_NAME: 'IDVAULT_DATA'
    [04/29/16 09:09:04.413]:BODSDEV PT: COLUMN_NAME: NULL
    [04/29/16 09:09:04.413]:BODSDEV PT: OLD_VALUE: NULL
    [04/29/16 09:09:04.413]:BODSDEV PT: NEW_VALUE: NULL
    [04/29/16 09:09:04.413]:BODSDEV PT:END Row.
    [04/29/16 09:09:04.413]:BODSDEV PT:Loopback row 43279.
    [04/29/16 09:09:04.413]:BODSDEV PT: Closed result set 56e58587.
    [04/29/16 09:09:04.413]:BODSDEV PT:Have no events to submit.
    [04/29/16 09:09:04.429]:BODSDEV PT:BEGIN Transaction
    [04/29/16 09:09:04.429]:BODSDEV PT: Not locking.
    [04/29/16 09:09:04.429]:BODSDEV PT: BEGIN Batch
    [04/29/16 09:09:04.429]:BODSDEV PT: UPDATE IDVAULT_EVENTLOG SET
    STATUS = 'S' WHERE RECORD_ID = 43279
    [04/29/16 09:09:04.429]:BODSDEV PT: END Batch
    [04/29/16 09:09:04.429]:BODSDEV PT: COMMIT
    [04/29/16 09:09:04.429]:BODSDEV PT:END Transaction

    So the username matches and indeed the driver says "nothing to do here"
    but STILL sets the status anyway!!


    Will Schneider;267349 Wrote:
    > On 4/28/2016 11:44 AM, SFDavies wrote:
    > >
    > > I'm trying to create an event log entry for the connected database to
    > > process. For example, the staging table is updated with changes from

    > the
    > > Identity Vault and an event log entry is created as a pointer to the
    > > changed row.
    > > The same event log is being used to publish data from a staging table
    > > into the Identity Vault.
    > > The document says two things about this.
    > > If you use the "driver's database username" in the perpetrator

    > column,
    > > 1) the driver will not process the event log entry via the Publisher
    > > channel unless 2) the "Allow Loopback" parameter is set to true.
    > >
    > > Unfortunately, even though I've used what I think is the username,

    > and
    > > "Allow Loopback" is false, the driver Publisher sets the status to

    > 'S'
    > > and deletes the entry.
    > >
    > > Any suggestions?
    > > By "driver database username" I presume it means the username in the
    > > "authentication" section of the driver config?
    > >
    > > Database is Oracle 11g. IDM Driver version is 3.5.9 with latest

    > Oracle
    > > JDBC. IDM is 4.0.2
    > >
    > >

    >
    > Your understanding is correct. I'm not aware of any bugs related to
    > that feature (ever).
    > It must be a mismatch in the configs. Set the trace level to 5 on the
    > driver, stop the driver, delete the trace, and then
    > start it up so you have a nice clean start.
    >
    > You should see the driver initialization parameters that include the
    > configured database admin name. That string needs to
    > match exactly to what is in your perpetrator column. Looking at the
    > trace as opposed to the config should clear up any
    > confusion. Keep in mind that depending on how your Oracle was installed
    > the name MAY be case sensitive. I believe when the
    > driver does the event log query it is actually using a where clause to
    > exclude that perp name so it needs to be exactly the same.
    >
    > If that still doesn't do it, post the driver startup and then post a
    > level 5 trace of the polling event.
    > We can pursue it further from there.
    >
    > --
    > -----------------------------------------------------------------------
    > Will Schneider
    > Knowledge Partner http://forums.netiq.com
    >
    > If you find this post helpful, please click on the star below.



    --
    SFDavies
    ------------------------------------------------------------------------
    SFDavies's Profile: https://forums.netiq.com/member.php?userid=866
    View this thread: https://forums.netiq.com/showthread.php?t=55787

  • On 4/29/2016 3:24 AM, SFDavies wrote:
    > So the username matches and indeed the driver says "nothing to do here"
    > but STILL sets the status anyway!!


    Ohhhh, yes. That is the expected behavior.
    Since you are wanting to ignore the loopback event this is the desired outcome.
    What are you hoping the event would do?
    Since you are using queryback events there really isn't anything useful in the event log for this user.
    If you want to capture events so you can look at history there is a setting for that too where the value won't be deleted
    from the log and instead will be moved to the _PROCESSED table. There is a stored procedure to do that and a driver config
    setting.

    Does that address your need? Or what problem are you trying to solve then?

    --
    -----------------------------------------------------------------------
    Will Schneider
    Knowledge Partner http://forums.netiq.com

    If you find this post helpful, please click on the star below.
  • IDM 4.6.4 | Driver: JDBC | Database: Oracle 12c | Publication mode: Triggered 

    Issue: Unable to get events from Event Log Table thru publisher channel. Though there are new events, the remote loader log says "Have no events to submit." as below.

    DirXML: [11/29/19 18:39:05.09]: TRACE:  BEGIN Row.
    DirXML: [11/29/19 18:39:05.09]: TRACE:                RECORD_ID:    1197
    DirXML: [11/29/19 18:39:05.09]: TRACE:                TABLE_KEY:    'LOCATIONKEY=617'
    DirXML: [11/29/19 18:39:05.09]: TRACE:                STATUS:       'N'
    DirXML: [11/29/19 18:39:05.09]: TRACE:                EVENT_TYPE:   6; update row (query-back)
    DirXML: [11/29/19 18:39:05.09]: TRACE:                TABLE_NAME:   'LOCATIONS'
    DirXML: [11/29/19 18:39:05.09]: TRACE:                COLUMN_NAME:  'Street'
    DirXML: [11/29/19 18:39:05.09]: TRACE:                OLD_VALUE:    'King'
    DirXML: [11/29/19 18:39:05.09]: TRACE:                NEW_VALUE:    'Queen'
    DirXML: [11/29/19 18:39:05.09]: TRACE:                PERPETRATOR:  NULL
    DirXML: [11/29/19 18:39:05.09]: TRACE:  END   Row.
    DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLING ResultSet.next()
    DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLED
    DirXML: [11/29/19 18:39:05.09]: TRACE:  RETURNED VALUE:  false
    DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLING ResultSet.close()
    DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLED
    DirXML: [11/29/19 18:39:05.09]: TRACE:                Closed    result set 49bc1d2c.
    DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLING PreparedStatement.clearParameters()
    DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLED
    DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLING Statement.ClearWarnings()
    DirXML: [11/29/19 18:39:05.09]: TRACE:  CALLED
    DirXML: [11/29/19 18:39:05.09]: TRACE:  Have no events to submit.
    DirXML: [11/29/19 18:39:05.09]: TRACE:  Remote Loader: Received document from publication shim.
    DirXML: [11/29/19 18:39:05.10]: TRACE:  <nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
         

     

    Can anyone help please?  Highly appreciate your time and cooperation

  • Stupid queston:  Is the table/column in the Filter?

  • Yes, they are in filter

    Thanks for checking