Hi all,
Some months back, I started a discussion on OData read modification exits, and had about the fact that they didn't exist. I was building a UI5 app and needed to be able to filter out values in an OData entity on the server. I also wanted to make sure that
- the records returned for any given entity belonged to the user, and
- to ensure that one user couldn't retrieve the records of another.
Relying on OData filters was not a suitable solution. It seemed, however, that a read modification exit in the OData service definition would suit this need perfectly! So why wasn't there such a mechanism?
Now, to begin the story...
I have a table MYTABLE, with the columns ID, VALUE, USER. This table records sensitive values in the VALUE column, and each row belongs to a user, denoted by the USER column.
Originally, I built my OData service definition like this:
service {
"SCHEMA"."MYTABLE" as "SecureValues"("ID");
}
Too easy. Expose my table via. an entity, and read only the records I need by filtering on USER. Unfortunately, this meant that anyone with the service URL (i.e. http://server/path/to/service.xsodata) could merely replay a query from my UI5 app to get access to all of the values in this table/entity. Even if I was filtering by USER in the app, this could easily be subverted to obtain all values in a raw OData response. So that was a BIG problem.
Then came my moaning. Mostly about the fact that OData entity definitions support update, create, and delete modification exits, but not read. I was like, "WHY?!?!?!?!?!" In my head it seemed that would be the easiest thing ever! So I continued hunting around, mostly in vain, to find a solution. Finally, I settled on using an intermediary server (Node.js) as a reverse-proxy to filter records in requests. In effect, this intermediary performed some work on the URL to apply the username to the query string, and forwarded this on to HANA. It was inelegant and didn't really solve the security problem, but did mean I no longer had to hard code the username query string parameter into URLs. Small win. Or was it? I had just introduced another server into the chain. With more code. And more bugs... That didn't really simplify anything, especially where this was to be deployed within a corporate network. In fact, it made it less appealing to interested parties. Back to the drawing board.
So I then poured through the documentation for Views in HANA, and analytic view privileges surfaced as a possible solution. But these brought with them a host of other issues (not to do with the technology, but to do with my usage of them). All I was trying to do was filter out values on the server. By using an analytic view, I was also introducing sums/counts/aggregations into the process, and I didn't need any of it.
I JUST WANTED TO FILTER BY USER ON THE SERVER
Analytic views also didn't allow for unions. For a union, I'd need an Attribute view. So where I needed a union between two (or more) tables, I'd have the tables unioned in an Attribute view. Overhead. The Attribute view (read: union) would then be embedded in an Analytic view with Analytic privileges. Overhead++. The Analytic privilege used a stored procedure to identify the current user. Overhead++.
Basically, that didn't work. And waaaaaaay too complicated for what I needed. "There has to be an easier way".
While hunting around SCN for solutions to another problem, I arrived at it. It was like a bolt of lightening struck me square in the forehead. Or, for those Despicable Me fans, a light bulb moment.
| The other problem - Configuring an App Site |
|---|
I was trying to configure a new App Site for our HANA Fiori Launchpad. Not a difficult procedure, but there are a few steps. You can read up on that if you're keen... Two articles from w.zhou Creating news tiles with SAP HANA UI Integration Services (UIS) Creating custom tiles with SAP HANA UI Integration Services (UIS) And another useful article from ian.henry Exposing HANA Calc Views via OData to Fiori Tiles Lastly, some other threads helped me get that all sorted, one of which I contributed to with a vital piece of information that I had continually ignored: Create new Fiori launchpad in CAL-HANA | SCN |
Anyway, back the story. So in troubleshooting HANA Fiori Launchpads, I browsed through a lot of SAP-delivered code on HANA. And I discovered a beautiful thing. I discovered the HANA Table Function, or UDF for short (something rich.heilman covered way back in 2012! - Table User Defined Functions( Table UDF ) in HANA). Goodness I felt dumb.
And when used in combination with the very simple HDBView artifact (.hdbview), you end up with a view dependent on a user-scripted SQL procedure. In other words, you now get to write the code to read the table data! I also knew, from hunting through SAP code, that an .hdbview could be used in an XSOData entity definition. By this stage, I was well excited. "To HANA Studio!" I declared in the office, probably a bit loudly. I did get looks.
To recap, the mission is to filter out values that do not belong to the currently authenticated user. The app resides on HANA only, and uses HANA native form authentication to log in. Which means we can access the current, and session, user from HANA SQL Script.
So our table function (this is not compiled code, so bear this in mind when using it yourself - you will need to adjust this if you're using CDS, for example):
FUNCTION "SCHEMA"."GET_VALUES_BY_USER" ( )
RETURNS "SCHEMA"."MYTABLE"
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
RETURN SELECT * FROM "SCHEMA"."MYTABLE"
WHERE "USER" = CURRENT_USER;
END;
That's get_values_by_user.hdbtablefunction.
Quite clearly, this function reads from MYTABLE, but crucially, only reads the values that belong to the current user. It returns a table type of MYTABLE which you now know will only contain rows that match the currently logged in user. So that's the first part.
Now we wrap the .hdbtablefunction artifact up into an .hdbview. This is really simple.
schema = "SCHEMA";
query = "select * from \"GET_VALUES_BY_USER\"()";
depends_on_view=["GET_VALUES_BY_USER"];
And that's get_values_by_user_view.hdbview. Note, the view must have a different name to the table function. That's why I append _view to the name.
Activate those two. Now we have a view that only returns records belonging to a user! Ha! Awesome.
Last step - throw it into your .xsodata service definition. You will need to specify the key now with keyword key, because the view doesn't have one of it's own (perhaps you can add this somehow, I don't know).
service {
"SCHEMA"."get_values_by_user_view" as "SecureValues" key ("ID");
}
Now, when you call this .xsodata service entity, SecureValues, you will only get the rows belonging to the authenticated user. Bingo! What's more, all the OData filter parameters still work, but they will only be performed on the records returned by your view. So all of your UI5 model filters, and so on, will not need to change (other than to stop worrying about filtering on USER). One more thing - because your OData entity is no longer bound to a physical table (it's now bound to a view), you will need to implement create, update and delete modification exits for all OData entities for which you do this. No biggie. Some simple INSERT, UPDATE/UPSERT and DELETE (or delimit) SQL Script will sort that out - or XSJS if that's your thang.
I hope, hope, hope that others hunting around for solutions to server-side record filtering in OData will stumble across this post. I've attempted to wrap all of the articles/threads that helped me get to a workable solution into the one posting for this purpose.
Best o' luck,
Hagen
- SAP Managed Tags:
8 Comments
