WHAT CAN .NET CODE DO FROM WITHIN SQL

WHAT CAN .NET CODE DO FROM WITHIN SQL SERVER SQL Server. Code location evidence means very little for SQL Server assemblies, because .NET code is never loaded from the Internet or the local file system. SQL Server enforces a stronger security policy, using HPAs as well as three levels of security that are declared when the assembly is cataloged. If SQL Server determines that the assembly contains code it shouldn t be allowed to execute, CREATE ASSEMBLY simply fails. The .NET Framework class libraries are the only code loaded from the global assembly cache, and they are subject to strong constraints, which we will discuss shortly. Code access security enforces permission-based security through HPAs at execution time as well. With each access to any resource that requires a permission (such as a file or DNS resolver), the CAS access security inspects the call stack to ensure that every piece of code, up to the original caller, has the appropriate permission. This is known as the stack walk. Between code analysis at create assembly time and the execution-time stack walk, the .NET code access security system and SQL Server s extensions to strengthen it ensure that no code is called that could compromise the stability and security of the system in unforeseen ways. This is a big improvement over pre SQL Server 2005 compiled code, which consisted of extended stored procedures and COM-based components. Code Access Security and .NET Assemblies Because SQL Server controls assembly loading, as well as facets of .NET code execution, it can also assign a custom safety level to an assembly. Safety levels determine what non SQL Server resources .NET assemblies can access. There are three safety levels: SAFE, EXTERNAL_ACCESS, and UNSAFE. These are specified on CREATE ASSEMBLY and changed by using ALTER ASSEMBLY under the control of the database administrator. The different safety levels approximately correspond to the following. SAFE Can access computational .NET classes. Safety is equivalent to a T-SQL procedure. EXTERNAL_ACCESS Can access all code that SAFEmode can and, in addition, items like the file system and other databases through ADO.NET. Approximately equivalent to a T-SQL procedure that can access some of the system extended stored procedures. UNSAFE Can access most (but not all) code in a subset of the FX assemblies. Approximately equivalent to a user-written extended stored procedure without the bad pointer and memory buffer overflow problems.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

SECURITY What Can .NET Code Do from within

SECURITY What Can .NET Code Do from within SQL Server: Safety Levels SQL Server permissions take care of dealing with security from a SQL Server centric point of view. But if a .NET stored procedure can load arbitrary assemblies from the file system or the Internet, the security of the SQL Server process could be compromised. The first concern is taken care of by the new .NET hosting APIs. Aside from a specific subset of the .NET base class libraries, SQL Server handles all assembly loading requests. You cannot instruct SQL Server to load arbitrary assemblies from the local file system or the Internet. In addition, the IL code in each .NET assembly is checked for validity when CREATE ASSEMBLY is run. On a more granular level, .NET not only uses SQL Server user-based permissions, but also .NET code access security. Introduction to Code Access Security .NET code access security is meant to check the permissions of code before executing it, rather than checking the permissions of the user principal that executes the code. Code access security determines how trustworthy code is by mapping pieces of evidence such as where the code was loaded from, whether the code was signed with a digital signature, and even which company wrote the code to permissions. This evidence is collected and inspected when the code is loaded. Code access security matches evidence against the security policy, to produce a set of permissions. Security policy is a combination of enterprise security policy, machine policy, user- specific policy, and AppDomain security policy. The general concept of determining permissions from evidence and security policy is discussed in the .NET documentation. In most ordinary .NET programs, code access security is used when code is loaded, to determine the location (most likely, the file system or network) of the code. .NET assemblies loaded from SQL Server, however, can only be loaded from two places: The SQL Server database itself (user code must be cataloged and stored in the database) The global assembly cache (Framework class libraries, FX, only) When CREATE ASSEMBLY is run, the code is analyzed and any outside code that it calls (dependent assemblies) is also cataloged and stored inside

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

PERMISSIONS, VISIBILITY, UDTS Creating a table with

PERMISSIONS, VISIBILITY, UDTS Creating a table with the user-defined aggregates used in a constraint Defining a stored procedure, UDF, or trigger that uses the user- defined aggregate Defining a view using the WITH SCHEMABINDINGoption 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 bobattempts to execute CREATEASSEMBLYfor bobsprocs. The bobsprocsassembly has a method that references another assembly, timsprocs, that is already cataloged in the database. Bob needs to have REFERENCESpermission to the timsprocs assembly, because a schema-bound link will be set up between the two assemblies. If user bobcreates a procedure, bobproc1, that is based on a method in the bobsprocsassembly, no permissions are checked. However, if user fredcreates the bobproc1procedure, this will set up a schema-bound link. User fredneeds to have REFERENCES permission to the bobsprocsassembly. The procedure bobproc1in bobsprocsis specified as execution_context=caller. When user aliceattempts to execute bobproc1, she must have EXECUTEpermissions on the procedure, but the code runs as bob. (We ll discuss execution context shortly.) User alicethen defines a table, atable, using the UDT bobtype, which is part of the assembly bobsprocs. To do this, she needs REFERENCESpermission on the bobsprocsassembly and on the bobtypeUDT. User joeattempts to execute a SELECTstatement that contains the UDT bobtypein the table atable. To do this, he needs SELECT permission on atableand EXECUTEpermission on bobtype.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

SECURITY this gives FRED access GRANT EXECUTE

SECURITY this gives FRED access GRANT EXECUTE ON SomeCommonTypes TO FRED GO 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 EXECUTErights 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 SCHEMABINDINGoption 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 SqlServerdata provider. User-defined aggregates follow the same rules. A schema-bound link to a user-defined aggregate would consist of:

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

ASSEMBLY PERMISSIONS EXECUTEpermission on an assembly allows a

ASSEMBLY PERMISSIONS EXECUTEpermission 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 specific 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. Listing 6-2: Visible and Invisible Assemblies if assembly SomeTypes.dll uses assembly SomeCommonTypes.dll this will catalog SomeCommonTypes as well CREATE ASSEMBLY SomeTypes FROM \mysvrtypesSomeTypes.dll GO Let Fred access SomeTypes GRANT EXECUTE ON SomeTypes To FRED GO Fred will need direct access to SomeCommonTypes error: SomeCommonTypes does not exist in the SQL Server catalog GRANT EXECUTE ON SomeCommonTypes TO FRED GO this makes it visible ALTER ASSEMBLY SomeCommonTypes FROM \mysvrtypesSomeCommonTypes.dll SET VISIBILITY = ON

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

SECURITY EXTERNAL_ACCESS permission set is specified, should also

SECURITY EXTERNAL_ACCESS permission set is specified, should also have the EXTERNAL_ACCESS permission. The user should be a member of the sys adminrole if the UNSAFE permission set is specified. In addition, if you use the UNSAFEpermission 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 EXECUTEpermissions. 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. REFERENCESpermission 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, user- defined 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.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

ASSEMBLY PERMISSIONS create an assembly owned by

ASSEMBLY PERMISSIONS 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 CREATEASSEMBLYand 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 CREATEand 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

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

SECURITY than extended stored procedures and as safe

SECURITY 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. 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 AUTHORIZATIONparameter of CREATEASSEMBLYor ALTERASSEMBLY, 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

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

SQL SERVER PERMISSIONS AND THE NEW OBJECTS

SQL SERVER PERMISSIONS AND THE NEW OBJECTS this does two ownership checks even if FRED is DBO select_and_count GO By default, procedural code that uses a nondefault execution context can only access resources in the current database that is, you may not use three-part names at all. This is to guard against a user with DBO privilege in one database gaining access to data in another database. If you need to access resources in another database or system-level resources, you must grant appropriate permissions to the executor of the code. Another option is to sign the code with a certificate, map the certificate to a login, and grant permissions to the certificate. This option is in development at the time of this writing. SQL Server Permissions and the 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

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

This probably wasn t the desired result. Although EXECUTE

This probably wasn t the desired result. Although EXECUTE AS SELF looks interesting, it should be used with care because it can make ownership chains more complex. When the stored procedure count_rows_as_me accesses any table that the current owner does not own, an ownership chain will be broken and permissions on the underlying object will be checked. In addition, when a different stored procedure uses this stored procedure, it is possible that ownership chains could be broken at two levels, as shown in the script that follows. table FOO_TABLE is owned by DBO. using the count_rows_as_me procedure from the previous example SETUSER JAY GO this checks permissions if JAY is not DBO count_rows_as_me foo_table SETUSER FRED GO CREATE PROCEDURE select_and_count AS SELECT * FROM customers count_rows_as_me foo_table GO 196 SECURITY Authors Table Owner: Fred Bob Stored Procedure Owner: Fred SELECT*FROM Authors EXECUTE ( SELECT*FROM Authors ) No permission check Fred owns both Permission for Bob checked, dynamic SQL Authors Table Owner: Fred Bob Stored Procedure Execute as Self Owner: Fred SELECT*FROM Authors EXECUTE ( SELECT*FROM Authors ) No permission check Fred owns both Permission for Fred checked, dynamic SQL Figure 6-3: Using EXECUTE AS SELF with Dynamic SQL

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services