cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to activate a procedure which is calling HANA calculation View

Former Member
0 Kudos

Hi Team,

I am facing a strange issue while trying to activate a Procedure which is just calling another HANA Calculation View (Scripted).

Sample code:

When I am trying to activate this procedure it says " Insufficient privilege, not authorized".

It's very obvious that some access is missing don't have any idea why is this the case?

Please Note:

1. I am creating procedure in my Local Schema and _SYS_REPO has complete access to my schema.

2. _SYS_REPO also has all access to underlying schema where tables are stored.

3. MonthlyFlexiTimeSeriesAgg view has no "Analytic privileges" switched on.

4. Also, if I comment the call statement then I am able to activate the procedure.

5. I have also replaced the Calculation view (Line 13) with simple procedure but it still does not work.

6. Also, we have complete custom setup of access rights defined and hence we don't have direct access to execute procedure "GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT".

Please let me know what is missing here.

Thanks & Regards,

Anup

Accepted Solutions (1)

Accepted Solutions (1)

pfefferf
Active Contributor
0 Kudos

Hello Anup,

what I find a little bit strange here is that you call the generated procedure for the scripted calculation view in _SYS_BIC schema. Why you are not doing just a select on the calc. view?

Regarding the right: As you are creating a catalog procedure (although "simulating" the name template of a repository procedure) the checks are done against your user and not _SYS_REPO.

Regards,

Florian

Former Member
0 Kudos

Hi Florian,

Yes, you are absolutely correct. While I was waiting for the response, I did try that and it works.

Also, after I replaced the CALL statement with select, I am unable to create procedure in _sys_bic schema, but I can create the same in my local schema. It says "insufficient privilege". Do you know about this behavior?

Thanks & Regards,

Anup

pfefferf
Active Contributor
0 Kudos

What error message do you exactly get (please share a screenshot)? And why you wanna create the procedure in _SYS_BIC schema?

In think you do not have the required create privileges for _SYS_BIC. But you should avoid to create your procedure in that schema.

For details regarding the error you can always activate the authorization trace in the system.

Former Member
0 Kudos

Hi Florian,

As per your suggestion, I did try activating the HANA authorization trace and I found that user generally don't have EXECUTE rights on any procedures under _SYS_BIC schema, as it is a runtime generated objects.

Hence, after all trial and error, my findings says we cannot perform EXECUTE directly on run time generated procedures under _SYS_BIC schema.

The only way to get the solution working is to write a select statement on the view directly. Below is the screen shot of the final solution.

Let me know if you have any other thoughts

Regards,

Anup

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Anup,

Your procedure has SQL Security mode as Invoker as specified in the create procedure definition.

With change in the SQL Security mode, the privilege required changes.

There are 2 security modes:

  • SQL SECURITY DEFINER

        Based on the privileges of the user who is creating the procedure, the procedure will be created. Here, your privileges will come into play.

  • SQL SECURITY INVOKER

        This will ensure that procedure is created based on user who calls the procedure. Thus, the user who is creating should have privileges on objects as grantable to others.

Is your use case for invoker's right.

Also, as a first check, you can try changing the security mode to definer and check if you are still missing any other privileges.

Thank you.

Best Regards,

Anjali.

Former Member
0 Kudos

Hi Anjali,

I have tried changing the security mode as well but that did not work.

Thanks,

Anup

former_member183326
Active Contributor
0 Kudos

Why not run the authorization trace as Florian suggested?