Idea ID: 2695655

Audit Trail for Administrative activities

Status : Delivered
over 1 year ago

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. 

Tags:

  • - The Changes to Security Groups are already logged to table KNTA_ACCESS_SECURITY_HIST

    - The changes to assignments of Security groups to users are already logged in table KNTA_USER_SECURITY_HIST

    In next PPM version, following this idea, we are adding a feature that any user field modification will be logged to ITG_AUDIT_LOG table. One entry will be created for each modified field.

  • 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.

  • HI Etienne,



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



    Thanks,

    Kiran


  • 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.