ORDS, SODA & JSON in the Database

Sorry for the late reply, just saw this. I am from the SODA team at Oracle. We don't have this capability at the moment. You can only order by values in the JSON content, and not the document components that SODA maintain such as createdOn or lastModified. We can look into adding this functionality to SODA. Do you have any timeframe requirements by when you'd need this?

Hi,Ca

Would be nice to have this functionality, we now have  a workaround which doesn't use QBE.

Other question: can "label security" be used with SODA?

Not sure offhand - let me check and get back to you. Are you asking re SODA REST only or is using SODA Java a possibility? Both SODA REST and SODA Java work with VPD, that I do know. 

Btw, as far as the original question, as a workaround you can of course always add a timestamp to the JSON document itself, and then use it from the QBE (including the orderby).

I checked... I am not an expert on "Label Security", but at least so far I don't see why "Label Security" can't be used with SODA. Under the covers, SODA issues SQL against regular tables.

Some thing to be aware of:

1) Seems you'd need to setup a labeling function to populate the label column on insert (since you won't be able to change the inserts that SODA issues)

2) Typically "Label Security" works with database users, but with SODA REST app you'd typically have one or more collections in *single* DB schema (aka user), accessed by different web (i.e. ORDS) users. It looks like "Label Security" provides some mechanisms to work with such application level users. This would need to be investigated more. I see the following in the "Label Security" white paper:

<start quote>

Mapping Application Users to Database Users Oracle

Label Security supports common application architectures including situations where the middle-tier connects to the database using a single database account. To accomplish this, Oracle Label Security provides the ability for an authorized user to assume the label authorization profile of another user. The PROFILE_ACCESS authorization is required to execute the SET_ACCESS_PROFILE procedure. Oracle Label Security does not enforce a mapping between a physical database account and the user name specified when establishing user labels. For example, user labels and Oracle Label Security privileges can be assigned to a database user named SCOTT who happens to have a database account or an application user such as JSMITH who is only known to the application layer and doesn’t have a real account in the database. The only difference is that when the user SCOTT logs into the database Oracle Label Security will automatically establish an active session label based on levels, compartments and groups assigned to SCOTT. In order for the active session label to be established for application user JSMITH, a call to the Oracle Label Security function set_access_profile is required. This function acts as a proxy for Oracle Label Security and accepts an Oracle Label Security policy name along with an application user name. Applications can use one of the many Oracle SYS_CONTEXT variables in combination with the SET_ACCESS_PROFILE command. Applications using Oracle Enterprise User Security can pass the EXTERNAL_NAME SYS_CONTEXT value to the SET_ACCESS_PROFILE command.

<end quote>

Such API to setup application user info can be invoked via ORDS pre hook mechanism.

Anyway, hope this is of some help. The bottom line is that it looks like "Label Security" can be used with SODA, but it would need to be tried out to say for sure. Knowing more about your use-case would help as well. Feel free to PM me here if you need more info/assistance, and we can also reach out to the team that owns "Label Security" if needed.

Hi MaxOrgiyan,

Thanks for the useful info.

My pleasure. I added you as a friend here, so we can PM each other. If you'd like to take it further, PM me and we can help setting it up if needed.

Hi,

Maybe you could have a look at this one:

run SODA in package problem

Greetings.