Are Stored Procedures allowed on ReadOnly OData Producer?

[former_SQLA_member1694868]

Using SQL Anywhere 17.0.4.2053

Accessing a procedure via an ODATA producer with the option ReadOnly=true gives this message:

 "Cannot perform the requested operation.  This feed is read-only."

This is the URL:

http://localhost:8080/Client01/OBaby/WebAData?AID=5&$format=json

The issue is that the procedure in question is defined as

 serviceop get "dba"."WebAData" returns multiplicity "*";

and it contains a simple select with a defined result set. I would expect it to pass the ReadOnly requirement.

Are procedures accessible on a read-only OData producer?

[philippefbertrand]

Service operations (stored procedures) are not allowed when using read-only OData producers.

Given the convention that GET service operations aren't supposed to change the database, I can see how one would assume that they would be allowed. Unfortunately, there is no way for us to enforce this. One cannot determine if a given stored procedure modifies the database.

The natural thought when one creates a "read-only" service is that there won't be any surprises (such as a caution that the read-only rule doesn't apply to GET service operations). Therefore we error on the side of caution.

I would be interested in learning about the scenario you are trying to implement. Perhaps I may be able to make suggestions.

[former_SQLA_member1694868]

We offer a database solution to our clients. They want to be able to show that information to their own clients but only pieces at a time (the pieces belonging to each 2nd level client).

What we have implemented currently is that each of our clients' databases have a usergroup of web users that has no permission to the base tables or views, but only to specific procedures that select the data and include tests that verify those user's access to their subset of data.

Some of the web tools we are investigating seem to prefer OData over tool-specific ODBC connections for ease of use, but at the outset I was hoping to connect the OData to the existing procedures as they are already defined and handle the permission checks.

What would you recommend for that type of situation?

[VolkerBarth]

While I understand your concern, Bertrand, the docs seem to imply otherwise, i.e. they seem to lay the responsibility for the non-modification on the service op definer:

OData Producers assume that service operations invoked using GET requests do not modify the database. All service operations that modify the database should follow OData conventions and be restricted to POST requests only.

[philippefbertrand]

I know, I wrote it originally and read it before my post. The emphasis on "conventions" which we can't enforce. Maybe I'm being paranoid but I don't want anyone fooled by the read-only setting into believing that a bad service operation can't still modify the database.

[philippefbertrand]

I would do exactly as you are doing. Use the database's security and have users that can only execute the procedures owned by a user that has access to the required tables.