How to use a filter on the access control for a form?

[former_member557511]

Hi IdM Experts!

I tried to set up a filter in version 8 on a form's access control tab to limit which users can be selected. It is required that only users which are not disabled (MX_Disable <> 1) can be selected for the task. Unfortunately the filter does not seem to work, and even if I create the SQL statement myself it is not any better. If the filter (or SQL statement) is applied I cannot select any user, not even those which would be allowed.

Has anyone experienced the same thing?

Thanks for any help in advance!

zkormany

[Steffi_Warnecke · Accepted solution]

@Zoltan & @ Deva,

you guys use a lot of joins and views (e.g. vallink for a normal attribute) with too much info. And it looks like you never came across "not in".

select mcmskey as mskey from idmv_entry_simple with (nolock) where mcentrytype = 'MX_PERSON'
and mcmskey not in (select mskey from idmv_value_basic with (nolock) where attrname = 'MX_DISABLED')

.

The needed query does not seem too complicated to me. Or am I missing something?

.

Regards,

Steffi.

[former_member557511 · Accepted solution]

Hi Steffi,

that solved it. I don't know why but your query gets accepted by the filter and my previous one was not. Maybe it can't interpret joins?

Anyhow thank you for your help!

Kind regards,

Zoltan

[Steffi_Warnecke · Accepted solution]

You're welcome. 🙂

Try to create the simplest query possible. The access control is a bit sensitive, not everything works, that should work. It's the same with IDM 7.2 (which we use).

.

Regards,

Steffi.

PS: If you don't reply to my post, I won't get an info, that there is a reply. ^^ So I changed your answer to a comment on my answer.

[former_member557511]

Hi Deva,

sorry for the late reply. Here is the SQL Query generated by the filter:

SELECT DISTINCT(mcmskey) FROM idmv_entry_simple B WITH(NOLOCK) INNER JOIN idmv_vna M5 ON B.mcmskey = M5.mskey where mcIDStore=1 and ((M5.ATTRNAME = 'MX_DISABLED' AND M5.sval <> '1'))

It does not work (it would be another topic why the query builder behaves like this) because it does not give any users back, so I created a query by myself:

select distinct(a.MSKEY) FROM idmv_bw_current_values a left join idmv_bw_current_values b on a.mskey = b.mskey and b.AttrName = 'MX_DISABLED' where b.mskey is null and a.EntryType = 'MX_PERSON'

Unfortunately that query does not work either, none of the user can be selected on the UI.

Kind regards,

Zoltan

[devaprakash_b]

Hi Zoltan,

Can you please try this query

select distinct dis.mskey 
from idmv_entry_simple per with (nolock)
inner join idmv_vallink_basic dis with (nolock) on per.mcmskey = dis.mskey
where per.mcentrytype = 'MX_PERSON' and per.mcidstore = 1 
and dis.mcattrname='MX_DISABLED' and dis.mcsearchvalue<>'1'

or
select distinct (mskey) from idmv_vallink_basic with (nolock) 
where mcattrname ='MX_DISABLED' and mcsearchvalue <> '1' and mskey in (select distinct mcmskey from 
idmv_entry_simple with (nolock) where mcidstore=1 and mcentrytype='MX_PERSON')

run the above query in database and see if you are getting any results. If yes, then go to the form, under access control tab and configure a below

Select Filter option for the On Behalf Of field and paste the above mentioned query in the filter box. If still not working, kindly paste the screen shot of the form.

Regards,

Deva

[former_member557511]

Hi Deva,

your queries deliver inactive entries (MX_DISABLED = 1). What I would need is to query active users only (the attribute MX_DISABLED does not exist for such users). The SQL-query I posted above correctly delivers the active users, however if I set this SQL-query as a filter none of the users can be selected.

Kind regards,

Zoltan

[devaprakash_b]

Sorry Zoltan,

I have edited my query, can you kindly paste the screenshot of the access control tab of your form.

[former_member557511]

Hi Deva,

your queries do not work. The reason is that if a user is active then the attribute MX_DISABLED is not mapped to him at all. This means that if I take your second query for example already the first part does not bring any results:

select distinct (mskey) from idmv_vallink_basic with (nolock) 
where mcattrname ='MX_DISABLED' and mcsearchvalue <> '1'

-> resultset: 0

You can try it on any installation of SAP IDM it will be the same, you won't get any results.

What works however is this query:

select distinct(a.MSKEY) FROM idmv_bw_current_values a left join idmv_bw_current_values b on a.mskey = b.mskey and b.AttrName = 'MX_DISABLED' where b.mskey is null and a.EntryType = 'MX_PERSON'

Unfortunately if you use this query in a filter on the access control tab of a form it does not work.

Kind regards,

Zoltan

[devaprakash_b]

hi Zoltan,

can you please share the SQL query and provide screenshots to analyse and resolve your issue.

Regards,

Deva