Skip to Main Content
  • Questions

  • Application / Database design with 1:1 table relationship for most secure entities. Does it make sense or not at all?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Peter.

Asked: June 01, 2016 - 4:11 pm UTC

Last updated: June 02, 2016 - 7:42 am UTC

Version: Oracle Database 10g / 11g / 12c

Viewed 1000+ times

You Asked

Tom,

First of all I want to say that I am a big fan of yours and consider you THE Oracle guy if I may say so.

Here is a question.

Environment / Database set up
One organization with multiple databases. One database is used a master database with entities that would have to be stored redundantly in the other databases. All other databases request information from the master database using their own table id. That way depending on a database only selected data from the master table is available. Explanation:

Master database
----------------
employees table
employee_id, first_name, last_name,address, db1_ employee_id, db2_employee_id, etc


Db1 database
------------
employees table
stores only its own employee_id (db1_employee_id in the master database)


Db2 database
------------
employees table
stores only its own employee_id (db2_employee_id in the master database)

That the idea is for a database setup organization / enterprise wide. We already have employees table (from this example) stored multiple times across all the databases - given only employee_ids in dependent databases nevertheless multiple tables. Here is the main question: in order to make it more secure is it logical / reasonable to store some of the information in one table in separate schema in the master database (in this example information that could be used easily to identify person). Explanation:

Master database
---------------

Schema 1
All tables
employees table
employee_id or employee_id and less secure columns / data

Schema 2 more secure, that is even from the database side the table is not accessible to everyone that has access to the database / application schema from the back end.
employees_secure table
employee_id, first_name, last_name,address, db1_ employee_id, db2_employee_id, etc

Rationale: this question is taking aside views with invisible columns etc. The main question is: is this denormalized database from the point when the employees table is divided into two tables: employees and employees_secure (the other databases are already using the same approach).
Or simply you should always keep the database in the highest normalized form (taking aside denormalization for increased performance). This question applies only to very few tables that store data that is easy to figure by a human and should be accessible only on a record by record for the majority of users.

Is it logical / reasonable approach or a big NO?

Thank you,
Peter

and Chris said...

Great to hear you're a fan of Tom's - he's taking a break at the moment though!

http://www.oracle.com/technetwork/issue-archive/2015/15-sep/last-asktom-2650480.html

So you'll have to make do with Connor and me for now :)

Onto your question:

Splitting the table across schemas could work. It's messy though. A simpler approach is to split the table within the same schema. Provided each application connects with it's own user, then you can place employees_secure in the same schema as employees. Just ensure only the app users that should see it have select privs. If your apps connect as the table owning user this won't work though...

Or an even easier option would be to use VPD to mask the sensitive columns

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1855489000346413353

The whole setup sounds convoluted to me though. Why do you have separate databases vs. different schemas in one database?

Also: splitting a table in two like this isn't "denormalization". If you want to know more about normalization/denormalization check out these links:

http://bkent.net/Doc/simple5.htm
http://fastanimals.com/melissa/WhitePapers/NormalizationDenormalizationWhitePaper.pdf

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Peter Polanski, June 01, 2016 - 4:43 pm UTC


Peter Polanski, June 01, 2016 - 5:02 pm UTC

Hi Connor,

Thank you for your quick response.
Two tables storing redundant data about the same entity should break the first normal form, correct or not? In this case even if we only talk the following situation

employees table
---------------
employee_id



employees_secure table
----------------------
employee_id, first_name, last_name etc

Employee_id is in two tables and I could easily get by with only one table in this case employees_secure.


I wanted to put the secure table (for a very few tables) in a separate schema (still accessible by an application by using a link for example - given this is an overhead for the application performance).

As far as multiple databases storing redundant data / information. It is actually very common situation. Very often the same information about the same entity is stored in multiple databases. Instead of that probably easier and more logical is to store the data for an entity that appears in multiple databases in one master database. One place for updating the attributes for that entity. I would say in this case it is less messy.

My question with the more secure table / schema is as follows. Is it worth it from security point of view or all other existing mechanisms of securing data access are giving the same level of security or even more. That part might be messy. :)


Thank you,
Peter
Chris Saxon
June 02, 2016 - 7:39 am UTC

Actually it's Chris, but thanks anyway!

"Two tables storing redundant data about the same entity should break the first normal form, correct or not?"

Incorrect!

A table is in 1NF if it meets the following conditions:

- There's no top-to-bottom ordering to the rows.
- There's no left-to-right ordering to the columns.
- There are no duplicate rows.
- Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
- All columns are regular

These are all about a single table. Nothing stated about duplicating columns in a second table.

https://en.wikipedia.org/wiki/First_normal_form

I agree having one place as a master copy of the data is a good idea. Given that you have the master database to stop duplication, why do you have separate databases at all? Why can't you have just one?

The solution I'm proposing is:

All tables exist in one schema - DATA_OWNER.
All applications connect with their own user - APP_USER_1, APP_USER_2, etc.
Each app user only has privileges on the tables in DATA_OWNER they have permissions on.

So now you have a choice:

Stick with one table, EMPLOYEES. Then use Virtual Private Database to mask the sensitive columns from the apps that shouldn't have access. You need to be on EE to do this.

OR

Split the table into two as you've suggested: EMPLOYEES and EMPLOYEES_SECURE. "Everyone" has access to EMPLOYEES. But only some apps have access to EMPLOYEES_SECURE.

So to be explicit:

Splitting the table for security is an option. But there are also other ways to achieve the same goal (other than views).

Peter Polanski, June 01, 2016 - 5:14 pm UTC

Connor,

I am taking back the remark that you even need a link from app schema to secure schema. I would probably set up an app role that can access both schemas. I am asking if it makes sense at all. The idea is that most of the users that have access to the application schema from back end wouldn't be able to access secure schema. I know that you can achieve similar result using views etc.

Thank you,
Peter
Chris Saxon
June 02, 2016 - 7:42 am UTC

I think splitting the table can make some sense (though I would also explore other options).

Dividing across schemas seems an unnecessary complication to me.

Chris

A reader, June 02, 2016 - 8:32 am UTC

Thank you Chris for answering my questions.

If I would split a table into two I would keep the secure one in a separate schema though. But at the end of the day I would have to say what you said: "Splitting the table for security is an option. But there are also other ways to achieve the same goal (other than views)." This idea is closed. :)

As far as having one database instead of multiple ("... why do you have separate databases at all?") that's reality. :)
In case when we have multiple databases that's the approach. But I agree that having one database (whenever possible) is the way to go.


Thanks,
Peter
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.