SQL Error : insufficient privilege: Not authorized at ptime/query.... HTTP Connection to HANA Analysis Office

[Former Member]

Hi,

We currently have two environments which I will summarize below.  After a HANA db upgrade from version 95 to version 102 all views which have descriptions added to an attribute now produce an error about authorizations.

Environment Overview:

Non Prod:

• HANA DB version 102
• SBOP 4.1 SP7
• Office Analysis 2.2 Sp3.1
• Lumira 1.30

Prod:

• HANA DB version 95
• SBOP 4.1 SP7
• Office Analysis 1.4 SP7
• Lumira 1.30

It is worth noting that when making the move to Analysis 2.x and upgrading your hana system, you no longer can use ODBC to connect to HANA.  We have our non prod environment configured to use HTTP connection with SAML SSO through SBOP.  This is working great with no problems. The issue we are facing is that any attribute in a view which has a description mapped to it fails with the error: Caught exception : exception 42111: SQL Error : insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:3389 (42014)

If I connect to our non prod hana database using the odbc connection method and analysis 1.4 SP7 not issues or errors are thrown and the descirptions are displayed as normal.  If I switch to Analysis 2.2 SP3.1 and use the HTTP connection I get the authorization error which I noted above. .

I have done further testing and found Lumira 1.30 connects to non prod hana ( direct connect not HTTP) and works fine producing the attributes and descriptions with out error. Has anyone else encountered this error?  I have not found any SAP notes or blogs with this problem.  I do have a case open with SAP but currently no traction.

[Former Member · Accepted solution]

After working with SAP this issue has been deemed a bug with our current hana db revision 102.02.  SAP noted that the issue is fixed in Rev 102.06 and Rev 112.

[Former Member · Accepted solution]

Hi,

so how to solve this issue, Shall we upgrade the database? and is it a big project?

Thanks

[Former Member · Accepted solution]

We implemented the patch of Rev 102.06 and that fixed the problem and we currently are seeing no issues.  In terms of "is it a big project" that depends on your environment and number of users.  For us the patch was a simple 4 hour outage.

[former_member183326 · Accepted solution]

@ Jalal Altarah

Your issue is not a bug. Your issue is that you haven't gotten the correct user to create the Role which has the select privilege on it. Please do this first.

[Former Member · Accepted solution]

Sorry, your wrong. This is a BUG and was fixed my SAP. We had the correct roles and such.

[former_member183326 · Accepted solution]

Excuse me Chuck, read my response again. I wasn't referring to you, I was referring to Jalal. Read it again.

[Former Member · Accepted solution]

Hi,

I contact SAP and they confirm the upgrade solution.

Thanks

[Former Member]

Hi Chuck,

The issues you are facing could be either SELECT auth missing on _SYS_BIC tables or _SYS_RT tables.

I've had the same issue with HANA SPS09 Rev 96 where I found a bug with in the packaged role called INA_SERVICE_ROLE which doesn't have the SELECT on a view being used by Analysis.

Cheers

Kiran

[former_member183326]

Have you tried looking here:

Running an authorization trace you can get a better idea of the issue.

[Former Member]

Hi Michael,

Thank you for the response.  I had looked at the traces however I still come up rather short. Perhaps you could shed some light.

After following the trace steps I do indeed find an error and an object ID.

The error is: UserId(525867) is not authorized to do SELECT on ObjectId(2,0,oid=576460752360058538)

When I then do a "select * from objects where object_oid = ' 576460752360059958';" Zero rows are returned for this object ID

[former_member183326]

Have you checked the info in

2097965 - Additional setting for using the HANA Service Information Access

I am not sure if this is relevant.

It seems as though your user is not authorised to view this Oid.

Can you run this oid in the system that is working correctly and check can it be viewed there?

[Former Member]

I looked at note 2097965 (very familiar with) and we have already added these missing items to the role.  Originally we were not able to SSO without them.

You asked to run this oid in the system that is working correctly.  That is what the problem is.  Using only my non prod system ( HANA 102) and Analysis 2.2 sp3.1 which requires the HTTP connection protocol my users gets the error message. If I use my non prod system but use Analysis 1.4 sp7 which uses ODBC that user has NO error message and produces data like normal.

On another note I ran the Authorization Dependency Viewer with HANA studio using the said user and the report produced NO authorization issues ?

I logged onto the system as SYSTEM and ran the select statement for the oid in question; it fetched 0 row (s)