Oracle Database Discussions

Hi All,
let's talk about data security in Oracle databases
and an appropriate application design model(from pure database perspective) to mitigate the risk of data leakage.

Most of the time I see(and I suppose (since I don't have enough experience) this is something like a standard) that customer-related data
is stored in tables where sensitive and non-sensitive data is stored in one and the same table.
Be it card holder data or customer's personal data and so on.

For example:

CUST table:
- cust_id
- username
- password
- address
and so on.

CUST_CREDIT_CARD
- cust_id
- ccard_number
- credit_card_expiry_date
and so on.

Let's pretend for this example that CUST.PASSWORD and CUST_CREDIT_CARD.CCARD_NUMBER are the sensitive data columns.

There are several schemas in the database:

APP_DATA
APP_CODE
APP1
APP2
APP3
APP4

APP[1-4] are database users used by 4 applications to access the database.
These 4 applications have access to the database data through APP_CODE(contains procedures/function/packages and so on) or direct access on APP_DATA objects.

This means that BY DEFAULT APP[1-4] have access to APP_DATA(through APP_CODE or directly)
and if one of APP[1-4] is prone to SQL Injection, sensitive data in CUST and CUST_CREDIT_CARD data will be compromised.

In order to avoid this we'll have to examine all the code-base for SQL Injection prone code.

For the past several days, I think of a way to reduce the code-base that might be prone to SQL Injection(thus reduce work related to SQL Injection checks)
and reduce the risk of SQL Injection in future.

Is this following applicable? Please, specify other advantages/disadvantages/improvements. Thanks in advance!

The plan is:

1. Create a new schema APP_DATA_SENSITIVE with default tablespace app_data_sensitive_enc that is encrypted(TDE) using Oracle Advanced security option.
This schema will hold sensitive data from APP_DATA schema.
By using encrypted tablespace, we'll be able to avoid sensitive data leakage by stolen disk/tape backup.

2. Move sensitive data CUST.password and CUST_CREDIT_CARD.ccard_number to new tables in the newly created schema
APP_DATA_SENSITIVE.CUST_SENITIVE.password
APP_DATA_SENSITIVE.CUST_CREDIT_CARD_SENITIVE.ccard_number

Moving the sensitive data will help us to reduce the performance implication of
-(see 5) setting auditing on these tables
-(see 1) using encryption of tablespace where sensitive data resides.
If we use encryption for the original tables'(CUST and CUST_CREDIT_CARD) tablespace performance will degrade since frequent modifications are done on non-sensitive columns.


3. Create password protected role that will expose
APP_DATA_SENSITIVE.PKG_SENSITIVE_DATA_USAGE package to APP[1-4].

4. Fix the code so every usage from APP[1-4] for password and ccard_number is by using:

set role identified by 'secure_role_password';
APP_DATA_SENSITIVE.PKG_SENSITIVE_DATA_USAGE.[PROC_NAME/FUNC_NAME];
unset role;

This way the code that needs the sensitive columns will use them by providing a password.
By default APP[1-4] will not have direct access to sensitive data columns and all code that is prone to SQL Injection
but doesn't need the sensitive columns will not be able to get the sensitive data.
All new code, that doesn't need sensitive data columns, that is prone to SQL Injection will not have access to the sensitive data.

This way we'll have to check only the code between ‘set role’ and ‘unset role;’ commands which is much less compared to the whole code-base(APP_CODE).

5. Enable auditing on APP_DATA_SENSITIVE tables.
Most of the time non-sensitive data is frequently modified and most companies avoid setting full auditing on tables like APP_DATA.CUST and APP_DATA.CUST_CREDIT_CARD
If sensitive data is moved to APP_DATA_SENSITIVE.CUST_SENITIVE and APP_DATA_SENSITIVE.CUST_CREDIT_CARD_SENITIVE
most of the time the sensitive data is not frequently modified and setting auditing will not have such big performance impact.



Advantages that I can think of:
- better auditing from performance point of view and easier monitoring of audit trails on sensitive database data
- tablespace encryption helps to avoid stolen backup data leakage
- easier checks for application SQL Injection checks since application users will not have access to sensitive data BY DEFAULT and prone code will be easier to find(it is always between set role .... unset role)
- Database Vault easier implementation once sensitive data is moved to separate objects.

Disadvantages I'm aware of:
- Development and QA effort to redesign the application, but in my opinion such columns are not so frequently used, so the efforts will not be huge.
80%-20%( gain-pain ;) )
- employees that support the code-base in production will have access to the password protected role password.


What do you think about such kind of a model?
Please, specify other advantages/disadvantages/improvements.

Thanks in advance!

Additional considerations that i forgot to add during my initial problem description:

- what if APP_DATA and APP_CODE are one and the same schema
- it's a best practice to:
encrypt sensitive columns by the application layer(code that is outside the database - be it ruby, c#, java and so on).
This way, 'separation of duties' makes it harder to access the sensitive data in clear text from the database itself.
the idea is to further restrict access on this data from the database layer(even the data is encrypted by the application).

In our example if CUST.PASSWORD and CUST_CREDIT_CARD.CCARD_NUMBER are encrypted by APP 1 and are used by all other apps(APP[2-4]) need this data read-only,
this means APP1 db user must have select/insert/update permissions on the two sensitive data columns and APP[2-4] must have select access on the two sensitive data columns.

Edited by: Adrian Angelov on Feb 22, 2011 3:01 AM