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).

PASS Security VC Presentation – The Dirty Business of Auditing

On Thursday, June 22, at 1 PM EDT / 10 AM PDT, I’ll be presenting for the PASS Security Virtual Chapter.

Registration Link

Here’s what I’ll be speaking on:

The Dirty Business of Auditing

Auditing is often a dirty word among DBAs because it equates to more work with little perceived business value. However, auditing is a necessary evil for most businesses and it usually falls to the DBA to ensure SQL Server is properly audited. In this session we’ll look at what the operating system and SQL Server provide for us in order to meet the requirements of internal and external auditors, regulatory legislation, and even overbearing system owners who want to know everything about what’s going on in their application. We’ll consider what tools to use to quickly implement the properly level of auditing to meet the need.