Remember What It’s Like to Be a Rookie

File this under “soft skills.” Let me start with a recent experience.

Last week I was leading a team of youth working around their local community. My oldest son was one of my co-leaders and he had just come back from his first year at The Citadel as well as Basic Camp as an Army officer candidate. He had learned new leadership skills and techniques over the past year and he was looking to try them out. A couple of times during the week he pulled techniques from his leadership toolkit that I didn’t think were effective for the situations he was dealing with. However, I have the benefit of hindsight and experience.

I’ve been working with children and youth since my days as a drug and alcohol resource educator while I was a cadet at The Citadel, basically going back about 25 years. My son has been in leadership roles for the last 3 years or so. Therefore, I have 20 more years of experience to draw in than he does. While it was relatively easy for me to consider alternative techniques from what he used, a large part of that is because of the experience I have had. I admit, it’s hard to consider things from his perspective. It’s hard remembering what it was like when I was newly working with youth.

We can make this same mistake in our professions, especially in IT. There was a #SQLChat discussion in which the topic of source control came up. It’s easy to think about what should be a standard professional practice but it’s easy precisely because we have the benefit of experience. Source control’s importance is often clear due to experience. Those of us who have ever lost code understand the need for source control. But if someone hasn’t been exposed to using source control  for tasks such as branching and/or hasn’t suffered the loss of code, it’s usefulness may not initially seem important. After all, day-to-day it’s an additional set of administrative tasks that takes time away from coding. Given that use of source control is not widely taught in academia as a must practice, that means a lot of new graduates from computer science curricula aren’t aware of its importance.

We also have to realize that those new to the field may not be getting slots in mature development shops. That means they may be going to shops that don’t use source control or don’t use it properly. So if they aren’t getting the knowledge in college and they aren’t getting it at their initial job, we shouldn’t be surprised if its importance is lost on them. To that point, the question of whether or not they are professionals came up. They are professionals. It’s not their fault that there isn’t a standard professional body of knowledge (BoK) for development like there is for professional engineers or project managers (see PMI’s PMBOK) . If we had such a BoK, it might be appropriate to make a distinction as is done with engineers. However, until then, we’ve got to remember to look through their eyes and seek to educate.

And this doesn’t just apply to source control. It applies to any area or topic we consider important in our profession. Another item that came up after the #SQLChat ended was handling data. As an industry we’re only now becoming mature in this topic. We’ve been forced to due to numerous public security breaches. This is just one example among many. Remember to try and look at something through a rookie’s eyes. It’s likely a different perspective than through yours as a graybeard, or on the way becoming a graybeard.


Security Basics: The Principle of Least Privilege

Whenever I’m asked about creating a security model for an application or database, I tell folks to follow the Principle of Least Privilege. There are several definitions out there, some wordier than others. Here’s mine:

Give the permissions necessary to do the job. No more. No less.

If this is the basis for your security model, you’re in good shape. I often tie the Principle of Least Privilege into the CIA Triad for information security. I’ll cover that in another post. However, the CIA triad is an acronym of these three words: Confidentiality, Integrity, and Availability. These are referring to systems and data. With that as a basis, here’s how the Principle of Least Privilege is connected to the CIA triad:

The permission to do the job.

Nothing more.

  • Threatens confidentiality.
  • Threatens integrity.

Nothing less.

  • Threatens availability.

Recording of PASS Security VC Webinar

If you were unable to attend this month’s PASS Security Virtual Chapter webinar, The Dirty Business of Auditing, it has been published to YouTube.

As requested, here are the slides: The Dirty Business of Auditing (278 KB).

The Three A’s: Auditing

Authentication and Authorization, the first two of the three A’s of security, control who gets access to what. However, at some point we’ll need to do who is accessing that what and when it happened. That brings us to the third A: Auditing.

Auditing isn’t strictly required on all systems. Going back to our example from yesterday of the almost 21 year-old, the doorman at the club likely isn’t keeping track of the names of who entered and who was rejected. There is no auditing. In that case, it’s not important. What is important is controlling who gets into the club. In a lot of cases, it may be in the club’s best interest to not know who went in.

But within information technology, auditing is a big deal. It becomes a bigger and bigger deal as we want to know about when data is being viewed and when it’s being changed. SQL Server has received better and better auditing options as we’ve received newer versions. However, the very least bit of auditing that most folks recommend is to audit failed logins. You can set this up in the SQL Server properties like so:

This setting is read at start-up, so SQL Server will need to be restarted anything we make a change here. Once it’s on, whether we’re just auditing failed logins or we’re capturing both successful and failed logins, we’ll see entries in the SQL Server error log and the OS’ Application Event Log.

As I said, this is the minimal level. SQL Server gives us a bunch more. However, for the purposes of detailing the three A’s of security, this is all that needs to be said for now.

The Three A’s: Authorization

Having covered authentication yesterday, let’s move on the second A, authorization. Authentication was about proving identity. Now that we know that identity, we can determine permissions. Just because we have authentication doesn’t mean we have authorization. Let me give you an example. 

A club only permits patrons who are 21 years-old and up. A college student walks up to the entrance and hands the doorman his driver’s license. The doorman looks and realizes that the student is just two weeks shy of his 21st birthday. At this point we have identification because the ID card provides it. However, because our student isn’t 21, though he’s close, he isn’t permitted into the club. He’s authenticated, but not authorized. 

Our second A, authorization is about looking at permissions and determining what someone can do. With respect to SQL Server, it can be easy to confuse authentication and authorization for some things. 

For instance, when we add a Windows login to a SQL Server, it’s not unusual for someone to think that we are allowing SQL Server to authenticate that login. However, that’s not the case at all. What we are actually doing is granting permission to connect to SQL Server, and you can see this if you query sys.server_permissions. SQL Server will attempt to authenticate any connection. If you aren’t granted the ability to connect to SQL Server, SQL Server will still authenticate you if you are using a Windows login. It passes off the authentication to the OS. If the OS comes back and says it has authenticated the login, then SQL Server takes those credentials and checks to see if the login has any permissions to connect. If it does, it is authorized and the connection is completed. However, if the login doesn’t have the connect to SQL Server permission, SQL Server will throw an error and then it terminates the connection. The login was authenticated but had no authorization. 

The reason for the confusion is because of SQL Server-based logins. In this case, SQL Server has to perform authentication, and if that succeeds, it then has to perform authorization. It actually performs both operations. But we don’t see these two separately, hence the confusion. 

Remember, authentication is about proving identity. Authorization is about determining permissions after identity has been established. 

The Three A’s: Authentication

When I start talking with folks about security, one of the areas of confusion I often find has to do with the three A’s of security. Specifically, the difference between the first two: authentication and authorization. Let’s look at the first today. 

Authentication is simply proving who you are. With authentication we are confirming identity. We are not worried about permissions. That’s authorization and that’s separate from authentication. 

The traditional way we authenticate in the computer world is by specifying a username and password. However, because anyone can grab and store a password, we often rely on multi-factor solutions to prove identity. The traditional way to think of multi-factor is two or more of the following:

  • What you have
  • What you know
  • What you are

For instance, you enter a password, what you know, and then a pseudo-random series of letters and numbers either generated by an application, fob, or read from a grid card, which is what you have. 

There are other means by which we authenticate. For instance, the Kerberos security protocol uses a trusted 3rd party, in the Windows world that’s an Active Directory domain controller, to attest to the identity of both the client and the server. Client and server certificates work similarly, with a trusted 3rd party providing some measure of identity verification. 

To close, nothing thus far is about determining what you are allowed to do. Authentication is simply about proving identity. Permissions have nothing to do with authentication. Authentication is when you prove you are who you say you are, whether you are a person, a user account, a service, a computer, or a web site. 

#SQLChat on Performance Issues

Twitter can often be a great source of information for the SQL Community, especially with the #SQLHelp hashtag. Another resource that not everyone is familiar with is #SQLChat, which Idera runs periodically. There are moderators that helps keep things going, including at least one person from the SQL Server community, but otherwise it’s open to anyone on Twitter to participate. Just keep an eye on #SQLChat or @Idera_Software during the time one is going on.

The next #SQLChat is Wednesday, June 28, at 12 PM EDT. It’ll be covering performance issues. Each #SQLChat has its target and the moderator will ask a series of questions which folks are able to respond to. These usually result in a chain of questions and answers which are highly educational for those following along. Given that performance is always a big deal, the next #SQLChat is definitely one to put on your calendar and follow when it occurs.

If you happen to miss a #SQLChat, remember, it’s a series of tweets. You can search for those tweets after the fact, either by looking for #SQLChat or viewing the tweets & retweets of @Idera_Software. Therefore, you can still glean knowledge from this community event even if you have a conflict and can’t participate (or forget about it until after it happens).

Previous Older Entries Next Newer Entries