Identity Manager

Determine the data relation between SAPGroup and SAPTransaction

Hi All

we do have a larger SAP environment connected to our Q1IM system. The whole environment is synchronized down to the transaction level. We now need to determine which SAP roles contain which transactions and vice versa. I did came up with the following query:

Select t.Ident_SAPTransaction, t.Description, g.GroupName, g.Description

from SAPTransaction t join SAPTransactionHasSAPAuthObject sthsao

on t.UID_SAPTransaction = sthsao.UID_SAPTransaction

join SAPGroupHasAuthObjectElem sghaoe on sghaoe.UID_SAPAuthObject = sthsao.UID_SAPAuthObject

join SAPGroup g on g.UID_SAPGroup = sghaoe.UID_SAPGroup

Does that look correct?

  • Hi Carsten,

    with your statement you will get the assignments of the transactions to the PROFILES.

    If you need the assignments to the ROLES you need 2 additional joins, e.g.:

    Select t.Ident_SAPTransaction, t.Description, gp.GroupName as 'Profile', gr.GroupName as 'Role'

    from SAPTransaction t

    join SAPTransactionHasSAPAuthObject sthsao on t.UID_SAPTransaction = sthsao.UID_SAPTransaction

    join SAPGroupHasAuthObjectElem sghaoe on sghaoe.UID_SAPAuthObject = sthsao.UID_SAPAuthObject

    join SAPGroup gp /*profile*/ on gp.UID_SAPGroup = sghaoe.UID_SAPGroup

    join SAPGroupInSAPGroup gig on gp.UID_SAPGroup = gig.UID_SAPGroupChild

    join SAPGroup gr /*role*/ on gig.UID_SAPGroupParent = gr.UID_SAPGroup

    Best regards,

    Steffen

  • Thanks for your feedback Steffen.

    Now it's "only" some performance tuning to get that running within ITShop. :-)

    Cheers

    Carsten

  • Hi Steffen

    It's me again. After implementing the query it seems like we do get incorrect results when looking for SAP-Roles having a certain transaction. The problem must be within the query, cause when i'm using one of my examples, navigate to the SAP profile that is assigned and take a look at the SAP Authorizations in the Manager, i do not see the Transaction Code i was looking for in the Query. Also our results do not comply with the search results of the SAP Admins of our customer. Any idea on that? Or would it be more sufficient to open a service request with Quest Support for such kind of assistance?

    Thanks

    Carsten

  • Hi Carsten,

    I don't understand the data structure for this (would need a little bit time - or more ).

    But I have a working solution for you.

    We already have a SQL function named "VI_F_SAP_TransactionForGroup" in the db.

    It needs one parameter: An UID_SAPGroup, this must be a SAP profile.

    If you want to know the assigned SAP transactions to one given SAP profile you can use this one, e.g.:

    select * from dbo.VI_F_SAP_TransactionForGroup('14a76512-241e-4622-936f-10cd958e4683')

    To get a complete list for all SAP profiles I implemented a wrapper for this function.

    (attached)

    You can call it with such a statement:

    select * from dbo.SE_F_SAP_TransactionForGroup()

    It returns the assignment of the SAP transactions to all SAP profiles.

    If you want to know the assignment to the SAP roles (this was the initial demand) you can use this table function for additional joins, e.g.:

    select

        phf.ident_saptransaction,

        gsr.GroupName as 'Single Role'

    from dbo.SE_f_sap_transactionforgroup() phf /*profile has function*/

    join SAPGroupInSAPGroup gig on phf.UID_SAPGroup = gig.UID_SAPGroupChild

    join SAPGroup gsr /*single role*/ on gig.UID_SAPGroupParent = gsr.UID_SAPGroup

    I hope this solves your problem.

    Best regards,

    Steffen

  • Our problem is exactly the other way round. :-)

    We're trying to implement a functionality into the ITShop where the requester can select the SAP System and Client, enter a Transaction Code (e.g. F-52 or MF52) or search for Transaction Codes using parts of the Transaction Code. Based out of those parameters we need to bring up all SAP Roles having GroupFlag = 1, having this transaction. All found SAP roles having an AccProduct will get shown in the ITShop and the user can select the appropriate role.

    Any ideas or would something like that be worth a service request?

    Thanks

    Carsten