Highlighted
Honored Contributor.
Honored Contributor.
1234 views

Change Data Capture in DB for user role modification to track

Jump to solution

We have request to track user role modification, whenever a user role get changes from Tester to Developer or something it should be able to track from Audit log.

So suggestion has come to enable Change Data capture at DB but not sure how this may help, 

 

if anybody have knowledge on this please share. thanks in advance 🙂 

Thank you...
0 Likes
1 Solution

Accepted Solutions
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: Change Data Capture in DB for user role modification to track

Jump to solution

Maybe chept is lucky. Maybe not.

I did not test the following code, but it implements the "idea" on an Oracle database.

It may have to be debugged and anyone using this is using it at his/her own risks.

TRIGGER YOURNAME_USERS AFTER UPDATE ON USERS FOR EACH ROW 
/*Declare all the PL/SQL variables*/
DECLARE 
currDate DATE;
user varchar2(2000);
session number(10);
contextid number(10);
/*new unique ID assigned to the new row in AUDIT_LOG*/
audit_log_id INTEGER;
/*Flag - property: [US_GROUP] was updated*/
USGROUPflag_0		BOOLEAN;
BEGIN 
AUDIT_LOG_ID:= 0;
/* In this part we get the session context stored in AUDIT_PACKAGE*/
SELECT AUDIT_PACKAGE.USER_NAME into user from dual;
SELECT AUDIT_PACKAGE.SESSION_ID into session from dual;
SELECT AUDIT_PACKAGE.CONTEXT_ID into contextid from dual;
IF user IS null THEN user := 'UNKNOWN_USER';END IF;
IF session IS null THEN session := -1;END IF;
IF contextid IS null THEN contextid := -1;END IF;
SELECT sysdate INTO currDate FROM dual;

/*Check which properties were updated, and set flag variables accordingly*/
/*Find out if property: [US_GROUP] was updated*/
IF (:old.US_GROUP = :new.US_GROUP) OR (:old.US_GROUP IS NULL AND :new.US_GROUP IS NULL) THEN 
USGROUPflag_0 := FALSE;
ELSE
USGROUPflag_0 := TRUE;
END IF;
/*Now we get the unique id that was automatically created by the AUTO_AUDIT_LOG trigger*/
SELECT AUTO_AUDIT_LOG.CURRVAL INTO AUDIT_LOG_ID FROM dual;
/*Insert a row into AUDIT_LOG if any property was updated*/
IF (USGROUPflag_0) THEN
INSERT INTO AUDIT_LOG (AU_FATHER_ID,AU_USER,AU_SESSION_ID,AU_TIME,AU_ACTION,AU_ENTITY_TYPE,AU_ENTITY_ID) VALUES(contextid, user, session, currDate, 'UPDATE', 'USER', :old.US_USERNAME);
END IF;
/*Now we actually insert rows to AUDIT_PROPERTIES, for each property that was updated*/
IF (USGROUPflag_0) THEN
INSERT INTO AUDIT_PROPERTIES(AP_ACTION_ID,AP_TABLE_NAME,AP_FIELD_NAME,AP_PROPERTY_NAME,AP_OLD_VALUE,AP_NEW_VALUE) VALUES(AUDIT_LOG_ID,'USER', 'US_GROUP', 'User Groups', :old.US_GROUP, :new.US_GROUP);
END IF;
END;

 This should create a row in the AUDIT_LOG table with the name of the changer and the name of the user for which the group(s) are changed. And another line in AUDIT_PROPERTIES table with the list of groups before and after the changes.

One would then issue a SQL request on these two tables to get all the changes, possibly for a period of time or about a particular user.

View solution in original post

12 Replies
Highlighted
Honored Contributor.
Honored Contributor.

Re: Change Data Capture in DB for user role modification to track

Jump to solution

Really appreciate if anyone look into this... 🙂

Thank you...
0 Likes
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: Change Data Capture in DB for user role modification to track

Jump to solution

If you have some fields with History enabled, you can see how it is done with triggers in the database. You then can take example on these triggers to create yours on an update on the USERS table and more specifically on the US_GROUP column in that table. Of course, this would not be supported as you significantly alter the database, but I assume that such changes are not so frequent as to impede the performances of the system.

Highlighted
Honored Contributor.
Honored Contributor.

Re: Change Data Capture in DB for user role modification to track

Jump to solution

thanks Chris for picking up this.. Really appreciated

 

In my case i am looking to track user role update happening... so which Field need to have History enabled ? 

If you give me little more hint i may be able to work further i guess... 

Thank you...
0 Likes
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: Change Data Capture in DB for user role modification to track

Jump to solution

Sorry, there is no way to enable history on the group(s) to which a user belongs.

What I was suggesting is that you look in the database how history for the other fields is implemented through triggers and take example on that to create your own trigger to add entries in the audit tables. This would be totally outside of ALM as there is no user interface that will show you the entries you can make through that way in the audit tables. So, you would also have to create reports to interrogate the audit tables and show the changes that you recorded.

So, to implement what I am suggesting you need more database skills than ALM ones.

Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

Re: Change Data Capture in DB for user role modification to track

Jump to solution
Perhaps something to consider is turning off the ability to modify the user groups. For instance, in my firm's ALM implementation where we have hundreds of projects, we have standard set of user groups with restricted permissions so changes like this can be controlled/prevented.
Highlighted
Honored Contributor.
Honored Contributor.

Re: Change Data Capture in DB for user role modification to track

Jump to solution

thats great idea.. i wanted few users to have TDadmin role permission but role modification  should be blocked.... how is it done ? UI or Code changes ? 

thanks again for youre valuble time on this.

Thank you...
0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Re: Change Data Capture in DB for user role modification to track

Jump to solution

Hi Chris,

I tried contacting our DBA they have less knowledge on this, hence it came to my head now 🙂

If you have done in past kindly share me some light, with realy time example I may achive my coustomised report. 

 

Thank you...
0 Likes
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

Re: Change Data Capture in DB for user role modification to track

Jump to solution

You have to establish a standard workflow/process for example: create new user groups that will not have the permissions you don't want regular users to be able to do or regular project admins (not add of tdadmin permissions) -- you have to create new user groups because you cannot modify the system user groups defined in the ALM tool.  Then in an ALM project, the system admin would only assign the user to the project admin user group/role and not the tdadmin user group.

Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

Re: Change Data Capture in DB for user role modification to track

Jump to solution
He already explained the "idea" of how to do it. But you want it done - code given to you etc. If he replies, you're lucky 🙂
0 Likes
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: Change Data Capture in DB for user role modification to track

Jump to solution

Maybe chept is lucky. Maybe not.

I did not test the following code, but it implements the "idea" on an Oracle database.

It may have to be debugged and anyone using this is using it at his/her own risks.

TRIGGER YOURNAME_USERS AFTER UPDATE ON USERS FOR EACH ROW 
/*Declare all the PL/SQL variables*/
DECLARE 
currDate DATE;
user varchar2(2000);
session number(10);
contextid number(10);
/*new unique ID assigned to the new row in AUDIT_LOG*/
audit_log_id INTEGER;
/*Flag - property: [US_GROUP] was updated*/
USGROUPflag_0		BOOLEAN;
BEGIN 
AUDIT_LOG_ID:= 0;
/* In this part we get the session context stored in AUDIT_PACKAGE*/
SELECT AUDIT_PACKAGE.USER_NAME into user from dual;
SELECT AUDIT_PACKAGE.SESSION_ID into session from dual;
SELECT AUDIT_PACKAGE.CONTEXT_ID into contextid from dual;
IF user IS null THEN user := 'UNKNOWN_USER';END IF;
IF session IS null THEN session := -1;END IF;
IF contextid IS null THEN contextid := -1;END IF;
SELECT sysdate INTO currDate FROM dual;

/*Check which properties were updated, and set flag variables accordingly*/
/*Find out if property: [US_GROUP] was updated*/
IF (:old.US_GROUP = :new.US_GROUP) OR (:old.US_GROUP IS NULL AND :new.US_GROUP IS NULL) THEN 
USGROUPflag_0 := FALSE;
ELSE
USGROUPflag_0 := TRUE;
END IF;
/*Now we get the unique id that was automatically created by the AUTO_AUDIT_LOG trigger*/
SELECT AUTO_AUDIT_LOG.CURRVAL INTO AUDIT_LOG_ID FROM dual;
/*Insert a row into AUDIT_LOG if any property was updated*/
IF (USGROUPflag_0) THEN
INSERT INTO AUDIT_LOG (AU_FATHER_ID,AU_USER,AU_SESSION_ID,AU_TIME,AU_ACTION,AU_ENTITY_TYPE,AU_ENTITY_ID) VALUES(contextid, user, session, currDate, 'UPDATE', 'USER', :old.US_USERNAME);
END IF;
/*Now we actually insert rows to AUDIT_PROPERTIES, for each property that was updated*/
IF (USGROUPflag_0) THEN
INSERT INTO AUDIT_PROPERTIES(AP_ACTION_ID,AP_TABLE_NAME,AP_FIELD_NAME,AP_PROPERTY_NAME,AP_OLD_VALUE,AP_NEW_VALUE) VALUES(AUDIT_LOG_ID,'USER', 'US_GROUP', 'User Groups', :old.US_GROUP, :new.US_GROUP);
END IF;
END;

 This should create a row in the AUDIT_LOG table with the name of the changer and the name of the user for which the group(s) are changed. And another line in AUDIT_PROPERTIES table with the list of groups before and after the changes.

One would then issue a SQL request on these two tables to get all the changes, possibly for a period of time or about a particular user.

View solution in original post

Honored Contributor.
Honored Contributor.

Re: Change Data Capture in DB for user role modification to track

Jump to solution

Hi  Chris_Grz,

got a doubt on this,  where is the table Project_SCHEMA_DB.AUDIT_PACKAGE.USER_NAME are you reffering to ? i know its not default schema. do i need special permission in Oracle DB to view this ?

Thank you...
0 Likes
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.