THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Fifth pillar - Secure

As I have mentioned in all of the previous posts, basic functionality is the foundation of any system. So it goes without saying that if you have just implemented a payroll system, everyone is getting paid.  To meet the basic bar that EVERYONE agrees upon, to be useful things have to work. Frankly, this is generally the only criteria which needs to be met for most systems to be considered complete, and since I don’t want get off on a rant, that is all that I will say (for now at least, it will be in the final introduction to the pillars when I get around to finishing them up.)  So as we move on to this first implementation pillar, we assume that even the worst system out there does what it is expected to do.  I realize this is probably a stretch in reality, but the best part of writing is that while reality figures into it, the goal is a practical version of perfection, not strictly attainable, but certainly approachable by any half decent programmer who knows the rules.

This next pillar deals with keeping eyes out of the places where eyes shouldn’t be. This pillar is Secure. In basic terms, everything is hunky dory until the Janitor finds out that the CEO is making 2.5 million dollars a month because he clicks on the wrong place in the app, or that “devious” employee who is quitting starts digging around and gives himself a raise…and it is impossible to track who did it.

In the initial post, I defined Secure simply as users being able to only see data they are privy to. Sounds easy enough, but when you start to consider what it means it is considerable more complicated than you might imagine.  In my books, I take the “easy” way out and simply cover security internal to a single database, and even that is not easy.  For the most part, the applications that I see/hear about tend to leave security to the applications that are written, and just give a lot of power to the application.  Is that a good idea?  Does it sound like a good idea as you read it?

No. And if you said yes, then you probably either have no responsibility for the security of your data, nor should you.  The fact is, even when using the application to enforce security, you are going to most likely need tables, columns, and coordination.  As an example, a time entry system is an easy thing to implement, until you hit security.  Everyone (except perhaps one person) has someone they report to. So rights to see everyone who works for you seems like a given, but this starts to be a hierarchy.  Then people have dotted line bosses, people who enter time for them (like an assistant or timekeeper), and if you do any cost analysis, all of these people probably have some entry as to how much they are costing to work there, particularly if they get any overtime. 

In the end you have to build tables and queries that enforce lots of row-level security that is not easily done, and if you try to do this without tables and data, it is completely inflexible….so then you have to deal with overrides….

Let’s imagine then, that you get the app straight, and anyone who legitimately gets into the application is not accidentally or purposefully seeing data they shouldn’t.  You are finally done, right? Well, of course not.

Security needs to be considered in terms of access from external sources, and internal sources. Meaning not just a novice hacker who works for you who might just whine about his salary in comparison to a coworker, but now you have to consider that Harry the Hacker out there gets access to the data, what then?  Could your company fall apart because they get your customer list? It doesn’t matter if you are top secret company, if Mr Hacker sends all of your customer an email stating “I have your name because I stole ________’s database,” how many more times will you give your name to that company? When I said I took the easy way out by only considering database security I meant it.

To get it right, you have to build an impenetrable fortress around your data, making completely certain that while friends can use the data from inside the fortress and outside too, (like customers accessing their data on a portal, or you with VPN), baddies can’t get in from any layer.  How you do that is a book in and of itself, and certainly cannot be covered in a post like this, or a book like mine.  In the end, you just have to work with system administrators and network guys and make sure that you are protected, all activity logged, etc, etc.

Of course, if enough administrators get together, you cannot stop or track them. When you control the logging, and the access, you control the world.  So the greater your need for security, the greater emphasis you had best be placing on checking background and checks and balances so that enough people are required to turn off logging/security that it would be impossible to get that many people involved without someone informing on the crowd.

Does this make you feel less secure? It should, unless the security of your data scares the crud out of you, you are not thinking hard enough.  But if you take the time to make sure your implementation design considers the needs of the users and their rights to access data while keeping others out, you will be fine…probably.

Published Tuesday, October 6, 2009 7:52 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Suddenlink Communications said:

Security issues are ignored by many companies because it costs too much.  They simply put their heads in the sand and pray nothing bad happens.

October 6, 2009 11:14 PM

Leave a Comment


This Blog


Links to my other sites


Privacy Statement