Audit Trail for Administrative activities

Idea ID 2695655

Audit Trail for Administrative activities

Briefly describe your idea:

PPM should implement audit trail logging for administrative activities like changes to Users and Security Model, Access Grants, etc. The audit trail should allow administrators/investigators to trace changes to the Security Model, when the changes took place and which user executed the changes.

Why is this important, when it would be used?

When unexpected transactions are discovered, investigators will need to rely on audit trail to find out whether the executor of the transactions had been given the access to perform the transaction, when was the access given and given by which user. Another scenario is when a user discovered that he or she no longer has access to certain modules or functions of PPM; administrator will need to find out changes to the access via the Audit trail.

If you were designing the solution, how would you do it?

I would want to capture the user-id used to execute the transaction, the timestamp when the transaction takes place, the operation and the details associated with the operation including the before and after values. 

5 Comments
Micro Focus Expert
Micro Focus Expert
Status changed to: Waiting for Votes

"Temper proof" would need to be further defined, it can mean many things.

Also, this might result in a huge amount of audit logs if synchronizing users security from LDAP with a report scheduled to run relatively often. 

Still, a very valid idea, so opening for votes.

New Member.. Kiran Kandikonda
New Member..

For this the HP has opened an enhancement request QCCR1L54899. But not implemented. can you let us know in which version or in which year implementing this functionality in PPM

Micro Focus Expert
Micro Focus Expert

Hi Kiran,

There's no ETA for when this ER will be implemented. This ER is already getting a significant number of votes so it's on the right way to make its way to our backlog if it keeps gaining traction.

If you need it right now it's relatively trivial to create DB Triggers monitoring & recording any changes in the security tables.

LAST_UPDATED_BY column can be used to capture user that made the operation.

New Member.. Kiran Kandikonda
New Member..
HI Etienne,



Can you please provide a sample SQL query, if you using for audit trial from PPM DB.



Thanks,

Kiran


Micro Focus Expert
Micro Focus Expert

One basic trigger to insert a row into ITG_AUDIT_EVENTS upon new user creation could look like that:

create or replace 
trigger TRG_KNTA_USER_INSERT 
AFTER INSERT ON KNTA_USERS
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO ITG_AUDIT_EVENTS
    (
      AUDIT_EVENT_ID,
      EVENT_TYPE_CODE,
      CREATED_BY,
      LAST_UPDATED_BY,
      USER_ID,
      EVENT_DATE,
      CREATION_DATE,
      LAST_UPDATE_DATE,
      MESSAGE
    )
    VALUES
    (
      ITG_AUDIT_EVENTS_S.nextval,
      15,
      NVL(:NEW.CREATED_BY, -1),
      NVL(:NEW.CREATED_BY, -1),
      NVL(:NEW.CREATED_BY, -1),
      SYSDATE,
      SYSDATE,
      SYSDATE,
      'User ID '  || NVL(:NEW.CREATED_BY, -1)   || ' has created user with username '   || :NEW.USERNAME
    );
END TRG_KNTA_USER_INSERT;

Thanks,

Etienne.

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.