Consider the following scenario:
- There is a Unix user named
gaius on the Oracle server with external authentication, so in Oracle there is a corresponding user called ops$gaius. When logged into a shell, I can also log straight into my Oracle schema, and my cron jobs don't need a password embedded in script either.
- Remote OS authentication is permitted, on the assumption that the LAN is 100% secure and the clients can be trusted (same as
rlogin/rsh used to be normally allowed)
- An attacker gets his or her laptop onto the LAN by whatever means, knows that I work there, and creates a local user on their laptop called
gaius and runs SQL*Plus as that user
- Oracle sees (i.e.
OSUSER in V$SESSION) is gaius and logs that remote user in as ops$gaius
That's not only laughably easy to spoof, but putting on my cynic's hat, Oracle can't make any more money selling you their fancy single sign-on product... Which by the way does fulfill all the points you raise as advantages of OS-level auth. Two passwords better than one is entirely spurious; most people will set them to be the same anyway (there's no mechanism in Oracle to prevent this).
The general principle is that it is extremely difficult to defend in software when an attacker has physical access. And never trust the client.
One thing jumps out here:
The entire process uses the same set of login credentials
Problem
So hypothetical userX (whether some meatsack using Excel, or IIS AppPool Identity) can see some views and code. It doesn't matter what database these views and code are in because userX is setup in 3 databases anyway.
However, you lose ownership chaining like this.
Let's say WebDB.dbo.SomeProc calls PrivateDB.dbo.SomeTable. UserX requires permissions on both objects. If this was OneDB.WebGUI.SomeProc using OneDB.dbo.SomeTable then only the OneDB.WebGUI.SomeProc needs permissions. Permissions on referenced objects with the same owner are not checked.
Note: I haven't looked too deeply at cross database ownership chaining. I only know plain old "ownership chaining" well
Now, as per comments you really have 2 databases that can be combined. Not 3 which was originally implied. However, the intermediate and web can be combined.
The other "private" databases can perhaps be combined, but that'll be a separate issue. See the bottom link for a fuller discussion of "one database or many"
Solution?
If the extra databases are code containers only, then schemas are a better idea.
This sounds like you've used "Database" where you should use "Schema" (in the SQL Server sense, not MySQL sense). I'd have a WebGUI schema, a Helper or Common schema (to replace Intermediate database) and Desktop schema. This way you separate permissions based on clients and just have one database
With one database (in addition to "ownership chaining") you can also start to consider indexed views, SCHEMABINDING (I use it always) and such that can't be done with seperate databases
For more on schemas, see these questions:
Finally, there appears no reason to have separate databases based on "transactional integrity not required". See this question to explain this:
Decision criteria on when to use a non-dbo schema vs a new Database
Best Answer
Your approach seems to be sound. However, I am unsure of what real business intelligence can be gleaned if the reporting is made off of incomplete data. Create a dedicated user with limited privileges (IE No writing/dropping allowed). There is always a tradeoff of information security and information quality.
Perhaps it would be best to understand what you're hoping to accomplish with this reporting and identify what sensitive info can be removed.