Storing Images in SQL Server (2005)

Inline:




On 9/20/06 1:23 PM, "James D. Stallard"
<jamesleafgrove.com> spoketh to
all:

> Thor
> 
> I have to ask the obvious question:
> 
> . Why are you re-inventing the (perfectly god) wheel?
> 
> The filesystem is easy to manipulate from within SQL
Stored Procedures, or
> directly from the app, with VB or WSH - or you're
preferred development
> environment. We already know how to secure a filesystem
and it's credentials
> database is already available to you as part of the OS.
Lastly, the
> filesystem is much faster than SQL at returning file
objects, due to the way
> in which they are stored.

Well, a couple of reasons.  Like I said in my post to
Matthew,
replication/portability to secure destinations is the most
pressing reason.
If this were a static app that only lived in the DMZ, it
would be a no
brainer-I'd leave things as they are now.  But it's not--
I need to securely
move files around to various locations from the internal to
the DMZ and to
my off-site location.  Doing this from a file-system
standpoint brings in
issues of transport (CIFS,FTP, HTTP, etc), authorization
infrastructures,
trusts, and also file replication integrity.  To do it
securely is tricky...
I'm doing it now at the file-system level, but it just
seems like there is a
better way to do it.

> It's quite likely that the only thing SQL can do
faster/better in your
> scenario is return the "Select *" query
faster than the filesystem could
> return a "DIR" command. Everything else
will be ponderous by comparison.
> 
> It strikes me that this may have something to do with
why WinFS hasn't made
> it into Longhorn as databases simply aren't designed
for this type of data.>

Valid points all... But to me, binary data is binary data -
I don't know
that I would say "dbs are not designed for this type
of data," particularly
in the case of SQL 2005 - what's the real difference
between a web server
delivering binary data to a browser from a file or a stream
object? I've
done a good bit of research into various developer
newsgroups about this,
and many people share your feelings of "why re-invent
the wheel."  But I
have to say, in most of the cases where people say "I
want to do this- how
do I?" no one really answers the question- rather,
they say "don't do it
like that." 

I can totally see how performance could be an issue, but
when my clients log
on to my secure site to view project status, they'll only
be looking at a
few documents per project site. But I'm still not totally
convinced that
performance issues will be evident to the user at all. 
There is something
to be said for dynamically creating needed content streamed
directly to the
user's browser in these cases.

Re-inventing the wheel here I think really gives us the
opportunity to take
advantage of some interesting security mechanisms, both in
the areas of
replication/portability and access controls (think views).

Thanks for the thoughts... Still chewing on it all ;)

t 


> Hope this helps crystallise your thoughts.
> Cheers
> 
> James
> 
> James D. Stallard, MIoD
> Microsoft and Networks Infrastructure Technical
Architect
> Leafgrove Limited
> Web: www.leafgrove.com
> Email: j a m e s  l e a f g r o v e . c o m (remove the
spaces)
> Mobile: +44 (0) 7979 49 8880
> Skype: JamesDStallard
> 
> 
> 
> -----Original Message-----
> From: listbouncesecurityfocus.com [mailto:listbouncesecurityfocus.com] On
> Behalf Of Thor (Hammer of God)
> Sent: 19 September 2006 19:35
> To: Focus-MS
> Subject: Storing Images in SQL Server (2005)
> 
> Greetings security professionals:
> 
> I'm starting a new development project where I'm
considering moving image
> and document data into my database rather than storing
the files in the
> server filesystem.
> 
> I've been mulling over the security implications of
this, and want to see
> what others are doing in this area.  The first thing
that comes to mind is
> row-level security, and how others are handling the
"flow-through" from
> table permissions to file system permissions where
you're creating the
> resultant files.  In my environment, I have directory
structures for
> individual clients, with NTFS permissions applied to
the different client
> directories so Client A can only see their own data,
and not Client B's.
> I'm concerned that a possible breach could allow
Client A to see Client B's
> data unless I impose row-level security on the DB or
create multiple views
> for each client.  I'm open to thoughts on how to best
manage that.  Also,
> are you guys "streaming" the content from
DB directly into the browser, or
> are you creating a temporary file first, storing that
in the file system,
> and then referencing that temp file?  If so, how are
you handling
> permissions on that? Via inherited directory
permissions?  And what about
> the context of the web user?  You give them delete
permissions to "clean up"
> the temporary files?  The "steaming"
context seems a better way to do it...
> 
> Just seeing what issues those who have gone through the
deployment process
> have run into.
> 
> Thx
> T
> 
> 
> 
>
------------------------------------------------------------
---------------
>
------------------------------------------------------------
---------------
> 
> 
> 
> 
>
------------------------------------------------------------
---------------
>
------------------------------------------------------------
---------------
> 
> 
> 



------------------------------------------------------------
---------------
------------------------------------------------------------
---------------