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.
Assembly permissions -- Who can catalog and use an assembly?
In order to catalog assembly code to SQL Server, a user must have the ability to execute the CREATE ASSEMBLY DDL statement. ALTER ASSEMBLY and DROP ASSEMBLY are related DDL statements. By default, only members of the sysadmin server role and the db_owner and ddl_admin database roles have the permission to execute the assembly-related DDL statements. The permission can be granted to other users. The user or role executing the statement becomes the owner of the assembly. In addition, it is possible to assign an assembly to another role using the AUTHORIZATION parameter of CREATE ASSEMBLY or ALTER ASSEMBLY, as shown in Listing 6-1.
Listing 6-1: Using ASSEMBLY DDL
— — create an assembly while logged on as sysadmin — owned by sysadmin — CREATE ASSEMBLY SomeTypes FROM 'mysvrtypesSomeTypes.dll' GO — create an assembly owned by DBO — while logged on as sysadmin CREATE ASSEMBLY SomeMoreTypes AUTHORIZATION dbo FROM 'mysvrtypesSomeMoreTypes.dll' — alter the first assembly to be owned by DBO ALTER ASSEMBLY SomeTypes AUTHORIZATION dbo
In the most common scenario, CREATE ASSEMBLY reads bytes from the Windows file system; although if you specify CREATE ASSEMBLY, specifying the hexadecimal bytes that make up the assembly as part of the CREATE ASSEMBLY DDL statement, no file system access of any kind is required. The preceding example reads bytes from a network share. ALTER ASSEMBLY may also read bytes from the file system if the options of ALTER ASSEMBLY that reload code or load debugging symbols are used. Some Windows security principal must have the permission to read the required files. But what security principal is used? This depends on the privilege of the user running the SQL Server service process and whether the SQL Server user is using Windows integrated security or SQL Server security to log in to the server.
If the user is logged in using a SQL Server security login, the access to the remote file system will fail. SQL Server logins can, however access the remote file system, using the credentials of the service process (if "sa") or other credentials if defined to SQL Server users as shown earlier. When a Windows security login is used, access to the bits is obtained through impersonation. That mean file system access will fail if the user running the SQL Server service process does not have the (Windows) right to perform impersonation -- that is, to change the currently executing thread so it executes as a different user. If the user running the SQL Server service process has impersonation authority and the user is logged in to SQL Server as an NT user, the request to read bytes executes using an impersonation token of the currently logged-on user.
One final piece of the puzzle is needed for CREATE ASSEMBLY and ALTER ASSEMBLY. We can define three different levels of code access security for a specific assembly -- SAFE, EXTERNAL_ACCESS, and UNSAFE, listed in order of decreasing code safety. Although these levels relate to code access security, additional permissions are required to execute CREATE and ALTER ASSEMBLY and give the resulting assembly any permission set other than SAFE. The executing user should have CREATE ASSEMBLY permission or be a member of the ddl_admin, dbowner, or sysadmin roles, and if the EXTERNAL_ACCESS permission set is specified, should also have the EXTERNAL_ACCESS permission. The user should be a member of the sysadmin role if the UNSAFE permission set is specified. In addition, if you use the UNSAFE permission set, you must sign your assembly using either a certificate or a strong named key. The certificate or strong named key must then be cataloged to the database, so that it is known to SQL Server.
Permissions and Assemblies
Some of the permissions that relate to an assembly are based on the user's identity -- that is, normal SQL Server authorization. In general, access to all the .NET-based SQL Server objects is predicated on the checking of three different types of interobject links. These are known as invocation links, schema-bound links, and table-access links. Invocation links refer to invocation of code and are enabled by the EXECUTE permissions. The code may be managed or Transact-SQL code, such as a stored procedure.
Examples of this could be a user calling a database object (for example, a user calling a stored procedure) or one piece of code calling into another piece of code (for example, an assembly calling another assembly, or a procedure accessing a UDT column).
Schema-bound links are always between two database objects and are enabled by the REFERENCES permission. The presence of the schema-bound link causes a metadata dependency in SQL Server that prevents the underlying object from being modified or dropped as long as the object that references it is present. For example, you cannot drop an assembly if it contains a user-defined type that has been cataloged, and you cannot drop a user-defined type that is in use as a column in a table.
Table-access links correspond to retrieving or modifying values in a table, a view, or a table-valued function. They are similar to invocation links except they have a finer-grained access control. You can define separate SELECT, INSERT, UPDATE, and DELETE permissions on a table or view.
REFERENCES permission gives a user the ability to reference CLR stored procedures and user-defined functions, when using a VIEW that is created with the WITH SCHEMABINDING option. With respect to triggers, userdefined types, and assemblies, REFERENCES permission gives a user the ability the create objects that reference these; for example, REFERENCES on a UDT gives a user permission to create tables that use the UDT as a column. REFERENCES permission allows the grantee to define schema-bound links to that object.
EXECUTE permission on an assembly allows a user to catalog additional assemblies that invoke methods or instantiate public classes within that assembly. These allow interassembly invocation links. Granting a user EXECUTE permission on an assembly does not automatically give him access to the stored procedures, user-defined functions, and UDTs that are defined within an assembly as SQL Server objects. Permissions to the specificm object to be accessed must also be granted.
Shared Assemblies and Security
As we discussed in Chapter 2, when you execute the CREATE ASSEMBLY DDL, SQL Server uses .NET reflection to determine which other assemblies your assembly depends on. It catalogs all of these as well. This means that there are two types of user assemblies that SQL Server 2005 will load: "visible" and "invisible." By visible, we mean those assemblies that have a SQL Server object name associated with them.
Users can only obtain permissions on visible assemblies, because the GRANT DDL statement requires a name. This makes invisible assemblies private to the assembly that references them. To share assemblies, make them visible and grant REFERENCES permission to others. This is shown in Listing 6-2.
Click for the next excerpt in this series: Permissions, visibility, UDTs and user-defined aggregates
Click for the book excerpt series or visit here to obtain the complete book.