Beginning SQL #5 - Security

Welcome back to me! It's been 7 months since my last blog post. I fell into a well last October and have not had access to an internet connection until just this week. It was a difficult ordeal for me. I was fed through a garden hose they managed to snake down to me so I have been living off of oatmeal and water, I did get them to put a steak in a blender and feed it down to me but it clogged up the hose.

Anyway, last week was PASS SQL Rally in Dallas. I was lucky enough to get to go on the company dime and it was worth every penny. There were a bunch of great speakers and I learned a lot of good info that I can use at work. The only problem I had was deciding which speakers to go see. There were many sessions I wanted to see that were at the same time. I got some advice from a fellow CACTUSS member who suggested that if I had to make a choice to pick the speaker who wasn't local because chances are if they are local you can see them again at a local user group meeting or local SQL Saturday.

This week I am going to return to my Beginning SQL series and talk about SQL Server security. This is a topic where I am going to have to learn to practice what I preach. I have been known to take the quick and easy route when setting up security for applications that hit my SQL servers. The policy I should be following is the policy of most restrictive permissions. Don't give any user or app any more permissions that it needs.

Principals and Securables
The people or applications that are given access to your SQL Database are known as principals.  There are three different types of principals

• Windows - This can be a windows user or group that is given access to to the server. The user or group can come from either Active Directory, in a domain environment, or the users and groups that are defined locally on the server.
  
• SQL Server - These are users (called logins) or groups (called Roles) that are created locally within the SQL instance and are authenticated using SQL Server Security. These live at the SQL Instance Level.
   
• Database - These are users or roles that are created within each Database. 

Windows and SQL Server principals (logins) only give you access to the server. You need to associate the login with a database user or role or a Server role. When you create a Login on the SQL Server you can choose either Windows authentication or SQL Server Authentication. Windows Authentication allows you to search either the local or domain directory to choose an already exisiting user/group. SQL Server authentication allows you to create a username password combination that only exists for that SQL Instance.

Below is the New Login dialog box:

Database principals are the users that you can use to assign actual permissions to databases and database objects. While logins live at the server level database principals live at the Database level. You can have different database users and roles for each of your databases on the server. This allows the granularity of access control that I mentioned earlier. There are three types of Principals at the Database level.

• Database Users - Database users are the security level at which actual connections to the database and it's objects are made. You can create a Database user,  link it to a Server level login then assign it to Database Roles to give it the permissions needed to access the database.
  
• Database Roles - There are a number of fixed database roles that exist in SQL but you can also create your own user-defined roles. Database roles (like Windows Security Groups) allow you to assign permissions to a group of people (database users) rather than having to give permissions to the database objects to each individual user. This allows for ease of administration, especially in a larger environment.
  
• Application Roles - This allows you to create a security context for an application to connect to the SQL Server Database

Securables are the objects that you want to control access to. Objects in SQL server can be secured at three different levels.

• Server - These are the objects that live at the Server level (like logins) that are granted access to at a Server level. You can only give access to server securables to server principals (not database users).
  
• Database - These are the objects that live within a Database but are not at the next level (schema). These include: Database users, schemas, Database roles, etc.
  
• Schema - These are the objects that live within a schema in a database. A schema is an added layer of security that allows you to group database objects into different security groups. These objects include tables, view and stored procedures.

Well, there you go the 50 foot view of principals and securables. Researching this piece really helped me understand the different levels of security in SQL server. If you want more detail on the subject I would suggest you pick up a copy of "Accelerated SQL Server 2008" which is available from Amazon in print or for the Kindle. Next time I will dive into the SQL server Agent. 

Hasta Luego,
RutherfordTX