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.

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.

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

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.

Logical Query Processing

Msc IT Last Year Paper Fully Solved

M. Sc. (Sem. I) (IT) Examination

January/February 2009

P-I03 DBMS & Database Administration

TIME: 3 Hours MAX. MARKS: 70

Q.1	Fill in the blanks		5
	(1)	Privileges are removed from a user with REVOKE command.
	(2)	ALERT.LOG file will gives the oracle instance status information.
	(3)	Control File and Redo Log file can be mirrored by oracle.
	(4)	SYS DBA role gives complete authority in oracle database.
	(5)	ROWID is an internal physical address for every row in every nonclustered table in the database.
Q.2	Answer briefly :		15
	(1)	Name three types of files that make up the oracle database.
	Ans	Control File, Redo Log File and Database File
	(2).	What is auditing used for ?
	Ans	Auditing is monitoring of selected user database actions. It is used to Investigate suspicious database activity. Gather Information about specific database activity.
	(3).	What is oracle SID?
	Ans	The Oracle System Identifier (SID) identifies the Oracle instance on the machine. It is usually set up as an ORACLE_SID symbol is used to name of the Oracle background processes and to identify the SGA area in memory.
	(4).	Name four types of segments.
	Ans	Table (Data Segment), Index, Cluster, Rollback Segment, Temporary Segment, Index Organized Table, Table Partition, Nested Table.
	(5).	What is mirrored-online redo log?
	Ans.	In an attempt to preserve the data within the online redo logs in much the same way as control files, Oracle introduces redo log groups. They enable redo logs to be mirrored across multiple disks. SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; Instead of having individual redo logs, each of which contains a distinct series of transactions, the redo logs are broken down into groups and members.
	(6).	Name different kinds of tuning at database level.
	Ans	Application Tuning. Database Tuning. I/O Tuning Memory Tuning Contention Tuning Operating System Tuning
	(7).	Which command is issued to force the checkpoint ?
	Ans	SQL> ALTER DATABASE SWITCH LOGFILE; SQL> ALTER DATABASE CHECKPOINT;
	(8).	What parameter will format the name of archived redo logs?
	Ans	SHOW PARAMETER LOG_ARCHIVE_START SQL> ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE;
	(9).	List various shutdown methods.
	Ans	Shutdown Normal (Default) Shutdown Transaction Shutdown Immediate Shutdown Abort
	(10).	What is log switch ?
	à	LGWR writes new copy of changed data from Redo Log buffer to Online Redo Log files. When Online Redo log File is Full LGWR begins writing into next redo log file. Moving from One Redo log file to next Redo log file for Writing is called Log Switch. It is Automatically occurs when Online Redo log file is Full. You can do it Force fully using following Command : SQL> Alter database switch logfile;
	(11).	What is UTLBSTAT and UTLESTAT?
	Ans.	Oracle provides tools that enable you to examine in detail what the Oracle RDBMS was doing during a specific period of time. They are the begin statistics utility (utlbstat) and the end statistics utility (utlestat). These scripts enable you to take a snapshot of how the instance was performing during an interval of time. They use the Oracle dynamic performance (V$) tables to gather information.
	(12).	What is tablespace?
	Ans	A tablespace is the name given to a group of one or more database files. When objects are created, you can specify in which tablespace they will occupy storage. This gives you control over where and how much storage is used. You can specify the amount of storage that users are allowed to use in each tablespace in the database. To Create a Tablespace : SQL> CREATE TABLESPACE T1 DATAFILE F:\DB1.DBS’ SIZE 10M;
	(13).	What is an Oracle Instance ?
	Ans	Oracle Instance is Combination of Memory Structures (SGA) and Background Processes. SGA : Shared Pool, Redo Log Buffer, Database Buffer Cache, Large Pool, Java Pool Background Processes : DBWn, LGWR, PMON,SMON, ARCHn, Checkpoint.
	(14).	Name the Three stage of Oracle Instance Startup?
	Ans.	STARTUP Nomount, Mount, Open, Force, Recover, Restrict
	(15).	What is profile ?
	Ans	The database profile is Oracle's attempt to enable the DBA to exercise some method of resource management upon the database. A profile is "a named set of resource limits." To Create a Profile SQL > CREATE PROFILE BOSS LIMIT ( FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2 IDLE_TIME 600 CONNECT_TIME 500 SESSIONS_PER_USER 5 );
Q.4	Answer in one word or one sentence :		10.
	(1).	Which tool is commonly used to create queries and execute them against SQL Server databases?
		Query Analyzer
	(2)	What are the three types of Transact-SQL statements that SQL Server supports?
		DDL, DCL, and DML
	(3).	What is Transact-SQL?
		Transact-SQL is a language that contains the commands used to administer instances of SQL Server; to create and manage all objects in an instance of SQL Server; and to insert, retrieve, modify, and delete data in SQL Server tables. Transact-SQL is an extension of the language defined in the SQL standards published by ISO and ANSI.
	(4).	What methods can you use to create a SQL Server database object?
		SQL Server provides several methods that you can use to create a database: the Transact-SQL CREATE DATABASE statement, the console tree in Enterprise Manager, and the Create Database wizard (which you can access through Enterprise Manager).
	(5).	Which statement should you use to delete all rows in a table without having the action logged?
		The TRUNCATE TABLE statement
	(6).	Which three types of cursor implementations does SQL Server support?
		Transact-SQL server cursors, API server cursors, and client cursors
	(7)	Name three common database tasks accomplished with triggers
		Maintaining running totals and other computed values; creating audit records; invoking external actions; and implementing complex data integrity
	(8)	What is the default sort order for an index key?
		An index key is sorted in ascending order unless you specify descending order (the DESC keyword).
	(9).	Name a SQL Server tool you can use to monitor current SQL Server activity.
		The Current Activity node of Enterprise Manager and SQL Profiler are two SQL Server tools that monitor current activity.
	(10).	What are several keywords that you can use in a select list?
		DISTINCT, TOP n, and AS