I’m in the process of reviewing my notes and demos for my upcoming session, Introduction to FileTables, at SQL Saturday Orlando. There’s a few things I’ve been meaning to write about, so this post will be on some of the topics I’ve skipped. Some have come from questions I’ve be asked, others from just trying different thing.
Multiple Filegroups and FileTables
However if you browse to where the filestream data is stored you can’t see which is which. Not a big deal since you shouldn’t be accessing the files from here anyway.
You can have multiple filestream filegroups in a database, but they can’t share space. Each one needs it’s own directory. For example you can’t put 2 filestream filegroups at C:\MyFiles\Data, but you can create 1 at C:\MyFiles\Data1 and another at C:\MyFiles\Data2.
Databases usually only have one default filegroup. If you’ve set up filestreaming for your database then you’ll have 2 defaults, one for your tables and indexes and one for filestream data. Any FileTable you create will be created in the default filestream filegroup, unless you specify which filegroup to use. To do that you add the FILESTREAM_ON option…
CREATE MyTable AS FILETABLE FILESTREAM_ON Secondary
In Windows you can specify who has access to what directories, including sub-directories. For example let’s say you have a directory called Documents, with Billing, Human Resources, and Orders sub directories. You can easily set permissions on the HR directory so only the HR department can access the files, and the same for Billing and Orders. But even though you create directories in FileTables, you can’t set permissions the same way. The directory is a record in the table, and if you have access to the table you have access to the directory.
That’s not to say that you can’t still deny access. There are a few different methods of securing your FileTable files.
The easiest way would be to create a separate FileTable for each department, or however you want to split your files. Then grant access on each table the same way you’d grant access to any other table in the database. . .
Another method would be to update the is_hidden column to 1 in the tale for each directory and/or file you want to block access to. When someone accesses the FileTable shared directory they won’t see those objects. However, if they have set the option to see hidden objects in Windows Explorer, they’d be able to see and access them. You can update the is_offline column to 1, and they can see the objects but not open them. Or update the is_readonly column to 1, and they can open the files but not modify them.
If you only want to store the files in a FileTable, you may want to look at the filestreaming options for your database. You can set FILESTREAM Non-Transacted Access to Off and the only access would be through the database. Or you can set it to ReadOnly to allow access but no modifications.
I may have another post on FileTable security in the future. In the meantime, I’d love to hear from you. Let me know if you’re using FileTables, what you like/dislike about them, any tips or tricks you’d like to share.
See you in Orlando in a few weeks!