BOBJ data authorizations based on SAP

[Former Member]

Hi there,

I am here for some advice.

I have a Webi report running on a Universe created in IDT - Using an ODBC connection.

The users who are expected to run these reports are SAP users, with certain authorizations to limited company codes. The users have been imported from SAP into BO using SAP authentication.

I am trying to ensure that these users can only see in the reports the data that they are authorized to see in SAP. But since the connection was created using my credentials, all users are able to see everything that I can see (no restrictions to company codes)

What's the best way of doing this? I have read about row level security in IDT, but it seems to be able to only restrict for hard coded set of company codes but not by determining the authorizations based on user login.

There is NO BW/Bex layer between SAP and Webi.

There is no SSO in place either.

Hope the query is clear. Please let me know if you need further details.

Regards,

Aparna

[Former Member · Accepted solution]

Hi Aparna,

I assume that SAP maintains the company codes for the list of users. If you can work with SAP experts along with ETL developer, you should be able to build a table in your reporting database (assuming that you have access to ETL developer and that you can create new tables) that holds this mapping of users and company codes. The ETL can be scheduled to make sure that it is up to date with the data in SAP.

With the mapping table available in reporting database, you can now use this table in your universe to restrict the data by joining it with the fact (transaction) tables and using @BOUSER variable (assumed that SAP userid and BO userid are the same otherwise you need to find a way to maintain this mapping as well).

Kind Regards

Mohan

[Former Member · Accepted solution]

Thanks Mohan.

Yes - the SAP and BO user ID are the same.

Will this method work when the UserID is not part of the query? What if an user with edit access created new reports from scratch. Will the filter still be applicable?

Regards,

Aparna

[Former Member · Accepted solution]

The user id will become part of the query when you use @BOUSER variable.And it will work even when a user creates a new query from scratch, you just need to make sure that they don't have access to Edit SQL access as they would be able to get around the SQL to remove the condition and see all the records

Some reference articles

https://www.youtube.com/watch?v=X9DJuV0_vY4

http://scn.sap.com/thread/2046089

Cheers

Mohan

[Former Member · Accepted solution]

This is definitely worth a try. I was hoping there would be a more straight forward/ SAP recommended way of doing this (like creating Alias when using BW authorizations), but this seems to be the best way forward so far.

Thanks again - I'll let you know how it goes.

Regards,

Aparna

[Former Member · Accepted solution]

Update - the solution suggested by Manoj did work. I added a filter based on the BOUSER instead of creating a Data Security Profile - I hope it doesn't make any difference.

Thanks for that.

The scenario where it breaks is when the user has access to every company code (*) and obviously the system can't identify * as a valid company code in the fact table - so the net effect is same as having no access at all.

Any suggestions on how this can be tackled?

Regards,

Aparna

[Former Member · Accepted solution]

Hi Aparna,

Glad that it worked. For "*" to work, you may want to modify your join with facts in such a way that the condition is always true.

If your join condition is (FACT_TABLE.COLUMN = SECURITY_TABLE.COLUMN and SECURITY_TABLE.USER_ID = @BOUSER()), change it as below,

((FACT_TABLE.COLUMN = SECURITY_TABLE.COLUMN or '*' = SECURITY_TABLE.COLUMN)and SECURITY_TABLE.USER_ID = @BOUSER())

This will ensure that every row is selected when the USER_ID has '*' as the restriction.

Cheers

Mohan

[Former Member]

Hi,

perhaps this could help

Regards,

Rogerio

[Former Member]

Thanks Rogerio.

My concern is that once these reports are delivered and the users and their access keeps changing, there shouldn't be a need to manually do anything to keep BOBJ auths in sync with that in SAP.

It would be ideal if BO could read the auths at run time and determine which company codes to show to the user.

Regards,

Aparna