Determine the data relation between SAPGroup and SAPTransaction

Question

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?

Steffen.Einert · Answer

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

Steffen.Einert · Answer

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

Endpoint Privilege Management

Determine the data relation between SAPGroup and SAPTransaction