Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Feb 15 at 09:49 AM

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

    Add comment
    10|10000 characters needed characters exceeded

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

  • Jan 22 at 08:00 AM

    hi Zoltan,

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

    Regards,

    Deva

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 06 at 02:11 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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