Skip to Content

Enhanced database trace information for authorization issues in SAP HANA SPS 12

Several new and enhanced security features are available with SAP HANA SPS 12. For an overview see SAP HANA SPS 12 What's New: Security - by the SAP HANA Academy.

One further enhancement available with SPS 12 is the improved usability of the database trace for authorization issues.

When faced with authorization errors like "insufficient privilege: Not authorized", you typically enable database tracing for the "authorization" component and raise the trace level to INFO, for example using the SAP HANA studio like this:

However, understanding the information in the resulting trace file could be difficult. Let's have a look at some examples.

If a user ELKE performs a SELECT on a table sys.p_objects_ but is not authorized to do so, the pre-SPS 12 trace would have looked like this:

[36096]{200088}[18/-1] 2016-01-14 17:30:54.969173 i Authorization    SQLFacade.cpp(01535) : UserId(151595) is not authorized to do SELECT on ObjectId(2,0,oid=133410)

[36096]{200088}[18/-1] 2016-01-14 17:30:54.969225 i Authorization    SQLFacade.cpp(01960) :

    schemas and objects in schemas :

    SCHEMA-133151-SYS : {} , {SELECT}

        TABLE-133410-P_OBJECTS_ : {} , {SELECT}

[36096]{200088}[18/-1] 2016-01-14 17:30:54.969241 i Authorization    query_check.cc(03644) : User ELKE tried to execute 'select * from sys.p_objects_'

The name of the user and queried table is in there, but a little hard to find. And there's no information on the actual authorization problem (which privilege is missing?)

In SPS 12, you can see much more easily what the problem is:

[33536]{300098}[25/-1] 2016-05-11 15:21:02.156360 i Authorization    SQLFacade.cpp(02507) : User ELKE is missing privilege SELECT for TABLE SYS.P_OBJECTS_

[33536]{300098}[25/-1] 2016-05-11 15:21:02.156406 i Authorization    query_check.cc(03626) : User ELKE tried to execute 'select * from sys.p_objects_'

Another example: User UTE tries to access the view ELKE.ELKEVIEW view but gets a "not authorized" error despite having SELECT privilege on the schema ELKE. The problem here is that the owner of the view (ELKE) doesn't have the SELECT WITH GRANT OPTION on all dependent objects. But which one?

The pre-SPS 12 trace of such an authorization error would have looked like this:

[36096]{200088}[18/-1] 2016-01-14 17:30:57.197126 i TraceContext     TraceContext.cpp(00878) : UserName=UTE, ApplicationUserName=D024855, ApplicationName=HDBStudio, ApplicationSource=csns.sql.editor.SQLExecuteFormEditor$2$1.run(SQLExecuteFormEditor.java:856);, StatementHash=647967f17e04607ca7e7df165c6a7b88

[36096]{200088}[18/-1] 2016-01-14 17:30:57.197100 i Authorization    SQLFacade.cpp(01415) : UserId(151595) is not authorized to grant SELECT on ObjectId(2,0,oid=151599)

[36096]{200088}[18/-1] 2016-01-14 17:30:57.197157 i Authorization    SQLFacade.cpp(01958) : check for GRANT/REVOKE

[36096]{200088}[18/-1] 2016-01-14 17:30:57.197161 i Authorization    SQLFacade.cpp(01960) :

    schemas and objects in schemas :

    SCHEMA-151594-MASTER : {} , {SELECT}

        TABLE-151599-MTAB : {SELECT} , {}

        TABLE-151611-MTAB4 : {SELECT} , {}

        TABLE-151623-MTAB8 : {SELECT} , {}

        TABLE-151602-MTAB1 : {SELECT} , {}

        TABLE-151608-MTAB3 : {SELECT} , {}

        TABLE-151620-MTAB7 : {SELECT} , {}

        TABLE-151626-MTAB9 : {SELECT} , {}

        TABLE-151605-MTAB2 : {SELECT} , {}

        TABLE-151617-MTAB6 : {SELECT} , {}

It's difficult to see who or what has the problem.

In SPS 12, the problem is more easily identified:

[33536]{300098}[25/-1] 2016-05-11 15:21:09.101423 i TraceContext     TraceContext.cpp(00878) : UserName=UTE, StatementHash=647967f17e04607ca7e7df165c6a7b88

[33536]{300098}[25/-1] 2016-05-11 15:21:09.101407 i Authorization    SQLFacade.cpp(02507) : User ELKE is not allowed to grant privilege SELECT for TABLE MASTER.MTAB4

[33536]{300098}[25/-1] 2016-05-11 15:21:09.101451 i Authorization    check_view.cc(01103) : User UTE is not authorized to use VIEW ELKE.ELKEVIEW because of missing grantable privileges on underlying objects

Tags: