SQL Server permissions and new objects |
 |
By Bob Beauchemin, Niels Berglund and Dan Sullivan
28 Feb 2005 | Addison-Wesley |
|
|
 The following excerpt, courtesy of Addison-Wesley, is from Chapter 6 of the book "A First Look at SQL Server 2005 for Developers" written by Bob Beauchemin, Niels Berglund and Dan Sullivan. Click for the complete book excerpt series or purchase the book.
SQL Server permissions and new objects
We have six new kinds of SQL Server objects in the managed world of SQL Server 2005. Three of these objects are managed code variations on SQL Server objects:
- Stored procedures
- User-defined functions
- Triggers
Three of the objects are new with SQL Server 2005:
- Assemblies
- User-defined types
- User-defined aggregates
The reason that all these objects are new is that they all run executable
code, rather than having SQL Server run the code. In previous versions of
SQL Server, extended stored procedures or COM objects using COM automation to run code always ran that code in the context of the Windows user account that was running the SQL Server service process. With the introduction of a managed environment that can control aspects of code loading (through the assembly loading policy mentioned in Chapter 2) and code execution through Host Protection Attributes (HPAs) that work through code access security, execution of .NET assembly code is safer than extended stored procedures and as safe as running Transact-SQL code from inside SQL Server.
When SQL Server is used as a host for the .NET runtime:
- Managed user-code does not gain unauthorized access to user data
or other user code in the database.
- There are controls for restricting managed user-code from accessing any resources outside the server and using it strictly for local data access and computation.
- Managed user-code does not have unauthorized access to system resources such as files or networks by virtue of running in the SQL Server process.
- CLR procedures and functions are a way to provide security wrappers similarly to the way T-SQL procedures and functions do, by using ownership chaining.
We'll first look at the extension of the traditional SQL Server object security to the new objects and then go on to describe .NET-specific considerations.
Click for the next excerpt in this series: Assembly permissions -- Who can catalog and use an assembly?
Click for the book excerpt series or visit here to obtain the complete book.
|
|
|
|
