With the ever increasing push to S4 HANA, its important that basis or security folk are able to trace any authorization issues on the database level.
This blog will deal with the concept privileges & Authorizations with the SAP HANA Database and how can you turn on a trace in SAP HANA if an issue arises with a users privileges on the database level, not the application level. 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. For more information on SAP HANA privileges please see the SAP HANA Security Guide: https://help.sap.com/viewer/b3ee5778bc2e4a089d3299b82ec762a7/2.0.02/en-US
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:
SAP DBTech JDBC: [258]: insufficient privilege
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 Ptime Exception: insufficient privilege: [2950] user is not authorized : occurred while unfolding
These errors are just examples of some the different authorization scenarios 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 authorization 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 authorization 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 authorization 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; _____________________________________________________________________________________________________________________________ ______________________________________________________________________________________________________________________________ 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. Analytical Privileges: Normal Privileges If you need to check which privileges are assigned to a particular user can use the view EFFECTIVE_PRIVILEGES This view can be accessed only using the filter on USERNAME as per this example: SELECT * FROM EFFECTIVE_PRIVILGES WHERE USER_NAME= <username> Analytical Privileges To view what AP’s are assigned to a specific user account, you can use the below SQL: SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = ‘<username>’ AND OBJECT_TYPE = ‘ANALYTICALPRIVILEGE’ If the user is granted multiple AP’s, the definition of individual AP’s can be examined using the following SQL: SELECT * FROM STRUCTURED _PRIVILEGES WHERE STRUCTURED_PRIVILEGE_NAME LIKE %<analytical_privilege_name>%’ For more useful information on Privileges can be seen in the following KBA’s: KBA #2220157 – Database error 258 at EXE insufficient KBA #1735586 – Unable to grant privileges for SYS_REPO.-objects via SAP HANA Studio authorization management. KBA #1966219 – HANA technical database user _SYS_REPO cannot be activated. KBA #1897236 – HANA: Error" insufficient privilege: Not authorized " in SM21 KBA #2092748 – Failure to activate HANA roles in Design Time. KBA #2126689 – Insufficient privilege. Not authorized KBA #2250445 – SAP DBTech JDBC 485 – Invalid definition of structured privilege: Invalid filter condition