It's good to see all your bright and chipper faces here at SQL University. Hopefully you've digest Monday's lesson on Authentication. Today we're going to discuss Authorization. Now Authorization can only take place after Authentication has been accomplished. If SQL Server doesn't know who you are, it can't determine what you have access to. Therefore, if you've not gotten the class notes and looked at the videos, it may help you to review before proceeding further. If you're caught up, let's continue. Authorization is simply the permissions a particular person has. With respect to SQL Server, SQL Server is basically saying, "Once I know who you are, I can tell you what you are allowed to do."

Permissions - A Game of "Simon Says"

If you've ever played the game of "Simon Says," you know that you only react to the instruction given when Simon says to do so. If you react to any other instruction, you're out of the game. If Simon says to do something and you don't, you're out, too. SQL Server works kind of like Simon says. If you tell SQL Server that a certain permission is to be given to a user, then it's like you played Simon says, only you are Simon. SQL Server will carry out that permission exactly as you said. So if you tell SQL Server that Jimbo has the ability to read data from a particular table (granting SELECT permissions on that table), then SQL Server will allow Jimbo to read data from that table whenever he asks for it. Likewise, SQL Server will not allow access if you do not give explicit permission. SQL Server is the ultimate Simon Says player. So if you don't tell SQL Server that Jimbo can read data from that table, SQL Server won't let him (with the exception of ownership chaining, which we'll look at on Friday).

Securables - A Whole Lot of Tupperware

When I was growing up, Tupperware was expensive. It's not like today when I can run down to the Dollar General and pick up disposable containers for a couple of bucks. In SQL Server, starting with SQL Server 2005, there is a new concept called securables. Securables, in a nutshell, are anything you can assign permissions against. There are also special types of securables called scopes, which are nothing more than securables that can contain other securables. Within SQL Server there are three scopes:

• Server
• Database
• Schema

Servers have securables like logins and endpoints as well as the securables/scopes databases. Databases have securables like users and symmetric keys as well as the securables/scopes schemas. And schemas have the securables we normally think of like tables, views, and stored procedures. That's the hierarchy. Now, normally we grant permissions directly against tables, views, and stored procedures. So if I grant SELECT on a table, then the person can execute a SELECT query against the data in that table. If I think of Tupperware, my wife has given me permission to open up the Tupperware containing the salad. But the Tupperware containing the lasagna, well, that's off limits. So I have SELECT rights against the Salad Tupperware but I have no rights against the Lasagna Tupperware. SQL Server is my wife, but SQL Server is always on duty. So if I try and open up that lasagna, I'm going to immediately get an Access Denied!

Now let's take this a step further. If you do it right, you can put smaller Tupperware containers inside larger Tupperware containers. This is the concept of using scopes in SQL Server. Imagine that the lasagna container fits inside the salad container. My wife has given me permission to open up the salad container. Lapsing back to my past as an 8 year-old boy, I interpret her permission as saying, "You can eat anything in the salad container. So I take the lasagna container and put it inside the salad container, dumping any salad that prevents the true prize from fitting. I close up the salad container and then say to myself, "Well, time to eat what's in the salad container." And then I proceed to open both containers, first the salad one, then the lasagna one, and eat the lasagna. Sure, my adult self knows I'm in trouble as soon as my wife catches on, but we're talking lasagna! SQL Server operates as our eight year-old selves do. If you give the permission on the larger container, or scope, it applies to the securables contained within. For instance, if I give SELECT permission against a schema, all the tables and views contained in the schema that permission applies to as well. So we need to think about that when it comes to giving out permissions. We can either give it directly to the object (securable) in question or to the scope that contains it.

GRANT, DENY, and REVOKE - Understand the Interaction

GRANT gives the permission. So if I GRANT SELECT on a table, the person can read from the table. DENY blocks the permission. So if I DENY SELECT on a table, the person cannot read from the table. Also, DENY is a trump. If you've played the game of Spades, all spades cards trump cards of any other suit, regardless of numeric value (so a two of spades will trump a 10 of diamonds or an ace of clubs). When you've got spade against spade, it's all about numeric value. As a result, unless you're playing with house rules which include jokers, nothing trumps the Ace of spades. The DENY is like the Ace of spades. It doesn't matter what the other permissions are, if a particular person has a DENY, it will trump any GRANTs that the person may already have. And that bears explanation.

It is possible for multiple sets of permissions to apply to a particular person. We'll get into the how on Friday, but assume that three different sets of permissions apply to a particular person. Set A grants SELECT against a table. Set B grants INSERT and UPDATE against the same table. SQL Server will aggregate the permissions and give you the sum of all of them. So if we're just considering sets A and B, the person has SELECT, INSERT, and UPDATE on the table. If you've worked with permissions at the file and folder level on the operating system, you should be familiar with this type of behavior. Now let's look at set C. Set C has a DENY on UPDATE. Remember, DENY trumps everything. So the DENY on UPDATE in set C is going to trump the granted UPDATE in set B. So once we consider all 3 permission sets, the person really only has SELECT and INSERT.

By the way, you can replace one for the other. So, for instance, assume set C should have been a granted UPDATE instead of DENY.  If you decide to replace DENY with the GRANT, you can do so simply by executing the GRANT permission. So when applied in the same manner (again, covered on Friday), they will replace each other. So if that's the case, why do we need REVOKE? REVOKE is a giant eraser. If the person has a particular permission, REVOKE removes it. It doesn't matter if it's GRANT or DENY. Instead of overwriting the permission, it simply gets rid of it. So if we go back to set C, and we didn't want to grant UPDATE permissions, but we wanted to get rid of the DENY, we could use REVOKE. If set C had permissions for other tables, we might not want to get rid of set C altogether. But we do need to correct the mistake without granting any additional permissions. In this case, REVOKE is ideal. It gets rids of the permission altogether. If the person doesn't have permission via another mechanism, we're back to that game of Simon Says again. Since Simon (or John or Marie or whoever you are) didn't say the person should have access, then the person doesn't gets access. If, however, the person had access through another means, such as set B, then the DENY is no longer there to block them.

Practical Application:

Okay, enough theory. Like last class, let's see some of this in action. Again, the videos are around 5 minutes or shorter.

• Understanding SQL Server Securables
• The Difference Between GRANT, DENY, and REVOKE in SQL Server
• Granting Permissions Against a Database Object (GUI only)