I've been working to build a deliverable package in HANA (UI5 application and supporting back-end) to send out to customers. And part of the development process, as many readers will know, is debugging stored procedures. In some cases, you're awesome, and you don't need to do this - "I know it works, I wrote it". But sometimes, just sometimes, it's helpful to turn to the debugger to see why a particular piece of functionality is not working as expected. But what to do when you don't have the auths to debug your own stored procedure?! Eeeeep!
To set the scene, I'm writing a UI5 app, with an OData HANA-only (i.e. no Gateway here) back-end, and have in place a number of OData modification exits. The modification exits are mostly stored procedures (.hdbprocedure files). Read up on these over here: Custom Exits for OData Write Requests - SAP HANA Developer Guide for SAP HANA Studio - SAP Library
I've got an update modification exit on one of my OData entities, Forecasts.
service {
"fa.apps.hpc.xs.views.forecasts::forecasts_by_user_view" as "Forecasts" key ("id")
navigates(
"ForecastFields" as "Fields",
"ForecastDataSet" as "DataSet",
"ForecastRuns" as "Runs"
)
create using "fa.apps.hpc.xs.procedures.forecast::createForecast"
delete using "fa.apps.hpc.xs.procedures.forecast::delimitForecast"
update using "fa.apps.hpc.xs.procedures.forecast::updateForecast";
}
That'd be this guy, in bold. If you're savvy, an update modification exit stored procedure needs a particular signature.
PROCEDURE "FA_APPS"."fa.apps.hpc.xs.procedures.forecast::updateForecast" (
IN new <TABLE_DEFN>,
IN old <TABLE_DEFN>,
OUT outtab <HTTP_ERRORS DEFN>
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
-- DEFAULT SCHEMA <SCHEMA>
AS
-- READS SQL DATA AS
BEGIN
-- Weave your update magic here
END;
I wanted to debug it, just for fun (actually, for a very real reason). The trouble I found however, is that because this stored procedure exists in another schema to my user's one, debugging is no longer automatically available. In Eclipse (I use Eclipse with HANA Add-ons, not HANA Studio, although effectively the same thing) you'll still be able to set the breakpoint, as per below. Nice blue dot. Perfect! Break point set.
But when you start debugging said procedure, you'll notice a little cross over your breakpoint.
Nooooooooooooooooooo!!!!!!!!
That didn't happen before when the stored proc belonged to my schema. But of course, that was my schema. My user owns it and everything in it, including debug privileges for all stored procedures. So I messed around a bit, not quiet grasping what was required here. I also followed the inimitable Rich Heilman's instructions, which you can peruse over here:Unable to Debug a activated Procedure in HANA XS environment. but they didn't quite do what I needed. Presumably, I already had sufficient auths in HANA to debug a procedure, but I didn't have auths to debug this procedure. So I persevered. As you do. And keep doing. And keep on doing... until eventually it clicked! Hunting though the Object Privileges I could apply to my user, low and behold, I see my stored procedure in the list:
"Hmmmmm" I think to myself. "I wonder what will happen if I..."
And there you have it. Once you add the stored proc to your user under Object Privileges, you then determine what specific privileges you should have (note: I'm logged in here as a user with user admin auths, so I can grant privileges to my user, but you could just as well use SYSTEM or whatever if this is not an important HANA instance).
So that's lovely to know. I can add stored proc auths directly to my user. But this is going to be a deliverable package/app. It follows that I should define such auths in an HDB Role artifact - heck, I'm doing that already for all other components of this app, so best keep on point. So, for the purposes of this post, I'll demonstrate adding this to my existing HDB Role artifact. Were you to do this productively, you might consider creating a separate .hdbrole file for debug/administration of the app. I'll keep everything all together for now. I should note, that I am not saving my user with the privileges above. I don't want to have to apply individual privs to each and every user - I already have an .hdbrole file, so I'll use that. Please also note that, shortly after writing this post, I found Rich's post on pretty much exactly this topic. New SQLScript Features in SAP HANA 1.0 SPS7 - specifically, the last section Debugging Authorisation Concept. I've basically just expounded on what's covered in this post, and the one I linked to earlier.
Right then, to my HDB Role! Now, this role is already applied to my user. So any changes to it should automatically apply to my user too.
role fa.apps.hpc.xs.roles::USER_ROLE {
// This is a very broad smattering of privileges for a schema, and will be paired back once the supporting
// stored procedures are in place.
schema fa.apps.resources:FA_APPS.hdbschema: SELECT, INSERT, UPDATE, DELETE, EXECUTE, DEBUG;
// Lots of guff here
// Here's the stored procedure declaration with the required privileges - EXECUTE and DEBUG
catalog sql object "FA_APPS"."fa.apps.hpc.xs.procedures.forecast::updateForecast" : EXECUTE, DEBUG;
// Lots of guff here
}
Add our procedure privileges as above (including DEBUG on the schema), activate the role, restart debugging of the procedure and...
Et voila. Our red cross has happily transformed into a green tick and we're off debugging! I don't know about you, but there's a special, warm place in my heart reserved for green ticks.
#happysigh
In the debugger...
We are breaking exactly out our breakpoint now. Excellent.
This is just a smattering of information about the gauntlet that can be HANA auths. The thing is, they're not really complicated. But it can be hard sometimes to figure out what's required. As this post ironically proves.
For completeness:
My instance is HANA Developer Edition, hosted in AWS via. CAL, ver 1.00.091 (SPS9)
Eclipse is Mars running latest HANA and UI5 Add-ons
Credit for both references to rich.heilman
- SAP Managed Tags:
