Other Permissions Considerations

In addition to using access controls on securable objects, you can use other methods to protect resources. A few common methods include these:

• Microsoft .NET permissions

• SQL permissions

• Role-based security

.NET Permissions

Using Microsoft .NET Framework permissions is a way to protect a resource when accessing it from managed code. Not only can you have role-based security that describes the privileges of a user , you can also use code access security to limit the access of the application s resources. For example, .NET applications can deny permission to the registry, thus preventing the application from accessing the registry. Chapter 15 covers the complex .NET Framework security model.

SQL Permissions

Almost all objects in Microsoft SQL Server can be secured by using permissions, much like ACLs can be set on securable objects in the Windows operating system. When an action is performed in SQL, the permissions for the user account executing the query are determined. For instance, when a user runs a stored procedure, reads or writes data, or creates a database or database item, the user must have the appropriate level of permissions.

SQL Server can use either defined SQL accounts or Windows accounts to grant permissions to a database. A user can be granted server-wide roles, in which they have powerful permissions over the entire server. In addition to server-wide roles, a user can be granted permission to a specific database. A user can be granted or denied permissions for objects in a database. Figure 13-8 shows the permissions for a user called test . The user is granted Execute permission on a stored procedure called proc_Test and is denied access on calling SELECT on the testTable table.

Figure 13-8: Permissions on testdatabase for the user test

What happens if the stored procedure called proc_Test does a SELECT * FROM testTable statement? Because the user is granted Execute permissions, the command succeeds even though the user is denied the SELECT command on testTable . Figure 13-9 shows the result in SQL Query Analyzer.

Figure 13-9: How the stored procedure still executes even though permissions are denied for the SELECT command

By restricting the permissions on the database for the user that connects to the SQL Server, you are effectively restricting what the user accounts can do on the database server. This method of defense is extremely useful if you have a Web application that uses a back-end SQL Server because a SQL injection bug will then be limited in how an attacker can use it. Refer to Chapter 16, SQL Injection, for more information on SQL injection attacks.

Important  

The Public role in SQL is equivalent to the Everyone group in the Windows operating system.

SQL Triggers

In addition to SQL permissions, an application might use SQL triggers to add business logic to restrict permissions when executing a query. The SQL engine automatically calls the triggers when data in a table is added, deleted, or modified; however, triggers cannot be used to determine whether a SELECT statement was executed.

SQL Security Functions

SQL Server provides security functions that can be used in a query to return information about users and roles. These include the following:

• IS_MEMBER   Indicates whether the current user is a member of a specified Windows group or SQL Server role

• IS_SRVROLEMEMBER   Indicates whether the current user is a member of a specified server role

Using such methods can provide a different type of access control policy.

Global Temporary Stored Procedures

SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is available only to the user that created it, but a global procedure is available to everyone. The problem with using global temporary stored procedures is that all users can access them, and the permissions cannot be revoked . Also, a global temporary stored procedure can be altered by anyone . Think of all the problems such vulnerability can cause, especially if the user account that is calling the global temporary stored procedure is a high-privileged account.

Global temporary stored procedures are created like any other normal stored procedure, but the procedure name is preceded by two pound signs (such as ## procedure_name ).

To find whether any global temporary stored procedures were created, you can search the SQL queries your application executes and look for something like this:

 CREATE PROC  ##<procedure_name>  

Or you can run the following query to see whether any temporary procedures are currently created in the tempdb database:

 SELECT name FROM tempdb..sysobjects WHERE name LIKE '##%' 

Important  

Your application should avoid using temporary global stored procedures because they can easily be modified by a malicious user.

SQL Server also allows the creation of global views and tables. These are created similarly to how global temporary stored procedures are, prefixing the name with ## when creating the object. The preceding SQL query can also help you find any global views and tables that are created.

Role-Based Security

Your application might implement its own permission model using a role-based technique. Somewhat like Windows groups, a role-based security model grants access to particular users. However, instead of using ACLs, the application can use its own mechanism to protect resources. Applications can still use the Windows operating system to handle user authentication, but can combine business logic and role-based authorization to determine the access level of a user for particular features. For example, a restaurant might use an application to manage such items as seat assignments, orders, and inventory. The application might define roles and their functions as shown in Table 13-3.

Table 13-3: Example Roles for Restaurant Application

Role	Description
Attendant	Assigns seats to guests makes reservations
Server	Creates orders handles bills
Cook	Reads orders updates status of orders
Manager	Does all of the above refunds customers handles inventory

When users log on to the restaurant application, the functions they can complete are limited to the roles they are assigned. For example, a Server cannot refund a customer; only the Manager can do that.

Testing an application that uses role-based security is a lot like testing the permissions on a securable object in the Windows operating system. Make sure that the privileges granted to each role make sense, and ensure the application enforces the permissions.