IDM Query for Business Role Data

[former_member96398]

Experts,

I'm looking for a query that will give me: Business Role name with assigned privilege names. This is for our design sessions so we can display if you choose a certain business role which backend roles that user will receive.

I figured out in the studio how to build a query similar to access with table joins. I have two tables that contain my business roles with MSKEY and another with privilege and MSKEY.

Can someone tell me what table I need to join to for the mapping of business role to privilege.

Also, if you know.

Is there a particular flavor of SQL I need to learn for IDM? And are there any known resources for useful IDM queries?

I need things like Roles with all it's attributes, similarly users with all attributes?

Thanks for any help.

Curtis

[Murali_Shanmu · Accepted solution]

Hi,

You can issue this command on the Database to get all the Business Roles and their assignment Privileges.

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

More details can be found on Ivan's reply in this thread.

I believe plain SQL commands will help you to begin with.  Have a look at this link - http://help.sap.com/saphelp_nwidmic72/en/mc/dse_idstore_views.htm

This lists out all the Views which can be used to query the database.

Cheers,

Murali

[ivan_petrov]

Hi Curtis,

This is the SQL for Business Role to Privilege relation.

I hope it will work for 7.1 because I'm using only simple views, but if it doesn't just tell me and I'll rework it with tables instead.

SELECT r.mcmskeyvalue as "BUSSINESS ROLE", p.mcmskeyvalue as "PRIVILEGE" FROM idmv_link_simple_active

JOIN idmv_entry_simple r ON mcothermskey = r.mcmskey AND r.mcentrytype = 'MX_ROLE'

JOIN idmv_entry_simple p ON  mcthismskey = p.mcmskey AND p.mcentrytype = 'MX_PRIVILEGE'

ORDER BY r.mcmskeyvalue;

About the system in different field - well there is a system in MSKEYVALUE, but it is very hard to extract it from there. So here it is my preposition. Create a new attribute (i.e. Z_SYSTEM) and attach it to MX_PRIVILEGE entry type. On Initial load fill the system in it. This will only trigger update of privileges. and if you are ready with that, just ask me here for the select and I'll prepare one for you

Best Regards,

Ivan

[former_member2987]

Curtis,

Try working through the views rather than the base tables.  I think you'll get better results. If you let us know what version of IDM you are using, we can provide some examples. Murali has already indicated some samples from NW IDM 7.2 in an other post.

As far as the flavor of SQL, it all depends on what you are running your IDM instance are.  Current choices are Microsoft SQL, Oracle or DB2 (Running in Oracle mode)

Matt

[former_member96398]

Matt,

I'm actually working on 7.1 version running on SQL.

So that most likely explains why the idmv_link_ext2 table is not in the views.

As requirements often do mine was just changed by my boss. We are producing presentations for the business to show what systems and privileges they get with the selection of a business role.

So is there a good query or multiple queries to build a list of Business Role->system/client->Privilege?

I'm not in too bad of shape because the sys/clnt is in the privilege name, but they have requested it in a separate field.

Regards,

Curtis

[Murali_Shanmu]

Hi Curtis,

I have not worked on 7.1 system and have no information on those tables.

Cheers,

Murali

[keith_zhang]

Hello Curtis,

Maybe you can refer to section 5 of this document about 7.1 typical queries:

http://scn.sap.com/docs/DOC-16303

Hope it helps.

BR, Keith

[former_member96398]

Murali,

Thanks for the input, I tried the query and the table idmv_link_ext2 doesn't exist in my views. I just see idmv_trace_basic and trace_data.

I believe I figured it out even though the query might not be very clean. I used the design query in editor and joined three tables, custom_report_all_mx_role and custom_report_all_mx_privilege with mxi_role_structure. This is returning the correct results. The query it produced is below.

SELECT     custom_report_all_mx_role.rolemskey, custom_report_all_mx_role.rolename, custom_report_all_mx_privilege.privmskey,

custom_report_all_mx_privilege.privname

FROM         custom_report_all_mx_role INNER JOIN

                      dbo.mxi_role_structure ON custom_report_all_mx_role.rolemskey = dbo.mxi_role_structure.rParent INNER JOIN

custom_report_all_mx_privilege ON dbo.mxi_role_structure.rChild = custom_report_all_mx_privilege.privmskey

Thanks though for showing me Ivan's thread, that is also very useful.

Regards,

Curtis