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.



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?

Announcing the World's First OLTP Database Machine with Sun FlashFire Technology

Apparently Larry Ellison is announcing a new product on Tuesday, an Oracle OLTP database machine with Sun FlashFire technology. Sign-up for the web cast here.

I first saw it here, at Francisco Munoz Alvarez's Oracle NZ blog.

Mark Rittman has some more speculation here.

Greg Rahn at Structured Data has one as well.

Talk about shiny new objects...

Baseball Data Modeling

Anyone out there like baseball? Ever had a desire to model a baseball game?

I do and I have tried a few times in the past. It get's pretty hairy down at the game game/inning/player level. If I remember correctly, substitutions tripped me up a bit. There there's the whole datawarehouse side, I'd like that to be part of the project as well.

I started a project on Google Code here, the name is pretty vanilla, baseball-database. If you join you can suggest a better name.

I'd like to try and talk Oracle into giving a few licenses to the recently released production version of SQL Developer Data Modeler. I've hit up @krisrice on Twitter, but he has no control over licenses, just development. I've also hit up Justin Kestelyn (@oracletechnet) who said he would look into it.

I've had no time lately to bother him; perhaps with a few more people...

If you are interested, just drop me a line chet at oraclenerd or message me through twitter.