Application-Based Security

Sometimes applications are coded such that the SQL Server 2005 security is less of an issue. This type of program relieves you of the responsibility of managing how the principals access data. This type of programming access is called application security.

Although the developers are largely responsible for setting up application security, it does not hurt to have a little background on what tools they use.

Application Security Methods

Developers use three basic tools to enforce application security. The first involves using one or more accounts to access the data on behalf of the principals. In this case, the accounts for the principals are often set up in standard tables in SQL Server, and the application checks these tables and accesses data based on the contents of the table. This is a secure way of using SQL Server because there are fewer accounts and privileges to manage. It does, however, place a higher burden on securing the application on the developer.

The second method is to use an application role. In this method, you create roles within the database and grant them rights just like any other account or role. The difference is that no database login accounts are associated with the role.

Finally, developers can start out with a lesser level of permissions and then "jump up" to a higher level of permissions in code. In effect, one account can act like another.

Whenever an application runs against the database, it has an authentication based on the rights and permissions granted to the SQL login account, the fixed server roles, the database login, and the database roles the account belongs to. The combination of all these rights and permissions form the execution context of the activity, all denoted by a number.

In addition to this execution context, the developers can switch a process to another account, on-the-fly. The two primary methods they can use are the EXECUTE AS and the SET PRINCIPAL features. In either case, the activity runs as if the account referenced by either of these commands called it.

You might think that this creates havoc in tracking the application chain. In fact, SQL Server 2005 records all logging and traces (more on that in the next chapter) with both accounts. The account that originally called the code is referenced, and the account that has the proper permissions granted to run the code is also referenced. SQL Server logging shows all the context switches used in the application.