Binary data in database
-
One part of a website I am writing involves serving PDF files. The number of files are not very large and will never go above 100. The client wants an interface to manage the files so that they can update them when newer versions are available. My question concerns the storage of the actual PDF files. My thought process tells me that I have two options. 1) Store the files on disk and manage the links to the files in the database. 2)Store the files in the database as binary data.
Does anyone have any experience with this? Pro or Cons for these options would be helpful. Which is more efficient? How will large files affect server performance. What affect will it have on other databases on the server?
Thank you.
Nerds are cool
Kindest Regards,David Petersen
dipetersen.com -
1) Experience was somebody elses headache.
2) Store to disk and store links in database is easiest and most efficient, plus you don't have to code for placement and readin in database.
3) You will impact overall performance by having to read out/in the file back to/from it's native format where a file copy and links in database do not require that overhead.
4) Just too much work IMHO.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
I agree with Antares686 - we store our PDFs to disk and store links in database. It is easiest and least painful!
-
Vote 3 for Anatares.
Keep in mind this also allows you to distribute these pdfs to other web servers or file servers if the load rises. On the Internet, you could also use a service like Akamai to lower the I/O and bandwidth needed.
Steve Jones
-
Bah humbug. Put'em in the db. They are secure, get backed up, etc. Ain't THAT much work!
Andy
-
-
Possibly. Besides, if MS does implement their plan of using a variant of MSDE for the file system, it WILL be in the db anyway!
Andy
-
-
Hey who needs security, there is always some bug to exploit and get around it anyway. HaHahahahahahahahah
But anyway, if you are using NTFS (which I hope) you can secure those files. Besides I prefer if someone is going to have edit rights they have to login with NT security so I can better track them."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Kowabunga!
I know Im the only one who likes blobs. Nothing to stop you from using NT security in the db. Another advantage is you can full text index. We're looking at using it for a project at work, have voice files that contain sensitive data - right now on a share but the problem is there is no easy way to set file permissions, have to fall back to share. So anyone with access to the share can potentially listen to any file. Thinking of gating it through COM+ so I can use impersonation, that way user doesn't have direct access. Soooooooo much easier if I had it in the db.
Andy
-
more secure than a file system? It shifts the control from the db to the admin, but the access control is the same.
Steve Jones
-
More secure...maybe not. Easier to manage, maybe. Allow me to ramble. Application allows users to record phone conversations (with the permission of the other person of course) which gets written to disk on the phone server. Because space is limited we'd like to move them off to an archive server. Playback is also done through the application, but does require a differnt level of user access in the app. In order to play the file the logged in user has to have permissions on the archive share. As long as they go through the app it's auditing tracks who accessed which file, what was done, etc. The problem is that since the user has to have access rights to the folder, in theory nothing stops them from browsing the folder and listening to ANY file. Definitely we can audit file access via NT and monitor for problems. That's after it's happened. I'd like to have a method of disallowing this entirely.
Options so far:
1) Use FTP to retrive the file for playback, lets us embed a userid/password, user has no access to the archive folder with the files.
2) Maybe use COM+ impersonation - they would have access from within the app but not to the folder using their own login.
3) Dig into the API and try to adjust file level permissions on the fly from within the app - they would have access in the app, but attempts to play files directly would fail.
4) Load the files into a blob and retrieve via stored proc. App uses embedded sql login, user has no direct access to the tables.
Note that when I say embedded password it's not in clear text in the executable. Also the file is not a standard audio format, can't play it with any standard player loaded on a machine. But it is highly sensitive data.
Im open to other ideas. Or at least vote on which of my 4 looks the best!
Andy
-
This has turned into a very interesting discussion. One project that I did controlled the physical files via NT security and managed the links in the DB. The interesting part of this project is that an AS400 produced the PDFs and saved them into a share. I then ran a query of the AS400 to get the filenames and who the files belonged to. Since this was an extranet application, I served up the files to each user without giving them direct access to the file path. I could lock down the directory that stored the files and then when a user requested the file, provided they had authorization to see the file, I sent the file to the user by changing the content header of the http:response. It worked very slick.
This new project is similar except that the PDF's are to be produced manually and uploaded manually and then served to the user via <a> links. It's not imparitive that the files are secure.
Enjoyed the discussion. thanks for your input!
Nerds are cool
Kindest Regards,David Petersen
dipetersen.com -
I understand that with the SQL login you can determine who the person is, join this to some table and allow them to "retrieve" the file. Makes sense. Personally, I'd prefer to lock them out of the share altogether and only allow the app to retrieve this data through some type of impersonation.
Of course, that requires time and effort. If you are in need of a speedy solution, the db works fine. I'd suggest, however, that you use a separate database to make recovery and backups, archiving, etc. easier for the data that is not sound. Use a separate "sound" db.
Steve Jones
-
I'll definitely be considering the separate db idea. The sound file might be associated with any one of my 200+ db's, but I can tag the row in the sound table with an ID that will identify the owner db. I like the impersonation idea myself, but compared to putting in the db seems kinda low rent. Probably end up doing it anyway though!
Andy
But anyway, if you are using NTFS (which I hope) you can secure those files. Besides I prefer if someone is going to have edit rights they have to login with NT security so I can better track them.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply