that's indeed what you should be doing
I would never rest enable the APP schema or DATA schemas - i would only enable the user schema - which then has EXECUTE/SELECT privs as necessary on your PL/SQL APIs and VIEWs, etc
Thanks Jeff, all of the examples I have come across enable the app owner schema, through which the ORDS_PUBLIC_USER connects by proxy. This has always seemed like it takes the object access control away from the database and puts it in the hands of ORDS.
Are there any examples you can point to that use this approach?
Examples, esp when done by me, are for simplicity and expediency - not intended for the real world
You should take a look at this
A scheme where you keep your data, code, and users separated is not an 'ords' thing, but general accepted way for building database applications.
The user can only do things like execute stored procedures in the code schema, and those stored procedures only have the necessary privs to interact with the data in the data schema. This is a bit extreme, most folks I've seen split out to 2 schemas vs 3
Thanks again. Yes that is exactly the way we normally expose our owner schemas to applications: Via a 'proxy' account that should only have execute on APIs within the owner schema to 'serve up' the application data. We are not so extreme as to use the 3 schema approach, but we find that an owner schema with a proxy works well. We're new to ORDS and the understanding was that ORDS 'needed' to enable the owner account directly, with security controls taken out of the database. Good to know that is not the case!