6 Security CHANGES IN SQL SERVER 2005 help

6 Security CHANGES IN SQL SERVER 2005 help make SQL Server more secure and security more approachable for the developer and the administrator. An entire new set of security requirements when hosting .NET code inside SQL Server are addressed by using traditional role-based security combined with .NET hosting API and attribute-based security. Classic SQL security is improved by separating users from schemas and integrating password management with Windows 2003 Server. And the security of SQL Server in general is enhanced by having options turned off by default. New Security Features in SQL Server 2005 SQL Server 2005 adds new security features, not only to make SQL Server more secure, but to make security more understandable and easier to administer. Some of these features will permit programmers to develop database applications while running with the exact privileges that they need. This is known as the principle of least privilege. No longer does every programmer need to run as database administrator or sa. The major new features include the following. Security for .NET executable code Administration and execution of .NET code is managed through a combination of SQL Server permissions, Windows permissions, and .NET code security. What

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

USER-DEFINED TYPES AND AGGREGATES very low level and

USER-DEFINED TYPES AND AGGREGATES very low level and will often require extra code beyond the function they are implementing, to ensure that the data is not corrupted. We ve covered most of the new features of SQL Server 2005 that directly relate to .NET, finishing up with user-defined types and user- defined aggregates. Chapter 2 pointed out that no matter how flexible or powerful a database is, a database without security is less than useful. The next chapter talks about how security has been considered at every level when the new functionality was designed. In addition, we ll see how permissions work with the .NET features.

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

WHERE ARE WE? LDim, that just make a

WHERE ARE WE? LDim, that just make a running total or count, this property should be set to true. If the return value of the user-defined aggregate should be null if no rows are processed for the aggregate, the IsNullIfEmptyattribute should be set to true. If this attribute is set to true, the query optimizer has the option of not even creating an aggregate object if there are no rows to be processed. However, even if this attribute is set to true, SQL Server may still create an instance of the aggregate object, even if there are no rows to process. The default value for this attribute is false, so it may be left out if this is not the case. Where Are We? User-defined types are extensions to the SQL Server built-in scalar types. They are used in the same way and for the same purpose. They allow us to use an application-specific format for the string representation of a value for example 1 ft in much the same way we use a string representation of a date such as 12/1/1998 for a DATETIMEbuilt-in data type. User-defined types implement a number of well-known methods and use the SqlUserDefinedTypeAttribute attributes. These can be accessed from within SQL Server once their assembly has been added to a database and CREATE TYPEhas been used to add them. The user-defined type can be used in the definition of a column type for a table, a variable type, or a parameter type in a stored procedure or function. It is often useful to add user-defined-type-specific methods that can manipulate or extract information from an instance of that type. It is also often useful to add utility functions that can create initialized instances of a user-defined type. User-defined types can also expose their properties and fields. This is useful when one of the fields of a user-defined type must be accessed or manipulated. User-defined aggregates allow an aggregate function to be created using a CLR language. There is no way to create an aggregate function using T-SQL. Aggregate functions are often created for use with user- defined types. They may also be created for built-in types, and order variant aggregates may be created to calculate aggregate values that depend on the processing of rows in a particular order. Both user-defined types and user-defined aggregate implementation must always be aware that they are manipulating data in SQL Server at a

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

USER-DEFINED TYPES AND AGGREGATES The class that implements

USER-DEFINED TYPES AND AGGREGATES The class that implements the user-defined aggregate must be marked as [Serializable] and, if Format.Native is used, must also be marked as [StructLayout(LayoutKind.Sequential)]. Listing 5-33 shows the usage of the SqlUserDefinedAggregateAttributeby LDimSum. Listing 5-33: SqlUserDefinedAggregateAttribute for LDimSum [Serializable] [StructLayout(LayoutKind.Sequential, InvariantToOrder=true)] [SqlUserDefinedAggregate(Format.Native)] public class LDimSum { … } There are other properties of SqlUserDefinedAggregateAttribute that you may add to further define the behavior of a user-defined aggregate. This attribute do not affect the functional operation of the aggregate but can provide hints to the optimizer, which can improve the performance of the aggregate. For example, an aggregate is invariant to null that is, its final value is not dependent on nullvalues and the optimizer knows that it has the option of not passing nulls to the aggregate. If the value of a user-defined aggregate will be the same, whether or not some of the values it aggregates are null, it should set the IsInvariant- ToNulls property of the SqlUserDefinedAggregateAttribute to true. Its default value is false, so it can be left out if in fact the aggregate value will change when nulls are aggregated. If this property is set to true, the query optimizer has the option of not calling the Accumulate method for null values. However, you cannot assume that it will not call the Accumulatemethod in this case. If the value of a user-defined aggregate will be the same if duplicate values are passed into it, it should set the IsInVariantToDuplicates property of the SqlUserDefinedAggregateAttribute to true. The default value of the property is false, so it can be left out if this is not the case. If this property is set to true, the query optimizer has the option of not calling the Accumulate method for duplicate values. However, you cannot assume it will not call the Accumulatemethod in this case. If the value of a user-defined aggregate will be the same no matter in what order it processes rows, the IsInvariantToOrder property should be set to true. The default value for this property is false, so it can be left out if this is not the case. Note that for most simple aggregates, like

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

USER-DEFINED AGGREGATES copies the data it needs from

USER-DEFINED AGGREGATES copies the data it needs from Price Aggregator 1 so that it can continue with the aggregate calculations. Steps 7 and 8 call the Accumulate method on Price Aggregator 2 in order to process the last two rows needing processing in the Invoice table. Step 9 calls the Terminate method on Price Aggregator 2 to complete the calculation of the aggregate. Note that neither Terminate nor Merge is called on Price Aggregator 1. This example is just one possible way in which SQL Server might use multiple aggregator objects to calculate an aggregate. Listing 5-32 shows the implementation of Mergefor LDim. Listing 5-32: Implementation of the LDim Merge Method public class LDimSum { // running total of aggregate in inches double length = 0.0; public void Merge (LDimSum Group) { // add the running total of Group // to current running total of // this aggregator object length += Group.length; } … } The Merge implementation for LDimSum just adds the running length total for the aggregator that is passed in to its own. Note that even though the example in Figure 5-3 showed Merge being called immediately after Init, this may not always be the case. This is why the Merge method for LDimSum adds the length to its current running total rather than replacing it. There are a few other things SQL Server needs to know about a user- defined aggregate. A SqlUserDefinedAggregateAttribute is added to the class that implements the user-defined aggregate to provide this information, much as the SqlUserDefinedTypeAttributeis used for a similar purpose on user-defined types. SQL Server may have to serialize an instance of a user-defined aggregate. The Format property of SqlUserDefinedAggregateAttribute is required and serves the same purpose the Format property of the SqlUserDefinedTypeAttribute does. Its value may be Format.Native or Format.UserDefined.

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

USER-DEFINED TYPES AND AGGREGATES { LDim d =

USER-DEFINED TYPES AND AGGREGATES { LDim d = new LDim; d.Value = length; d.Units = in ; return d; } … } The Terminatemethod creates a new LDimobject and sets its value and units. Note that this implementation of Terminaterequires that the value and units fields of LDim have internal scope and that LDim and LDimSum are part of the same assembly. The Merge method is used to combine an aggregate object with the aggregate object on which Merge is called. To understand what Merge must do and why it exists, we need to understand how SQL Server calls the methods in aggregator objects. In the beginning, we said that SQL Server may, in some cases, use more than one aggregate object to calculate an aggregate. Figure 5-3 shows two aggregator objects being used to calculate an aggregate function of the Price column in a table named Invoice. Note that there are two aggregator objects: Price Aggregator 1 and Price Aggregator 2. The numbers in circles indicate the steps in which the aggregate is calculated. In step 1 Init is called on Price Aggregator 1. Steps 2, 3, and 4 call the Accumulate method on Price Aggregator 1 to process three rows from the Invoice table. Note that the rows are not processed in the order in which they exist in the table. Step 5 calls the Init method on Price Aggregator 2, followed by step 6, which calls Merge. When Mergeis called on Price Aggregator 2, Price Aggregator 1 is passed in as a parameter. The Merge function implementation Price Price Aggregator 1 Aggregator 2 IdLine NumberPrice Invoice Table Init Merge Terminate 1 3 2 4 5 7 6 8 9 Init AccumulateAccumulate Merge Terminate Figure 5-3: Method Order Using Two Aggregates

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

USER-DEFINED AGGREGATES count that will be used later

USER-DEFINED AGGREGATES count that will be used later to calculate an average. Listing 5-30 shows the implementation of Accumulatefor LDimSum. The Terminate function returns the aggregate value. The return type can be any scalar; it need not be the type being aggregated. In this case, because it is part of an aggregate that sums LDims, it does return an LDim. Listing 5-30: Implementation of the LDimSum Accumulate Method public class LDimSum { // running total of aggregate in inches double length = 0.0; public void Accumulate(LDim dim) { // normalize value to inches if (dim.units == ft ) { dim.value *= 12; } if (dim.units == yd ) { dim.value *= 36; } // added current value to running total length += dim.value; } … } The Accumulate method for LDim first normalizes the value of the dimension to inches, and then adds it to the running total that it keeps in the lengthfield. The Terminatemethod, if it is called at all, is the last method called on an aggregator object. Its purpose is to return the scalar that is the result of the aggregation. In the case of LDimSum, the result of the aggregation is just the current running total in the lengthfield. Listing 5-31 shows the implementation of Terminatefor LDimSum. Listing 5-31: Implementation of the LDimSum Terminate Method public class LDimSum { // running total of aggregate in inches double length = 0.0; public LDim Terminate()

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

USER-DEFINED TYPES AND AGGREGATES the aggregator in much

USER-DEFINED TYPES AND AGGREGATES the aggregator in much the same way as a constructor is. However, it cannot depend on the fields of aggregator being set by an initializer as a conventional constructor can. Note that Init may be called more than once, but your implementation should always assume that each time it is called, it is for a new aggregate calculation. Listing 5-29 shows how the Init method must be used. Listing 5-29: Implementation of the LDim Init Method public class LDimSum { double length = 0.0; public void Init() { // Init does not depend on initializer // in class definition length = 0.0; } … } The Terminatemethod is the last method called on an aggregate object during an aggregate calculation; however, in some cases it may not be called at all. The Terminate method returns an instance of a scalar that is the result of the aggregate calculation. In the case of LDimSum, it returns an LDim. For a given instance of an aggregator for example, an LDim object there is an order in which the methods in its implementation will be called. Note that in some cases SQL Server will use multiple instances of an aggregator in the process of calculating the aggregate. Also note that SQL Server may not always create a new instance of an aggregator class to do an aggregate calculation, but may reuse an instance of an aggregator from a previous calculation. The Accumulate method is called once for each row being aggregated. The input parameter type determines the type of the aggregate function. In the case of LDimSum, the input parameter is of type LDim, meaning that this is an LDim aggregator. Note that although Accumulate is called once for each row being aggregated, these calls may be spread over multiple aggregator objects. The purpose of the Accumulatemethod is to collect, one row at a time, the information that will be required to calculate the result of the aggregation. For LDimSum this means maintaining a running sum. For other aggregates other information may be collected for example, a

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

USER-DEFINED AGGREGATES Listing 5-27: Using the LDimSum Aggregate

USER-DEFINED AGGREGATES Listing 5-27: Using the LDimSum Aggregate CREATE TABLE Boards ( weight INT, length LDim ) GO INSERT INTO Boards VALUES (3, N 1 in ) INSERT INTO Boards VALUES (2, N 1 in ) INSERT INTO Boards VALUES (3, N 1 ft ) INSERT INTO Boards VALUES (3, N 2 in )) GO SELECT CONVERT(CHAR, LDimSum(length)) FROM Boards WHERE weight = 3 go 14 in A user-defined aggregate is a public class that implements the four functions shown in the skeleton implementation of LDimSumin Listing 5-28. Listing 5-28: Skeleton of an LDimSum User-Defined Aggregate public class LDimSum { public void Accumulate(LDim dim) { } public LDim Terminate() { } public void Init() { } public void Merge(LDimSum) { } } SQL Server will use one or more instances of a user-defined aggregate class to calculate an aggregate. In some cases, it may create new instances when calculating an aggregate, in others it may reuse instances from a previous calculation, and in some cases a combination of both. Your implementation of the aggregate may not depend on SQL Server using any particular one of these behaviors. The first method SQL Server will call on a user-defined aggregate is Init, when it uses it for an aggregate calculation. Initis used to initialize

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

USER-DEFINED TYPES AND AGGREGATES You can create your

USER-DEFINED TYPES AND AGGREGATES You can create your own user-defined aggregates with SQL Server 2005. One reason you might want to do this is that you have created your own user-defined type and need to be able to aggregate it. None of the built-in aggregates in SQL Server will work with a user-defined type, so you will have to create your own in this case. A second reason is performance. You do not need the SUM aggregate to calculate the sum of a column. Listing 5-26 shows a SQL batch that calculates the sum of prices that the example in Listing 5-25 did, but it does not use the SUMaggregate. Listing 5-26: Calculating a Sum without an Aggregate DECLARE sumCursor CURSOR FOR SELECT price FROM ITEMS WHERE size = 3 OPEN sumCursor DECLARE @sum float SET @sum = 0 DECLARE @price float FETCH NEXT FROM sumCursor INTO @price WHILE @@FETCH_STATUS = 0 BEGIN SET @sum = @sum + @price FETCH NEXT FROM sumCursor INTO @price END CLOSE sumCursor DEALLOCATE sumCursor PRINT @sum The sum technique shown in Listing 5-26 uses a CURSOR to iterate through the results of a query and add up the prices. It is at least an order of magnitude slower than using the built-in SUM aggregate and uses a lot more resources on the server because of the CURSOR. Prior to SQL Server 2005, if you needed an aggregate other than one of the built-in ones provided by SQL Server, you would have used this technique to create your own aggregation. In SQL Server 2005, you can write your own aggregate, and its performance will be on the order of the built-in aggregates. In this section, we will look at creating a user-defined aggregate that is the equivalent of SUM for the LDim user-defined type. We will call this aggregate LDimSum, and it will produce an LDim that represents the arithmetic sum of the LDims that it processes. Listing 5-27 shows a SQL batch that makes use of the LDimSumaggregate function.

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