While often when I first create a database it's like this

Application_1--------Database

They almost always will end up something like this:

Application_1-------------|
Application_2-------------Database
Application_3-------------|

Application_1 may still need full read/write access to everything in the database to operate. Application_2 may only need read access but needs that access to everything. Application_3 may only need partial access. I have also experienced situations where say changes to application_1 requires modifications to the underlying database that application_2 does not know about but may need to know the stuff at some point in the future. It would seem such a layering of schemas would provide architecture to support such needs.

Thanks for the very prompt response and the explanation of the benefits of this approach. It's an idea I'd not come across before, but I see the advantages in terms of security and audit.

Great explanation.

How would you recommend referencing objects in the data schema from the application schema?

By explicit schema.object, private synonyms, views, etc?

Would you put any views into the "code" or "data" schema? To me, they're "code" and also they might contain calls to functions which would be in the "code" schema too. But they could reasonably be considered "data". I'd be interested to hear from anyone that's done this before in a production environment and how they divided object types between the two schemas.

The original question is similar to this one:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2168279457908#935647000346474709

Duke - in the system you described in the other thread, what did you do with views?

Chris-- We did it like Tom indicates. Originally I built the views as verification tools (to specify and check the results of ETL transformations), so they were part of the app/ETL/code schema, rather than data schema.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:47458244605081
-- Duke

I began to use this concept a few years ago. It really does work great, but you have to be diligent about it. The developers I worked with really hated it because they had to "wait for access" before creating code. Instead of view it as a positive, they complained to management that it "slow productivity". I fought for a few days, but in the end I was told to fall in line and go back to the lazy way of implementing things. It wasn't too long before developers where accidentally dropping/truncating tables and I got to spend time recovering them. Thank God for the recycle bin and flashback queries.

While I've rarely seen databases retired (though I have seen them re-modeled or expanded), I've seen applications get retired or simply go unused. Being able to basically just drop a schema to retire an app would seem to be a benefit. I've also come across situations where you might want to temporarily disable access to a certain application but the data.

You'd have to think very carefully before dropping a code-schema when you retired an app. Think about the real-world situations where you start by having one code-schema for your application. Then you later develop another application, but some of the code is common with the original app (eg. accessing customer details). Do you duplicate all the relevant packages into your new app schema? Or do you extract the common code and put them into a third "common" schema. Or does the new app just use the packages it wants from the original app's schema? The first two approaches mean you've got to change your original app too. So which do you think is the most likely to happen?
I don't really think there'd be many DBAs brave enough to drop or even temporarily disable one of the code schemas as you suggest.

Chris,

I find many DBAs are willing to do very crazy stuff.

Sorry I wasn't clear. I said BASICALLY drop (I thought that would be clear but I was wrong) not JUST drop or DROP <schema>, your not going to just do this willy-nilly in the operational environment but will have an easy, well-known, documented and testable methodology to retire an application which will more thoroughly do the job (I can't tell you the number of times that potential security and data quality issues have arisen because an obsolete app was not fully eliminated). You would be able to monitor for possible dependencies (dependencies would be clear and concise, easily understood at a glance even for less technical people) and should have all known dependencies documented. Also, these dependencies indicate that you need to think about moving those shared objects to the data level or to something I'd call a shared "library" schema. You also may have a situation where they really aren't two applications. In any case, you know this things going in and already have them documented so your not just futzing about.

Locking down application_schema1 does not necessarily mean that application_schema2 that uses code in the locked schema is also unusable (though you may fully lock it down instead of retiring an application), just that no one can use application_schema1.

Anyway, the best piece of advice I ever got was "your mileage will vary".

Tom -
can't the same control over privileges and access be achieved even if the packages and tables/views exist in the same schema provided all the packages use the invoker-rights access model?
Eg - all access to data is via packages (no stand-alone funcs/procs and no direct querying of tables/views). Tables and pkgs are in schema APP_OWNER to which the application may not connect directly. The application connects using a sep. account APP_USER to which grants on tables/views and the pkgs are given (either directly or via roles). Even though a package is in the same schema as tables, if there were a TRUNCATE statement in the pkg (as you mentioned in your arguement) it would fail unless the invoking user (APP_USER) had explicit privilege to do this (and you can't grant TRUNCATE as a privilege anyway).
The APP_USER schema would only need to contain private synonyms to the packages it was going to access (unless using owner.object notation). You could even make sure the APP_USER itself couldn't modify synonyms (or anything) in its own schema by only granting it CREATE SESSION and no other system privs. Instead any synonyms it needed could be maintained by an admin account with CREATE ANY SYNONYM priv.
I've used this approach successfully at a couple of different sites.
Surely where fine-grained access control is required over data, it'd be better to use Oracle's in-built FGAC and not try and invent your own half-baked version with views in the way you're describing?

(Thanks to Chris for opening this thread, and as usual to Tom for being so clear, concise and concrete.)

@Chris: the "invoker rights" model doesn't protect the data from arbitrary or accidental changes by the SQL developers. It also seems a bit more complex to test properly during development.

@Tom: based on various answers of yours, I've been recommending this setup :
- a DATA schema
- one or more APP schemas as stated above
- roles with execute privileges on APP packages
- USER schemas with assigned roles
- no table APIs (they nullify this layered approach).

Secure the data from the app, secure the app from the user.

Is this a good recommendation? Anything to add?

Sorry, I didn't make it clear that I already follow the principles of using a PL/SQL API-layer and my application code only contains calls to packages and no SELECT/INSERT/UPDATE/DELETE statements.
I've also re-read the chapter on Invoker Rights in your book "Expert Oracle" (though my edition only covers up to 8.1.7 - is there a newer edition available?) and I understand that a lot of information about DML statements in definer-rights packages is gathered at compile-time whereas with invoker-rights this couldn't be determined until run-time. So it means there's more overhead in working this out when the statement executes and additionally the objects referred to in the DML may vary from user to user, depending on the particular environment.
Are these the main two reasons why you emphasise so strongly that invoker-rights should generally not be used?

I agree with Tom - invoker rights are most useful for utilities, not in general. For example, we have a package (in a 'utility' schema) that creates new date-based range partitions for a given schema/table. The code that issues the DDL must be in a call path that maintains invokers rights, so it can do this on behalf of the calling schema.

But for a typical application design, how would you propose using invokers rights and keeping control? If you did this:
User U
--> calls package A.P1 (which uses invokers rights)
--> which performs SQL on table A.T1
then user U must have rights to perform SQL on A.T1 - which means he/she/it can do things like DELETE FROM A.T1; with or without your application code.

<quote>
.. user U must have rights to perform SQL on A.T1 - which means he/she/it can do things like
DELETE FROM A.T1; with or without your application code.
</quote>
Mike - thanks : this makes it perfectly clear.

Hello Tom.

Keeping data and code separated through the use of different schemas as discussed seems eminently sensible.

I wonder how best to scale it to a situation where there are multiple versions of the application code and multiple independent sets of data at different versions.

For example, we currently have schemas cust0 through custN. Each of these 150-odd schemas is at one of about 20 'versions' ie slightly different table structures etc. In addition each schema has the appropriate version of application code compiled into it.

I would like to separate the data from the code. However, having to have a customer app version schema dedicated to each individual customer data schema (ie 300 schemas in total) doesn't seem like a big improvement.

Is it possible to have custData0 .. custData149 and appVer0 .. appVer19 schemas and have client calls on behalf of customer X (running version Y) go through appVerY using custDataX?

I'd thought that compiling everything in appVerN with invoker rights might be the way to achieve this... I'll take your word that it's likely a path to madness.


For context this is an N-tier app with all db connections made as custN.

Thanks for your response, Tom.

Yes, we're using 11g R2. EBR looks interesting. Having a single active edition per session seems as if it would limit it's usefulness though.

Independently versioning different modules of the system and picking-and-mixing them for an individual customer is a useful capability.

I guess with EBR (using one schema, in contrast to multiple 'version' schemas) if
- cust1 and cust2 both used modules A and B and
- cust1 wanted to (only) upgrade to A' and
- cust2 wanted to (only) upgrade to B'
you'd need a new edition providing A,A',B and B' and need to rewrite both customers client applications. And have custN roles that prevent the client application from using the wrong version of a module.


Anyway, assuming a single data schema for all customers and EBR...

Migrating shouldn't be to hard if we add the primary key of the 'customer' (currently a table with a single row) to every table's pk and then use that to create a VPD. And partitioning tables by that customer pk would get us back to effectively the same physical structure. Customers do have wildly different data in some tables though so a single execution plan across all customers might not be as efficient.

My main concern is longevity of editions. There are non-technical reasons why customers aren't immediately upgraded to the latest versions. Is it feasible to continue using an old edition, with cross-edition triggers in place, for extended periods (eg months) or are they only intended for a few hours/days during the upgrade window?

Are there serious performance implications of running old editions under multiple layers of cross edition triggers?
I expect a percentage slow-down to 5 significant figures based upon the extensive information I've given you. :)

Thanks.