Technical Articles
Simplified Authorization Troubleshooting in SAP HANA
Are you getting authorization errors on an SAP HANA Database with version 2.00 SP04 or higher?
- Yes? Then read on.
- No? Then please use this guide to solve authorization issues.
Now there is a new, simplified way to analyze authorization errors. A new error message is presented when the user does not have the required permission to run the operation.
When performing an operation or querying a view without the necessary privilege you will receive the following error:
[258]: insufficient privilege: Detailed info for this error can be found with guid ‘<some GUID>’
SOLVE the error
1. Determine if the command is executed on the SYSTEM Database or a Tenant Database.
2. Log to the corresponding SYSTEM Database or Tenant Database with a user that has EXECUTE object privilege on procedure GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS. (GRANT EXECUTE ON GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS to <db_user>).
3. Execute this command:
call SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS('<GUID>', ?);
For example, with GUID 6D5E848AF9C6E049BE2EEF2E134AE1E1 the command is:
call SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS ('6D5E848AF9C6E049BE2EEF2E134AE1E1', ?);
4. The result will present the name of the missing privilege and the database user which is missing the privilege.
GUID;CREATE_TIME;CONNECTION_ID;SESSION_USER_NAME;CHECKED_USER_NAME;PRIVILEGE;IS_MISSING_ANALYTIC_PRIVILEGE;IS_MISSING_GRANT_OPTION;DATABASE_NAME;SCHEMA_NAME;OBJECT_NAME;OBJECT_TYPE B7874469081CB649A35CB7D514105DFC;Mar 28, 2021 10:59:52.278 AM;348,254;DEVINEM;DEVINEM;SELECT;FALSE;FALSE;AB1;_SYS_REPO;EMPLOYEE_NAME;TABLE
I made it more readable below.
The output tells a story. Here user DEVINEM is missing the object privilege SELECT for table _SYS_REPO.EMPLOYEE_NAME.
Here is another example:
GUID;CREATE_TIME;CONNECTION_ID;SESSION_USER_NAME;CHECKED_USER_NAME;PRIVILEGE;IS_MISSING_ANALYTIC_PRIVILEGE;IS_MISSING_GRANT_OPTION;DATABASE_NAME;SCHEMA_NAME;OBJECT_NAME;OBJECT_TYPE 0D9EB27F79D2A745AECE1969DF5CB1A9; Mar 15, 2021 10:59:52.278 AM;398,715;SYSTEM;DEVINEM;SELECT;FALSE;TRUE;AB1;DATA_BANK;SECURITY_BASE;TABLE
Again, made more readable looks like this:
NO result from SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS?
- The retention time might have expired. The information associated with the GUID is stored by default for 144 hours. If the retention time has expired then recreate the issue and obtain a new GUID.
indexserver.ini -> [authorization] -> insufficient_privilege_error_details_retain_duration = 144
- You executed the command on the wrong database, SYSTEM Database instead of the Tenant or Tenant instead of SYSTEM Database etc.
CONCLUSION
At this point the correct privilege has been identified and assigned to the user. Now the user is free to proceed with their operation or preview.
MORE information:
- 1809199 – SAP HANA DB: Debugging user authorization errors
- SAP HANA Administration Guide for SAP HANA Platform > Resolve Insufficient Privilege Errors
- 2159014 – FAQ: SAP HANA Security
- Chapter 11. How can tracing be activated for security topics like authorization, authentication and login?
There is also a SAP Note showing an example about how to solve such an authorization issue:
3038175 - Error: (dberror) [258]: insufficient privilege: Detailed info for this error can be found with guid
What about when no GUID is given for an insufficient privilege error? Now the auth trace no longer works from 2.0 onwards, I have auth errors without a GUID, and no way to trace them. Any thoughts?
Do you have a screen shot?
Perhaps these are BW related objects? You should check the necessary privileges have been assigned using RS2HANA_GEN. See 3122299 - "user is not authorized" error during HCPR data read
Thanks for the suggestion Michael. In this instance it is not using a BW Hybrid scenario, i.e. HCPR, but connecting Analysis for Office directly to HANA DB. To take even this out of the equation, I debug using a copy of the HANA DB user, and run direct SQL select statement with this user and get the specific auth error.
My original problem was that now we are on HANA 2.0 SP05, the user specific trace no longer seems to generate a trace file, and as the error does not give a GUID then the 'new' method is not applicable.
In the end I enabled a global authorisation level 'INFO' trace at a time of low system usage (otherwise the log could have been quite big) and found an issue with an analytical privilege which had been switched to 'applicable to all data models' so users without this privilege had auth issues.
I am not sure if there is a better way than turning on a global DB trace to track down this sort of error but it got me there in the end.
Thanks
Nice blog Mike, these types of blogs are still the best for troubleshooting via google search, the SAP notes are very hit and miss via Google. Hope is all well 🙂
Hi Michael,
Very Nice blog, but I think there should be a way to view all the missing auths in a single go. Do you think there's a way to achieve this ?
I don't think there is a way to view more than one authorization at a time in SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS or in any other procedure.
Please consider suggesting this as per Note 11 - Would you like to shape & influence future releases of SAP Products?
Hi Michael,
Thanks for the suggestion, I have raised a Improvement request for this. Request ID: 289668 . Please do consider voting for it , if you think this would make life easier for people like us.
leaving a link behind for this improvement: https://influence.sap.com/sap/ino/#/idea/289668
Regards,
Ashu