Pages

Thursday, January 28, 2010

SQL SERVER – Differences in Vulnerability between Oracle and SQL Server

In the IT world, but not among experienced DBAs, there has been a long-standing myth that the Oracle database platform is more stable and more secure than SQL Server from Microsoft. This is due to a variety of reasons; but in my opinion, the main ones are listed below:

A. Microsoft development platforms are generally more error-prone and full of bugs.

This (unfairly) projects the weaknesses of earlier versions of Windows onto its other products such as SQL Server, which is a very stable and secure platform in its own right.

B. Oracle has been around for longer than SQL Server and must therefore be more stable and secure.

Well, this does not count for anything. Being around longer does not mean that you are necessarily wiser. Need more proof? – look at General Motors.

Let us look at the comparisons between Oracle’s DB platform and SQL Server:

Number of reported vulnerabilities for per product

In my opinion, this is the most basic test for stability and security – the number of errors and bugs reported for a product is roughly proportional to its security and stability. Note that this number is usually compiled by independent information-security companies; so, there is no question of “hiding the numbers.”

In this regard, Oracle fares poorly as compared with SQL Server. Oracle Corporation releases an amazingly large number of patches and Critical Patch Updates (CPUs) for its DB platform. To be fair, following are some of the arguments that support Oracle DB (together with answers for those same arguments):

Oracle runs on several platforms, while SQL Server only runs on Windows

Answer: No, the patches and bugs reported are almost all cross-platforms, which implies that they are OS-independent.

Oracle DB also includes several other components, so we are not comparing like with like

Answer: Here, I considered only the database server components. This implies that any problem arising from components such as the Intelligent Agent or the Oracle Application Server has not been included.

Let us compare the Nov 2009 vulnerability reports of the both Oracle11g [1] and SQL Server 2008 [2].

Product Advisories Vulnerabilities
SQL Server 2008 0 0
Oracle11g 7 239

This is not only for the latest DB platforms: Oracle 11g and SQL Server 2008. No, if we take a historical perspective, Microsoft patched 59 vulnerabilities in its SQL Server 7 – 2000 and 2005 databases in the past 6 years, while for the same period Oracle issued 233 patches for software flaws in its Oracle 8, 9 and 10g databases. Moreover, in 2006, Microsoft SQL Server 2000 with Service Pack 4 was ranked as the most secure database in the market together with the PostgreSQL open source project. Oracle10g was placed at the very bottom of the same list.

DBAs are wary and tired of patching the Oracle DB

A survey conducted in January 2008 [3] showed that two-thirds of Oracle DBA’s do not apply security patches. The underlying cause of this is that Oracle Corporation releases a huge number of patches and fixes for various bugs, which itself leads to this secondary problem. There is a lot of fatigue and effort involved in tracking, testing and installing several patch releases every year. In 2009 alone, Oracle released 33 patches for its DB.

However, I am not at all suggesting that Oracle DBAs are lazy or do not take database security seriously. The main reason why many DBAs are very wary of patching Oracle databases is the complexity involved. First, note that patch testing, and also CPU testing is a long and intensive process. Because of the large numbers of bug fixes and CPUs released by Oracle, many application vendors whose products run on an Oracle DB simply do not have the time to test a patch, or as soon as they do so, another one is released. This, in turn, implies that if their clients risk installing unapproved patches, then the vendor can rightfully refuse to support them in case that patch then causes an error in the application.

Slavik Markovich, the Chief Technology Officer of database vendor Sentrigo Inc, said at a conference: “To apply the CPU, you need to change the binaries of the database. You change the database behavior in some ways that may affect application performance. So applying security patches to a database typically involves testing them against the applications that feed off the database. This is a very long and very hard process to do, especially if you are in enterprises with a large number of databases and applications. Applying these patches means months of labor and sometimes significant downtime, both of which most companies can’t afford.”

Microsoft has a working system of patch testing and rollout, whereas Oracle does not have such a system

Trustworthy Computing is a Microsoft tool that proactively identifies and allows you to install missing patches. When Microsoft launched this initiative, many people did not take it seriously. But now it has proven to be a lifesaver for many busy DBAs and system administrators who simply do not have the time to worry about installing patches. Oracle does NOT have an equivalent tool.

Also, Oracle also does not make life easier for companies who want to keep their databases secure, making it complex to download and install patches. With SQL Server, you can schedule automatic installation of updates and patches. Moreover, if it causes an undesired effect on your application, you can simply uninstall it, leaving the database at it was prior to the update. This is somewhat similar to the System Restore feature of Windows. With Oracle DB, both the installation and removal of patches are complex events that are not easy to do and undo, except for a seasoned DBA.

However, the single most crucial factor in Microsoft’s DB-security-management success is its Security Development Lifecycle (SDL). The use of SDL [4] implies that knowledge obtained after resolving the problems is never lost; instead it is ploughed back into the cycle. Therefore, instead of repeating the same mistakes every time, you can at least ensure that the new code is more secure than the old code, even though it is not completely secure. For instance, the mistakes that were committed and resolved while developing SQL Server 2005 were not repeated during the development of SQL Server 2008. However, there is one issue that bothers developers and DBAs who use Oracle DB: they come across the same mistakes in every version used by them. Eventually, when one problem is resolved, many a time the results are not problem-free and in turn, a new error or problem is created – overall, there is no consistent and reliable problem-solving technique for correcting bugs and fixes. In fact, database consultant Karel Miko estimates that Oracle Corp. is about 5 years behind Microsoft in patch management.

Summary

I hope this article helps to debunk the myth that SQL Server is a less stable and less reliable platform than Oracle DB. As many researchers and security consultancy firms worldwide have pointed out, SQL Server is consistently more secure and much less prone to errors and bugs than Oracle DB.

Thursday, January 21, 2010

SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function

Different ways to insert the values from a stored procedure into a table. Let us quickly look at the conventional way of doing the same.

Please note that this only works with the stored procedure with only one resultset. Let us create a stored procedure that returns one resultset.

/* Create Stored Procedure */
CREATE PROCEDURE TestSP
AS
SELECT
GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO

Traditional Method:

/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSP
GO
/* SELECT from TempTable */
SELECT *
FROM #tempTable
GO
/* Clean up */
DROP TABLE #tempTable
GO

Alternate Method: Table Valued Function

/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(
MyDate SMALLDATETIME,
IntValue INT
)
AS
BEGIN
DECLARE
@MyDate SMALLDATETIME
DECLARE @IntValue INT
INSERT INTO
@retTestFn
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *
FROM dbo.TestFn()
GO

It is clear from the resultset that option 2, where I have converted stored procedures logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. Performance of the stored procedure is “usually” better than that of functions.

Monday, January 11, 2010

SQL Challenge

Here is a challenge that takes you away from those repetitive boring type of queries that you write over and over again, several times a day. All of us, the database people, are familiar with thinking in set based manner as well as row by row style. Here is something that is very interesting where you might need to process records in a 'three-line-at-a-time' fashion.

For the purpose of this challenge, imagine that you are working for a bank which just decided to scan all the banking documents. Assume that they have an old fashioned scanner which scans the documents and produces a text file with the customer number. So far so good. Well, not really! Unfortunately the scanner produces a graphical representation of the customer number using three lines of symbols: space, unerscores and pipe characters.

Here is an example of the output produced by the scanner.

  





Here are the rules to keep in mind while reading and recognizing the output generated by the scanner.

  • Each digit is represented using 9 cells (3x3)
  • Only spaces, underscores and pipe characters are used
  • The number of digits in each account number may vary.
  • The Scanner is not 100% reliable and it might produce some digits that are invalid

The Challenge

Your job is to read the output produced by the scanner and identify the the customer number represented by each image. Remember that the scanner is not very reliable and it might produce invalid digit representations. For each digit that is not valid, set the value to 'X'

Sample Data

Here is the sample data for this challenge. Please take care with spaces, tabs and carriage returns as each digit is represented by three lines of text and if a space, tab or carriage return is misplaced, the whole image will be distorted.

Id          ScanNumber
----------- ---------------------------
1            _  _  _  _  _  _  _  _  _  
            | || || || || |  || ||_ |_|  
            |_||_||_||_||_|  ||_| _| _| 
                           
2               _  _  _  _  _  _     _ 
            |_||_|| || ||_   |  |  ||_ 
              | _||_||_||_|  |  |  | _|
                           
3            _  _  _     _  _  _  _  _  
            |_ |_|| || ||_ |_| _|  ||_| 
            |_||_||_||_||_||_||_   | _| 
                           
4               _  _  _  _  _  _     _ 
            |_||_|| ||_||_   |  |  ||_ 
              | _||_||_||_|  |  |  ||_|
                           
5               _  _  _  _  _  _     _ 
            | ||_|| ||_||_   |  |  ||_ 
              | _||_||_||_|  |  |  ||_|
                           
6            _     _  _     _  _  _  _ 
            | |  | _| _||_||_ |_   ||_|
            |_|  ||_  _|  | _||_|  ||_|


Expected Results

Based on the sample input and the rules discussed earlier, here is the expected output.

Id          Value
----------- ---------
1           000007059
2           490067715
3           680X68279
4           490867716
5           X90867716
6           012345678


Sample Scripts

Use the following script to generate the sample data for this challenge.

DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(116))
 
INSERT INTO @t
SELECT  1,--> 000 007 059
'_  _  _  _  _  _  _  _  _ 
| || || || || |  || ||_ |_|
|_||_||_||_||_|  ||_| _| _|
                           
' UNION 
SELECT 2,-->  490 067 715
'   _  _  _  _  _  _     _ 
|_||_|| || ||_   |  |  ||_ 
  | _||_||_||_|  |  |  | _|
                           
' UNION
SELECT  3, --> 680 X68 279
'_  _  _     _  _  _  _  _ 
|_ |_|| || ||_ |_| _|  ||_|
|_||_||_||_||_||_||_   | _|
                           
' UNION
SELECT  4,--> 490 867 716
'   _  _  _  _  _  _     _ 
|_||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
'  UNION
SELECT  5,--> X90 867 716
'   _  _  _  _  _  _     _ 
| ||_|| ||_||_   |  |  ||_ 
  | _||_||_||_|  |  |  ||_|
                           
' 
UNION 
SELECT 6,--> 012 345 678
'_     _  _     _  _  _  _ 
| |  | _| _||_||_ |_   ||_|
|_|  ||_  _|  | _||_|  ||_|
                         
Notes
  1. Each record may have more than three lines of data (each line is separated by a CR and LF). Your code should consider only the first three lines.
  2. The length of the first three lines of each recrd will always be the same and will be divisible by three.
  3. There may be 3x3 blocks of spaces in the string. In such a case, you should generate an empty space in the output. If a 3x3 block does not create a valid digit (except for the case of a 3x3 block of spaces), you should generate an "X".
  4. The number of 3x3 blocks in each record may vary

Sunday, January 10, 2010

Practical Exam Program


M.Sc (IT & CA)

Practical Exam Program

for

Shri. M. N. Virani Science College, Rajkot

January - 2010


Date

Time

Subject

15-1-2010

Friday

9:00 A.M. to 10:30 A.M.

P101 : OOP using VC++

2:00 P.M. to 3:30 P.M.

P102 : Web Technology Concepts

16-1-2010

Saturday

9:00 A.M. to 10:30 A.M.

P103 : DBMS & Advanced Database Administration

2:00 P.M. to 3:30 P.M.

P104 : O.S & Network Management

21-1-2010

Thursday

9:00 A.M. Onwards

P105 : Project - I














* For more information Please refer College Notice board.


Thursday, January 7, 2010

Logical Query Processing