SAP HANA Authorization Tracing

This document will deal with issues regarding analytical privileges with SAP HANA.

So what are Privileges some might ask?

System Privilege:

System privileges control general system activities. They are mainly used for administrative purposes, such as creating schemas, creating and changing users and roles, performing data backups, managing licenses, and so on.

Object Privilege:

Object privileges are used to allow access to and modification of database objects, such as tables and views. Depending on the object type, different actions can be authorized (for example, SELECT, CREATE ANY, ALTER, DROP, and so on).

Analytic Privilege:

Analytic privileges are used to allow read access to data in SAP HANA information models (that is, analytic views, attribute views, and calculation views) depending on certain values or combinations of values. Analytic privileges are evaluated during query processing.

In a multiple-container system, analytic privileges granted to users in a particular database authorize access to information models in that database only.

Package Privilege:

Package privileges are used to allow access to and the ability to work in packages in the repository of the SAP HANA database.

Packages contain design time versions of various objects, such as analytic views, attribute views, calculation views, and analytic privileges.

In a multiple-container system, package privileges granted to users in a particular database authorize access to and the ability to work in packages in the repository of that database only.

So, you are trying to access a view, a table or simply trying to add roles to users in HANA Studio and you are receiving errors such as:

• Error during Plan execution of model _SYS_BIC:onep.Queries.qnoverview/CV_QMT_OVERVIEW (-1), reason: user is not authorized
• pop1 (rc 2950, user is not authorized)
• insufficient privilege: search table error: [2950] user is not authorized
• Could not execute ‘SELECT * FROM”_SYS_BIC”.”<>“‘ SAP DBTech JDBC: [258]: insufficient privilege: Not authorized.SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

These errors are just examples of  some the different authorization issues you can see in HANA Studio, and each one is pointing towards a missing analytical privilege. 

Once you have created all your models, you then have the opportunity to define your specific authorization requirements on top of the views that you have created.

So for example, we have a model in HANA Studio Schema and its called “_SYS_BIC:Overview/SAP_OVERVIEW”

We have a user, lets just say its the “SYSTEM” user, and when you query this view you get the error:

Error during Plan execution of model _SYS_BIC:Overview/SAP_OVERVIEW (-1), reason: user is not authorized.

So if you are a DBA, and you get a message from a team member informing you that they getting a authorisation issue in HANA Studio. What are you to do?

How are you supposed to know the User ID? And most importantly, how are you to find out what the missing analytical privilege is?

So this is the perfect opportunity to run an authorisation trace through the means of the SQL console on HANA Studio.

So if you follow the below instructions it will walk you through executing the authorisation trace:

1) Please run the following statement in the HANA database to set the DB  trace:

alter system alter configuration (‘indexserver.ini’,’SYSTEM’) SET 

(‘trace’,’authorization’)=’info’ with reconfigure;

2) Reproduce the issue/execute the command again/

3) When the execution finishes please turn off the trace as follows in the Hana studio:

alter system alter configuration (‘indexserver.ini’,’SYSTEM’) unset 

(‘trace’,’authorization’) with reconfigure;

__________________________________________________________

Only use this when instructed by SAP. It’s recommended to use “INFO” rather than “DEBUG” in normal circumstances.

If you would like a more detailed trace on the privileges needed you could also execute the DEBUG level trace.

1) Please run the following statement in the HANA database to set the DB  trace:

alter system alter configuration (‘indexserver.ini’,’SYSTEM’) SET

(‘trace’,’authorization’)=’debug’ with reconfigure;

2) Reproduce the issue/execute the command again

3) When the execution finishes please turn off the trace as follows in the Hana studio:

alter system alter configuration (‘indexserver.ini’,’SYSTEM’) unset

(‘trace’,’authorization’) with reconfigure;

_________________________________________________________

So now that you have turned the trace on and reproduced the issue, now you must turn off the trace.

You should now see a new indexserver0000000trc file created in the Diagnosis Files Tab in HANA Studio

So once you open the trace files, scroll to the end of the file and you should see something familiar to this:

e cePlanExec      cePlanExecutor.cpp(06890) : Error during Plan execution of model _SYS_BIC:onep.Queries.qnoverview/CV_QMT_OVERVIEW (-1), reason: user is not authorized

i TraceContext    TraceContext.cpp(00718) : UserName=TABLEAU, ApplicationUserName=luben00d, ApplicationName=HDBStudio, ApplicationSource=csns.modeler.datapreview.providers.ResultSetDelegationDataProvider.<init>(ResultSetDelegationDataProvider.java:122);csns.modeler.actions.DataPreviewDelegationAction.getDataProvider(DataPreviewDelegationAction.java:310);csns.modeler.actions.DataPreviewDelegationAction.run(DataPreviewDelegationAction.java:270);csns.modeler.actions.DataPreviewDelegationAction.run(DataPreviewDelegationAction.java:130);csns.modeler.command.handlers.DataPreviewHandler.execute(DataPreviewHandler.java:70);org.eclipse.core.commands

i Authorization    XmlAnalyticalPrivilegeFacade.cpp(01250) : UserId(123456) is missing analytic privileges in order to access _SYS_BIC:onep.MasterData.qn/AT_QMT(ObjectId(15,0,oid=78787)). Current situation:

AP ObjectId(13,2,oid=3): Not granted.

i Authorization    TRexApiSearch.cpp(20566) : TRexApiSearch::analyticalPrivilegesCheck(): User TABLEAU is not authorized on _SYS_BIC:onep.MasterData.qn/AT_QMT (787878) due to XML APs

e CalcEngine      cePopDataSources.cpp(00488) : ceJoinSearchPop ($REQUEST$): Execution of search failed: user is not authorized(2950)

e Executor        PlanExecutor.cpp(00690) : plan plan558676@<> failed with rc 2950; user is not authorized

e Executor        PlanExecutor.cpp(00690) : — returns for plan558676@<> 

e Executor        PlanExecutor.cpp(00690) : user is not authorized(2950), plan: 1 pops: ceJoinSearchPop pop1(out a)

e Executor        PlanExecutor.cpp(00690) : pop1, 09:57:41.755  +0.000, cpu 139960197732232, <> ceJoinSearchPop, rc 2950, user is not authorized

e Executor        PlanExecutor.cpp(00690) : Comm total: 0.000

e Executor        PlanExecutor.cpp(00690) : Total: <Time- Stamp>, cpu 139960197732232

e Executor        PlanExecutor.cpp(00690) : sizes a 0

e Executor        PlanExecutor.cpp(00690) : — end executor returns

e Executor        PlanExecutor.cpp(00690) : pop1 (rc 2950, user is not authorized)

So we can see from the trace file that User who is trying to query from the view is called TABLEAU. TABLEAU is also represented by the User ID (123456)

So by looking at the lines:

i Authorization    XmlAnalyticalPrivilegeFacade.cpp(01250) : UserId(123456) is missing analytic privileges in order to access _SYS_BIC:onep.MasterData.qn/AT_QMT(ObjectId(15,0,oid=78787)).

&

i Authorization    TRexApiSearch.cpp(20566) : TRexApiSearch::analyticalPrivilegesCheck(): User TABLEAU is not authorized on _SYS_BIC:onep.MasterData.qn/AT_QMT (787878) due to XML APs

We can clearly see that TABLEAU user is missing the correct analytical privileges to access the _SYS_BIC:onep.MasterData.qn/AT_QMT which is located on Object 78787.

So now we have to find out who owns the Object 78787. We can find out this information by querying the following:

select * from objects where object_oid = ‘<oid>’;

Select * from objects where object_oid = ‘78787’

Once you have found out the owner for this object, you can get the owner to Grant the TABLEAU user the necessary privileges to query the object.

This has since changed in the new revision of SP12, see here.

Changes as of SP12

As SP12 has now been released, the understanding of the authorization file has now been simplified .

I created a SP12 system and did a quick test to show what changes have been made.

So by the following the same procedure as listed above, I create a user called TEST. This TEST user is a stock user with little or no privileges assigned to it.

Here you can see the TEST user trying to open the “Content” folder but is getting the error “Insufficient privilege (EXECUTE on REPOSITORY_REST)”

Having turned on the authorization trace as shown above, you can see in the indexserver trace:

[4620]{300158}[29/-1] 2016-05-26 18:12:19.571421 i Authorization    SQLFacade.cpp(02539) : User TEST is missing privilege EXECUTE for PROCEDURE/FUNCTION SYS.REPOSITORY_REST

[4620]{300158}[29/-1] 2016-05-26 18:12:19.571461 i Authorization    query_check.cc(03674) : User TEST tried to execute ‘{ call SYS.REPOSITORY_REST (?,?) }’

The new feature in SP12 clearly shows you that the user TEST is missing the EXECUTE privilege for the PROCEDURE/FUNCTION SYS.REPOSITORY_REST. This eliminates the process of querying the oid number and finding the objects and the owners .

Please be aware that if you find that the owner of an object is _SYS_REPO, this is not as straight forward as logging in as _SYS_REPO as this is not possible because SYS_REPO is a technical database user used by the SAP HANA repository. The repository consists of packages that contain design time versions of various objects, such as attribute views, analytic views, calculation views, procedures, analytic privileges, and roles. _SYS_REPO is the owner of all objects in the repository, as well as their activated runtime versions.

You have to create a .hdbrole file which which gives the access ( Development type of role, giving select, execute, insert etc access) on this schema. You then assign this role to the user who is trying to access the object.

Insufficient Privilege Error When Trying to Access Database Objects After Import

You have received an export of database objects, imported them and are now trying to access them with a SQL query. Even though you have ensured that you have assigned all the right privileges for the schema and the objects, selects keep failing with the following error:

SAP DBTech JDBC: [258] insufficient privileges

In this case it’s worth to look for phrase STRUCTURED PRIVILEGE CHECK in the create.sql files, remove it and re-import the export.

Another option that is available for analyzing privileges issues was introduced as of SP9. This comes in the form of the Authorization Dependency Viewer.