Is the separation of data from different applications via Oracle schemas considered secure?

Question

I am evaluating the pros and cons of having different Oracle schemas vs. separate Oracle servers. Having a dedicated server for each application is really expensive and I only want to consider this if the security benefits are worth it.

One of the main sticking points is the topic of SQL injection. In case application A has an SQL injection which compromises all the data in schema A in the Oracle database, would it be possible for an attacker to access the data in schema B (in the same Oracle database) as well?

I am leaning towards the opinion that separation via schemas is enough except for the most critical of data stores. Is this the best practice and what are the main factors to consider?

Answer 1

It is a common concept to create specific Database-Users for each application. These users should have the minimum privileges needed to do their job.

Lets apply this to your example:

• ApplicationA uses DataBaseUserA which has the privileges to Read/Insert/Update/Delete TableA in SchemaA.
• ApplicationB uses DataBaseUserB which has the privileges to Read TableB in SchemaB.
• Of course you also got at least one DBA account.

Now consider ApplicationB is vulnerable to SQL-injection:

• select * from SchemaA.TableA gets injected and executed by DataBaseUserB.
• The DataBase response would be ORA-00942: table or view does not exist

At most use-cases i would consider this behaviour to be secure enought. Of course a completey different DataBase-instance would add another layer of security in case of some unknown security critical issue in Oracle DB.

Answer 2

In the scenario you described, if Schema A is compromised then -- barring some unknown vulnerability with Oracle itself -- it would only have access to data from Schema B to the extent that priviliges from B had been shared with A.

In case this isn't clear, let's assume you have TABLE1 and TABLE2 in Schema B. Now lets assume you execute the following command in Schema B:

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA_B.TABLE1 TO SCHEMA_A;

Then, if Schema A is compromised, TABLE1 is at risk, but TABLE2 should be safe.

And if you instead execute the following in Schema B:

GRANT SELECT ON SCHEMA_B.TABLE1 TO SCHEMA_A;

Then your risk is reduced (but not eliminated): The evil doer that penetrated Schema A could see the data for TABLE1 (which still might be very bad, depending on what is there) -- but should not be able to change the data.

Now back to the point that I italicized: ("barring some unknown vulnerability with Oracle itself"): A lot of the vulnerabilities that have caused enormous grief started as unknown vulnerabilities with specific applications. If you have the luxury of separating the servers (taking into account server expense, maintenance time and expense, current and future needs to share data directly), then the more isolation you can give to your data, the more secure it is.