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:

User DEVINEM is missing the object privilege SELECT with option ‘grantable to others’ for table DATA_BANK.SECURITY_BASE.

5. Assign the missing privilege to the Database user. See GRANT Statement (Access Control).

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?