Student Publications

Database & Information Systems Management
Country :

Available for Download: Yes

Sharing knowledge is a vital component in the growth and advancement of our society in a sustainable and responsible way. Through Open Access, AIU and other leading institutions through out the world are tearing down the barriers to access and use research literature. Our organization is interested in the dissemination of advances in scientific research fundamental to the proper operation of a modern society, in terms of community awareness, empowerment, health and wellness, sustainable development, economic advancement, and optimal functioning of health, education and other vital services. AIU’s mission and vision is consistent with the vision expressed in the Budapest Open Access Initiative and Berlin Declaration on Open Access to Knowledge in the Sciences and Humanities. Do you have something you would like to share, or just a question or comment? We would be happy to hear from you, please use the Request Info link below.

For more information on the AIU's Open Access Initiative or to view other Student Publications, click here.


University Home
AIU Mission Vision
Bachelor Study
Masters Study
Doctoral Study
Areas of Study
Press Room
Video Conferences
Open Access
Apply Online



Sr. No.


Page No.





Comprehensive Security Mechanism



Access Control Methods






A Simple Demonstration



Data Encryption



Encryption of Data-At-Rest



Encryption of Data-In-Motion



Encryption of Data-At-Rest-How To?



Performance Problems – How to minimize?



Encrypting All Columns in a Table



Encrypting All But One Column in a Table



Encrypting Only the required Row in a Table



Key Management














The digital age is upon us, and instant access to information is not a request anymore, it is now an absolute requirement. Serving as a backbone for instant access is the relational database management system, plainly known as the RDBMS. Databases serve as the virtual warehouses of digital information and are able to hold our most critical assets. As such, to properly maintain the integrity and confidentiality of the data contained in a database, the need for securing data structures and the data elements itself is growing by leaps and bounds. One of the many required methods for securing databases is to encrypt the information stored within them in tables. Unfortunately however, there are many misconceptions surrounding what database encryption actually is and how it should be implemented.
Encryption is a complex subject and its proper implementation requires a grasp of not only the theories pertaining to encryption, but also a detailed knowledge of the practical applications in the real world. Due to this fact alone, very often, the line between access control and encryption is blurred, resulting in the encryption solutions simply supplementing the access control mechanisms already in place. This paper will discuss the appropriate use of encryption technology and its proper implementation.



No single security solution can properly protect an information system. What is most important is to understand the concept of looking beyond your nose – an in depth research in to critical objects and means to secure the same.
What this means is that more than a single layer of security is required in order to adequately protect a computer system. A good example of this in-depth defense mechanism is a castle. A castle can and does contain multiple defense systems, including but certainly not limited to huge re-enforced castle walls, archers on the walls, etc. Break it down and you will see that individually, each defense system would not be able to deter an attack, but when combined, the castle becomes very difficult to penetrate – maintains integrity.
Encryption is one of the layers of security needed to effectively secure your database. Of course, without implementing other security measures first, encryption is an inefficient and good for nothing solution. Any attempts to encrypt data that is not locked down utilizing the proper access controls leads to poor system performance and poor security. For instance, you may have on your laptop PGP Installed, and you encrypt your private key using a strong alphanumeric passkey, would you feel comfortable providing public access to the said key? I don’t think so!



Now before even considering the implementation of encryption, you need to ensure that proper access controls are in order and properly in place. Setting up access controls require the configuration of users and the actions they should be able to perform within the database. Within a database, access control consists of creating users, and granting them the privilege to act on objects together with performing certain (controlled/restricted) commands and tasks. The built-in controls and mechanisms within the database are your best means of providing baseline access controls. Third party softwares are also present in the market to provide you with extra powerful access controls if you are as paranoid as I am…



Once you have access controls in place, only then you should consider implementing encryption. Encryption provides an additional restriction if access controls are circumvented or compromised. In rather layman terms, encryption should stop someone who has already broken through the first line of defense-the access controls. Even when hacker (for lack of a better term) has broken through the access controls, encryption forms the next barrier of entry.

A simple demonstration
To demonstrate an appropriate method of using encryption on an operating system, let us take a look at Microsoft’s Encrypted File System (EFS) within a Windows environment. In this example, we will walk through securing a document that contains secrets critical to your organization’s success, stored on a file server. The first thing you should do is set NTFS (New Technology File System) permissions on the file to prevent unauthorized users from reading the contents in the first place. This is access control and should always be the first line of defense. However, there are several weaknesses with this sort of access control;

  1. NTFS permissions do not prevent system administrators from accessing the files, hence, if an attacker gains control of the operating system, system administrator privileges can be used to read the data file.
  2. The attacker can bypass the ‘permission check’ by booting the server into a different operating system; this will allow any access controls implemented by the original operating system to be dissolved.


For instance, let us say an attacker is aware of a buffer overflow in Microsoft Windows 2000 that allows him/her to run shell commands on the server. The attacker can then reset the Administrator password on the file server and even though the appropriate permissions are in place, they can be evaded by. Now the attacker has the ability to read the files on your file server using the Administrator account.
The proprietary softwares have many security holes that the research teams keep filling up, but where there are ten brains developing software and one hundred brains trying to break through the defenses in place, you do the math! So the next logical question that comes to mind is that is this type of attack avoidable?
First of all, your system should be patched well enough to withstand most
buffer overflows. However, we live in a world where buffer overflows and other DoS attacks are discovered on a daily basis, there is little chance you can “guarantee” invulnerability to this type of an attack.
However, encryption offers a reasonable, although far from perfect, solution. One way to protect your data even if an attacker gains full control over the data is by encrypting it. The EFS (encrypted file system) can encrypt the file(s) based on the user’s password. By establishing a password as a key to the encryption, you (may) have prevented an attacker with full control of the operating system from reading the file.

There are two things to be noted very carefully;

  1. Encryption does not protect data from being deleted.
  2. Encryption does not protect data from being modified (although it does provide you a way to tell if an unauthorized change has been made).

Keep in mind the capabilities of encryption and its purpose. It is important that you maintain the proper backups so that if someone deletes or changes your encrypted data, you can restore that data from a previously set point.



Encryption can be categorized into the following two types;

  1. encryption of ‘data-at-rest’
  2. encryption of ‘data-in-motion’

The problems and approach of each type of encryption is very different and require separate research on both the fields, however, this paper addresses the issue of encrypting data-at-rest only.
Whatever the case, we should touch briefly on encryption of data-in-motion. This (data-in-motion) mode of encryption hides information as it moves across a network from the database server to the client or from the client back to the database. Data-in-motion includes traffic moving over your local area network (LAN), the Internet, or even over a wireless network (may include WAN). There are various standards for this type of encryption in the world today, some of the most famous and most used include;

  1. SSL (Secure Sockets Layer)
  2. TLS (Transport Layer Security)
  3. IPSEC (Secure Internet Protocol)

Most database vendors today have adopted the SSL standard, and include the ability to send traffic between the client and database over an SSL tunnel using some combination of RSA, RC4, DES, or Diffie-Hellman algorithms. Encryption of data-in-motion is necessary to prevent someone from intercepting traffic as it goes back and forth between the client and the database. Encryption of data-in-motion is also effective at preventing attacks such as session hijacking and replay attacks. Encryption of data-in-motion is typically implemented at session level, the network layer above the protocol being encrypted. Network communications are encrypted as they are being transmitted over the wire media and decrypted as they are received at the other end. Each command sent by the client is encrypted as it is sent and decrypted as it is received by the database. Each result is returned from the database, and is encrypted as it is sent and decrypted as the client receives it.



Encryption of ‘data-at-rest’ is the encryption of information stored in the databank or the database itself. Consider that most attacks do not occur on data-in-motion rather most security attacks take place where data sits for long periods of time (at data end points). This leaves us in an interesting situation because encryption of data-in-motion is already widely adopted. Even the most security-conscious/paranoid database administrators have not adopted encryption of data-at-rest.



There are several possible strategies to encrypt ‘data-at-rest’, and each strategy has certain advantages and disadvantages. The following pages of this paper will outline these strategies and will propose the best solution available.
Encryption of data-at-rest can be performed in several ways; one such way is to encrypt the actual database files at the operating system level. An example of using this strategy is to encrypt an entire database file using Microsoft’s EFS within a Windows environment. Although this may pose to be a very simple exercise, there are many weaknesses associated with using this strategy, a few of the major ones are;

  1. You cannot selectively encrypt individual pieces of data. This approach results in encrypting the entire file, which means all the data is encrypted. This causes serious performance problems for reading records from the database. Every time data is read from the database, it is encrypted or decrypted regardless of the whether or not the data really needs to be secured. This adds significant overhead to any action performed against the database.
  2. Encrypting the entire file not only adds the overhead of reading all data, but also leads to other additional overhead when recording pointers, indexes, and other internal data structures that must be encrypted and decrypted for any operation against the database. Ideally, when an insert is made to a database, the only encryption required should be the encryption of the data being inserted. Using file-based encryption, the information to determine where in the file to store the new record, the index, and many other internal file structures must be decrypted in addition to the data being inserted.
  3. Another weakness is that different pieces of data cannot be encrypted with different keys. Imagine if you had a database that contained both sales and personnel information. The Human Resources department should have access to the personnel data but not the sales data. The sales department should have access to the sales data but not the personnel data. Using file-level encryption, this cannot be achieved because operating system file encryption encrypts the entire file, not sections of the file.
  4. File-based encryption only protects the data from operating system-level attacks. If an operating system user copies the physical database file, the information is secured from that user. It does not protect the data from a user who breaches the database. When someone breaks into the database, it will gladly decrypt the information for the database user. This is because it appears to be a properly authenticated user, thereby circumventing the encryption.


A more efficient and rather effective way to encrypt information in a database is to perform the encryption on a column and row level. To further explain this concept, think of a table (for instance) containing a list of customers. Within this customer table, following information is stored;

  1. Customer ID
  2. Customer name
  3. Customer address
  4. Customer credit card number


In this table there is little reason to encrypt the customer ID. It is most likely that you would only want the credit card information encrypted. You gain several advantages by only encrypting your most sensitive data, which in this case is credit card detail. One advantage is that you can minimize the performance hit incurred by only encrypting sensitive information. For instance, when a user attempts to search the table for specific user, they incur very minimal overhead because only the data that must be decrypted is the data found in a specific row – a small subset of the data. Even better is the fact that when you select from other tables, which do not require encryption, there is absolutely no additional overhead added.
One of the serious problems encryption solves is protecting data from being read by administrators. This is accomplished by encrypting data utilizing a secret not known by the database administrator. Of course, the most important part of this statement is that the encryption must be dependent on restricting the administrator from discovering this secret, and utilizing it to decrypt the information within the database.
For instance, if the administrator can simply reset the password of an account, logon to the account, and access the data, encryption has failed to protect the data from that administrator. Encryption should be based on a secret such as a password. Therefore, if encryption was implemented utilizing a password as a secret, the database administrator could not just simply reset the password of an account to decrypt the data.
However, this also means that when the user needs to change his or her password, this also involves resetting the decryption keys. Investigating this statement a little closer, the encryption system that we are referring to here would utilize a single key to encrypt and decrypt data in each column. A copy of this key, called the column key, is then stored encrypted with the user’s password.
To further illustrate this concept, consider this practice; when you decide to change your password, you must first login with your current password, decrypt each column key, and then re-encrypt each column key with your new password replacing the old copy of the key. Next time you login with your new password, you can decrypt the keys with the new password. If an administrator simply changes the database password and logs in, he/she will decrypt the key to the wrong values because they are using the wrong password, and will be unable to decrypt the values in the table. Using this technique, encryption is truly dependent on a secret, providing you a way to store data within your database that even an administrator cannot view.



One of the most important decisions you will make when utilizing encryption within your database is when you answer the following question: “What data should I encrypt?” Database lookups are designed to be very efficient. Unlike typical file systems, databases are expected to look through millions of rows searching for specific items in seconds. This need for fast access and retrieval places additional hardships on encrypting databases. A database cannot afford to encrypt and decrypt each piece of data it must search. Therefore, it is critical to properly plan encryption based on how an application will use the database. For instance, let us imagine that we have a table with five columns and one million rows. The table contains customer information with the following columns;

  1. CustomerID
  2. CustomerName
  3. CustomerAddress
  4. SalesRegion
  5. CreditCardNumber


Here are some alternatives with the respective performance pros and cons for each implementation;


What if you encrypt all five columns? If you select from the table for a specific CustomerID, you will be forced to decrypt the CustomerID of all 1 million rows. This will result in a huge overhead. When you insert into the table, the overhead is not substantial, however if you update the column based on the CustomerID, you will again be forced to decrypt the CustomerID for every single row.


What if instead you encrypt all columns except the CustomerID? When you select from the table for a specific customer ID, you are not required to perform any decryption until you find the actual row that matches the selection criteria. This is because the engine in the database will only look at the column you selected from for all rows. Only when it finds a row that meets the criteria you indicated would decryption need to occur. The additional time required to select from the table in this query has been reduced to almost negligible. Decryption of the rows found will cause some overhead, however this overhead is minimal since the row set is relatively smaller.
In the same case, if you selected from the table looking for a specific CustomerName, you would again be forced to decrypt all 1 million rows, resulting in a very slow query. This is because the database engine will need to decrypt the customer name for every row in order to find the rows that match the search criteria. The same would apply to Updates and Deletes. Now if you selected from the table based on the CustomerID and the CustomerName, the search would be substantially faster because decryption would only occur for rows that matched the CustomerID. Of course, if the database’s query processor decided to search based on CustomerName before CustomerID, the results would be entirely different. When the query processor decides to perform the lookup on CustomerName first, the database is forced to decrypt CustomerName for every row.



If you encrypted only the credit card numbers, you would substantially reduce the chance that any query might significantly slow down the database. The only significant performance hit would be if the query processor decided that searching on the CreditCardNumber column would be the most efficient search. This would only occur if you were to search for a specific credit card number without providing any other search criteria.
Before actually deciding which columns to encrypt, you should first gather a list of the most common statements executed against the database. Most large applications are highly dependent on a handful of queries. Analyzing the use and frequency of SQL statements allows you to make an informed decision on how encrypting a column will affect the overall database query performance.



One of the main problems with encryption is key management. Very often we see encryption implemented by hard-coding a password into a procedure or script. Even if you use the most robust encryption algorithms and the strongest keys, this implementation of database encryption is inherently flawed. The problem is reduced to the fact that you are relying on access control to implement security. The idea behind in-depth defense strategy should not rely on one layer of security to protect another layer, because in this case, once access control is compromised, encryption is also immediately compromised. This does little to improve the security posture of your database.
Encryption should be layered on top of access control because encryption should protect the data when access controls are bypassed. In cases when an operating system user is able to gain full control of the database, they can then easily bypass the access controls, however, with encryption, when implemented properly, uses mathematical algorithms to prevent someone with full control over the system to access critical data objects.





Deciding to build your own security system is not a task most people should endeavor to undertake. There are a myriad of details to deal with, such as padding or dealing with NULL values that result in complications, and leaves you open to attack if implemented incorrectly. Your best bet is to find a system that provides all the features you need built-in. While encryption will indubitably require some additional administrative work, you should find a solution that minimizes this impact. When evaluating the possible solution, you should carefully consider each system. It is most important to understand how the system works and judging for your self whether the solution provides true encryption, or does it break down when access controls are compromised. Whatever technology you decide to implement, talk to the vendor to understand the underlying architecture. If the vendor is not open to critical analysis with their underlying architecture, chances are they do not want to know that something is wrong, and you should keep searching for a better suited solution. An encryption system should not attempt to hide the implementation details by concealing how the code works. If it does however, be wary, and use expert resources to research further into the proposed solutions, chances are you might actually find what you are looking for, because not many people are in the market for professional data protection.





  1. Directory of Database Management:
  2. Resource Center for DB Management:
  3. Elmasri Navathe. (2000)                                                       Fundamentals of Database Systems (3rd Edition)                           NY: Addison-Wesley
  4. Modern Database Management (7th Edition)                                    by Jeffrey A. Hoffer, Mary Prescott, Fred McFadden         Publisher: Prentice Hall; 7 edition (April 6, 2004)
  5. Database Management Systems                                                  by Raghu Ramakrishnan, Johannes Gehrke, Raghu Ramakrishnan, Johannes Gehrke                                             Publisher: McGraw-Hill Science/Engineering/Math; 3 edition (August 14, 2002)
  6. Database Management: An Organizational Perspective                 by Richard T. Watson                                                            Publisher: Wiley; 1 edition (June 2, 1998
  7. White Papers and Case Studies (resources) on ISM:
  8. IT Management:
  9. Strategic Management and Information Systems: An Integrated Approach                                                                                   by Wendy Robson Publisher: Trans-Atlantic Publications; 2nd edition (August 1, 1996)
  10. Management Information Systems, Eighth Edition                           by Kenneth C. Laudon, Jane P. Laudon                                 Publisher: Prentice Hall; 8 edition (February 20, 2003)
  1. Information Systems Management in Practice, Sixth Edition     by Barbara C. McNurlin, Ralph H. Sprague                               Publisher: Prentice Hall; 6 edition (July 16, 2003)

Home | Spanish | Portugese | Chinese | French | Online Courses | Available Courses | View Course Demo | Career Center | Available Positions | Ask Career Coach | The Job Interview | Writing Resume | Accreditation | Areas of Study | Bachelor Degree Programs | Masters Degree Programs | Doctoral Degree Programs | Course and Curriculum | Human Rights | Online Library | Links Exchange | 54 Million Records | Press Room | New Look | Representations | Student Publications | Share with Us | Alumni | Graduates | Sponsors | General Information | Mission & Vision | School of Business and Economics | School of Science and Engineering | School of Social and Human Studies | Download Center | Admission Requirements | Tuition | Apply Online | Faculty & Staff | Distance Learning Overview | Student Testimonials | Frequently Asked Questions | Distance Learning Request Information | Register for Program | Admission Application Form

Copyright ® 1979 - 2007, 2008 Atlantic International University . All rights reserved.