Back when I was working on a master’s degree, one of our professors told us a story about a colleague who learned how to navigate the Unix system. She was so thrilled about learning this new skill that, in her exuberance, she stumbled upon some hidden human resource files containing confidential information. Eventually, an administrator realised that the woman had located these files and told her “you can’t do that.” Well, she could and did do that. Even though the files were hidden in the hopes that people wouldn’t find them, they were not secured. Security by obscurity doesn’t work because someone will eventually find what you are trying to hide if they are motivated and put enough effort into it.
On the other hand, there is the problem of showing resources to people when they don’t have rights to them. Nothing is more annoying than clicking on a link and then being told that you do not have permission to view the resource. It would have been better if you hadn’t known about it at all.
In a Windows shop, administrators control what people can do and access by using Active Directory ACLs (access control lists), group membership, and Group Policies. Security in AD can get complicated quickly. To ensure administrators follow the principle of least privilege, proper planning and assigning rights to groups instead of to individual users are essential. I’ve not been an AD administrator, but from a user perspective, it seems to do a good job of both securing and hiding resources.
SQL Server doesn’t always hide what you shouldn’t see. Say an account only has SELECT permission on one table in one database on a server. The account can view all the databases when using SSMS, even those where they can’t connect. After connecting to that one database, they only see the one table in SSMS, but querying other tables returns an error message stating that SELECT permission is denied. A different message appears when attempting to query a nonexistent table. While the specific messages are useful for troubleshooting permissions problems, they give too much information to someone with evil intentions.
Once security has been figured out, it easy for organisations to enable self-service for things like business intelligence or even provisioning virtual machines. It’s essential, however, that the user is not overwhelmed with too many choices. Having too much from which to choose wastes time and can be quite frustrating.
Take a product like SQL Clone, for example, with its new Teams feature. Until the recent 4.0 release, DBAs could only control who could create images and clones, not which ones they could create. For a small shop, that might be good enough. In a bigger team, this might discourage DBAs from allowing self-service to keep someone from creating a clone from an image they shouldn’t see. With the new Teams feature, a DBA has precise control so that developers can only create clones from specific images. This makes self-service more attractive and saves time for everyone involved.
Just hiding what people shouldn’t see never works. Showing them more than they need to see is annoying for the users. Getting security right is a win for both admins and users.
Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.