Permissions, visibility, UDTs and user-defined aggregates |
 |
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.
Permissions, visibility, UDTs and user-defined aggregates
A user-defined type must be defined in the SQL Server catalog to be visible
to SQL Server stored procedures and other T-SQL procedural code, just as an assembly is. Once a UDT is defined in the SQL Server catalog, users need the appropriate permission to invoke it, just as they do for any other database object.
Classes in an assembly are not directly accessible to T-SQL but may be used by other assemblies if they are public. For example, a CLR-based user-defined function may want to make use of a class from an assembly other than the one in which it is defined. This will only be allowed if the identity used to access the user-defined function, or other CLR-based procedural code, has EXECUTE rights to that assembly.
A UDT that is cataloged to SQL Server with CREATE TYPE is secured through permissions like any other SQL Server object. As with assemblies, you can grant REFERENCES and EXECUTE permissions on a UDT; with a UDT, however, the meaning is slightly different. Schema-bound links, in the context of a UDT, consist of:
- Creating a table with the UDT as a column
- Defining a stored procedure, UDF, or trigger on the static method of a UDT
- Defining a view using the WITH SCHEMABINDING option that references the UDT
EXECUTE permission on a UDT is defined at the class level, not at the method level. Granting EXECUTE permission on a UDT does not automatically grant permission on every stored procedure or user-defined function in the UDT. This must be granted by granting permission to the stored procedure or UDF SQL Server object directly. EXECUTE permission is also required to fetch a UDT or execute its methods from code inside the SqlServer data provider.
User-defined aggregates follow the same rules. A schema-bound link to a user-defined aggregate would consist of:
- Creating a table with the user-defined aggregates used in a constraint
- Defining a stored procedure, UDF, or trigger that uses the userdefined
aggregate
- Defining a view using the WITH SCHEMABINDING option that uses
the user-defined aggregate
REFERENCES permission would be required to create any of the database objects listed earlier.
Ownership chains apply when using user permissions with SQL Server objects, just as they do when using other SQL objects, like tables and views. Here are a few examples that will illustrate the concepts.
- User bob attempts to execute CREATE ASSEMBLY for bobsprocs. The bobsprocs assembly has a method that references another assembly, timsprocs, that is already cataloged in the database. Bob needs to have REFERENCES permission to the timsprocs assembly, because a schema-bound link will be set up between the two assemblies.
- If user bob creates a procedure, bobproc1, that is based on a method in the bobsprocs assembly, no permissions are checked. However, if user fred creates the bobproc1 procedure, this will set up a schema-bound link. User fred needs to have REFERENCES permission to the bobsprocs assembly.
- The procedure bobproc1 in bobsprocs is specified as execution_context = caller. When user alice attempts to execute bobproc1, she must have EXECUTE permissions on the procedure, but the code runs as bob. (We'll discuss execution context shortly.)
- User alice then defines a table, atable, using the UDT bobtype, which is part of the assembly bobsprocs. To do this, she needs REFERENCES permission on the bobsprocs assembly and on the bobtype UDT.
- User joe attempts to execute a SELECT statement that contains the UDT bobtype in the table atable. To do this, he needs SELECT permission on atable and EXECUTE permission on bobtype.
Click for the next excerpt in this series: What can .NET code do from within SQL Server: Safety levels
Click for the book excerpt series or visit here to obtain the complete book.
|
|
|
|
