AnsweredAssumed Answered

webservice findUserAccountMappings filtering options

Question asked by Andrew Beauchamp on Jan 10, 2017
Latest reply on Jan 12, 2017 by Razvan Dumitriu

Like • Show 0 Likes0
Comment • 3

Hello RSA Link,

I have an interesting problem not sure if anyone can help. I'm using a webservice call findUserAccountMappings and it can return one (or more) mapping for the an account.

My command:

Comnand: findUserAccountMappings&format=properties&returnColumns=user_id&account_id=1975

Return: user_id=2507,2528

As you can see there it is returning two users ids. Two double check, I've ran the follow database query to ensure it is a valid response.

Query the Database on the account:

select * from PV_USER_ACCOUNT_MAPPING where ACCOUNT_ID = '1975';

CREATION_DATE

ADC_ID

USER_ID

REMOVE_STATE

DELETION_DATE

IS_DELETED_USER

ACCOUNT_ID

STATE

CREATED_BY

DELETED_BY

ADD_STATE

COMMENTS

LAST_REVIEWED_DATE

25/MAY/16

2507

1975

Valid

25/JUN/16

2528

06/JAN/17

1975

Valid

I only the most recent valid mapping. This account is actively mapped to userid 2507 and was mapped to 2528 in the past.

In the database, DELETION_DATE is a null value.

For example:

findUserAccountMappings&format=properties&returnColumns=user_id,rem&account_id=1975&deletion_date=

findUserAccountMappings&format=properties&returnColumns=user_id,rem&account_id=1975&deletion_date=null

The above examples do not work.

How do I filter by DELETION_DATE when there is not date? This value is the only one available which can distinguish the two results?

For context - this command is used to reset employee passwords when the account is mapped to one user.

No one else has this question

Outcomes

Venkata Ramana Reddy Kanaparthy

Jan 11, 2017 4:33 AM

There is not a good way to filter on the deletion_date as you have noticed. The workaround may be to get the deletion_date as well apart from user_id and only consider the users without a deletion date.

1 person found this helpful

Actions

Andrew Beauchamp Jan 11, 2017 10:45 PM

Thanks for your help.

I’ve done a test and filtering on the “is_deleted_user” value will work. But to do this, I will have to modify the identity collector to stop collecting users who are terminated.

Not sure if that is the right thing to do as we don't delete users only terminate them. I'm not a fan of modifying data in flight esp. HR data. However, from a mitigation treatment point of view - if the user was "re-activated" it would be un-deleted OR if it was suddenly mapped to an active account it would come up as a violation.

What are you throughs?

Actions

Razvan Dumitriu @ Andrew Beauchamp on Jan 13, 2017 4:43 AM

Hi Andrew,

I am not quite clear about what you are trying to achieve but as far as I understand your have a problem filtering on the "deletion_date", as it expects a value to match.

One quick idea I have is to adjust the view used by the ws call, so that it will make filtering/matching easier:

CREATE OR REPLACE FORCE VIEW "AVUSER"."PV_USER_ACCOUNT_MAPPING" ("CREATION_DATE", "ADC_ID", "USER_ID", "REMOVE_STATE", "DELETION_DATE", "IS_DELETED", "IS_DELETED_USER", "ACCOUNT_ID", "STATE", "CREATED_BY", "DELETED_BY", "ADD_STATE", "COMMENTS", "CAS1", "CAS2", "CEVA", "LAST_REVIEWED_DATE", "SCOTTWX2") AS 
SELECT  CREATION_DATE, ADC_ID, USER_ID, REMOVE_STATE, 
DELETION_DATE, NVL2(DELETION_DATE,1,0) as "IS_DELETED",
IS_DELETED_USER, ACCOUNT_ID, case upper(STATE) when 'IC' then 'Incorrect' when 'VA' then 'Valid' when 'UN' then 'Unnecessary' else STATE end as "STATE", CREATED_BY, DELETED_BY, ADD_STATE, COMMENTS, CAS1, CAS2, CAS1 as "CEVA", LAST_REVIEWED_DATE, CAS2 as "SCOTTWX2"  FROM T_AV_USER_ACCOUNT_MAPPINGS   WITH READ ONLY ;

Basically you are adding a new column ("IS_DELETED") on which you will be able to filter in your ws call:

https://10.0.0.80:8443/aveksa/command.submit?cmd=findUserAccountMappings&returnMaxRows=4&format=properties&is_deleted=0

This should not break any other usage as it just adds a new column to the result set.

Just make sure when you will edit the attributes or upgrade the product that the view is not broken and you edit it again as needed.

/Razvan

Actions

3 Replies

Venkata Ramana Reddy Kanaparthy Jan 11, 2017 4:33 AM
Mark Correct
Correct Answer
There is not a good way to filter on the deletion_date as you have noticed. The workaround may be to get the deletion_date as well apart from user_id and only consider the users without a deletion date.
1 person found this helpful
Like • Show 2 Likes2
Actions
Andrew Beauchamp Jan 11, 2017 10:45 PM
Mark Correct
Correct Answer
Thanks for your help.

I’ve done a test and filtering on the “is_deleted_user” value will work. But to do this, I will have to modify the identity collector to stop collecting users who are terminated.

Not sure if that is the right thing to do as we don't delete users only terminate them. I'm not a fan of modifying data in flight esp. HR data. However, from a mitigation treatment point of view - if the user was "re-activated" it would be un-deleted OR if it was suddenly mapped to an active account it would come up as a violation.

What are you throughs?
Like • Show 0 Likes0
Actions
- Razvan Dumitriu @ Andrew Beauchamp on Jan 13, 2017 4:43 AM
  Mark Correct
  Correct Answer
  Hi Andrew,
  
  I am not quite clear about what you are trying to achieve but as far as I understand your have a problem filtering on the "deletion_date", as it expects a value to match.
  
  One quick idea I have is to adjust the view used by the ws call, so that it will make filtering/matching easier:
  CREATE OR REPLACE FORCE VIEW "AVUSER"."PV_USER_ACCOUNT_MAPPING" ("CREATION_DATE", "ADC_ID", "USER_ID", "REMOVE_STATE", "DELETION_DATE", "IS_DELETED", "IS_DELETED_USER", "ACCOUNT_ID", "STATE", "CREATED_BY", "DELETED_BY", "ADD_STATE", "COMMENTS", "CAS1", "CAS2", "CEVA", "LAST_REVIEWED_DATE", "SCOTTWX2") AS SELECT CREATION_DATE, ADC_ID, USER_ID, REMOVE_STATE, DELETION_DATE, NVL2(DELETION_DATE,1,0) as "IS_DELETED", IS_DELETED_USER, ACCOUNT_ID, case upper(STATE) when 'IC' then 'Incorrect' when 'VA' then 'Valid' when 'UN' then 'Unnecessary' else STATE end as "STATE", CREATED_BY, DELETED_BY, ADD_STATE, COMMENTS, CAS1, CAS2, CAS1 as "CEVA", LAST_REVIEWED_DATE, CAS2 as "SCOTTWX2" FROM T_AV_USER_ACCOUNT_MAPPINGS WITH READ ONLY ;
  
  Basically you are adding a new column ("IS_DELETED") on which you will be able to filter in your ws call:
  https://10.0.0.80:8443/aveksa/command.submit?cmd=findUserAccountMappings&returnMaxRows=4&format=properties&is_deleted=0
  
  This should not break any other usage as it just adds a new column to the result set.
  
  Just make sure when you will edit the attributes or upgrade the product that the view is not broken and you edit it again as needed.
  
  /Razvan
  Like • Show 2 Likes2
  Actions

webservice findUserAccountMappings filtering options

Outcomes

Related Content

Recommended Content

Incoming Links