ORACLENERD: Database Application Security

Database Application Security - A Visualization

Security, in regards to the database, is pretty broad. There are a multitude of ways to secure your database. From physical or logical access to SQL Injection.

This is a (not so) pretty picture of how I visualize security from an application point of view.

Secure OLTP Application

The base of any application are the tables. Access to those tables should be limited to database views and PL/SQL packages (API) that are owned by the application (schema).

SELECT privileges on those views should be given to only users who need it (users also includes other applications). Views present the data in the format that is required by the application. VPD (column or row) can be used to further restrict access to sensitive data.

EXECUTE privileges on the APIs (PL/SQL) should only be given to those applications that need it. Under no circumstances, should direct INSERT/UPDATE/DELETE privileges be given to another user.

Much of my thoughts on this matter are directly traceable to reading AskTom for so many years. I can't point to any one specific article, I think it's the accumulation/internalization of reading for so long.

It makes sense though.

Who better to decide the functions (INSERT/UPDATE/DELETE) that can be performed against a given set of tables than the person(s) who created them? If said person has left, make someone else responsible for them. I've fought hard over the years to implement this "pattern" and have been fairly unsuccessful. It is difficult to go against years of doing it one way (full access to the underlying tables) to forcing someone or something to use the API.

The idea to visualize it came to me recently and I need to put it down.

What do you think? Am I full of it? Is this reasonable? Anything I left out?

Labels: database, design, oltp, security

¶ 2/21/2010 06:17:00 PM

Comments:

"Under no circumstances, should direct INSERT/UPDATE/DELETE privileges be given to another user."

There are valid reasons for separating the API schema (and view schema) from the data schema (requiring grants from the data schema to API schema).

If you do that, even if the API schema is abused (think SQL injection) it has limited privileges on the data schema. It can't drop constraints/triggers (or tables/indexes), it can't grant privileges. You can even use a resource profile to stop it reading too many records in a single call.

It also means the data schema account can be locked (or no CREATE SESSION).

# posted by

Gary Myers : February 21, 2010 6:57 PM

Personally, I wouldn't be so dogmatic that under no circumstances should I/U/D privileges be ever given to another user.

It is important just to have a line drawn somewhere, where you say "anything in here is trusted" and "anything out here is untrusted". If the line surrounds all the tables in a schema, and all the views and packages are outside the line, that's fine - and easy to communicate to developers - but I wouldn't say that's the only valid design. It's not the most flexible policy, for one thing.

For example: I've helped design one application which included a number of public "staging" tables which were intended to accept any external input; the data would then be validated, transformed and loaded into the "real" tables that were more protected. The staging tables were "outside the line" and we used a naming convention to tell them apart.

# posted by

Jeffrey Kemp : February 21, 2010 8:19 PM

Gary,

I'm not a big fan of separating the data from the API (different schemas, if I understand you correctly). I like the closely coupled.

That said, I've never worked in or nor seen that type of setup so I would have to ponder a bit on the advantages/disadvantages.

chet

# posted by

oraclenerd : February 21, 2010 8:23 PM

Jeffrey,

It is dogmatic, I know. I am that way for a reason, just seen way too much crap out there.

I would consider allowing access, but it would have to be a unique situation. All this is of course is predicated on me having absolute control, which I rarely do. :)

You also seem to be describing a datawarehousing type application. I was/am trying to describe my ideal OLTP design.

I'm just going on my experience, especially the last couple of years. Give someone an inch and they'll take a mile.

# posted by

oraclenerd : February 21, 2010 8:29 PM

I try to separate the API layer from the schema for a couple of reasons.

1) Security as noted by Gary. Don't want people with access to API layer accidentally being able to mess with tables.

2) Some technologies expose all components of a schema. For example the Native Web Services will expose all tables, views and PL/SQL in a schema. If you are using them you may require at least 2 API schemas. Those exposed through native web services and those that are not. A similar situation can be true of Mod PL/SQL if you configure it incorrectly. Separating is safer.

I prefer schema_owner, api_owner(s), login_user(s) approach.

Each to their own. :)

Cheers

Tim...

# posted by

Tim... : February 22, 2010 4:42 AM

@chet:
"That said, I've never worked in or nor seen that type of setup so I would have to ponder a bit on the advantages/disadvantages."

You mean you *still* haven't finished the EBS install challenge? How am I supposed to teach you about patching, then? ;-)

EBS is all about the separation of API from data. The APPS user owns all (or close enough to all to round up) of the PL/SQL, and the individual product schemas hold all of the data.

# posted by

jpiwowar : February 22, 2010 3:09 PM

@john

I haven't had time to breathe lately, let alone muck around with my EBS install. I'll get to it, I have to. I still have my theory about APEX integration that I'd like to prove/disprove.

# posted by

oraclenerd : February 22, 2010 8:55 PM

Tim,

I haven't worked with those technologies either. Man, I haven't worked with a lot! That's frustrating.

My quest to best Mr. Kyte is way behind schedule!

Seriously though, you make valid points.

Without knowing (or having experience in) those other technologies, I can only rely on your experience. I would still test it out though. :)

# posted by

oraclenerd : February 22, 2010 9:00 PM