GRC
HR
SCM
CRM
BI
Expand +


Article

 

Quickly Resolve SAP HANA Authorization Errors and Issues

by Kehinde Eseyin, Security Architect

May 25, 2018

See how to activate tracing to resolve authorization errors in SAP HANA. You then can troubleshoot authorization errors related to repository role activation, SQL privileges, and analytical privileges.

The following typical but fictitious business scenarios describe how to troubleshoot and resolve SAP HANA authorization issues. I cover these topics:

  • The SAP HANA authorization concept
  • The tracing functionality in SAP HANA
  • How to activate tracing
  • How to deactivate a trace
  • Troubleshooting and resolving role activation errors
  • Resolving missing SQL privileges and analytical privileges errors

I explain the differences in the details of the trace files in:

  • SAP HANA prior to and up to Support Package 11—specifically, the screenprints used in this article are from Support Package 09
  • SAP HANA starting from Support Package 12—specifically, the screenprints used in this article are from Support Package 12

This article addresses the following commonplace SAP HANA authorization errors:

  • Insufficient privilege: not authorized at ptime/query/checker/query_check.cc:3302
  • Cannot get the data provider outlin.: SAP DBTech JDBC: [258]: insufficient privilege
  • Error executing the query. SAP DBTech JDBC: [258]: insufficient privilege: Not authorized
Overview of the SAP HANA Authorization Concept

Every user who needs to work on the SAP HANA database requires appropriate authorization, which is typically assigned in the form of a privilege or a role. Typically, these users include the following:

  • Business end user: consumption of reports using client tools
  • Modelers: creation of model and reports
  • Database administrators: operation and maintenance of the database

Basically, a privilege allows you to define restrictions for access to data and execution of actions. Roles on the other hand allow you to group privileges to be assigned together to a user. Roles are in fact the best strategy for granting privileges as they allow you to implement both simple and complex reusable authorization concepts that can be modeled on business roles.

(Note: SAP recommends that authorizations should be assigned to users via roles.)

The following types of privileges are available in SAP HANA:

  • Object privilege: Authorize access to data and operations on database objects by restricting access to and modification of database objects (for example, table and view)
  • SYSTEM privilege: Authorize execution of administrative actions for the entire SAP HANA database
  • Package privilege: Restrict the access to and the use of packages in the repository of the SAP HANA database
  • Analytical privilege: Authorize read access to analytic, attribute, and calculation views at the processing time of the user query and provide row-level access control based on the dimensions of the relevant view
  • Application privilege: Authorize access to SAP HANA Extended Application Services (SAP HANA XS) application functions   
Overview of the Tracing Functionality in SAP HANA

Different traces are available for obtaining detailed information about the actions of the database system. You can activate and configure traces on the Trace Configuration tab of the Administration editor.The following traces are available in SAP HANA:

  •   Database trace
  •   SQL trace
  •   User-specific trace
  •   Performance trace
  •   End-to-end traces
  •   Expensive statement trace (individual SQL statements whose execution time exceeds a configured threshold)
  •   Kernel profiler
  •   Plan trace
How to Activate Tracing

An administrator will typically activate a trace in order to analyze an issue such as an authorization error. Various traces are available for obtaining detailed information about the actions of the database system—for example, a user-specific trace. User-specific traces extend the configured database trace by allowing you to change the trace level of components in the context of a user. The trace levels configured for components in these contexts override those configured in the database trace. Traces can be activated and configured on the Trace Configuration tab of the Administration editor.

To activate tracing, launch SAP HANA studio. Double-click the instance you want to work on. Figure 1 displays.


Figure 1
Select the instance

Enter the password for the user as shown in Figure 2.


Figure 2
Enter the password

Click the OK button and Figure 3 displays.


Figure 3
Double-click the instance

Double-click the instance and navigate to the Trace Configuration tab in the screen that displays as shown in Figure 4


Figure 4
Tracing configuration screen

Click the new configuration icon. In the screen that opens, check the Show All Components check box as shown in Figure 5.


Figure 5
Activate the Show All Components check box

As shown in Figure 6, specify values for the Context Name, Database User, and Application User fields. Also, enter Authorization in the search term and enter INFO against the INDEXSERVER component in the System Trace Level column.


Figure 6
Definition of a trace level for the INDEXSERVER

Click the Finish button and Figure 7 appears.


Figure 7
Confirmation for the creation of a user-specific trace

How to Deactivate a Trace

If an inappropriate trace level is set for SAP HANA database components, a high amount of trace information may be generated during routine operations. This can impair system performance and lead to unnecessary consumption of disk space. Hence, it is good practice to activate tracing with caution and delete the traces once they are not needed. Navigate to the Trace Configuration tab in the SAP HANA studio (Figure 8).


Figure 8
Initial screen of trace configuration

Click the delete icon against the user trace entry that you want to maintain. Figure 9 appears.


Figure 9
Confirmation screen for trace deletion

Click the Yes button to go to Figure 10, which shows that  the deleted trace no longer appears in the User-Specific Trace section.


Figure 10
User-Specific Trace section without the deleted trace
Troubleshooting and Resolving Role Activation Errors

Roles can be created as design-time roles (repository roles) or as run-time roles (catalog roles) in SAP HANA. However, it is recommended that you use repository roles in your SAP HANA landscape because they offer many advantages over catalog roles such as:

  • Versioning
  • Transportability—supports integration with a standard transport mechanism
  • Segregation of duties—separation of role creation from role granting/revoking

The _SYS_REPO is one of the standard users in the SAP HANA database. This user is responsible for activating objects in the repository. Basically, with repository roles, it is possible to create roles as design-time objects in the repository of the SAP HANA database. Design-time roles can be transported from the development or QA system to the production system, where they are activated to be available at run time.

With the SAP HANA repository, there is only one technical user, _SYS_REPO, that does the activation job at the background. There is no option to switch to other technical users.

When creating repository roles, _SYS_REPO must have the appropriate authorization, else there will be a repository activation error at the point of saving the roles. Let’s go through a typical scenario. Launch the editor of SAP HANA Web-based Development Workbench via the URL: : /sap/hana/ide/editor/">http://<hostname>:<port>/sap/hana/ide/editor/ . Figure 11 appears.


Figure 11
Initial screen of the SAP HANA Web-based Development editor

Right-click a package folder as shown in Figure 12.


Figure 12
Menu option to create a role in a package

Choose New > Role and in the dialog box that opens, specify the role name as shown in Figure 13.


Figure 13
Definition of the role name

Click the Create button. Figure 14 displays a status message confirming the creation of the role.


Figure 14
Confirmation of successful role creation

Click the Object Privileges tab and Figure 15 appears.


Figure 15
Initial screen to assign an object privilege to a role

Click the add icon. In the screen that opens, select the Run-time radio button option and enter _SYS_EPM in the search field as shown in Figure 16.


Figure 16
Select the Run-time radio button

Highlight the object to be assigned from the search result as shown in Figure 17.


Figure 17
Selection of an object from the search result

Click the OK button. In the screen that opens, choose EXECUTE and SELECT Privileges as shown Figure 18.


Figure 18
Assignment of privileges to a role

Click the save icon. The following error message appears (Figure 19):
Error while activating /ZKENNY/ZR_EPM_TEST1.hdbrole:[ZKENNY/ZR_EPM_TEST1.hdbrole]: insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:3302


Figure 19
Error message while activating a role in the repository

Let’s look at the trace log for more information. Navigate to the Diagnosis Files tab of the SAP HANA system (Figure 20).


Figure 20
Diagnosis Files tab of the SAP HANA system

Click the applicable trace file and Figure 21 appears.


Figure 21
Details in the trace log file

This is an excerpt from the trace log file:

[1781]{1334}[41/56052163] 2017-04-10 16:14:53.857280 e REPOSITORY       activator.cpp(00628) : Activator::activateObjects: Activation 498 completed with errors. Session error: Repository: Activation failed for at least one object; At least one error was encountered during activation. Please see the CheckResult information for detailed information about the root cause. No objects have been activated.(40136)
Check results with severity "error":
(ERROR, error code: 258, error message: insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:3302, object: {tenant: , package: ZKENNY, name: ZR_EPM_TEST1, suffix: hdbrole}, location: , time stamp: 2017-04-10,16:14:53.830, unformatted error message: insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:3302)

From the error message, it is clear that the issue relates to the activation of a repository object. There is no check performed on the activating user's privileges during the activation process itself (except the general check on the development authorization). All objects activated via the SAP HANA repository are owned internally and executed by the repository's _SYS_REPO user. Hence, it is obvious that the authorization issue relates to the _SYS_REPO technical user. Now I will check if _SYS_REPO has the appropriate object privilege authorization. This can be done directly in SAP HANA studio by checking the assigned object privileges of the _SYS_REPO user as shown in Figure 22.


Figure 22
Assign object privileges to user _SYS_REPO

Alternatively, you can execute the command below as shown in Figure 23, which returns a blank result:

SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = ‘SYS_REPO’ AND OBJECT_TYPE = ‘SCHEMA’ AND SCHEMA_NAME = ‘_SYS_EPM’


Figure 23
Using the SQL editor to confirm the assignment of object privilege to a user

As shown in Figures 22 and 23, the _SYS_REPO does not have an applicable object privilege on the _SYS_EPM object, which explains the activation error. I will go ahead and add the object privilege to the user in the SAP HANA studio as shown in Figure 24. Note that the basic steps to do this are not shown in the article.


Figure 24
Assignment of an object privilege to the _SYS_REPO user

Let’s check via the SQL editor for confirmation of the assignment via the SQL statement used initially (Figure 25).


Figure 25
Confirmation of the assignment of _SYS_EPM object privilege to the _SYS_REPO user

With the authorization now assigned to the _SYS_REPO, let’s try to save the role again as we tried in Figure 19. Figure 26 displays a successful activation status message.


Figure 26
Confirmation of successful activation of repository role

Resolving Missing SQL Privileges and Analytical Privileges

The trace file is an important tool used to troubleshoot authorization issues in the SAP HANA database. The content of the trace file differs in pre Support Package 12 and post Support Package 11 of the SAP HANA database. Hence, in this section, I show the difference using fictitious business scenarios.

Follow the steps below to reproduce the errors associated with missing SQL and analytical privileges. To do this, I have created a role (ZDEMO_ROLE) and assigned it to ZDEMO_USER in SAP HANA systems HA1 (running on Support Package 12) and IDS (running on Support Package 09).

(Note: The demo system used in this article is based on the SAP HANA SHINE content. The  data in the system might be different because of the difference in version and the existence of test data and objects that might not exist in the other system.)

In SAP HANA studio, while logged on as demo user ZDEMO_USER in system HA1, follow menu path Content > sap > hana > democontent > epm > models > Attribute Views. Right-click the option AT_PURCHASE_ORDER_WORKLIST as shown in Figure 27.


Figure 27
Attempt to display the content of an attribute view - AT_PURCHASE_ORDER_WORKLIST

Click the Data Preview button and Figure 28 displays the error message:

Cannot get the data provider outline
SAP DBTech JDBC: [258]: insufficient privilege: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/HANA__FA_CO_LIN64GCC48HAPPY_rel_fa~hana1sp12/s/ptime/query/checker/query_check.cc:3713
 


Figure 28
Authorization error message

 
Now, I navigate to the Diagnosis Files tab of the HA1 system as shown in Figure 29.
 


Figure 29
Diagnosis Files tab of the SAP HANA system HA1

Click the applicable trace file using the user name and timestamp and Figure 30 appears.


Figure 30
Content of a trace file in system HA1

An excerpt from the trace file in Figure 30 is shown:
[24724]{352889}[30/-1] 2017-04-14 12:55:02.442692 i Authorization    SQLFacade.cpp(02528) : User ZDEMO_USER is missing privilege SELECT for VIEW _SYS_BI.BIMC_DIMENSION_VIEW
[24724]{352889}[30/-1] 2017-04-14 12:55:02.442740 i Authorization    query_check.cc(03710) : User ZDEMO_USER tried to execute 'SELECT *  from "_SYS_BI"."BIMC_DIMENSION_VIEW"WHERE DIMENSION_UNIQUE_NAME = '[AT_PURCHASE_ORDER_WORKLIST]' AND CATALOG_NAME = 'sap.hana.democontent.epm.models' AND CUBE_NAME = '$ATTRIBUTE''
[24724]{352889}[30/-1] 2017-04-14 12:55:02.446870 e mdx              search.cpp(03386) : Error:258 insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/HANA__FA_CO_LIN64GCC48HAPPY_rel_fa~hana1sp12/s/ptime/query/checker/query_check.cc:3713

I will attempt to repeat the display of the attribute view  AT_PURCHASE_ORDER_WORKLIST on system IDS (not shown) and then navigate to the Diagnosis Files tab of system IDS as shown in Figure 31.


Figure 31
Diagnosis Files tab of the SAP HANA system IDS

Click the applicable trace file using the user name, timestamp, and the content of the trace log appears (not shown). An excerpt of the trace file is displayed:
[1673]{200344}[37/-1] 2017-04-14 13:01:31.738844 i Authorization    SQLFacade.cpp(01353) : UserId(156274) is not authorized to do SELECT on ObjectId(2,0,oid=143792)
[1673]{200344}[37/-1] 2017-04-14 13:01:31.738867 i Authorization    SQLFacade.cpp(01750) :
    schemas and objects in schemas :
    SCHEMA-143524-_SYS_BI : {} , {SELECT}
        TABLE-143792-BIMC_PROPERTIES : {} , {SELECT}

[1673]{200344}[37/-1] 2017-04-14 13:01:31.738873 i Authorization    query_check.cc(03299) : User ZDEMO_USER tried to execute 'SELECT *  from

[1673]{200344}[37/-1] 2017-04-14 13:01:31.739357 e mdx              search.cpp(03095) : Error:258 insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:3302

From the trace logs in system HA1 and IDS, it is clear that the user is missing specific SQL privileges. However, system HA1 explicitly states the missing authorization (SELECT for VIEW _SYS_BI.BIMC_DIMENSION_VIEW) while system IDS states the object ID (oid=143792) of the missing authorization.

Now, we need to find the user-friendly name of the object ID in the system IDS. This can be found via the SQL command shown in Figure 32:

SELECT * FROM OWNERSHIP WHERE OBJECT_OID IN (143792)
 

Figure 32
Output of SQL command in the SQL editor

With the output of the SQL command, it is now clear that the missing authorization is SELECT on table BIMC_PROPERTIES of the _SYS_BI schema. So, let’s assign the missing SQL privilege to the role ZDEMO_ROLE assigned to ZDEMO_USER as shown in Figures 33 and 34 for systems HA1 and IDS, respectively.


Figure 33
Assignment of an object privilege to a role in the SAP HANA system HA1


Figure 34
Assignment of an object privilege to a role in the SAP HANA system – IDS

Now that the missing authorization is assigned to the user via the role, try the step in Figure 27 again in system HA1 and IDS. Figure 35 appears, which shows that the initial issue with the SQL privilege has been fixed.


Figure 35
Initial screen to display attribute view content

Click the Raw Data tab and Figure 36 appears with the error message:

Error executing the query
Error: SAP DBTech JDBC: [258]: insufficient privilege: Not authorized


Figure 36
Error message while attempting to display the attribute view

I will proceed to review the trace logs as I did before (steps not shown).

Below is an excerpt of the applicable trace log entry in system HA1
[24746]{355840}[47/-1] 2017-04-14 14:34:03.783193 i Authorization    XmlAnalyticalPrivilegeFacade.cpp(01330) : Authorization error: User ZDEMO_USER is missing analytic privileges in order to access _SYS_BIC:sap.hana.democontent.epm.models/AT_PURCHASE_ORDER_WORKLIST; AP _SYS_BI_CP_ALL was not granted
; AP sap.hana.democontent.epm.models/AP_PURCHASE_ORDER_PROD_CAT was not granted
; AP sap.hana.democontent.epm.models/AP_PURCHASE_ORDER was not granted
[24746]{355840}[47/-1] 2017-04-14 14:34:03.783246 i Authorization    TRexApiSearch.cpp(19749) : TRexApiSearch::analyticalPrivilegesCheck(): User ZDEMO_USER is not authorized on _SYS_BIC:sap.hana.democontent.epm.models/AT_PURCHASE_ORDER_WORKLIST (v 1008050) due to XML APs

Below is an excerpt of the applicable trace log entry in system IDS:
[1752]{200513}[49/-1] 2017-04-14 14:32:02.258216 i Authorization    XmlAnalyticalPrivilegeFacade.cpp(01301) : UserId(156274) is missing analytic privileges in order to access _SYS_BIC:sap.hana.democontent.epm.models/AT_PURCHASE_ORDER_WORKLIST(ObjectId(26,0,oid=3381)). Current situation:
 AP ObjectId(24,2,oid=57): Not granted.

 AP ObjectId(24,2,oid=4014): Not granted.

 AP ObjectId(24,2,oid=4021): Not granted.

 AP ObjectId(24,2,oid=4461): Not granted.

 AP ObjectId(24,2,oid=4471): Not granted.
[1752]{200513}[49/-1] 2017-04-14 14:32:02.258238 i Authorization    TRexApiSearch.cpp(21229) : TRexApiSearch::analyticalPrivilegesCheck(): User ZDEMO_USER is not authorized on _SYS_BIC:sap.hana.democontent.epm.models/AT_PURCHASE_ORDER_WORKLIST (152726) due to XML APs

These two logs show that the user is missing analytical privileges; however, the trace log from system HA1 explicitly states the name of the missing analytical privileges (sap.hana.democontent.epm.models/AP_PURCHASE_ORDER, AP _SYS_BI_CP_ALL and sap.hana.democontent.epm.models/AP_PURCHASE_ORDER_PROD_CAT).

However, system IDS only shows the object IDs (57, 4014, 4021, 4461, 4471) of the missing analytical privileges. We can find out the user-friendly name of the analytical privileges via the SQL command as shown in Figure 37:

SELECT * FROM OWNERSHIP WHERE OBJECT_OID IN (57, 4014, 4021, 4461, 4471)


Figure 37
Output of the SQL commend in the SQL editor

I will progress to assign the missing analytic privileges to the demo role ZDEMO_ROLE (steps not shown) as shown in Figures 38 and 39 for systems HA1 and IDS, respectively.


Figure 38
Assignment of an analytic privilege to a role in the SAP HANA system – HA1


Figure 39
Assignment of an analytic privilege to a role in SAP HANA system IDS

Then repeat the step of Figure 36. Figure 40 appears with the content of the attribute view AT_PURCHASE_ORDER_WORKLIST.
 


Figure 40
Content of the AT_PURCHASE_ORDER_WORKLIST attribute view

An email has been sent to:





 

Kehinde Eseyin

Kehinde Eseyin is a security architect. He holds a bachelor’s degree in computer science. He has about 12 years of IT security, governance framework, IS risk, and compliance experience gained by working in numerous global organizations. Over the years, he has demonstrated competencies in security design, information assurance, cyber security, data privacy, threat and vulnerability management, penetration testing, business architecture, project management, IT audit, IS controls framework, and identity and access management.



COMMENTS

Please log in to post a comment.

No comments have been submitted on this article. Be the first to comment!


SAPinsider
FAQ