OData read exits – a delightful solution
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 Wenjun 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
Hagen, thanks for sharing - and I enjoy your humor 😉
The more OData exits I write, the more I wonder why I use OData 😉
You're welcome Paul, thanks for the comment! I hear you on the OData mod exits. But for its few shortfalls, I love how simple it is to create an OData entity in HANA. Couple lines of code, and you've got a completely RESTful service! Means I can spend more time introducing bugs into my UI5 code base 😉
Couldn't you use the WHERE clause directly in the hdbview?
Hi Wolfgang,
absolutely you could. And that would to a degree be easier! Reflecting on the difference between the two options, I am reluctant to write a lot of SQL in an .hdbview file, simply because the file is not a proper editor (no syntax highlighting or keyword completion) and the number of double-quote escapes (\" ) would drive me bananas. For small scripts, like my SELECT, that'd be perfect. For anything more involved, I'd opt for a dedicated function.
But to reiterate, you absolutley could put that WHERE clause directly into the .hdbview - and I thank you for your suggestion!
Hagen
A concern from my side would be, have you considered using Calculation View and embedded the filtering logic inside, which could avoid double maintained two artifacts, right?
Former Member,
YOU ARE THE MAN !!!
I'm working on some crucial stuff and I had your exact problem and the same thought process !!
WHY NOT READ EXITS ???? Because it would solve my problem ! Row level authorization cant be applied on tables and Analytical privileges work only on Analytic / Calculation Views.
I was breaking my head to find a solution. Finally decided that - IF No other way - The logic has to be implemented on the client side ! NOT SECURE !
Thanks for this solution ! Will try this out ! .
Just the article I wanted to read - thankful for this Hagen.
I have a slightly modified problem, am using the OData for read on several simple tables - building a SAPUI5 application. This is a multi page application - One PAGE 1 - I authenticate and authorize the user for a particular VENDOR data.
Subsequently data on all other pages is filtered by this criterion of VENDOR.
Problem: on the UI, I discovered that in DEBUG mode, I can change the VENDOR value from '001' to '002' and see all the data that I am NOT AUTHORIZED for!!! The applicatoin is still in BUILD phase - is there a way to extend the READ exit you showed to enforce somekind of an authority check? is there any kind of inbuilt feature with XSODATA that enforces a concept of SESSION KEY that can be defined once the application starts and can be checked for each subsequent ODATA call thorugh out the users session?
Should I build the whole 9 yards of creating my session data and having a TABLE FUNCTION that does this check each time an odata call is made?
Would like to hear your views.
Regard
Sudarshan
Hi,
Is it possible to access SAML assertion attribute in the HANA DB layer?
In above example, if i want to filter records based upon assertion attribute of the user then is it possible?
Code Example:
RETURN SELECT * FROM “SCHEMA”.”MYTABLE”
WHERE “SUPPLIER” = $.session.samlUserInfo.supplierid;
I don't want to add filtering of assertion attributes in the UI and also I don't want to move away from XSODATA.
I have implement XSODATA read exit using above method but want to filter data based upon assertion attributes which I am maintaining in SAP Cloud Platform Identity Authentication Service.
Thomas Jung Please help. Thanks!
Regards,
Vikas Madaan