Chapter 6 – Database Security – Introduction to Database Management Systems

Chapter 6

Database Security

Security of information has become perhaps the most significant issue of modern computing. Modern security mechanisms also take care of message integrity, confidentiality, and non-repudiation. The essense is to take a message and transform it to make it non-understandable.

Chapter Highlights

  • Classification of Data
  • Meaning of Information Security
  • Principles of Security
  • Introduction to Cryptography and Encryption Technologies
  • Database Security and Statistical Databases


6.1.1 Importance of Data

It is important to know which data is more important and which is not. As we know, all data is not equally important to an organisation. Thus, some data might be critical, some nice to have, and finally, some completely irrelevant. Therefore, we must be able to classify data and based on such classification attach importance and priorities to it. We can then take the toughest measures to protect the most critical data and reasonable steps to protect the data that is useful to some extent.

Data classification also helps in ensuring that data is protected in the most cost-effective manner. Any security mechanism involves basic costs, overhead and maintenance costs. The importance of data must be ascertained in order to ensure that the right data is being protected and to determine if the data being protected actually needs that sort of protection. Data classification is a very important tool to this effect.

Each class of data has separate requirements and processes related to how the data is accessed, made use of, and destroyed when no longer necessary. For example, in an organisation, confidential information might be accessible only to the senior management. Therefore, some broad-level requirements for making this data accessible can be specified as follows:

  • At least two persons must enter their access codes
  • The data cannot be printed on paper
  • Each and every action taken by the user while accessing the data must be recorded in detail into an audit log

Such data may require degaussing and destruction of media when it is no longer useful.

Some other information may be classified as slightly sensitive, but not that confidential. Such data might be accessible to a large group of people. Only a user id and password may be used to access such data. The audit log may not be very detailed and the actual auditing process may happen infrequently. The data may be printed and while destroying it normal processes, such as formatting the disk, may be good enough.

The remaining information can actually be public property. All employees may be able to access it. No specific auditing or destroying mechanisms may be required.

Protecting digital information is not significantly different from protecting information in paper form. For example, we sign cheques to ensure that any alterations can be detected and dealt with. This can also ensure that we are authorising a payment. Similarly, we can digitally sign a document on a computer to protect it against unauthorised alterations and to ensure that nobody can tamper with them.

6.1.2 Private Organisations versus Military Classifications

Organisations choose different security models and employ varying security practices, based on the classification of data. The type of organisation and its aims and objectives mainly determine the level of security models and practices. One organisation that is keen on high-level security measures is the military. Military organisations are highly concerned about disclosing their data to any outsider. On the other hand, private organisations are more worried about the integrity and availability of data. This is shown in Fig. 6.1.

Fig. 6.1 Varying focus on data security

These factors greatly influence the way different types of organisations classify data. Before it implements data classification mechanisms, an organisation, be it military or private, must take decisions regarding the sensitivity mechanisms that they would employ. For instance, one organisation may choose to use only two classifications: confidential and public. On the other hand, another organisation may choose top secret, confidential, sensitive and public as its classifications. Table 6.1 shows the various ways these classifications can be done for private business organisations.

Table 6.1 Classification of data for private organisations

Classification Description Example

  Needs special measures to ensure the integrity of data

  Protects from unauthorised changes and deletion

  Demands a very high assurance of accuracy and completeness

  Profits, earnings, forecasts

  Financial information

  Details of operations


  Must be accessible within the organisation only

  Can seriously affect the organisation if compromised

  Program code

  Plans to beat competition

  Trade secrets


  Personal information

  Should be used within the organisation

  Unauthorized disclosure outside can affect people in the organisation

  Human resource details

  History of personnel

  Medical records


  Can affect competitiveness if disclosed in an unauthorised manner

  Functional specifications of a product or service

  Technical information about a project


  Any data that cannot be classified as any of the above

  Disclosure of data is not desired, but also would not have significant impact

  Next project to be executed

  Software used in a project

Table 6.2 shows the various ways these classifications can be done for military organisations.

Table 6.2 Classification of data for military organisations

Classification Description Example

  Very important data

  Can cause harm to the nation's interests if disclosed in an unauthorised manner

  Details of nuclear operations

  Plans for the troops in a state


  Must be accessible within the organisation only

  Can seriously affect the organisation if compromised

  Details of the plans for the next quarter

  Evaluation regarding arms and ammunition

Top secret

  Very serious implications to the nation (and to the world) if disclosed

  Details of weapons to be used in a war

  Spy satellite information

Sensitive but unclassified

  Secret but not so important as compared to the above classifications

  Still can cause serious damage, if disclosed

  Personnel records

  Medical records


  Any data that cannot be classified as any of the above

  Disclosure of data is not desired but would not have significant impact

  Recruitment plans

We will note that the only category that is common to private businesses and military organisations is the confidential data. Let us now have a pictorial view the levels of sensitivity for these two types of organisations, as is shown in Fig.6.2. As we can see, the topmost category specifies the most sensitive information, whereas the category at the bottom represents the least sensitive information.

Fig. 6.2 Sensitivity levels of data for private and military organisations

Once they decide on the mechanism to be employed, organisations (private or military) need to come up with the criteria that they would like to adopt to for each category of information. Some of them are listed below. An organisation can use them for ascertaining the sensitivity of data.

  • Who should access this data?
  • Who should maintain this data?
  • Where should the data be kept/stored?
  • Who should be allowed to reproduce the data?
  • Which data need special identification tags and labels?
  • Usefulness of data
  • Age of data
  • Criticality of data
  • Kind of damage that can be caused on the disclosure of data
  • Kind of damage that can be caused on the modification of data
  • Kind of damage that can be caused on the deletion of data
  • Laws, regulation, compliance and audit requirements
  • Impact of the data on national security
  • Cultural issues, if any

Fig. 6.3 Procedure for classification of data

Having classified data and selected the most appropriate sensitivity mechanism, the next logical step is to specify how each classification needs to be dealt with. It is necessary to now define the provisions for access control, identification and tagging needs. It is also necessary to specify how data should be entered, stored, maintained, transmitted and destroyed. Any pending issues pertaining to audits, monitoring and compliance need to be tackled. Different classifications come with different degrees of security and so, have different requirements.

We can now outline the procedure for classification of data, as shown in Fig.6.3.


There are certain principles of security. If these principles are broken, then the security of the information is under threat. It then poses significant risks to the information. Let us assume that person A wants to send a paper check worth $100 to person B. What are the factors that A and B will think of in such a case? Person A will write the check for $100, put it inside an envelope, and send it to B.

  • Person A would like to make sure that no one except B gets the envelope, and even if someone does they do not get to know about the details of the check. This is the principle of confidentiality.
  • Persons A and B would like to ensure that no one can tamper with the contents of the check (such as the date, amount, signature and payee details). This is the principle of integrity.
  • Person B would like to be confident that the check has indeed come from A, and not from someone else posing as A (in which case it could be a fake check). This is the principle of authentication.
  • What will happen if B deposits the check in the bank, the money is transferred from A's account to B's, and then A denies having written or sent the check? The court will use A's signature to disallow A to refute this claim and settle the dispute. This is the principle of non-repudiation.

These are the four chief principles of security related to data. We shall discuss all these security principles in the next few sections.

6.2.1 Confidentiality

The principle of confidentiality specifies that only the sender and the intended recipient(s) should be able to access the contents of a message.

Confidentiality gets lost if an unauthorised person is able to access the contents of a message. An example of compromising the confidentiality of a message is illustrated in Fig. 6.4. As we can see, user A has sent a message to user B. Another user, C (an attacker), somehow accesses this message, which is not desired and therefore defeats the purpose of confidentiality. This type of attack is called interception.

Fig. 6.4 Loss of confidentiality

6.2.2 Authentication

Authentication helps establish proof of identity.

The authentication mechanism ensures that the origin of a message or document is correctly identified. For instance, suppose user C sends an electronic document over a computer network to user B by posing as user A. How would user B know that the message has come from user C, who is posing as user A? A real-life example of this could be a case of user C, posing as user A, sending a purchase request to merchant B. The merchant might willingly send the goods to C thinking that user A has requested for the goods! This concept is shown in Fig. 6.5. This type of attack is called fabrication.

Fig. 6.5 Absence of authentication

6.2.3 Integrity

When the contents of a message are altered after the sender sends it, but before it reaches the intended recipient, we say that the integrity of the message is lost.

For example, suppose a check for $100 is issued to pay for books bought from the US. However, when the issuer sees his next account statement, he is amazed to see that the check has resulted in a payment of $1000! This is a case of loss of message integrity and is illustrated in Fig. 6.6. Here, user C alters the contents of a message originally sent by user A, which was actually meant for user B. User C somehow manages to access it, alter its contents, and send the changed message to user B. User B has no way of knowing that the contents of the message have been altered after user A had sent it. User A also does not know about this change. This type of attack is called modification.

Fig. 6.6 Loss of message integrity

6.2.4 Non-repudiation

There are situations where a user sends a message and later denies having sent it. For example, suppose user A sends a funds transfer request to bank B. After the bank executes the funds transfer as per the instructions A says that the funds transfer instruction to the bank was never sent! Thus, A repudiates, or denies, the funds transfer instruction.

The principle of non-repudiation defeats possibilities of denying something after having done it.


In simple terms, cryptography is a technique of encoding and decoding messages so that they are not understood by anybody except the sender and the intended recipient. We employ cryptography in our daily life when we do not want a third party to understand what we are saying. For instance, you can have a convention wherein Ifmmp Kpio actually means that you are saying hello to your boyfriend John (that is, Hello John!). Here each alphabet of the original message (i.e. H, e, l, etc.) is changed to its next immediate alphabet (i.e. I, f, m, etc.). Thus, Hello becomes Ifmmp, and John becomes Kpio. Thus, when John makes a phone call to you and your husband is around, you say Ifmmp Kpio! Only you and John know its meaning!

Cryptography uses the same basic principle. The sender and recipient of the message decide on an encoding and decoding scheme and use it for communication. In technical terms, the process of encoding messages is called encryption. While the original text is called plaintext, when encrypted it is called ciphertext. The recipient understands the meaning and decodes the message to extract the correct meaning out of it. The process of decoding a encrypted message is called decryption. Fig. 6.7 depicts this.

Fig. 6.7 Encryption and decryption process

Note that the sender applies the encryption algorithm and the recipient applies the decryption algorithm. The sender and the receiver must agree on this algorithm for any meaningful communication. The algorithm basically takes one text as input and produces another as the output. Therefore, the algorithm contains the intelligence for transforming messages. This intelligence is called the key. Only the persons having intelligence about the message transformation, that is access to the key, can encrypt and decrypt the messages.

6.3.1 Types of Cryptography

Based on the number of keys used for encryption and decryption, cryptography can be classified into two categories: symmetric key cryptography and asymmetric key cryptography, as shown in Fig. 6.8.

Fig. 6.8 Types of cryptography Symmetric key cryptography Also called secret key cryptography, in this scheme only one key is used and the same key is used for encryption and decryption of messages. Obviously, both the parties must agree upon the key before any transmission begins and nobody else should know about it if it is to remain effective. The example in Fig. 6.9 shows how symmetric key cryptography works. Basically the key changes the original message to an encoded form at the sender's end, while at the receiver's end, the same key is used to decrypt the encoded message, thus deriving the original message out of it. IBM's Data Encryption Standard (DES) uses this approach. It uses 56-bit keys for encryption.

Fig. 6.9 Symmetric key cryptography

In practical situations, symmetric key cryptography has a number of problems; one problem being that of key agreement and distribution. How do two parties agree on a key in the first place? One way is for someone from the sender's side (say A) to physically visit the receiver (say B) and hand over the key. Another way is to courier a paper on which the key is written. Both are not exactly very convenient. A third way is to send the key over the network to B and ask for a confirmation. But then, if an intruder gets the message, he can interpret all the subsequent messages!

The second problem related to symmetric key cryptography is more serious. Since the same key is used for encryption and decryption, one key is required per communicating party. Suppose A wants to securely communicate with B and also with C. Clearly, there must be one key for all communications between A and B; and another distinct key for all communications between A and C. The same key as used by A and B cannot be used for communications between A and C. Otherwise, there is a chance that C can interpret messages between A and B, or B can do the same for messages between A and C! Since the Internet has thousands of merchants selling products to hundreds of thousands of buyers, using this scheme would be impractical because every buyer-seller combination would need a separate key!

Thus IBM's DES has been found to be vulnerable. Therefore, better symmetric key algorithms have been proposed and are in active use. One way is to simply use DES twice with two different keys (called as DES-2). A stronger mechanism is DES-3, wherein key-1 is used to encrypt first, key-2 (a different key) is used to re-encrypt the encrypted block, and key-1 is used once again to re-encrypt the doubly encrypted block. DES-3 is quite popular and is in wide use. Other popular algorithms are IDEA, RC5 and RC2.

Encryption is like codifying something so that others do not understand what we mean. Small children use code language to discuss their secrets with each other. In a sense, that is nothing but encrypting information. On the other hand, decryption is exactly the opposite; it transforms codified information back into normal, readable form. Asymmetric key cryptography This is a better scheme and is also called public key cryptography. In this type of cryptography, two different keys (called as a key pair) are used; one key is used for encryption and only the other key for decryption. No other key can decrypt the message – not even the original key used for encryption. The benefit of this scheme is that every communicating party needs just a key pair for communicating with any number of other communicating parties. Once a user obtains a key-pair, he can communicate with anyone else on the Internet in a secure manner.

There is a simple mathematical basis behind asymmetric key cryptography. If we have an extremely large number that has only two prime number as factors, we can generate a pair of keys. For example, consider the number 10. Number 10 has only two factors—5 and 2. If we apply 5 as an encryption factor, only 2 can be used as the decryption factor. Nothing else – not even 5 itself – can do the decryption. Of course, 10 is a very small number. Therefore, this scheme can be broken into with little effort. However, if the number is large, even years of computation cannot break the scheme.

One of the two keys in this form of cryptography is called public key and the other private key. If we want to communicate over a computer network such as the Internet in a secure manner we would need to obtain a public key and a private key. We can generate these keys by using standard algorithms. The private key remains with the user as a secret and must not be disclosed to anybody. However, the public key is for the general public. It is disclosed to all parties that one wants to communicate with. In this scheme, in fact, each party or node publishes his public key. Using this, a directory can be constructed where the various parties or nodes (i.e. their ids) and their corresponding public keys are maintained. One can consult this and get the public key for any party that one wishes to communicate with by a simple table search. Suppose A wants to send a message to B without having to worry about its security. Then, A and B should each have a private key and a public key.

  • A's private key should be known only to A. However, A's public key should be known to B.
  • Only B should know B's private key. However, A should know B's public key.

The working of such a system of simple:

  1. When A wants to send a message to B, A encrypts the message using B's public key. This is possible because A knows B's public key.
  2. User A sends this message (encrypted with B's public key) to B.
  3. User B decrypts A's message using his private key. Note that only B knows about his private key. Thus, no one else can make any sense out of the message even if one manages to intercept it. This is because the intruder does not know about B's private key and only B's private key can decrypt the message.
  4. When B wants to send a message to A, exactly the reverse of the above takes place. B encrypts the message using A's public key. Therefore, only A can decrypt the message with the use of his private key.

This is illustrated in Fig. 6.10.

Fig. 6.10 Asymmetric key cryptography

The RSA algorithm

Let us examine a practical example of public key encryption. In 1977, Ron Rivest, Adi Shamir and Len Adleman at MIT developed the first major public key cryptography system. This method is called as Rivest-Shamir-Adleman (RSA) scheme. Even today, it is the most widely accepted public key solution. It solves the problem of key agreement and distribution so that there is no need to send thousands of keys across the network just to arrive at an agreement. All one needs to publish is one's public key. All public keys can then be stored in a database which can be consulted by anyone. However, the private key only remains with the original user. Thus, it requires a very basic amount of information sharing among users.

The RSA algorithm is based on the fact that it is easy to find and multiply large prime numbers, but extremely difficult to factor their product. The following discussion about RSA is a bit mathematical in nature, and can be safely skipped in case you are not interested in knowing the internals of RSA. However, if you are keen to know the mathematical details, you can continue reading. Let us now understand how RSA works. Fig. 6.11 shows an example of the RSA algorithm being employed for exchanging encrypted messages.

Fig 6.11 Example of RSA algorithm

Assuming that sender A wants to send a single character F to receiver B this will work as follows. We have chosen such a simple case for ease of understanding. Using the RSA algorithm, the character F would be encoded as follows.

  1. Use the alphabet-numbering scheme (i.e. 1 for A, 2 for B, 3 for C and so on). As per this rule, 6 would represent F. Therefore, at first F would be encoded to 6.
  2. Choose any two prime numbers, say 7 and 17.
  3. Subtract 1 from each prime number and multiply the two results. Thus, we have (7 – 1) × (17 – 1) = 96.
  4. Choose another prime number, say 5. This number, which is the public key, is called Ke in the RSA terminology. Therefore, Ke = 5.
  5. Multiply the two original prime numbers of step 2. We have 17 × 7 =119.
  6. Calculate the following: (Original encoded number of step 1)Ke modulo (Number of step 5) that is 65 modulo 119, which is 41.
  7. The number thus obtained (41) is the encrypted information to be sent across the network.

At the receiver's end, the number 41 is decrypted to get back the original letter F as follows.

  1. Subtract 1 from each prime number and multiply the two results — (7 – 1) × (17 – 1) = 96.
  2. Multiply the two original numbers, that is, 17 × 7 = 119.
  3. Find a number Kd such that when we divide (Kd × Ke) by 96, the remainder is 1. After a few calculations, we can come up with 77 as Kd.
  4. Calculate 41Kd modulo 119. That is, 4177 modulo 119. This gives 6.
  5. Decode 6 back to F from the alphabet numbering scheme.

It might appear that anyone who knows about the public key Ke (5) and the number 119 could find the secret key Kd (77) by trial and error. However, if the private key is a large number, and another large number is chosen instead of 119, it will be extremely difficult to crack the secret key. This is what is done in practice.

Statistical database are like summary reports. You can make out the overall happenings in a database, but it is not so easy to identify individual pieces of information.

6.4 Digital Signature

Using the techniques described earlier, we can sign a computer-generated document or message just as we sign a check. A message thus signed contains our digital signature. This also involves the same principles of encryption and decryption.

Signing a document digitally involves the following steps:

  1. The sender calculates a unique value called message digest or hash of the message (say MD1).
  2. The sender encrypts the message digest with its private key. This is the digital signature of the message (say DS).
  3. At the receiver's end, the original message and the corresponding digital signature are received.
  4. The receiver also calculates its own message digest of the message (say MD2).
  5. The receiver decrypts the digital signature (DS) of step 2 with the public key of the sender. This gives the receiver the message digest as was calculated by the sender in step 1 (MD1).
  6. The receiver now compares MD1 with MD2. If the two match, the receiver is sure that the message sent by the sender in step 2 authentic and not tampered with.

The process is shown in Fig. 6.12.

Fig. 6.12 Digital signature process

The other important feature supported by digital signatures is non-repudiation, that is, a sender cannot deny having sent a message. With IT laws in place in various countries, this has legal implications as well. Since a digital signature requires the use of the private key of the sender (which is supposed to be known only to the sender), once a message is digitally signed, it can be legally proven that the sender had indeed sent the message.

This can be very useful in tricky situations. For instance, suppose a user authorises a bank payment transaction over the Internet. After the payment is made, the user claims that the transaction was never performed. In such situations, which can be very common as the use of the Internet for conducting business transactions becomes widespread, digital signatures can play a crucial role. They can help settle legal disputes.


Using the concept of database control, we can specify who can access a database. Database access can be granted in two ways. In other words, there are two types of database control: discretionary control and mandatory control. This is shown in Fig. 6.13.

Fig. 6.13 Types of database control

Let us discuss what these types of control mean.

6.5.1 Discretionary Control

In this type of database control, the users of the database system have access rights, also called privileges.

For example, we can define conditions such as:

(a) User U1 can access table T1 but not table T2.

(b) User U2 can access table T2 but not table T1.

This type of database control is very common and very flexible. We can define database authorisations, which tell us what the users of the database can do. In other words, database authorisations are exactly the opposite of database constraints.

We shall discuss this type of control in greater detail with SQL syntaxes and examples.

6.5.2 Mandatory Control

Here, each database object (e.g. table) has a classification level (e.g. top secret, secret, confidential, sensitive, unclassified) and each database user has a clearance level (e.g. top secret, secret, confidential, sensitive, unclassified).

This is a very rigid scheme. A user can access a database object only if she has an appropriate clearance level. Military or government organisations generally opt for this form of database control. After the US Department of Defense (DoD) published their guidelines regarding this type of control, database vendors have started implementing them.

The implementation of classification and clearance levels is quite interesting. In it each user ID has an associated clearance level. Suppose we want to implement the authorisation levels at the row level, every row of every table in the database would have an additional column, called as Class. This column indicates the classification level. Let us suppose that we codify the clearance/classification levels as Top secret = 1, Secret = 2, and Confidential = 3; and ignore any other possibilities. Every database row will contain a value of 1, 2 or 3 in the Class column, depending on its sensitivity.

Let us consider the structure for an Employee table as follows:

Emp_ID, Name, Salary, Department, Class

Let us consider that we have two users, U1 and U2. User U1 has a clearance level of 1 and user U2 has a clearance level of 2. Therefore, user U1 will be able to retrieve all the rows from the table. On the other hand, user U2 would be able to retrieve only rows having Class = 2 or 3. Given this background. let us assume that user U2 attempts to execute the following query:

SELECT Name, Salary

FROM Employee

WHERE Salary > 1000

In this case, the DBMS would automatically modify this query as follows:

SELECT Name, Salary

FROM Employee

WHERE Salary > 1000 AND Class >= 2

Note that the DBMS figures out that the clearance level of user U2 is 2 and, therefore, prevents the user from retrieving rows whose class level is 1. Similarly, other attempts of the user to work with class level 1 would be defeated. For example, if user U2 attempts to insert a row in the Employee table, then the DBMS would set the Class value to 2 or 3 for that row, and only then perform the insertion operation.

Data and information were not treated as something very significant in the early days. However, now almost everything is computerised and automated. With the advent of electronic commerce and the proliferation of the Internet, data has become even more significant, and is now treated as a very precious commodity. Loss of data or damage to data is as bad as, or at times worse than, damage to or loss of paper money.


Before we discuss the various security mechanisms of controling database access, let us discuss a few points which will help us understand the basics of RDBMS security.

  • Database users are theoretically similar to the users of an operating system. However, there are many differences between the two types of users.
  • Database users are typically authenticated with the use of user name and password. However, in most cases, this may not be visible to the end user.
  • All objects under the control of a specific user are considered as belonging to the same schema.
  • In technical terms, a database user is sometimes called as Authorisation Identifier (Authorisation ID).
  • All the operations performed by a user on a database during one set of events is said to make up one database session. Another name for this is database connection. In it a user opens a connection to a database, performs the intended tasks and then breaks the connection. However, to establish the connection, the user must have the appropriate authentication credentials.
  • A user is allocated a set of privileges. These privileges decide what the user can and cannot do.


At a broad level, database privileges can be classified into two types: system privileges and object privileges, as shown in Fig. 6.14.

Fig. 6.14 Types of database privileges

Let us discuss the two types of privileges in brief.

  • System privileges: These privileges are related to the access of the database. They govern things such as permission to connect to the database, the right to create tables and other objects and database administration permissions. These types of privileges are not standardised across various RDBMS products.
  • Object privileges: These privileges are focused on a particular database object in question, for example, a table or view. Object privileges are standardised and do not depend on a particular RDBMS product. They have been a part of the RDBMS technology right from its inception and, i.e. such, have become far more standardised across various RDBMS products. However, various RDBMS products also provide specific object privileges in addition to the basic set of object privileges that is standard and uniform for all the RDBMS products.

The idea of system and object privileges is shown in Fig. 6.15.

Fig. 6.15 System and object privileges

Our discussion will be mainly restricted to the object privileges.


6.8.1 Operations and Privileges

Setting up appropriate object privileges can protect various database tables and views. Object privileges help in applying security mechanisms on tables and views. Table 6.3 lists the standard operations related to table and view privileges.

Table 6.3 Object privileges

Operation Privilege specifications
ALTER If a user has this privilege on a table, that user can change the structure of the table by using an ALTER TABLE statement. Note that this privilege is related to tables only. This is not applicable to views.


The user having this privilege on a table can query that table. It means that the user is allowed to access data from that table.


A user with this privilege can create new data. That is, the user can add new rows to a table.


This privilege allows the user to change the data in a table. This privilege can be restricted to specific columns in a table. For example, if a table contains five columns named A through E, we can restrict the update privilege of the user to columns B and E only.


A user with this privilege can delete one, more, or all rows in the specific table.


A user with this privilege can declare a foreign key relationship that is based on one or more columns of the table as the parent key. The access can be limited to a few columns of the table. This privilege is specific to tables. It cannot be applied to views.


A user with this privilege can drop (i.e. delete) the table itself. Note that this is different from the DELETE operation privilege. In the case of DELETE, the user can delete one, more, or all rows of a table – that is the data in the table. However, the user cannot touch the structure or the existence of the table. In the case of DROP, however, the user can delete the table itself. This means that not only all the data in the table will vanish, but the table definition/structure (and therefore, all the depending definitions/structures such as indexes and views) will also vanish.


If this privilege is allowed, the user can create an index on a table.

Note that the table or view owner has all these privileges by default. The owner can, in turn, give these privileges to the other users. This is described in the next section.

6.8.2 Granting Object Privileges

The GRANT statement is used to give database privileges to other users.

The general syntax of the GRANT statement is shown in Fig. 6.16.

Fig. 6.16 Granting privileges to other users

For example, suppose Prashant is the DBA. Obviously, Prashant would have all the privileges on the database and its objects, such as tables and views. Now suppose Prashant wants to give Ana (another user), a privilege to perform SELECT operation on a table called Sales. In such a scenario, Prashant can execute the following statement:


After Prashant executes the statement, Ana can execute a SELECT statement on the Sales table. Prior to this, she would not have been able to do this. Of course, even after the granting of privileges, Ana would only be able to execute the SELECT statement on the Sales table (unless Prashant has already given her other privileges).

When a user issues a GRANT statement to the RDBMS, the RDBMS cannot execute it blindly. The most important check it must perform is to ensure that the user who is issuing the command has the necessary privileges to grant the specified privileges to other user(s). The RDBMS has to be satisfied that the user issuing the GRANT command is the owner of the object (i.e. table or view) or has the necessary privileges to do so. If the user does not have sufficient privileges to grant privilege to other users, the RDBMS refuses to execute the GRANT command. A simple flow chart for this check is shown in Fig. 6.17.

Fig. 6.17 Execution of GRANT command

Another important point is that even if the GRANT operation is successful, the user who has received the necessary privileges cannot grant them to other users. That is, when GRANT is successful, it only allows the user receiving the privileges to perform the specified operations. However, it prevents that user from handing over privileges to other users.

For example, let us assume that Prashant has successfully granted the SELECT privilege to Ana on the Sales table. Let us further assume that there is a third user by the name Radhika, who also does not have the SELECT privilege on the Sales table. Just because Ana has now obtained the SELECT privilege on the Sales table, she cannot simply give Radhika the same privilege. Only Prashant or another user who has the rights to give grants to other users can give the SELECT privilege on the Sales table to Radhika, or any other user. This idea is shown in Fig. 6.18.

Fig. 6.18 Restrictions on GRANT

Of course, Prashant can continue giving Ana more privileges. For instance, Prashant could now allow Ana to perform UPDATE operations on the Sales table by issuing the following statement:


Various other combinations of the basic technique are possible. For instance, Prashant can allow the SELECT and INSERT privileges to Ana by using just one statement:


Similarly, Prashant can give one privilege to multiple users at the same time. For example, Prashant can allow both Ana and Radhika the SELECT privilege by using a single statement:

GRANT SELECT ON Sales TO Ana, Radhika;

Finally, Prashant can hand out multiple privileges to multiple users at the same time. For example, Prashant can allow both Ana and Radhika the SELECT and UPDATE privileges by using a single statement:


6.8.3 Restricting Object Privileges to Certain Columns

After Prashant issues the UPDATE privilege to Ana on the Sales table, Ana can update values in any column of that table. This sort of situation is not desired by many who meant users to be able to update only specific columns of a table. If privileges are to be restricted to certain sections only, the syntax of the GRANT command changes slightly. With reference to the UPDATE command, the syntax for restricting a privilege to certain columns is shown in Fig. 6.19.

Fig. 6.19 Granting selective UPDATE privileges

The names of the columns for which the user should have update privilege appear inside the bracket after the table name.

For example, let us assume that the Sales table contains four columns, namely Salesperson_ID, Customer_ID, Sale_Date and Sale_Amount. Let us assume that Ana is a sales officer, who must not be able to update the sales amount. She should be allowed to update the values in the three other columns. In such a case, Prashant can issue the following GRANT statement:

GRANT UPDATE ON Sales (Salesperson_ID, Customer_ID, Sale_Date) TO Ana;

Now, Ana can update the ID of the salesperson, the ID of the customer and the date on which the sale happened in the Sales table. However, she cannot update the values in the amount field. Of course, Prashant can allow the UPDATE privilege on only one column (rather than on multiple columns) to any user. For example, Prashant can allow Radhika to update just the date on which the sale happened:

GRANT UPDATE ON Sales (Sale_Date) TO Radhika;

REFERENCES privilege is similar in concept to UPDATE privilege. The idea behind REFERENCES privilege can be summarised thus:

When user A grants the REFERENCES privilege to user B, user B can create a foreign key reference on a table owned by A.

For example, Prashant can allow Kapil the privilege of using the salesperson ID and customer ID columns of the Sales table as parent keys to any of the foreign keys in his table. For this, Prashant needs to execute the following statement:

GRANT REFERENCES (Salesperson_ID, Customer_ID) ON Sales TO Kapil;

Assuming that Kapil has another table called SalesDetails, for which he needs to set up this foreign key relationship with the Sales table, the conceptual will look as shown in Fig. 6.20.

Fig. 6.20 Effect of allowing the REFERENCES privilege

Like in UPDATE privilege, we can omit the column names in the REFERENCES privilege as well. That is, we can allow the foreign key constraint to be put on all or any of the columns in a table. For instance, Prashant could execute the following statement:


As a result of executing of this statement, Kapil can base the foreign key of his table on any or all columns of the Sales table.

The INDEX privilege is used to create an index on a table.

As we know, an index is used to speed up access to the rows of a table. It is a set of lookup entries against the actual rows in the database table. The syntax for granting INDEX privileges is the same as the one for REFERENCES. The syntax is shown in Fig. 6.21.

Fig. 6.21 Granting INDEX privilege

We should note that the syntax for INDEX privileges is not standardised across the various RDBMS products, and may vary slightly.

6.8.4 Granting All Privileges at the Same Time

We have so far discussed the following situations with respect to the granting of object privileges:

Granting one privilege to one user
Granting one privilege to multiple users
Granting multiple privileges to one user
Granting multiple privileges to multiple users

Let us now move one step ahead. Can we do the following?

Grant all the privileges on one table to a user
Grant one privilege on one table to all users

Based on the syntaxes discussed so far, this does not look possible. However, the SQL language of RDBMS provides two powerful keywords, namely ALL and PUBLIC for this purpose.

By using ALL, we can give all the privileges on a single table to a user.
By using PUBLIC, we can give a specific privilege on a single table to all users.

Of course, another possibility is to combine ALL and PUBLIC, in which case, the following happens:

By using ALL and PUBLIC together, we can give all the privileges on a single table to all the users.

Let us discuss these concepts with a few examples.

Suppose Prashant wants to give all the privileges on the Sales table to Kapil. Then, Prashant can execute the following command:


The keyword PRIVILEGES is optional. So, the above statement is equivalent to the following statement:

GRANT ALL ON Sales TO Kapil;

Similarly, Prashant can give SELECT privilege on the Sales table to all the users with the help of the following command:


As a result, all the users in the system would now be able to execute the SELECT statement on the Sales table. This has been made possible by using just one powerful statement.

What would happen if Prashant executes the following statement?


Quite clearly, all users would be able to perform all the operations on the Sales table. That is, all users would now have all the privileges on the Sales table. Quite clearly, this is a very dangerous command from a security perspective. It must be used with great caution and only after understanding its implications. Otherwise, it can lead to disaster.

We can now summarise the syntaxes of the ALL and PUBLIC keywords, as shown in Fig. 6.22. and Fig. 6.23. Fig. 6.24. shows the syntax for combining them.

Fig. 6.22 Using the ALL keyword

Fig. 6.23 Using the PUBLIC keyword

Fig. 6.24 Combining the ALL and PUBLIC keywords

6.8.5 Allowing Others to Grant Privileges

So far, we have discussed cases in which only one person, who is either the DBA or owns rights on the objects can give privileges on those objects to other users. Does it mean that only one user is responsible for giving privileges to other users all the time? This seems to be a limiting factor in a big database implementation, where there could be thousands of tables and hundreds of users. If only one person has to deal with all the responsibilities of access rights, she can quickly get overloaded.

Precisely to deal with this problem, SQL provides a feature by which a person at the DBA level can not only grant privileges to other users but can also allow those users to grant privileges to more users. The idea is shown in Fig.6.25.

Here, the DBA has granted certain privileges to users A, B and C. What is notable, however, is that the DBA has granted the special privilege of granting privileges to others to user B only. This means that user B not only has certain privileges on certain objects, but can, in turn, grant certain privileges to any user.

Fig. 6.25 Granting the privilege to grant privileges to others

What would be the syntax for doing this? The DBA (say Prashant) would need to use the keywords WITH GRANT OPTION while granting object privileges to user B (say Radhika). The command would be as follows:


This statement means that Prashant wants to give the SELECT privilege on the Sales table to Radhika. In addition, Prashant also wants Radhika to be able to grant privileges to other users in a similar fashion. As we have noted earlier, this is achieved by using the keywords WITH GRANT OPTION.

Of course, because Prashant has given just SELECT privilege to Radhika on the Sales table, Radhika would also be able to give only SELECT privilege to other users. Radhika cannot, for instance, give an UPDATE privilege on the Sales table to other users, simply because she herself does not have it in the first place!

Now, Radhika can grant SELECT privilege to Kapil as follows:


As a result, Kapil can now access the Sales table with the SELECT privilege. Better yet, Radhika could have allowed Kapil to grant privileges further to other users with the help of the following command:


In general, the syntax for giving the rights to grant privileges to others is specified as shown in Fig. 6.26.

Fig. 6.26 Using the WITH GRANT OPTION keywords

6.9 Taking Away Privileges

What if Prashant is told that Radhika is leaving the organisation and she must not be able to access the Sales table (for that matter, any table, but we shall ignore this here)? How can Prashant take back the privileges that he had given to Radhika earlier? For this purpose, the REVOKE command can be used.

By using a REVOKE command, a privilege granted earlier can be
taken back.

For example, Prashant can execute the following command:


Prashant can take back multiple privileges with the help of the following command:


Better yet, Prashant can revoke all the privileges of Radhika at one shot as follows:


Similarly, Prashant can revoke the UPDATE and INSERT privilege from Ana and Radhika using a single command as follows:


An interesting question arises here. Suppose Prashant had granted certain privileges to Ana WITH GRANT OPTION. Further, Ana had granted certain privileges on the same table (Sales) to Kapil, again WITH GRANT OPTION. If Prashant now withdraws the privileges of Ana on the Sales table, what happens to Kapil's privileges? Going one step ahead, what happens to the privileges that Kapil may have granted to other users, and so on? For this, we should remember the following rule:

When we revoke a privilege that was granted WITH GRANT OPTION, all the users who had received the privilege because of that grant option also lose it.

Thus, Kapil and anyone else who got the privileges on the Sales table directly or indirectly from Ana would lose them.

Similarly, a user can revoke a privilege only if she had originally granted it. A user cannot take away the privilege given by another user.

6.10 Filtering Table Privileges

So far, we have not mentioned anything about restricting the privileges to specific areas of a table. That is, when Prashant gives the Sales table SELECT privilege to Ana, she can access all rows and all columns of that table. There are situations when this is not desirable. Instead, it may be necessary to provide access to a user only to certain columns, certain rows or a combination of the two. For example, let us mention a few cases of this situation.

Ana must have access only to the Salesperson ID and Customer ID columns of the Sales table.
Radhika should be allowed to access sales that happened on 7 June 2003 only.
Kapil should be able to see only the Sales date and Sales amount for sales below $100.

Clearly, in order to deal with such requirements, the normal mechanism of providing privileges is not good enough. We must have some means of providing access only to the information as the business case demands. To deal with such cases, database views should be created, and then privileges should be given on those views.

For example, to deal with the case of Ana gaining access only to the Salesperson ID and Customer ID columns of the Sales table, two steps are needed: (a) Create a view containing only these two columns, and (b) Give Ana a privilege on this view, and not on the Sales table (also called the base table). The commands for this two-step process are as follows:


SELECT Salesperson_ID, Customer_ID Creation of the view

FROM Sales;

GRANT SELECT ON Anasview TO Ana; Giving privileges on the view

Note that Ana does not have an access to the Sales table. However, she is given SELECT privilege on the view called Anasview. Since this view contains only two columns in which she is interested, the purpose of restricting Ana's access only to these columns is achieved.

Let us now examine our second requirement:

Radhika should be allowed to access sales that happened on 7 June 2003 only.

Cryptography is a very old technique for protecting information. Over the years, it has become richer and more and more sophisticated, especially with the usage of computers. Modern cryptography techniques are quite comprehensive in nature and are hard to break into. But attackers find newer ways of breaking in, and the security experts need to keep working on still better techniques to deal with them.

It should not be difficult to imagine that the following statements would do the trick:

CREATE VIEW Radhikasview AS

SELECT * Creation of the view

FROM Sales

WHERE Sale_date = ‘7 June 2003’; Giving privileges on the view

GRANT SELECT ON Radhikasview TO Radhika;

Note that in the case of Radhika, we have filtered rows, rather than columns. In the case of Anasview, we had filtered the columns and not the rows. Note that the filter can be applied to rows as well as columns. For instance, let us examine the last case:

Kapil should be able to see only the Sales date and Sales amount for sales below $100.

In this case, the creation of the view should be based on the filtering of rows as well as columns, as shown below.


SELECT Sale_date, Sale_amount Creation of the view

FROM Sales

WHERE Sale_amount < 100; Giving privileges on the view

GRANT SELECT ON Kapilsview TO Kapil;

Let us now consider another situation in which we change the SELECT privilege of Kapil to UPDATE. We also allow him access to all the columns in the table. Thus the two commands in the above operation would now look as follows.


SELECT * Creation of the view

FROM Sales

WHERE Sale_amount < 100; Giving privileges on the view

GRANT UPDATE ON Kapilsview TO Kapil;

What would be the result of this change? Kapil would now be able to update any columns pertaining to the Sales table via the Kapilsview view. This is a very useful feature. However, unless used carefully, it can prove very dangerous, at times. If we examine carefully, we would realise that Kapil can, if he desires, change the amount of a transaction record to a value greater than 100, whereas he is supposed to access only records that are valued at less than 100.

Another powerful feature which is used to prevent such situations, is the CHECK OPTION. When this option is specified, the user can perform updates to the base table through the view, but only within the boundaries of the view definition. Thus if the CHECK OPTION is specified, Kapil can update the sales amount of a record only to a value less than 100. This makes good sense because Kapil is allowed to access and update only those records whose sales amount is less than 100. If Kapil sets the amount of a record to a value greater than or equal to 100, the whole purpose of restricting Kapil's privileges in the first place would be lost! The modified definition of the view would look as follows:


SELECT * Creation of the view

FROM Sales

WHERE Sale_amount < 100


The GRANT statement would remain unchanged. Because we have inserted the WITH CHECK OPTION clause, Kapil can update the value of the sales amount for any record. However, he can only change it to a value less than 100.

6.11 Statistical Databases

One attempt to thwart attacks on databases is the possible use of statistical databases.

A statistical database allows queries based on aggregate information, but not based on the information of individual rows.

For example, if we are using a statistical database of employee and payroll information, the following question is quite legal:

What is the average pay of the male employees in this organisation?

However, the following question is quite illegal:

What is the salary of Deepa?

Note that the first question is based on cumulative data, whereas the latter is based on an individual row.

This property of statistical databases is shown in Fig. 6.27.

The benefit of this approach is quite obvious. Individual data can be protected quite easily, as it is not accessible even to the authorised users of the database system. At the most, an attacker can retrieve aggregate information, which is useful in some ways, but not to a very large extent.

Fig. 6.27 Statistical databases – What can and cannot be done?

Having made this statement, it is extremely important to understand that statistical databases do not solve all security problems. For example, if an attacker obtains a lot of information by executing a number of aggregate queries, then it might happen that the person will be able to actually obtain information even about individuals! This process is called deduction of confidential information by inference. We shall illustrate this with an example. Consider a Person table as shown in Fig. 6.4.

Table 6.4 Person table

Let us assume that an attacker wants to know Harsh's salary. The attacker knows that Harsh is a male, is educated up to BE (Bachelor of Engineering) and works as a programmer. However the attacker does not have an access to this table. Instead, he can access it in the form of a statistical table and execute queries of aggregate nature on this table. Thus, if the attacker executes the following query, it would fail – the DBMS would refuse the execution.


FROM Person

WHERE Name = ‘Harsh’

Result: This query is not allowed.

But does the attacker really need to execute this query at all? There is a very simple way to bypass the main table, and yet derive the information of interest by using the statistical databases technique. We shall consider two cases of this.

Case 1

Step 1


FROM Person

WHERE Sex = ‘M’ AND Occupation = ‘Programmer’ AND Education = ‘BE’

Result: 1

Now, the attacker knows that Harsh is the only person satisfying the above criteria in the Person table. Next, the attacker performs a simple trick.

Step 2


FROM Person

WHERE Sex = ‘M’ AND Occupation = ‘Programmer’ AND Education = ‘BE’

Result: 8500

Note that the attacker is able to compromise the database security without any problems. In the process, only legitimate queries have been run against the statistical database.

Case 2

Of course, things may not be so simple every time. But the attacker can find more interesting ways of beating the complexity. Consider the following.

Step 1


FROM Person

WHERE Sex = ‘M’

Result: 4

The attacker knows that there are four males in the Person table.

Step 2


FROM Person

WHERE Sex = ‘M’ AND (NOT (Occupation = ‘Programmer’))

Result: 2

The attacker knows that there are two male programmers and two male non-programmers in the Person table.

Step 3


FROM Person

WHERE Sex = ‘M’ AND (NOT (Occupation = ‘Programmer’))

Result: 8500

The attacker knows that the Harsh's salary could be 8500.

Step 4


FROM Person

WHERE Sex = ‘M’ AND (NOT (Occupation = ‘Programmer’))

Result: 8000

The attacker knows that the Harsh's salary could be 8000.

Thus, although unable to find out Harsh's exact salary, the attacker is able to find out the range of Harsh's salary!

Access rights


Classification level


Data Encryption Standard (DES)


Discretionary control



Key pair

Message digest




Public key cryptography

Secret key cryptography

Symmetric key cryptography

Asymmetric key cryptography


Clearance level


Database privileges

Digital signature




Mandatory control


Object privileges

Private key

Public key

RSA algorithm

Statistical database

System privileges

The importance of data varies from one organisation to another.
Data can be classified as sensitive, confidential, private, proprietary and public.
There are four major concepts related to the security of data: confidentiality, integrity, authentication and non-repudiation.
Confidentiality means keeping a secret.
Integrity means preserving the contents of a message between the sender and he receiver.
Authentication means identifying a user or a system before they can access any data.
Non-repudiation means preventing the denial of an action.
Security can be achieved by using cryptography, an art of codifying data/messages.
Cryptography involves encryption and decryption.
In encryption, a readable message is transformed into an unreadable format.
In decryption, an unreadable message is transformed back into its original readable format.
Encryption and decryption are based on two aspects: algorithm and key.
If the communicating parties use the same (single) key, the encryption is called symmetric key encryption.
If the communicating parties use two different keys, the encryption is called asymmetric key encryption.
Digital signature prevents repudiation.
Database control can be classified into two types: discretionary control and mandatory control.
Database privileges can be divided into system privileges and object privileges.
SQL provides rich features for database control and privilege enforcement.
Privileges can be granted to or revoked from a database user.
Privileges can apply to tables, columns, indexes, references and so on.
Statistical databases can protect database information to a certain extent. They contain only summary information.

1. All organisations have the same sensitivity to data.
2. Private organisations treat secret data as the most sensitive.
3. Cryptography is the same as compression.
4. Encryption means encoding of data.
5. Decryption means decoding of data.
6. In asymmetric key cryptography, two keys are used per party.
7. RSA is a symmetric key encryption algorithm.
8. In SQL, GIVE command is used to give privileges to users.
9. In SQL, no user can give privileges to other users.
10. Statistical databases contain only aggregate information.

1. The highest sensitive data in the case of private organisations is the _________ data.
(a) confidential
(b) private
(c) sensitive
(d) public
2. The lowest sensitive data in the case of private organisations is the _________ data.
(a) confidential
(b) private
(c) sensitive
(d) public
3. The highest sensitive data in the case of military organisations is the _________ data.
(a) top secret
(b) secret
(c) confidential
(d) unclassified
4. The lowest sensitive data in the case of military organisations is the _________ data.
(a) top secret
(b) secret
(c) confidential
(d) unclassified
5. The principle of ___________ ensures that only the sender and the intended recipients have access to the contents of a message.
(a) confidentiality
(b) authentication
(c) integrity
(d) access control
6. If the recipient of a message has to be satisfied with the identify of the sender, the principle of _______ comes into picture.
(a) confidentiality
(b) authentication
(c) integrity
(d) access control
7. If we want to ensure the principle of ___________, the contents of a message must not be modified while in transit.
(a) confidentiality
(b) authentication
(c) integrity
(d) access control
8. Allowing certain users specific accesses comes in the purview of ___________.
(a) confidentiality
(b) authentication
(c) integrity
(d) access control
9. SQL uses the ______ command to provide users the necessary privileges.
(a) provide
(b) handover
(c) grant
(d) allow
10. ________ privileges apply to individual users.
(a) Object
(b) Table
(c) Index
(d) System

1. Write a note on classification of data.
2. Discuss the principles of security.
3. What is the difference between integrity and confidentiality?
4. Why are authentication and non-repudiation critical?
5. What is cryptography?
6. Explain the difference between symmetric and asymmetric key cryptography.
7. Explain the GRANT and REVOKE commands.
8. What is the grant option?
9. What are statistical databases?
10. Do statistical databases guarantee a very secure environment? Why?

1. During World War II, a German spy used a technique known as Null Cipher. With the use of this technique, the actual message is created from the first alphabet of each word in the message that is actually transmitted. Find out the hidden secret message if the transmitted message is President's embargo ruling should have immediate notice. Grave situation affecting international law, statement foreshadows ruin of many neutrals. Yellow journals unifying national excitement immensely.
2. Consider a scheme involving the replacement of alphabets as follows:

If Atul sends a message HSLDNVGSVNLMVB, what should Ana infer from this?

3. Study the role of XOR operation in cryptography.
4. Encrypt the following plain text bit pattern with the supplied key, using the XOR operation, and state the resulting cipher text bit pattern.

5. Transform the cipher text generated in the above exercise back to the original plain text.
6. Consider a plain text message I AM A HACKER. Encrypt it with the help of the following algorithm:
(a) Replace each alphabet with its equivalent 7-bit ASCII code.
(b) Add a 0 bit as the leftmost bit to make each of the above bit patterns 8 positions long.
(c) Swap the first four bits with the last four bits for each alphabet.
(d) Write the hexadecimal equivalent of every four bits.
7. Write a C program to perform the above exercise.
8. Investigate what a digital certificate is and how it is useful.
9. Learn about an algorithm called Advanced Encryption Standard (AES).
10. Find out the various built-in cryptographic capabilities in different RDBMS products.