SQL Server Security Model
November 28, 2007
Windows Authentication, SQL Server Authentication, Windows Groups, Database Roles, Schema, and Application Roles are all aspects used to manage SQL Server Security. So how do you use one or more of these items to develop your SQL Server security architecture for an instance of SQL Server? Is there a correct way or a wrong way for granting people access to SQL Server? In this article, I will discuss the different options available within SQL Server 2005 for managing security.
Windows verses SQL Server Authentication
For an application or a person to connect to an instance of SQL Server, they need to have or be associated with a login that is defined in the master database for the instance. A login can be either a Windows account or SQL Server account. When a person connects via a Windows account this is known as Windows Authentication, whereas when connecting using a SQL Server account it is call SQL Server Authentication.
A Windows account can be one of four different types of accounts. It can be a domain Windows account, a local Windows account, a Windows domain group, or a local domain group. A domain Windows account is an account that is defined to a Windows domain and that has a specific name like DJ\GREG, where DJ is the domain and GREG is a uniquely identifiable name within the domain. A domain Windows account is normally associated with an individual person, but can also be defined for an application, or can be used by multiple individuals. Windows accounts are defined to Active Directory.
A local Windows account is similar to a Windows account but is defined locally on the SQL Server machine, and has the following naming convention: <machine_name>\<login name>. So, if my SQL Server machine name was SSESQL01 then my local Windows account on that machine might be SSESQL01\GREG.
Another type of account is a Windows domain group. A Windows domain group is a unique name you define to Active Directory, much like a Windows account, but Windows domain groups can have members. A Windows domain group member can be one or more domain Windows accounts or groups.
A local Windows group is similar to a Windows group but it is defined locally on the SQL Server machine. Members of a local Windows group can be a local Windows account, a local Windows group, a Windows domain account, or a domain Windows group.
A SQL Server account is an account that is defined only within SQL Server. It is not known to Active Directory. When defining the account to SQL Server you provided a name and a password. With SQL Server accounts, SQL Server maintains the password for the account. This is different from a Windows account where the password is maintained by either Active Directory for Windows domain accounts, or the local machine for local Windows accounts.
SQL Server Login
In order for a person or an application to connect to SQL Server, it must have a login. A SQL Server login can be defined for a SQL Server account, a Windows Account, a Windows Group or a local Windows Group. When you define a SQL Server account, you are defining it in SQL Server so in effect you are defining a SQL Server login. However, when you define a Windows account/group it doesnt automatically become a login to SQL Server, because you are defining the Windows accounts and groups to Active Directory or the local SQL Server machine. So for Windows accounts and groups you need to specifically add them to SQL Server in order for them to become a SQL Server login.
To summarize, Windows Authentication is a login defined for a domain Windows account, a domain Windows group, a local Windows account, or a local Windows group, but a SQL Server Authenticated login can only be defined for a SQL Server account. Only SQL Server logins have passwords that are maintained by SQL Server. Windows account passwords are maintained by either Active Directory or the local machine depending on the type of Windows account. SQL Server Logins are created for SQL Server Accounts when they are defined, whereas you need to create a SQL Server login for a Windows account/group after it has been defined to Active Directory or the local SQL Server machine.
Associating Permissions to Login
When a login is granted access to log in to SQL Server they are only given access to log in but not access to any database or database objects within SQL Server. In order for a login to use SQL Server resources, they need to be specifically granted access to those resources. Permissions can be granted at the Server Level, Database Level, Schema Level, Database Role level, Application Role level or object level. I will describe each of these below.
Server Level Roles
Server level permissions allow logins access to perform various tasks in SQL Server at the server level. Server level rights are given to logins by making them members of server level roles. There are eight different server level roles: bulkadmin, dbcreator, diskadmin, processadmin, public, securityadmin, serveradmin, setupadmin, and sysadmin. Review Books Online for more details about each one of these server roles, and what kinds of access a login gets when they are a member of one of these roles. You would grant logins access to these server roles when you want the login to have specific server wide access. You are not able to add your own custom server wide roles to SQL Server.
In order for a login to perform T-SQL statements against a database, they need to be given access to that database. When a login is granted access to a database a user within the database is identified and created. Each database user name within a database can be associated with one and only one login. Granting a login access to a database doesnt give them any access to objects within a database. You need to grant rights to use an object specifically to a database user or they can inherit rights by being a member of a role within the database.
Database Level Roles
A database role is a way to group users within a database, and provided them access to database objects based on their role membership. When you have multiple database users that are going to have the same rights, it simplifies management if you use a database role. This way when you grant or revoke rights to a database role within a database, all the database users that are members of that role are affected by changes made to the database role.
You can define your own database roles to match your application requirements. You would create different roles for each different set of rights your application needs to control. If your application needs one set of users to be able to only read the data, while another set of users need update permissions then you would create two roles. One role might be named Viewer and would be given only rights to SELECT data for each table within the database. Then another role might be named Update for those that need to SELECT, INSERT, UPDATE, and DELETE permissions to all database tables.
Using database roles simplifies security management. Without database roles, you would need to assign permissions to each database user. By using database roles, you can assign permissions to the appropriate database role, and make users members of a database role to give them the permissions of the database role. Using database roles provides an easy way to identify, name, and manage the different grouping of database permissions.
Database Schema Level Permissions
You can use schema level permission to grant access to all the objects within a schema at one time. Granting schema level permissions to a role or database users can simplify the amount of security work you might need to perform. If a user has a particular access like SELECT at the schema level then this allows them to issue a SELECT statement against any table within the schema. When a new object is added to a database schema the database users get the same rights on the new object that they have been granted at the schema level.
Using schema level permissions reduces the need to change permissions each time an object is added to a schema. However, it also automatically allows users access to any new object without specifically granting them access. If your security policy requires that users be granted specific access to objects, then using schema level permissions might violate this kind of policy. So before you start granting schema level permissions think about the future implications this might have as your schema changes over time.
Application roles are roles associated with an application, and activated by an application. Application roles have no members, but rights are associated to an application role. Accessing and using database objects are granted to application roles just as they are to database roles, or users in the database, but unlike database roles, application roles have a password. Application roles are activated by a connection. To activate an application role the connection would execute the sp_setapprole SP passing an application role name and password. If a valid application role name and the associated password are provided then the permissions associated with the Application Role are enabled for the connection, and all rights associated with the user connection are removed. Once the application role is enabled, the application role rights remain active until the current connection is terminated.
Application roles are a great way of restricting database users from accessing SQL Server directly (outside the application) with rights created to support only a specific application. If all rights to a specific database object were granted to an Application Role then a user would not be able to gain these rights by login on to the SQL Server outside the application. That is unless they know the application role name and password. Therefore, if you plan to use Application Roles to ensure access is only gained by using the application then you will need to consider how you plan to manage the passwords for Application Roles.
Object Level Access
You dont have to grant access to objects using roles or schemas. You can grant access to a database user for each database object the user needs to access. If your database has only a single database user then granting access to each object is easier to set up than using a database role to grant access. However, if you have many database users, granting access at the object level to every user quickly becomes a management nightmare.
While you are designing your database, you need to determine how to manage access to your database objects. You can either manage access at the database user, database role, schema, or application role level. Depending on the number of users, the number of different security views you need, or how restrictive you want your security will help determine what kind of security model you will implement. If you have a single database user then granting access to the individual database users might be appropriate, but if you have a handful or thousands of users, managing security at the database user can become overwhelming very fast. Granting database role, and/or schema level access make managing permissions much easier when there are multiple users and multiple different security access needed. Application roles should be considered as a way to tighten your security rules, especially when you dont want users to be accessing/updating your database outside of the application. Consider all these different ways to manage security while you are designing your application. A little thought up front will minimize the effort and problems with your security model down the road.