RE: Controlling access to pdf/doc files (db "better" than filesys tem?)

[GRIFFITHS ian <ian.griffiths () liv-coll ac uk>]

My understanding of the initial problem was that data was only being
compiled in to files for dispatch to visitors anyway, and that files would
not necessarily exist should there not have been a request for them.  From
that angle, and perhaps not as BLOB type, but db lookup would have been the
only mechanism in place in that scenario.

Pretty much a solution in leaving all data in the db, rather that
pre-preparing pdf or doc files which the user may or may not end up
downloading.

If possible I'd like to take this opportunity to ask if anyone has more
information on the 'db vs filesystem' comparison.  Being as this is not
strictly security related (indeed it could be argued there is no clear
winner in this arena) it may well not be appropriate on the list.

Regards

Ian

-----Original Message-----
From: David Wall @ Yozons, Inc.
To: David Cameron; Sangita Pakala
Cc: webappsec () securityfocus com
Sent: 28/02/2004 19:13
Subject: Re: Controlling access to pdf/doc files (db "better" than
filesystem?)

> Why is storing the data in the BLOB a good solution? From what I can
see
> the only advantage of this is that it takes it off the file system.
How
> is this any different from moving the files to a location in the file
> system that is not accessible from online? A little obscure, not
> necessarily any more secure.

One key advantage that a db has over a filesystem is that you have full
transactional support.  Any meta data stored about the file is stored
with
the file so insert/delete/update work together with transactional
integrity,
especially since that file being stored may also be stored with other
records related to the file.

Another advantage is that when doing replication or backing up a
database,
the data and the file are backed up together.  If you use a filesystem,
then
you need to  backup the db and the filesystem and hope that the two
backups
create a consistent view if they were restored later.

Lastly, with db triggers/constraints, you are less likely to have a
filesystem file be removed without also removing the associated records
about that file.  That wouldn't be the case with a filesystem that only
has
filesystem-type security constraints compared to the more powerful
mechanisms inside a db.

> In SQL Server (can't comment on other platforms) BLOBS perform poorly
> and a real PITA to extract and work with, so why store files in the
db?

I think it would be interesting to see whether performance benefits of
being
in the db are real or not, to see if doing the query, opening and
streaming
the BLOB data from a db is any worse/better than doing a query, getting
a
file name, opening the file via the filesystem and then reading and
streaming the data back.  My impression is that the db would perform
better.

> that  in SQL Server is that all data in SQL Server is split over ~8k
> pages. When you add a BLOB it needs to be split into 8k chunks. When
you

But filesystems also store data into pages, often much smaller than 8k
chunk.

Our Signed & Secured application stores all files as BLOBs in a database
for
all of transactional and backup capabilities, but we've never run tests
of
100+ concurrent web users downloading files to see if the database or
the
filesystem would be faster.  In general, faster was less important to us
being able to support lots of concurrent requests because the speed of
retrieval from the db was always assumed to be faster than it could be
streamed back across typically slower Internet links.  After all, the
data
has to be sent back to a user's web browser, so the speed of the
transfer is
limited by the slowest link between the browser and the web server.

David