An additional bonus is the ability to properly meta-tag and thus view images in "virtual folders" or "views". Sure, this could be partially achieved through a well-defined folder system, but would certainly not be as dynamic (do we store by "project, document purpose, author", or "document purpose, author, project" or ... Etc).
I know this doesn't answer the security aspect, but provides an alternative reason to actually do it! A security aspect is that particular meta-tags can be filtered (e.g. User A is not allowed to access files relating to Project B). Again, ACLs on folders could partially do this, but what if a "blob" related to Project C and Project B, but was only stored in Project C folder? And do we then store two copies of the same blob in both A & B folders? Just some more food for thought on the "re-invent the wheel" arguments, Cheers, DK -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Thor (Hammer of God) Sent: Friday, 22 September 2006 1:18 AM To: Brian A. Reiter; [EMAIL PROTECTED]; [EMAIL PROTECTED]; Focus-MS Subject: Re: Storing Images in SQL Server (2005) Inline: > Relational Integrity comes for free with any relational database > system worthy of the name. You know that the BLOB is going to be there > if there is a foreign key constraint to other data in your database. > If you use a string "pointer" to the file system, you have no > assurance that the file will exist. This is not usually a problem > until a couple of years down the road when the original developers are > long gone and someone needs to move, migrate or upgrade the > application or the underlying OS. Then all the files go missing. If > you want to move the app to another server OS, you could be in more > trouble because the file path embeds platform-specific notions about > file-separator character, PATH_MAX and valid characters in paths and filenames that are platform-specific. > > Transactional Integrity comes for free with the database. A file is > inserted or it isn't. You can't have a part of a byte stream written > to a file buffer. Since you have a primary key in every table that > holds your BLOBs, you don't have to worry about whether any file might > be overwritten by the next upload. While not a huge security bonus, I have to say, this is the other major benefit I see... As it is now, even with what I'm sure is a comparatively small amount of content generation at my company, I see a very high number of "orphaned" files that end up living on the file-system forever. Files and images copied here and there, project ID's being renamed, but the "old" files and directory structures persisting, and the overall issue with synchronizing directory structures within this model. I did look at DFS, but that's a solution to a different problem- one of distributing files around within an infrastructure, mainly for high availability for users within an already-existing trust and authentication infrastructure. Replication is FAR easier to secure and control as far as getting the images from point A to point B. We can do what we want with them from there. > Finally, the database acts as a sandbox between your application and > the underlying OS. It's pretty inconceivable that a malicious file > inserted into your database could somehow be executed by your > underlying OS in the ordinary course of things. Your clients could be > affected but not the server. If you're using .NET as your client > database client, you also have the option of code-access security to > limit the I/O of your app so that it can't possibly write to the server outside of the database: > > //Permit read environmental variables PATH and PFPRO_CERT_PATH and > nothing else > [assembly: EnvironmentPermission(SecurityAction.RequestMinimum, Read = > "PATH;PFPRO_CERT_PATH;")] //Permit read > HKLM\Software\Microsoft\.NETFramework registry keys and nothing else > [assembly: RegistryPermission(SecurityAction.RequestMinimum, Read = > "HKEY_LOCAL_MACHINE\\Software\\Microsoft\\.NETFramework;")] > //Permit read assemblies from the GAC and no other file IO > [assembly: FileIOPermission(SecurityAction.RequestMinimum, Read = > "C:\\WINDOWS\\assembly;")] Thank you for this-- I was not aware of the fact this option existed... Big bonus. ;) I think I've got enough info to at least get started. I'm going to forge ahead with my development plan, and see for myself what "real" issues are created. This should be fun ;) t t --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
