[Note from Pinal]: This is a 29th episode of Notes from the Field series. Security is the task which we should give it to the experts. If there is a small overlook or misstep, there are good chances that security of the organization is compromised. This is very true, but there are always devils’s advocates who believe everyone should know the security. As a DBA and Administrator, I often see people not taking interest in the Windows Security hiding behind the reason of not expert of Windows Server. We all often miss the important mission statement for the success of any organization – Teamwork. In this blog post Brian tells the story in very interesting lucid language. Read On!
In this episode of the Notes from the Field series database expert Brian Kelley explains a very crucial issue DBAs and Developer faces on their production server. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of Brian in his own words.
When I talk security among database professionals, I find that most have at least a working knowledge of how to apply security within a database. When I talk with DBAs in particular, I find that most have at least a working knowledge of security at the server level if we’re speaking of SQL Server. One area I see continually that is weak is in the area of Windows file/folder (NTFS) and share permissions.
The typical response is, “I’m a database developer and the Windows system administrator is responsible for that.” That may very well be true – the system administrator may have the primary responsibility and accountability for file/folder and share security for the server. However, if you’re involved in the typical activities surrounding databases and moving data around, you should know these permissions, too. Otherwise, you could be setting yourself up where someone is able to get to data he or she shouldn’t, or you could be opening the door where human error puts bad data in your production system.
File/Folder Permission Basics:
I wrote about file/folder permissions a few years ago to give the basic permissions that are most often seen. Here’s what you must know as a minimum at the file/folder level:
Read - Allows you to read the contents of the file or folder. Having read permissions allows you to copy the file or folder.
Write – Again, as the name implies, it allows you to write to the file or folder. This doesn’t include the ability to delete, however, nothing stops a person with this access from writing an empty file.
Delete - Allows the file/folder to be deleted. If you overwrite files, you may need this permission.
Modify - Allows read, write, and delete.
Full Control - Same as modify + the ability to assign permissions.
File/Folder permissions aggregate, unless there is a DENY (where it trumps, just like within SQL Server), meaning if a person is in one group that gives Read and antoher group that gives Write, that person has both Read and Write permissions.
As you might expect me to say, always apply the Principle of Least Privilege. This likely means that any additional permission you might add does not need Full Control.
Share Permission Basics:
At the share level, here are the permissions.
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.
Like with file/folder permissions, these permissions aggregate, and DENY trumps.
So What Access Does a Person / Process Have?
Figuring out what someone or some process has depends on how the location is being accessed:
Access comes through the share (\\ServerName\Share) – a combination of permissions is considered.
Access is through a drive letter (C:\, E:\, S:\, etc.) – only the file/folder permissions are considered.
The only complicated one here is access through the share. Here’s what Windows does:
Figures out what the aggregated permissions are at the file/folder level.
Figures out what the aggregated permissions are at the share level.
Takes the most restrictive of the two sets of permissions.
You can test this by granting Full Control over a folder (this is likely already in place for the Users local group) and then setting up a share. Give only Read access through the share, and that includes to Administrators (if you’re creating a share, likely you have membership in the Administrators group). Try to read a file through the share. Now try to modify it. The most restrictive permission is the Share level permissions. It’s set to only allow Read. Therefore, if you come through the share, it’s the most restrictive.
Does This Knowledge Really Help Me?
In my experience, it does. I’ve seen cases where sensitive files were accessible by every authenticated user through a share. Auditors, as you might expect, have a real problem with that. I’ve also seen cases where files to be imported as part of the nightly processing were overwritten by files intended from development. And I’ve seen cases where a process can’t get to the files it needs for a process because someone changed the permissions.
If you know file/folder and share permissions, you can spot and correct these types of security flaws. Given that there are a lot of database professionals that don’t understand these permissions, if you know it, you set yourself apart. And if you’re able to help on critical processes, you begin to set yourself up as a linchpin (link to .pdf) for your organization.
If you want to get started with performance tuning and database security with the help of experts, read more over at Fix Your SQL Server.
Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: Notes from the Field, PostADay, SQL, SQL Authority, SQL Query, SQL Security, SQL Server, SQL Tips and Tricks, T SQL
