SAP DBTech JDBC: [258]: insufficient privilege ... again!

[Former Member]

I have found plenty of threads on the subject of "SAP DBTech JDBC: [258]: insufficient privilege", but nothing that seems to be helpful for my situation

The following statement on compposite provider 'COM_PROV_XYZ': 

select * from "_SYS_BIC"."system-local.bw.bw2hana/COM_PROV_XYZ";

fails with the message: 

Could not execute 'select * from "_SYS_BIC"."system-local.bw.bw2hana/COM_PROV_XYZ"'

SAP DBTech JDBC: [258]: insufficient privilege

The following select on calc view 'YYY' works though:

select * from "_SYS_BIC"."ProjectX/YYY"

Which agrees with the fact that the user has been granted roles with the followin auths:

(also the case for _SYS_BIC and _SYS_REPO)

Also:

Any ideas out there?

[former_member183326 · Accepted solution]

Hello,

Have you tried running a trace whilst reproducing the error?

Please see the Troubleshooting Authorizations Guide for information on how to activate the trace.

Please paste trace information here so we can have a look.

[Former Member · Accepted solution]

thanks for the link to the great guide of yours.

Below you find the trace:

[14433]{362618}[1901/-1] 2016-02-25 09:25:26.656715 i TraceContext     TraceContext.cpp(00923) : UserName=XXX, ApplicationUserName=thb_hem, ApplicationName=HDBStudio, ApplicationSource=csns.sql.editor.SQLExecuteFormEditor$2$1.run(SQLExecuteFormEditor.java:856);

[14433]{362618}[1901/-1] 2016-02-25 09:25:26.656708 i Authorization    qo_rewrite_rules.cc(00267) : User XXX is not authorized to access _SYS_BIC.system-local.bw.bw2hana/A9CO_ER_ES07 because he is missing any structured privileges applicable to that object

[former_member183326 · Accepted solution]

Ah ok, I still think there might be some missing information, could you upload the whole file to:

https://mdocs.sap.com/mcm/public/v1/open?shr=6qXkl5lljT5wMH_zorkvDvMOjjUuj2C-gtYhYPChc24

By looking at what you out up, it seems the user you are using have not been granted the correct privileges on _SYS_BIC. Have you made sure your user has granted the SELECT with SYS_REPO with grantable to others option?

[Former Member · Accepted solution]

It seems as if it does not have 'grantable to others'

Shall I change that and report back?

[former_member183326 · Accepted solution]

Yes switch to 'yes'. The issue is you seem to be missing the privilege for the object in SYS_BIC (A9CO_ER_ES07 ).

Grantable to others should fix this

[marcel_scherbinek]

Hello,

we had the same problem or error message while executing the model (calculation view based on generated SAP HANA view in BW) in a BW transformation. Every time we executed the DTP and the transformation (SAP HANA expert scripted based transformation) we received the same error message regarding missing structured authorization of the generated SAP HANA view.

Activating the transformation again solved the problem. So simple, quiet so complicated.

I guess it has something to do with a BW note with switched from "each view one analytical privilege in one role" to "one role for all analytical privileges". The change affected the transformation obviously only when reactivating it. But don't ask me which SAP Note causes this authorization switch.

Good luck!

Marcel

[Former Member]

Hi,

I order to view the data, Analytic privileges on the view are required for the user apart from the _SYS_BIC and _SYS_BI privileges.

Also, the trace above mentions:

User XXX is not authorized to access _SYS_BIC.system-local.bw.bw2hana/A9CO_ER_ES07 because he is missing any structured privileges applicable to that object

Here, structured privileges refer to the analytic privilege to the user.

Can you check if the user has the respective Analytic privilege.

For analytic privilege you can refer the help.sap.com at

Analytic Privileges - SAP HANA Developer Guide for SAP HANA Studio - SAP Library

Thank you.

Anjali.

[Former Member]

Can you pls let me know how you suggest going about doing that check? Thnx.

[Former Member]

Steps to check the analytic privilege

1. In the Calculation view system-local.bw.bw2hana/COM_PROV_XYZ , check the Properties for Apply Privilege as

If it is classic Analytic Privilege, you can check if an analytic privilege is created on it. You can do this by selecting the view in the Systems view and in the context menu select Where used

This will list the analytic privilege if any created on the package.

Once we know the analytic privilege, go to the user in the Security -> Users -> Username.

Check in the Analytic Privileges whether the same privilege is granted to the user. If not then grant the same.

If there is no analytic privilege defined on the view, you need to ask the administrator (or user who has the privilege to create the analytic privilege) to create one and grant it to your user.

Thank you.

Anjali.

[former_member183326]

Can you upload the full trace file to the container?

[Former Member]

It is not a calc view but a column view.

[Former Member]

Our security team has not yet done the change:

  grantable to others option

If that does not help I will post the trace  file. Makes sense I hope

[Former Member]

Hi,

A calculation/Analytic/Attribute view on activation generates a column view.

Can you check in the Content -> system-local.bw.bw2hana package if the corresponding design time view exists.

Thank you.

Best Regards,

Anjali.

[Former Member]

Yes the design time object is there

[Former Member]

Great.

You can thus follow the steps stated above to check if the analytic privilege is granted to the user.

[Former Member]

The change was done (pls see pic below).

Using the link you provided I have uploaded the trace file as you requested:

[Former Member]

Hi,

Did you try providing the user with the analytic privilege.

Regards,

Anjali.

[former_member183326]

Please grant the SYS_REPO user select on schema. (__SYS_BIC:BKW_futureERP_Budgetierung.erp/LP_IC02_INTERNAL)

grant select on schema to _SYS_REPO with grant option; ;

2280915 - Trying to create a HANA procedure gives an authorization error

[Former Member]

Anjali,

above u provided a screenshot of how to check for analytical priv on an analytical view. Pls provide a screenshot of how to check this on a column view.

Note: this is a column view, not a calc view

[former_member183326]

select * from sys.structured_privileges where structured_privilege_name in ( select structured_privilege_name from sys.structured_privileges where operand = '_SYS_BIC:/' order by structured_privilege_name );

[Former Member]

That returned an empty result set

[Former Member]

Thanks for your suggestion but that did not solve it either

This annoying problem refuses to go away

FYI: the schema has been added to the role:

and _SYS_REPO has been assigned the role:

Please let me know if this is correct and/or if you have any other suggestions.

I uploaded a fresh trace file hoping that you can take a look.

[Bojan-lv-85]

please post the result from

select * from effective_structured_privileges where user_name = '<user>' androot_schema_name = '_SYS_BIC' AND root_object_name = '<viewname>'

[Former Member]

I executed:

select * from effective_structured_privileges where user_name = 'MYUSER and

root_schema_name = '_SYS_BIC' AND root_object_name = '_SYS_BIC"."system-local.bw.bw2hana/A9CO_ER_ES07';

That also returned an empty result set

[Bojan-lv-85]

how about:

select * from effective_structured_privileges where user_name = 'MYUSER' and

root_schema_name = '_SYS_BIC' AND root_object_name = 'A9CO_ER_ES07';

?

BR, Bojan

[Former Member]

... also nothing

[former_member183326]

Hello,

Can you please check if you have checked the authorizations from a BW perspective?

Have you checked if all the relevant notes from BW-WHM-MTD-HMOD are implemented? (Check the notes regarding queries).

SAP Note 2107663 may be of help.

Please let me know

[Former Member]

Thanks for this info, but we are on SAPKW74012 (and the note refers to sub-11).

Activating authorization checks ST01 and RSECADMIN created no fail records in their respective logs (currently nothing has been marked as authorization relevant anyway). I assume that this is what you refer to with "checked the authorizations from a BW perspective".

Given that the object is a HANA object (a composite provider created in HANA Studio) and the error pops up when trying to execute an SQL statement in HANA Studio on the composite provider I assume this is not surprising.

The mystery remains!

[former_member183326]

Can you create an incident for this? Please send me the incident number

[Former Member]

I have opened incident 0000219580 2016 and noted in the text that it has been opened on your request.

Your asistance is greatly appreciated.

[former_member183326]

Thank you