Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

File/Folder/Share Permissions for DBAs and Database Developers

Every so often I run into a fellow DBA or database developer who isn't crystal clear on how file/folder and share permissions play together. If you're an MCSE or an experienced system administrator, this should be old hat for you. However, given that I've come across it enough, I thought it might make for a good blog post.

File/Folder Permissions

First, separate file/folder permissions from share permissions. We'll bring them back together in a moment. Let's just start with file/folder permissions. And we'll keep it simple. The ones that often come into play are the following:

  • Read - Allows you to read the contents of the file or folder. If you are talking about a file, this allows you to open up the file or copy the file. If you are talking about a folder, it allows you to see the contents of that folder.
  • Write  - Again, as the name implies, it allows you to write to the file or folder. Note that Write alone doesn't give delete permissions, but it might as well. There's nothing stopping you from writing a file that is empty, thereby destroying the previous contents.
  • Modify - Allows read, write, and delete.
  • Full Control - Same as modify + the ability to assign permissions.

Most of the time you're only looking to use read or write in a ETL task or the like. Full control should almost never be needed. You don't usually want your process modifying permissions on files and folders. Now, once we understand the basic permissions, let's talk about how Windows takes those permissions to determine what a user actually has. Unless there is an explicit DENY (like in SQL Server), the operating system aggregates the permissions. For instance, assume user John is a member of the following security groups ETL Users and Support. The permissions assigned to these groups are:

  • John - none are explicitly assigned because the best practice is to use security groups wherever possible.
  • ETL Users - Read permissions on the folder.
  • Support - Write permissions on the folder.

When we put those together, John will have both read and write permissions since the permissions are aggregated. Now, let's say that John is placed in another group, called DBAs. And DBAs have full control. Again, when we aggregate the permissions, John will end up with full control because it comprises read, write, and more. Let's take it one more step and say John is placed in the Developers group. Now the Developers group has a DENY on write. So if we look at all the permissions, we get:

  • John - none are explicitly assigned because the best practice is to use security groups wherever possible.
  • ETL Users - Read permissions on the folder.
  • Support - Write permissions on the folder.
  • DBAs - Full control on the folder.
  • Developers - Deny write on the folder.
  • John will have all the permissions given to full control EXCEPT write permissions. The operating system and SQL Server treat the DENY permission the same: it acts as a trump, regardless of the other permissions.

    There is one other thing I need to speak on here and that's the concept of inheritance. A file in a folder or a subfolder will, by default, inherit the permissions of the parent folder. You can add to these permissions, but if you want to continue inheriting permissions, you can't take any permissions away (though you could use DENY). So if the parent folder gives a group Full Control but on the subfolder you want the group to only have Read, inheritance isn't going to work. With that said, it is possible to break inheritance. And what this does is allow you to set whatever permissions you want on that file or subfolder, regardless of what the permissions are on the parent folder. However, you must then set and maintain the permissions manually. For instance, if I have a folder called C:\ETL where the security group ETL processes has Modify permissions (the ability to read, write, and delete files) but there is a subfolder C:\ETL\archive where I only want them to have Read permissions, I will have to break the inheritance on C:\ETL\archive and then I will have to set the permissions I want manually.

    Share Permissions

    Now that we've got the basics of file/folder permissions, let's look at share permissions. Share permissions are like file/folder permissions in that they aggregate and they handle DENY permissions in the same way. With respect to share permissions, there are only a few of them and they are rather straight-forward:

    • Read - Allows you to read the contents on the share.
    • Change - Allows you to read, write, and delete contents on the share.
    • Full control - Change + the ability to modify permissions.

    I won't go through an example since share permissions function the same way as File/Folder permissions.

    Putting the Two Together

    If the file/folder access is happening on the same computer, not going through the share, then only file/folder permissions apply. For instance, if I'm looking to read C:\temp\someDoc.txt on my local system, only file/folder permissions are in effect. I'm not going through a share, so the operating system won't consider the share permissions. But once I go through a share, such as if C:\ETL had a share on it, ETL$, and I access files through it, then both sets of permissions come into play. Here's how the operating system resolves what the effective set of permissions are:

    1. It aggregates the file/folder permissions for the resource (the file or folder) being accessed.
    2. It aggregates the share permissions for the share.
    3. It compares the two sets of permissions and takes the MOST restrictive.

    So if I have Change permissions on the ETL$ share (which exposes C:\ETL from the example above), but I only have Read file/folder permissions on the archive subfolder (C:\ETL\archive - see the explanation of inheritance above) that I'm accessing through that share, the most restrictive permission is Read. So when accessing the archive subfolder, I only have Read permissions. At the root of the share, which corresponds to C:\ETL (again, see the explanation of inheritance above), I have Modify permissions. Modify is effectively the same as Change, so I would have the ability to read, write, and delete files at that root level.

    Another example is where a user has Full control permissions at the file/folder level but has Read permissions at the share. If the user is accessing a file on the computer locally, then the user can do anything he or she wants. But if the user comes through the share, the user can only read the file. That's because the share permissions are most restrictive.

    And there you have it, a quick primer on file/folder/share permissions.

     

    Comments

    Posted by Jerry Hung on 16 July 2009

    Appreciate the quick recap, which definitely helps under some confusing scenarios

    Posted by Anonymous on 17 July 2009

    Pingback from  Links for the Week of 2009.07.17 | Jeremiah Peschka, SQL Server Developer

    Posted by Paul Mu on 20 July 2009

    Very useful as a reminder, even for those with MCSE :-;

    Posted by Laurie Dunn on 21 July 2009

    Thanks, very helpful summary.

    Posted by David on 21 July 2009

    Thanks for the quick overview... well written, to the point and easy to understand.

    Posted by Steve Jones on 21 July 2009

    Nice job, Brian. A good example for us to follow, and a refresher that's good to read through. Love to see a follow-up on specific permissions you might grant for, say an ETL job, for a DBA,e tc.

    Posted by Nicole Bowman on 21 July 2009

    Thank you for a very clear explanation. I am one of those that was a little hazy. Not any more now!

    Posted by Randy on 27 July 2009

    Unfortunatley it seems thatthis no longer holds true in Windows 2008. As recently tested, share permissions can now over-ride File system permissions. Read permissions on the filesystem and full control on the share, now allows writing to the share. (where the folder perms should restict to read-only). We assume this is a bug or glitch - we'll see if it becomes permanant in future releases.

    Posted by K. Brian Kelley on 28 July 2009

    It's not a bug or a glitch and the share permission isn't overriding... something I just tested. By default, in Windows Server 2008 users have a special permission to Create / Write Files and Folders. If you check the advanced properties under the security tab and navigate down, you'll see it. It's inherited from root. This does not make me very happy.

    Posted by Norman wong on 18 September 2010

    Thank you for sharing this information with us.

    <a href=”http://www.xcertvip.com”>MCSE</a>

    Posted by norman_wong15 23516 on 18 September 2010

    Thank you for sharing this information with us.

    http://www.xcertvip.com

    Posted by andrew.whettam on 27 February 2013

    Thanks Brian, good article.  For all the reasons you've explained above, it is a good idea to set share permissions to Authenticated Users: Full Control (which effectively takes them out of the equation) and then use file/folder permissions to manage security.  It's much simpler, just as secure and can save a whole load of grief trying to work out why something doesn't work as it should.

    Leave a Comment

    Please register or log in to leave a comment.