When you maintain a MySQL installation, it’s important to make sure that the information your users entrust to their databases is kept secure.The MySQL administrator is responsible for controlling access to the data directory and the server, and should understand the following issues.

Filesystem security. A Unix machine may host several user accounts that have no MySQL-related administrative duties. It’s important to ensure that these accounts have no access to the data directory. This prevents them from compromising data on a filesystem level by copying database tables or removing them, or by being able to read logs that may contain sensitive information.You should know how to set up a Unix user account to be used for running the MySQL server, how to set up the data directory so that it is owned by that user, and how to start up the server to run with that user’s privileges.

MySQL server security.You must understand how the MySQL security system works so that when you set up user accounts, you grant them the proper privileges for accessing the MySQL server. Users connecting to the server over the network should have permission to do only what they are supposed to be able to do.You don’t want to inadvertently grant overly permissive access to accounts due to faulty understanding of the security system!

Source of Information : MySQL (4th Edition)

• 0 comments »

General administration deals primarily with the operation of mysqld, the MySQL server, and with providing your users with access to the server.The following duties are most important in carrying out this responsibility.

Server startup and shutdown.You should know how to start and stop the server manually from the command line and how to arrange for automatic startup and shutdown when your system starts and stops. It’s also important to know what to do to get the server going again if it crashes or will not start properly.

User account maintenance.You should understand the difference between MySQL user accounts and Unix or Windows login accounts.You should know how to set up MySQL accounts by specifying which users can connect to the server, where they can connect from, and what they are allowed to do.You’ll also need to know how to reset forgotten passwords.

Log maintenance.You should understand what types of logs are available and which ones will be useful to you, as well as when and how to perform log maintenance. Log rotation and expiration are essential for preventing the logs from filling up your filesystem.

Server configuration and tuning. The MySQL server is highly configurable. Some of the operational characteristics that you can control include which storage engines the server supports, the default character set, and its default time zone. Another configuration issue involves server tuning. Your users want the server to perform at its best. The quick-and-dirty method for improving how well your server runs is to buy more memory or to get faster disks. But those brute-force techniques are no substitute for understanding how the server works.You should know what parameters are available for tuning the server’s operation and how they apply to your situation. At some sites, queries tend to be mostly retrievals.At others, inserts and updates dominate. The choice of which parameters to change will be influenced by the query mix that you observe at your own site.

Multiple server management. It’s useful to run multiple servers on the same machine under some circumstances.You can test a new MySQL release while leaving your current production server in place, or provide better privacy for different groups of users by giving each group its own server. (The latter scenario is particularly relevant to Internet service providers.) For such situations, you should know how to set up multiple simultaneous installations.

Updating MySQL software. New MySQL releases appear from time to time.You should know how to keep up to date with these releases to take advantage of bug fixes and new features. Understand the circumstances under which it’s more reasonable to hold off on upgrading, and know how to choose between the stable and development releases.

Source of Information : MySQL (4th Edition)

• 0 comments »

WCF is a technology that enables you to create services that you can access from other applications across process, machine, and network boundaries. You can use these services to share functionality across multiple applications, to expose data sources, or to abstract complicated processes. As with Web services, the functionality that WCF services offer is encapsulated as individual methods that are exposed by the service. Each method — or, in WCF terminology, each operation — has an endpoint that you exchange data with in order to use it. At this point, WCF differs from Web services. With Web services, you can only communicate with an endpoint with SOAP over HTTP. With CF services, you have a choice of protocols that you can use. You can even have endpoints that communicate through more than one protocol, depending on the network that you connect to the service through and your specific requirements.

In WCF, an endpoint can have multiple bindings , each of which specifies a means of communication. Bindings can also specify additional information, such as what security requirements must be met to communicate with the endpoint. A binding might require username and password authentication or a Windows user account token, for example. When you connect to an endpoint, the protocol that the binding uses affects the address that you use, as you will see shortly. Once you have connected to an endpoint, you can communicate with it by using SOAP messages. The form of the messages that you use depends on the operation that you are using, and the data structures that are required to send messages to and receive messages from that operation. WCF uses contracts to specify all of this. You can discover contracts through metadata exchange with a service. This is analogous to the way Web services use WSDL to describe their functionality. In fact, you can get information about a WCF service in WSDL format, although WCF services can also be described in other ways.

When you have identified a service and endpoint that you want to use, and after you know what binding you use and what contracts to adhere to, you can communicate with a WCF service as easily as with an object that you have defined locally. Communications with WCF services can be simple, one - way transactions, request/response messages, or full - duplex communications that can be initiated from either end of the communication channel. You can also use message payload optimization techniques, such as Message Transmission Optimization Mechanism (MTOM) to package data if required.

The WCF service itself may be running in one of a number of different processes on the computer where it is hosted. Unlike Web services, which always run in IIS, you can choose a host process that is appropriate to your situation. You can use IIS to host WCF services, but you can also use Windows services or executables. If you are using TCP to communicate with a WCF service over a local network, there is no need even to have IIS installed on the PC that is hosting the service.

The WCF framework has been designed to enable you to customize nearly everything you have read about in this section. Now that you have covered the basics about WCF services, you will look in more detail at these concepts
in the following sections.

Source of Information : Wrox Beginning Microsoft Visual C Sharp 2008

• 0 comments »

SQL Server automatically generates a random IV to help prevent statistical analysis attacks on columns of data. The need to eliminate patterns from encrypted data is at odds with the need to index and quickly search the same data. Indexing takes advantage of these patterns to organize data for efficient search and retrieval.

A hacker who knows the relative frequency with which certain pieces of encrypted data occur in a given column could use that information to deduce even further information about it. For example, a corporate database containing employee information in a table encrypted without the use of random IVs might leak additional information from the patterns provided. Consider the HumanResources.Employee table in the AdventureWorks database. Most of the executive and managerial titles occur only once, while the lower-level positions may occur dozens of times. A hacker might be able to infer additional information from this pattern, including information about which employees are paid the most. The hacker might use knowledge like this to help focus his attack. SQL Server’s random IV generation helps to eliminate these patterns from encrypted data. This has two main implications for T-SQL developers:

• The same IV used during encryption is required during decryption.

• The encryption functions are nondeterministic, which means that encrypting the same plain text multiple times with the same key will not generate the same encrypted text.

The nondeterministic nature of the SQL 2008 encryption functions makes it useless to index an encrypted column directly. Searching encrypted columns requires decrypting every value in the column and comparing them one by one. This is very inefficient and can be a bottleneck in your applications if your tables are large. Some methods have been suggested for increasing the efficiency of searching encrypted data. These methods generally include storing a hash of the encrypted data for indexing. The main problem with these methods is that they reintroduce the statistical patterns eliminated by the random IVs. You can take several approaches to strike a balance between data security and search efficiency. The most important recommendation is to not encrypt columns you will use heavily in your query search criteria (WHERE clause), sort criteria (ORDER BY clause), or grouping (GROUP BY clause).

However, sometimes you might not have a choice—you may need to encrypt a column that is part of your WHERE clause or other query criteria. One thing you can do to make this more efficient is to narrow down your results using other criteria involving nonencrypted columns first.

You can create a “pseudo-index” of your data by adding an additional column to your table with a one-way hash code of your plain text, and creating an index on that column. The built-in SQL Server 2008 HashBytes function can be used to generate a one-way MD5, SHA-1, or other hash value of your plain text and store it in the new column. Indexing this new plain text hash value column can make equality searches (using the T-SQL = operator) much more efficient. Range searches (operators like <, >, BETWEEN, and so on), however, cannot be used on hashed or encrypted data. One of the implications of pseudo-indexing with a hash value is that it once again opens the door for statistical analysis attacks using the hash values as a guide. Using a hash value as an index also makes dictionary attacks against the hashed values possible. A dictionary attack is one in which a hacker uses a large list of plain text values to try to guess the plain text of a hashed or an encrypted value by brute force.

Another method of pseudo-indexing encrypted data is a variation on the previous method, except that it uses a hashed message authentication code (HMAC) in place of the hash value. The HMAC basically takes a “secret” value, combines it with the plain text, and generates a hash value based on that data. Although the HMAC method provides protection against dictionary attacks, it doesn’t provide any additional protection against statistical analysis

The main thing to consider when using SQL Server’s data-encryption facilities is that encryption and search efficiency are opposing goals. The purpose of encryption is data security, often at the expense of search efficiency. While you can use the methods suggested here to increase the efficiency of SQL queries on encrypted data, the hash value and HMAC index methods require more storage and can actually circumvent SQL Server’s protection against statistical analysis (via random IV)..

Source of Information : Apress Accelerated SQL Server 2008

• 0 comments »

In addition to using certificates, asymmetric keys, and symmetric keys, you can encrypt your data using passphrases. A passphrase is a string or binary value from which SQL Server can derive a symmetric key to encrypt your data. The EncryptByPassPhrase and DecryptByPassPhrase functions allow you to use this type of encryption, as in the following example:

DECLARE @plain_text nvarchar(1000),
@enc_text varbinary(2000);
SET @plain_text = N'Ask not what your country can do for you...';
SET @enc_text = EncryptByPassPhrase(N'E Pluribus Unum', @plain_text);
SELECT 'Original plain text = ', @plain_text;
SELECT 'Encrypted text = ', @enc_text;
SELECT 'Decrypted plain text = ',
CAST(DecryptByPassPhrase(N'E Pluribus Unum', @enc_text) AS nvarchar(1000));

EncryptByPassPhrase accepts the plain text that you want to encrypt. DecryptByPassPhrase, on the other hand, accepts the previously encrypted cipher text that will be decrypted. For both functions, you can add an authenticator value to further obfuscate your encrypted text, as follows:

SET @enc_text = EncryptByPassPhrase(N'E Pluribus Unum', @plain_text,
1, N'Authentic');

Both functions return a varbinary(8000) value. After you use DecryptByPassPhrase, you may need to cast your result back to another datatype, such as varchar or nvarchar.

EncryptByPassPhrase and DecryptByPassPhrase use the Triple DES algorithm to encrypt and decrypt data. You cannot choose another algorithm to encrypt and decrypt with these functions.

Source of Information : Apress Accelerated SQL Server 2008

• 0 comments »

Prior to SQL Server 2005, T-SQL included a couple of very simple, very basic hash functions: CHECKSUM and BINARY_CHECKSUM. Neither of these hash functions is collision-free, and both return a 32-bit hash, which is well below the minimum length recommended by cryptographic experts for secure applications.

Introduced in SQL Server 2005, the HashBytes function accepts the name of a hash algorithm and an input string, as follows:

SELECT HashBytes ('SHA1', 'Now is the time for all good men...');

The hash algorithm used in the example is SHA-1. You can use MD2, MD4, MD5, SHA, or SHA-1 for this parameter. The former three are the Message Digest algorithms, which generate 128-bit hashes of the input. The latter two are the Secure Hash Algorithm, which generates a 160-bit digest of the input. The input to the HashBytes function is a varchar, an nvarchar, or a varbinary value. The result of HashBytes is always a varbinary value with amaximum length of 8,000 bytes.

SQL Server also provides functions to sign data with certificates and asymmetric keys, and to verify those signatures. This is useful for protecting the integrity of sensitive data, since any small change in the data will affect the signature. The SignByCert and SignByAsymKey functions sign your data with a certificate or an asymmetric key and return the signature as a varbinary. The length of the signature depends on the length of the certificate or asymmetric key’s private key. A 2,048-bit private key generates a 256-byte signature; a 1,024-bit private key generates a 128-byte signature; and so on. The formats for SignByCert and SignByAsymKey are as follows:

SignByCert ( certificate_ID, plaintext, password )
SignByAsymKey ( asym_key_ID, plaintext, password )

The SignByCert function accepts a certificate ID, which can be retrieved with the Cert_ID function. The SignByAsymKey function accepts the asymmetric key ID, which is retrieved with the AsymKey_ID function. The plaintext parameter in both functions is the plain text to be signed—a char, a varchar, an nchar, or an nvarchar value. The password is the password required to decrypt the certificate or asymmetric key, if it is protected by password.

You can verify previously signed data with the VerifySignedByCert and VerifySignedByAsymKey functions, which have the following format:

VerifySignedByCert ( certificate_ID, plaintext, signature )
VerifySignedByAsymKey ( asym_key_ID, plaintext, signature )

The VerifySignedByCert and VerifySignedByAsymKey functions accept a certificate ID and an asymmetric key ID, respectively. The plaintext parameter of both functions is the plain text that was previously signed, and the signature parameter is the varbinary signature that was generated. These two functions generate the signature for the plaintext value and compare the newly generated signature to the signature you pass in to the function. Both functions return a 1 if the data matches the signature, or a 0 if the data and signature do not match.

Source of Information : Apress Accelerated SQL Server 2008

• 0 comments »