0

I am not really a database guy. I had to use a localdb for an application that I created using ASP.NET MVC 5. Now, I understand that the type of database can easily be changed by altering the connection string.

What I want to do is for me to be able to select the users (preferably based on their Windows credentials) that can read from the database.

I am using a localdb v11.0. My schema looks like this:

File table:

  • Id [int]
  • content [varbinary(max)]
  • Email [nvarchar]
  • Access [nvarchar(10)]

How can I do this? Typically, a user will directly try to get the file content from the content column.

Also, is there a better way to store files in databases?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ritratt
  • 1,703
  • 4
  • 25
  • 45

2 Answers2

1

If you're wanting to restrict columns, you've got a good use case for creating a view. Grant select permissions on the view and don't give permission to the underlying table.

As far as storing files in the server, Microsoft has a paper called To BLOB or Not To BLOB on that.

Here's their conclusion:

-if your pictures or document are typically below 256K in size, storing them in a database VARBINARY column is more efficient

-if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient if you use the FILESTREAM attribute you can maintain

-in between those two, it can be a toss up

If you store the binary in the database, look at moving the file to it's own table if you're not accessing it with every call. If you're using a view, you can get columns from both tables.

If you're using filegroups take a look at files and filegroup architecture.

drewk
  • 116
  • 2
  • ok great. my files are pretty small. so i am doing the right thing. But I want to get the files directly from the database without going through a view or website or anything. Is there a way to do that? – ritratt Aug 07 '14 at 17:13
  • @ritratt: Not really. The problem with binaries is that they need context to be usable. For example, from SQL Server's perspective, a VARBINARY or FILESTREAM column filled with a PNG is the same as one filled with a JPG. You need the context of knowing that it's actually a PNG in there in order to pull out a PNG. SQL Server doesn't concern itself with that. – Chris Pratt Aug 07 '14 at 17:27
  • You're right that makes sense. So I suppose creating an admin control with a separate view is the best way to go about this. – ritratt Aug 07 '14 at 17:31
0

It would be better to handle this at the application level rather than trying to pass-through credentials of the user to the database to align with database-level access controls.

Basically all you need to do is enable Windows auth for your application. Then, your users will either automatically be logged on to your application if they are logged in on the domain, or will be required to login before accessing the site. You can then retrieve their username via User.Identity.Name on the actions that serve up these files and use that to select only from files they are allowed access to.

One complication is that you're relying on Email in your table. The value of User.Identity.Name for Windows auth will be in the form of Domain\User. If all the users on your domain have a predictable email address (user@domain.com), then you can use this value to construct the email address. Otherwise, you'll need to dig into AD to get the email address for the user.

Chris Pratt
  • 232,153
  • 36
  • 385
  • 444
  • the files are uploaded by anonymous party users to my website. Only I should be able to download them directly from the database in my organization. So your solution makes sense. Maybe I will try if I can't do it this way. thanks! – ritratt Aug 07 '14 at 17:12