Managing Access to Programmable Objects


Programmable objects, such as stored procedures and user-defined functions, have their own security context. Database users need permissions in order to execute stored procedures, functions, and assemblies. Once the database engine has checked for permission to execute a programmable object, it then checks permissions for operations performed inside programmable objects. When database objects access each other sequentially, the sequence forms an ownership chain. You will learn about ownership chains later in this chapter.

Managing Security for Stored Procedures

Stored procedures are probably the most frequently used database objects for a database developer. As with other database objects, stored procedures are subject to security. You need permissions to perform operations, such as creating a stored procedure, and users must have appropriate permissions in order to execute a stored procedure. Table 2-6 lists the permissions that you can grant on stored procedures.

Table 2-6. Stored Procedure Permissions

Permission

Description

ALTER

Can modify stored procedure properties

CONTROL

Provides ownership-like permission

EXECUTE

Can execute the stored procedure

TAKE OWNERSHIP

Can take ownership of the stored procedure

VIEW DEFINITION

Can view stored procedure metadata


Executing a Stored Procedure

When an application makes a call to execute a stored procedure, SQL Server checks if the current database user has the EXECUTE permission on the stored procedure. The following sample grants EXECUTE permission to Sara on the dbo.uspGetBillOfMaterials stored procedure. (The code in this and subsequent sections is included in the sample files as ManagingAccessToProgrammableObjects.sql.)

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant EXECUTE permission to Sara on a stored procedure. GRANT EXECUTE On dbo.uspGetBillOfMaterials TO Sara;


In the same way, if you want to prevent a user from executing a stored procedure, you can revoke or deny the EXECUTE permission for the user.

Managing Security for User-Defined Functions

User-defined functions are programmable objects, such as stored procedures. Basically, there are two types of user-defined functions: scalar functions, which return a single value, and table-valued functions, which return a table. Depending on the type of user-defined function, you need to grant either EXECUTE or SELECT permission (see Table 2-7).

Table 2-7. User-Defined Functions Permissions

Permission

Description

ALTER

Can modify stored procedure properties

CONTROL

Provides ownership-like permission

TAKE OWNERSHIP

Can take ownership of the stored procedure

VIEW DEFINITION

Can view stored procedure metadata

SELECT

Can select data returned from the user-defined function (for table-valued functions only)

EXECUTE

Can execute the user-defined function (for scalar functions only)


Executing Table-Valued Functions

When a user executes a table-valued function, SQL Server checks whether the user has SELECT permission on the table. You can grant this permission on a function in the same way you grant the SELECT permission on tables. The following sample grants SELECT permission to Sara on the dbo.ufnGetContactInformation user-defined function.

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant permission to Sara to execute a user defined function. GRANT SELECT ON dbo.ufnGetContactInformation TO Sara;


Note

There is another type of table-valued function, called an inline function. Inline functions are functionally equivalent to views, but support parameters. This type of function is equivalent to a view from a security standpoint.


Executing Scalar Functions

In order to execute a scalar function, a user needs to have EXECUTE permission on the function. You can grant EXECUTE permission on a scalar function in the same way that you grant EXECUTE permission on a stored procedure. The following sample grants Sara EXECUTE permission on the dbo.ufnGetStock function.

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant Sara permission to execute a user defined function. GRANT EXECUTE ON dbo.ufnGetStock TO Sara;


Managing Security for Assemblies

SQL Server 2005 provides you the ability to include .NET assemblies (objects which reference .dll files) inside the database engine and invoke these assemblies in stored procedures and functions. You can assign the same permissions to assemblies as for stored procedures. Refer to Table 2-6 for those permissions.

Defining a Permission Set

When you create an assembly, you need to specify a permission set. The permission set specifies a set of code access permissions that are granted to the assembly in SQL Server. There are three different permission sets:

  • SAFE The code executed by the assembly cannot access external system resources. SAFE is the most restrictive permission set and the default one.

  • EXTERNAL_ACCESS The assembly can access external system resources.

  • UNSAFE The assembly can execute unmanaged code.

SAFE is the recommended permission set for assemblies that do not need to access external resources.

More Info

You can find more information about Code Access Security on the following URL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod116.asp.


Executing an Assembly

When an application tries to access an object inside an assembly, the database engine checks whether the current user has EXECUTE permission. The following code is used to grant EXECUTE permission on an assembly to the user Sara:

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Grant Sara permission to execute an assembly. GRANT EXECUTE ON <AssemblyName> TO Sara;


By providing EXECUTE permission for one assembly, you are granting EXECUTE permission on all assembly objects to the user.

Managing Ownership Chains

An ownership chain is the sequence of database objects accessing each other. For example, when you insert rows in a table from a stored procedure, the stored procedure is the calling object and the table is the called object. When SQL Server traverses a chain, the database engine evaluates permissions in a different way than it would if you were accessing objects individually.

When you access an object within a chain, SQL Server first compares the owner of the object with the owner of the calling object. If it is the same owner, permissions on the called object are not evaluated. This feature is very useful for managing object permissions. For example, suppose Sara creates a table called Person.SupplierContacts and a stored procedure called Person.InsertSupplierContacts through which Sara inserts rows into the PersonSupplierContacts table. Since these two objects have the same owner, Sara, you only need to grant EXECUTE permission to other users on the Person.InsertSupplierContacts stored procedure in order to allow them EXECUTE permission on the PersonSupplierContacts table. You do not need to grant permissions on the table to other users.

Important

Ownership chains provide a powerful encapsulation mechanism. A database can be designed to expose access to data only through well documented public interfaces, such as stored procedures and user-defined functions, which hide the complexity of the database's actual implementation. Using ownership chains to maximum advantage, a database developer can deny access to all tables in a database to all users, yet still give them access to the data they need.


Managing the Execution Context

The login and user connected to a session or executing a procedure determines the execution context. Login and user tokens provide SQL Server 2005 the information for evaluating object permissions. SQL Server 2005 provides you the ability to change the execution context using the EXECUTE AS statement. This operation is called Context Switching.

Running EXECUTE AS

The EXECUTE AS statement allows you to explicitly define the execution context for the current connection. You can use EXECUTE AS to change the login or the user for the current connection. The execution context change is valid until another context change is made, until the connection is closed, or until a REVERT statement is executed. The following sample uses EXECUTE AS to change the execution context to user Sara.

-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Change the execution context to the user Sara. EXECUTE AS USER='Sara'; -- The following statement will be executed under Sara's credentials. TRUNCATE TABLE dbo.ErrorLog;


The above code will finish with an error because Sara does not have permission to truncate a table. The following code to truncate the table will succeed.

-- Change the execution context back to the original state REVERT; -- Now the following statement will be executed under -- the original execution context. TRUNCATE TABLE dbo.ErrorLog;


Managing Context Switching

Apart from controlling execution context for batches (groups of T-SQL statements sent to the server for execution, as in the TRUNCATE TABLE example above), you can control execution context for stored procedures and user-defined functions. When you switch the context in these modules, you can control which user account will be used to access objects referenced in the stored procedure or function. To accomplish this task, you can use the EXECUTE AS statement with the following modifications:

  • CALLER Statements inside the stored procedure or user-defined function are executed in the context of the caller of the module.

  • SELF Statements are executed in the context of the user who created or altered the stored procedure or function.

  • OWNER Statements are executed in the context of the current owner of the stored procedure or function.

  • <User>or<Login> Statements are executed in the context of the specified user or login.

The following sample creates a stored procedure that switches the context to the dbo user. Then it grants EXECUTE permission to Sara on the stored procedure and changes the context to test the execution of the stored procedure.

-- Create a stored procedure to execute statements --' as dbo. CREATE PROCEDURE dbo.usp_TruncateErrorLog     WITH EXECUTE AS 'dbo'     AS     TRUNCATE TABLE dbo.ErrorLog; GO -- Grant permissions to execute this procedure to Sara. GRANT EXECUTE ON dbo.usp_TruncateErrorLog TO Sara -- Change the execution context of this batch to Sara. EXECUTE AS [USER=]'Sara' -- Execute the stored procedure. EXECUTE dbo.usp_TruncateErrorLog


As you can see in the sample above, context switching can be a good approach to allow some operations to users that you cannot grant through permissions.




Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net