Visual Studio, SSRS, Oracle sys_context('USERENV', 'OS_USER')

Question

I am using Visual Studio 2012, Oracle 11g and SSRS 2012.

I have developed a report that checks the session USERID to determine if they can view the report or not against a table stored in an Oracle DB.

Everything works correctly in Preview mode for Visual Studio 2012; however, when the report is deployed to SSRS 2012 data is not returned, and this would be expected if the US.

SELECT COMP_FLAG, MBR_MMIS_IDNTFR, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ORG_CODE, ORG_NAME, ORG_PHONE, LAST_VERIFIED_DATE, RAC_START,RAC_END, RAC_CODE   
FROM  TPL_CHIP_MV
WHERE (COMP_FLAG <> 'N')
AND (1 =(SELECT COUNT(USER_ID) AS EXPR1
          FROM REPORT_AUTH
          WHERE (USER_ID = CONCAT('HLAN\',UPPER (SYS_CONTEXT'USERENV', 'OS_USER'))))))

Why does this work correctly in Visual Studio 2012 Preview Mode and not in SSRS 2012? What can I do to get this to work in SSRS?

I have reformatted for you using the code formatting button. — Brian Tompsett - 汤莱恩

Justin Cave Justin Cave · Accepted Answer · 2015-05-29T20:59:53

The problem is almost certainly that the os_user is different.

When you're running locally in preview mode, the account that is running Visual Studio is almost certainly your own personal account. So the os_user that the database sees is your personal operating system user. When you're deploying to the SSRS server, the operating system account that is running SSRS is almost certainly some service account. So the os_user that the database sees is the service account.

I doubt that you'd be able to use any of the USERENV context variables to limit access to certain rows when the report is deployed to SSRS. You'd need SSRS to tell the database who the end user is. Most likely, you can do this by looking at the User!UserID property within the report assuming that you've configured SSRS to use Windows authentication. Since that returns the domain in addition to the username, you may need to modify your lookup table to include the domain to match against.

Visual Studio, SSRS, Oracle sys_context('USERENV', 'OS_USER')

1 Answers