With optimistic concurrency control, the database checks resources to determine if any conflicts have occurred only when attempting to change data. If a conflict occurs, the application must read the data and attempt the change again. Pessimistic concurrency, on the other hand, control locks resources as they are required, for the duration of a transaction. Unless deadlocks occur, a transaction is assured of successful completion
5.5 Audit Tracking
Although auditing does not prevent system attacks, it is a vital aid in identifying intruders, attacks in progress, and to diagnose attack footprints (Meier, Mackman, Dunner, Vasireddy, Escamilla and Murukan). It is important to enable Windows operating system level auditing and SQL Server login auditing. SQL Server also offers an auditing capability that complies with the U.S. Government C2 certification. C2 level auditing provides substantially more audit information at the expense of increased disk storage requirements.
SQL Server 2005 will support native database encryption (Songini, 2004). Previous versions had relied on third party tools. Encryption is the conversion of data into a form, called a ciphertext, that cannot be easily understood by unauthorized people. Decryption is the process of converting encrypted data back into its original form, so it can be understood. In order to recover the contents of an encrypted signal, the correct decryption key is required. The key is an algorithm that "undoes" the work of the encryption algorithm. Data encryption adds an important layer of protection because any user trying to access the data needs the right password and the encryption key as well (Database Security and Administration, 2001). One advantage of data encryption is that files are unreadable to people who have access to the database, but no databases privileges.
5.7 Backup and Recovery
Backup and recovery is necessary to restore a database quickly and accurately after loss or damage. SQL Server can back up data in one of three ways: a full database backup, a transaction log backup, and a differential backup ("Backup"). The full option backups the entire database including the transaction log while a differential option backups data between full database backups. A sequence of transaction log backups provides for a continuous chain of transaction information to support recovery forward from database, differential, or file backups. Microsoft SQL Server supports password protection for backup media and backup sets. Using password protection helps safeguard backups against unauthorized restoration of databases, unauthorized appends to the media, and unintentional overwriting of the media.
5.8 SQL Injection Prevention Techniques
SQL Injection is caused by malicious alterations of SQL statements from their intended format, by exploiting weaknesses of a client application that is used to create them (Plicht, 2004, October). Most commonly, this takes place in Internet or intranet scenarios that require users to enter data via text boxes presented through a Web-page interface, which, in turn is used to populate a dynamically formed query.
Policht (2004, October) makes several recommendations for preventing SQL Injection. First, administrators should follow the principle of least privileged access, granting to applications only the rights required to operate properly. It is also important to implement the most secure authentication method possible (refer to section 5.2 in this paper). In cases where the number of applications users is low, creating separate SQL Server logins for each one and using these logins to define connection parameters to the target database from the Web application will make SQL Injection more difficult. Validation of a user's input by testing it for invalid or suspicious characters and checking the type of entered data is also necessary. And, replacing the SQL statement with an equivalent stored procedure, and setting the username and password values as its input parameters will render SQL Injection attacks ineffective. Finally, guarding information that might be revealed via error messages resulting from executing malformed SQL statements is another prevention technique.
Web Securities web-based architecture introduces additional security issues because Web communications are not designed with security in mind; they are stateless and use non-encrypted data transfer. Therefore, Web security measures such as firewalls, secure internet protocols such as SSL and digital certificates are necessary.
6.1 Firewall is a set of related programs, located at a network gateway server that protects the resources of a private network from users from other networks ("Firewall"). A firewall, working closely with a router program, examines each network packet to determine whether to forward it toward its destination. A firewall also includes or works with a proxy server that makes network requests on behalf of workstation users. A firewall is often installed in a specially designated computer separate from the rest of the network so that no incoming request can get directly at private network resources.
The Secure Sockets Layer (SSL) is a commonly-used protocol for managing the security of a message transmission on the Internet ("Secure Sockets Layer"). SSL has recently been succeeded by Transport Layer Security (TLS), which is based on SSL. SSL uses a program layer located between the Internet's Hypertext Transfer Protocol (HTTP) and Transport Control Protocol (TCP) layers. Sockets refers to the sockets method of passing data back and forth between a client and a server program in a network or between program layers in the same computer.
TLS and SSL are an integral part of most Web browsers and Web servers. If a Web site is on a server that supports SSL, SSL can be enabled and specific Web pages can be identified as requiring SSL access. SSL uses a public-and-private key encryption system which also includes the use of a digital certificate.
6.3 Digital Certificate is an electronic "credit card" that establishes a user's credentials when doing business or other transactions on the Web ("Digital Certificate"). It is issued by a certification authority. It contains a user's name, a serial number, expiration dates, a copy of the certificate holder's public key (used for encrypting messages and digital signatures), and the digital signature of the certificate-issuing authority so that a recipient can verify that the certificate is real.
The web provides a convenient, cheap, and instantaneous way of providing data access. Now that it is extremely easy to disseminate information, it is equally important to ensure that the information is only accessible to those who have the rights to use it in the way it is intended to be used. Network and operating system security are not sufficient to defend against dynamic threats to a Web application. Database and web application server techniques are a necessity. However, even with these extra layers of security in place, there's no such thing as a full-proof security system. As soon as new security features are added, hackers are busy finding new flaws. Still, prevention, detection and recovery will go a long way in thwarting security violations.
Ambler, S. (2004, April 1), "Concurrency Control," Available:
http://www.agiledata.org/essays/concurrencyControl.html#Collisions (Accessed 2005, January 5).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp (Accessed 2005, January 5).
Burleson, D., "Database security in your Web-enabled apps," Available:
http://www.governmentsecurity.org/articles/DatabasesecurityinyourWeb-enabledapps.php (Accessed 2005, January 6).
Carpenter. D. (2000, October 18). "Indexed views in SQL Server 2000," (App Design), Available:
http://www.sqlteam.com/item.asp?ItemID=1015(Accessed 2005, January 5).
Chapple, M. "Access controls in SQL," (Your Guide to Databases), Available:
http://databases.about.com/od/security/a/accesscontrols.htm (Accessed 2005, January 5).
Database security and administration" (2001), Available:
http://www.urgrgcc.edu/shastri/.%5CDatabase%20Course%5Cdb1-2a.htm (Accessed 2005, January 5).
Digital Certificate," Available:
http://searchsecurity.techtarget.com/sDefinition/0,sid14_gci211947,00.html (Accessed 2005, January 5).
http://searchsecurity.techtarget.com/sDefinition/0,sid14_gci212125,00.html (Accessed 2005, January 5).
Huston, B. (2003, May 18), "Protecting the SQL Server," (ITworld)) Available: http://security.itworld.com/4345/030518sqlserver/page_1.html (Accessed 2005, January 5).
Meier, J.D., Mackman, A., Dunner, M., Vasireddy, S., Escamilla, R., and Murukan, A. (2003, June), "Securing your database server," Available: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/THCMCh18.asp (Accessed 2005, January 5).
Mullins, C. (1998, December), "Using constraints in SQL Server," (Database Performance Management), Available:
http://www.craigsmullins.com/sql_1298.htm (Accessed 2005, January 5).
Optimistic and pessimistic concurrency," Available: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3kqb.asp (Accessed 2005, January 5).
Phippen, A., "Database security," Available:
http://18.104.22.168/search?q=cache:GUdU_c06ZFkJ:ted.see.plymouth.ac.uk/andyhandouts/ISAD320/Database%2520security.ppt++%22backup+and+recovery%22+and+%22database+security%22&hl=en (Accessed 2005, January 6)
Policht, M. (2004, April 23). "SQL Server 2000 security - part 2 (authentication)," (Database Journal), Available: http://www.databasejournal.com/features/mssql/article.php/3341651(Accessed 2005, January 5).
Policht, M. (2004, October 8), "SQL Server 2000 security - part 13 - SQL Injection attack," (Database Journal), Available:
http://www.databasejournal.com/features/mssql/article.php/3418281(Accessed 2005, January 5).
Secure Sockets Layer," Available:
http://searchsecurity.techtarget.com/sDefinition/0,sid14_gci343029,00.html (Accessed 2005, January 5).
Songini, M.(2004, July 27), "Microsoft offers sneak peak of SQL Server 2005," (Computerworld), Available: http://www.computerworld.com/databasetopics/data/software/story/0,10801,94822,00.html (Accessed 2005, January 5).
Zikopoulos, P. (2001). "The database security blanket," Available:
http://www.governmentsecurity.org/articles/Thedatabasesecurityblanket.php (Accessed 2005, January 5).