There are few good reasons to handle images in a database, perhaps the one most striking is the ability to handle MILLIONS of rows of data. We had a legacy app which stored files in the filesystem, only to have it become a severe roadblock as the number of files went from a testing environment of 100K files to a production environment with millions. In that instance it was deemed easiest and fastest to move those files into the db as BLOBs. Filesystems, unless you go for deep nested directory structures, aren't good at handling that many files.
We also didn't like the fact that important files were stored on an edge server--granted if the attacker owned the web server, they could probably own any files streamed through it as well, but at least it did shelter the files behind yet another layer of defense. Finally, if those files are dynamic, it does simplify backup--in the case of the legacy app described above, it was easier to backup the database rather than the database plus the edge servers (as well as maintain multiple copies of the files on each edge server). Still the best combination would be a fileserver (with large scale nesting of directories if needed) and a database to store the pointers, with a service that could toss the files into the edge server environment upon request (perhaps via a web service or other remoting like interface)--thus giving the ability to handle multiple edge servers, large scale quantity of files, while still providing security. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of James D. Stallard Sent: Wednesday, September 20, 2006 4:24 PM To: 'Thor (Hammer of God)'; 'Focus-MS' Subject: RE: Storing Images in SQL Server (2005) 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. 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. 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ------------------------------------------------------------------------ --- ------------------------------------------------------------------------ --- ------------------------------------------------------------------------ --- ------------------------------------------------------------------------ --- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
