When we talk about security, we often point to the Point of Least Privilege. I write a lot about applying this to SQL Server, but it’s important to handle this outside of SQL Server, especially at the file / share level. Why would we care about this as DBAs / DB Pros? Here are a few good reasons:

• Location of backups
• Files for replication
• Files used for ETL

Too Much Read Access:

By having these locations exposed with greater than required read access, it means folks can potentially get to the data and abuse it. You have an information disclosure / data exposure issue. It’s not just about trusting the individual. Anyone can fall prey to a malicious email which then deploys malware onto the system. From there the attacker hops, using the credentials of said user. Therefore, it’s important to lock down read access as much as possible.

Too Much Write Access:

This one is more of a concern with regards to ETL processes as there is typically minimal validation done on the files. For instance, do you perform an MD5 hash check on the files you might import into SQL Server? Therefore, if someone understands what’s in the files, it would be trivial to undermine the contents therein. This is why reconciliation checks and the like are so important. But even they can be beat.

Another problem, though, is that if someone has write access in both production and development, a write intended for development can happen in production. If it’s not caught, you could have a big problem with respect to that ETL process. Again, this isn’t a trust issue. All it takes is someone who has too much work to do (that describes just about everyone in IT), hasn’t had enough sleep lately (ditto), or someone who is distracted for some other reason.

Typically, when someone has write access, that person also has delete access. Therefore, if someone wanted to be malicious, you can imagine the kind of damage that could be done if they deleted the snapshot replication files or the backup files. The same is true for the ETL files, especially ones where there are some cycles turning to produce them or the files that are the result of manual processes (like where Excel spreadsheets are filled out and then imported).

The Bottom Line:

That’s why we have the Principle of Least Privilege. Applying it, regardless of your full trust in your personnel, is important. And as I described to someone recently, people change. Someone who is trustworthy but who is certainly underwater with respect to finances could consider committing an act that he or she would normally avoid. How fast can this happen? Divorce, unexpected medical bills, being ripped off, totaling a brand new car, etc., are all ways that a person’s finances can suddenly do a 180. Therefore, seek to lock down anywhere files are used as part of any of your processes.