idmv_value_basic_all View

[Former Member]

Hi,

Can someone please show me the SQL statement to access the below

(1) List all the Business Roles in the system

(2) List all the Business Roles and its Privileges

(3) List all the Users and their Business Roles

Thanks,

[ivan_petrov · Accepted solution]

Hi Rick,

Here they are:

-- all business roles

SELECT avalue FROM idmv_value_basic_all WHERE attrname='MSKEYVALUE' AND mskey IN (SELECT mskey FROM idmv_value_basic_all WHERE attrname='MX_ENTRYTYPE' AND avalue='MX_ROLE') ORDER BY mskey;

-- all business roles with their privileges

SELECT mcothermskeyvalue,mcthismskeyvalue FROM idmv_link_ext2 WHERE mcthisocname='MX_PRIVILEGE' AND mcotherocname='MX_ROLE' ORDER BY mcothermskeyvalue;

-- all users with their business roles

SELECT mcthismskeyvalue,mcothermskeyvalue FROM idmv_link_ext2 WHERE mcthisocname='MX_PERSON' AND mcotherocname='MX_ROLE' ORDER BY mcthismskeyvalue;

Best Regards,

Ivan

[Former Member · Accepted solution]

Thanks Ivan.

This is very helpful.

Do you execute these commnds via SQL Server client ? The reason I am asking this, is beacuse in my current implementation, access to the SQL is restricted to only BASIS Team. I am really struggling without this access.

Could you please let me know in general how this is handled in other Implementations ?

Thank you.

Rick

[ivan_petrov · Accepted solution]

What type of database you are using (Oracle, MSSQL, etc.) ?

[Former Member · Accepted solution]

Its MS-SQL database.  What difference does that make?

[former_member2987 · Accepted solution]

Rick,

Access to the database via MXMC_RT for running queries is essential in IDM development.  I would with your DBA and BASIS teams to get this access.  Point out exactly how much SQL Querying is needed with this product and it is essential for testing and developing queries, unless of course they would like to dedicate that much of their time to your project (I'm guessing a week of asking them to run queries to meet your deadlines as well as their own will help influence this )

If however, you really can't get the access you can "simulate" access to the client by running to ASCII passes with your queries in the from tab.

Good luck!

Matt

[ivan_petrov · Accepted solution]

Hi Rick,

I've asked, in order to propose a tool for SQLs and after it is MSSQL you'd better use MS SQL Studio.

And Yes you can execute SQLs from there.

I don't know why you have restricted access to IDM DB. There is simply no good reason about it. After all the IDM tables are in separate DB space and you will have access only on IDM tables if you can use MXMC_RT and MXMC_OPER users. As Matt said it is essential to have access to at least this 2 DB users.

Here there are several reasons for instance:

- All SQLs you are using in IDM should be build and tested in MSSQL Studio.

- I have a bunch of SQLs just for monitoring results from task execution

- You should monitor at least provisioning queue, dirty records table, active records (multiple views) , active links(multiple views), audit records(multiple views), etc.

I can not imagine to work on IDM project without this access.

Best Regards,

Ivan

Best Regards,

Ivan

[Former Member · Accepted solution]

Thanks Ivan. Yes are absolutely correct. I will have to persuade and get access.

[Former Member · Accepted solution]

Thanks Matt,

Yes, I agree.

Just as you mentioned, I have been nagging the BASIS with repeated requested. They will soon get frustrated as I would be consuming their time too.

Meanwhile, I shall use the To ASCII pass as you suggested.,

Thanks again.

[Former Member · Accepted solution]

In IdM 7.2 you can get entries directly from the idmv_entry_simple view which is a representation of the mxi_entry table. No need to access the vallink view which uses the mxi_values table and joins with mxi_attributes & mxi_link tables.

select mcMSKEYVALUE,mcDisplayName from idmv_entry_simple where mcEntryType = 'MX_PERSON'

Also use WITH (NOLOCK) on SQL Server, especially on sources of TO IDSTORE passes when using IDMV_VALLINK views or you can accidentally lock the rows you want to update in the To Pass and deadlock yourself.

[ivan_petrov · Accepted solution]

Hi Per,

As far as I remember the view idmv_value_basic_all was mentioned in original question. That is why I've used it.

But you are right that for list of users only, using idmv_entry_simple is much better

Best Regards,

Ivan

[Former Member · Accepted solution]

Hi Ivan,

Sometimes I have a hard time of leave the habits of developing procedures in the IdM the product behind 🙂 Just to illustrate some of what we do during development of the product to check performance and how much it matters even on a small set of 117 entries (and by no means is this accurate for all cases or even of importance when doing one off queries):

select mcDisplayName from idmv_entry_simple_all where mcEntryType = 'MX_PERSON'
(117 row(s) affected)
Table 'mxi_entry'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

VS

select mcValue from idmv_vallink_basic where mcAttrName = 'DISPLAYNAME' and mcMSKEY in
(select mcMSKEY from idmv_vallink_basic where mcAttrName = 'MX_ENTRYTYPE' and mcsearchvalue = 'MX_PERSON')

(117 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mxi_link'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MXI_Attributes'. Scan count 4, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MXI_VALUES'. Scan count 2, logical reads 683, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 87 ms.

These are the kind of thoughts that go through the head of atleast this IdM developer when seeing queries and as such I thought I'd mention the other view 🙂 More importantly though is to avoid using avalue='MX_ROLE'. Avalue = <something> is a big no because the avalue column is not indexed.

As such the execution cost of 1) using searchvalue = ... is 25% of 2) which is using avalue =  <> . Incidentally, the execution cost of the idmv_entry view query is 25% of 1) again, which was kind of amusing.

1)

select avalue from idmv_value_basic_all where AttrName = 'DISPLAYNAME' and MSKEY in
(select MSKEY from idmv_value_basic_all where AttrName = 'MX_ENTRYTYPE' and SearchValue = 'MX_PERSON')

2)

select avalue from idmv_value_basic_all where AttrName = 'DISPLAYNAME' and MSKEY in
(select MSKEY from idmv_value_basic_all where AttrName = 'MX_ENTRYTYPE' and avalue = 'MX_PERSON')

This might have become some sort of an obsession from working on the IdM product for some years, so consider this a little insight into what goes on into the development of the procedures of the product you're using 🙂

Best regards

Chris (SAP IdM developer)

[Former Member · Accepted solution]

Thanks Chris.

[ivan_petrov · Accepted solution]

Hi Per,

Well thank you for the info, only as I told you, I already know that. But the request was to bring out the users from one certain view, that is why I did it that way

And then I've used idmv_link_ext2 view for the next selects

Best Regards,

Ivan