ODBMS - Security Authorization

5.c) Discuss briefly about the implementation aspects of security. [6]Database security is the system, processes, and procedures that protect a database from unintended activity. Unintended activity can be categorized as authenticated misuse, malicious attacks or inadvertent mistakes made by authorized individuals or processes. Database security is also a specialty within the broader discipline of computer security.
Databases provide many layers and types of information security, typically specified in the data dictionary, including:
• Access control
• Auditing
• Authentication
• Encryption
• Integrity controls
• Database security can begin with the process of creation and publishing of appropriate security standards for the database environment. The standards may include specific controls for the various relevant database platforms; a set of best practices that cross over the platforms; and linkages of the standards to higher level polices and governmental regulations.
• An important procedure when evaluating database security is performing vulnerability assessments against the database
The results of the scans should be used to harden the database in order to mitigate the threat of compromise by intruders. vulnerability assessment is a preliminary procedure to determine risk where a compliance program is the process of on-going risk assessment
Two crucial aspects of database security compliance include patch management and the review and management of permissions (especially public) granted to objects within the database. Database objects may include table or other objects listed in the Table link. The permissions granted for SQL language commands on objects are considered in this process.
Application level authentication and authorization mechanisms should be considered as an effective means of providing abstraction from the database layer. The primary benefit of abstraction is that of a single sign-on capability across multiple databases and database platforms. A Single sign-on system should store the database user's credentials (login id and password), and authenticate to the database on behalf of the user
Another security layer of a more sophisticated nature includes the real-time monitoring of database protocol traffic (SQL) over the network, and/or local monitoring of database activity using software agents. Analysis can be performed on the traffic for known exploits or network traffic baselines can be captured overtime to build a normal pattern used for detection of anomalous activity that could be indicative of intrusion. These systems can provide a comprehensive Database audit trail in addition to the intrusion detection (and potentially protection) mechanisms
After an incident occurs, the usage of Database Forensics can be employed to determine the scope.
A database security program should include the regular review of permissions granted to individually owned accounts and accounts used by automated processes. The accounts used by automated processes should have appropriate controls around password storage such as sufficient encryption and access controls to reduce the risk of compromise. For individual accounts, a two-factor authentication system should be considered in a database environment where the risk is commensurate with the expenditure for such an authentication system


1.g) List out the main types of threat that could affect a database system. [4]
Database Vulnerabilities (The many fronts of the security war!)
Basically database security can be broken down into the following key points of interest.

• Server Security
• Database Connections
• Table Access Control
• Restricting Database Access
Server Security
Server security is the process of limiting actual access to the database server itself, and in my humble opinion it is the most important angle of security and should be carefully planned.
The basic idea is this, "You can't access what you can't see". Why in the name of the Almighty (or whoever else you believe in, or if you are an Atheist, substitute your own name here) would you let your database server be visible to the world. This is not a web server here, there should be no such thing as an anonymous connection. Now some people would say, "Well, what if your database server is supplying information to dynamic web pages?", well I'll turn that around and say, "Your database back end should never be on the same machine as your web server, not just for security, but for performance!" If your database server is supplying information to a web server then it should be configure to allow connections only from that web server. Now that bring mes to the next point of discussion:
Here Trusted IP Access has
limited the database server
to only answering information
requests from the known IP of
the web server.


Trusted IP addresses
Every server, should be configured to only allow trusted IP addresses. You don't allow just anyone to come into your house and talk to your children. In the same respect you should know exactly who should be allowed to "talk" to your database server.
If it's a back end for a web server., then only that web server's address should be allowed to access that database server. If the database server is supplying information to a homegrown application that is running on the internal network, then it should only answer to addresses from within the internal network.
Also please none of this cheap mentality of hosting your web databases on the same server that houses internal database information. Why would you have internal information out in the DMZ, its not called the DMZ for nothing.
Database Connections
These days with the number of Dynamic Applications it becomes tempting to allow immediate unauthenticated updates to a database. I say, "Ney!" to such laziness. If you are going to allow users to make updates to a database via a web page, ensure that you validate all updates to ensure that all updates are warranted and safe. For example ensure that you are removing any possible SQL code from a user supplied input. If a normal user should never be inputting it don't allow the data to ever be submitted.
If you are one of those administrators that feels the need to use ODBC connections ensure that every connection uses it's own unique user to access the shared data. It personally makes my skin crawl when I see the user account "sa" used for every connection and data source on the server. Does every employee in your company have keys to every room in the building? I'll let you address that problem quietly if they do.
Table Access Control
Table access control is probably one of the most overlooked forms of database security because of the inherent difficult in applying it. Properly using Table access control will require the collaboration of both system administrator and database developer, and we all know that "collaboration" is a foreign word in the IT industry.
An example would be allowing read access to user imputed information to the public. If a user just imputed the information whey would they have to look at it within the same session. Or, if a table is just used for system reference why should it have any other permissions beside read available?

Unfortunately table structure and proper relational database structure and development is a little out of the scope of this article. But, keep a look out for it in my upcoming articles.
Restricting Database Access
Now being that we have completed a basic overview of database security I want to dive a little further into the specifics of server security. Mainly into the network access of the system. Specifically targeting Internet based databases, since they have been the most recent targets of attacks. All web-enabled applications have ports that they listen to ( I know this is pretty basic to most of you but, it needs to be said for the beginners!).
Most cyber criminals ( I always refrain from the media sensationalized term "Hackers" or "Crackers") are going to do a simple "port scan" to look for ports that are open that popular database systems use by default. Now I say by default, because you can change the ports a service listens on, which I personally feel is a great way to throw off a criminal.
First they will attempt to determine if a machine is even at a specific address. They will do this by pinging the system. (If you don't know what ping is quietly close this article, you need to do some studying first!) This is done by simply opening up a command line and typing "ping".
C:\ ping 127.0.0.1
or
root@localhost: ~$: ping 127.0.0.1
The response should look like this:
Pinging 127.0.0.1 with 32 bytes of data:
Reply from 127.0.0.1: bytes=32 time<10ms TTL=128
Reply from 127.0.0.1: bytes=32 time<10ms TTL=128
Reply from 127.0.0.1: bytes=32 time<10ms TTL=128
Reply from 127.0.0.1: bytes=32 time<10ms TTL=128
Ping statistics for 127.0.0.1:
Packets: Sent = 4, Received = 4, Lost = 0 (0%
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
An example of the ping command
being used on a windows box.
Click to enlarge .


The criminal now knows there is a system answering at this address. First thing to prevent this is to disable any ICMP packets. This will prevent ping requests from being replied to.
There are many ways to prevent open access from the Internet and each database system has it's own set of unique features as well as each OS. So I am merely going to touch on a few methods.
• Trusted IP addresses - UNIX servers are configured to answer only pings from a list of trusted hosts. In UNIX, this is accomplished by configuring the rhosts file, which restricts server access to a list of specific users.
• Server account disabling- If you suspend the server ID after three password attempts, attackers are thwarted. Without user ID suspension, an attacker can run a program that generates millions of passwords until it guesses the user ID and password combination.
• Special tools -Products such as RealSecure by ISS send an alert when an external server is attempting to breach your system's security.
Oracle has a wealth of authentication methods:
• Kerberos security- This popular "ticket"-based authentication system sidesteps several security risks.
• Virtual private databases- VPD technology can restrict access to selected rows of tables.
• Role-based security- Object privileges can be grouped into roles, which can then be assigned to specific users.
• Grant-execute security- Execution privileges on procedures can be tightly coupled to users. When a user executes the procedures, they gain database access, but only within the scope of the procedure.
• Authentication servers-Secure authentication servers provide positive identification for external users.
• Port access security - All Oracle applications are directed to listen at a specific port number on the server. Like any standard HTTP server, the Oracle Web Listener can be configured to restrict access.
Databases need to have level of security in order to protect the database against both malicious and accidental threats. A threat is any type of situation that will adversely affect the database system. Some factors that drive the need for security are as follows:

- Theft and fraud
- Confidentiality
- Integrity
- Privacy
- Database availability

Threats to database security can come from many sources. People are a substantial source of database threats. Different types of people can pose different threats. Users can gain unauthorised access through the use of another person's account. Some users may act as hackers and/or create viruses to adversely affect the performance of the system. Programmers can also pose similar threats. The Database Administrator can also cause problems by not imposing an adequate security policy.

Some threats related to the hardware of the system are as follows:

- Equipment failure
- Deliberate equipment damage (e.g. arson, bombs)
- Accidental / unforeseen equipment damage (e.g. fire, flood)
- Power failure
- Equipment theft

Threats can exist over the communication networks that an organisation uses. Techniques such as wire tapping, cable disruption (cutting / disconnecting), and electronic interference can all be used to disrupt services or reveal private information.
Countermeasures

Some countermeasures that can be employed are outlined below:

- Access Controls (can be Discretionary or Mandatory)
- Authorisation (granting legitimate access rights)
- Authentication (determining whether a user is who they claim to be)
- Backup
- Journaling (maintaining a log file - enables easy recovery of changes)
- Encryption (encoding data using an encryption algorithm)
- RAID (Redundant Array of Independent Disks - protects against data loss due to disk failure)
- Polyinstantiation (data objects that appear to have different values to users with different access rights / clearance)
- Views (virtual relations which can limit the data viewable by certain users)
Threats to Databases
• Loss of confidentiality
– Protection of data from improper disclosure
• Loss of integrity
– Information be protected from improper modification
• Loss of availability
– Making data available to a user with a legitimate right

Introduction to Database Security
Database security begins with physical security for the computer systems that host the DBMS. No DBMS is safe from intrusion, corruption, or destruction by people who have physical access to the computers. After physical security has been established, database administrators must protect the data from unauthorized user and from unauthorized access by authorized users.There are three main objects when designing a secure database application, and anything prevents from a DBMS to achieve these goals would be consider a threat to Database Security.
(1)Integrity

Database integrity refers to the requirement that information be protected from improper modification. Modification of data includes creation, insertion, modification, changing the status of data, and deletion.Integrity is lost if unauthorized changes are made to the data by either intentional or accidental acts.
To prevent the loss of integrity from happening-->Only authorized users should be allowed to modify data.
e.g. Students may be allowed to see their grades, yet not allowed to modify it.
(2)Availability
Authorized user or program should not be denied access. For example, an instructor who wishes to change a grade should be allowed to do so.
(3)Secrecy

Information should not be disclosed to unauthorized users. For example, a student should not be allowed to examine other students’ grades.
To achieve these objectives, a clear and consistent security policy should be developed to describe what security measures must be enforced. In particular, we must determine what part of the data is to be protected and which users get access to which portions of the data. Next, the security mechanisms of the underlying DBMS and operating system, as well as external mechanisms, such as securing access to buildings, must be utilized to enforce the policy. We emphasize that security measures must be taken at several levels.
Why is database security important?

If the loss of system or data integrity is not corrected, continued use of the contaminated system or corrupted data could result in inaccuracy, fraud, or erroneous decisions. In addition, unauthorized, unanticipated, or unintentional disclosure could result in loss of public confidence, embarrassment, or legal action against the organization.
Some database crime news
Credit card database hacked
Cops tap database to harass, intimidate


Countermeasures to database security threats
(1) Inference control -->The corresponding countermeasure to statistical database security.

Statistical database is a database which contains specific information on individuals or events but is intended to permit only statistical queries. (e.g. averages, sums, counts, maximums, minimums and standard deviations. However, it is possible to obtain confidential data on individuals by using only statistical queries. Inference control technique are used to prevent this from happening. (e.g. we can prohibit sequences of queries that refer repeatedly to the same population of tuples.
(2) Flow Control

"Flow control regulates the distribution or flow of information among accessible objects. A flow between object X and object Y occurs when a program reads values from X and writes values into Y. Flow controls check that information contained in some objects does not flow explicitly or implicitly into less protected objects. Thus, S user cannot get indirectly in Y what he or she cannot get directly from X." Elmasri,Navathe(P747)
(3) Encryption

"The idea behind encryption is to apply an encryption algorithm to the data, using a user-specified or DBA-specified encryption key. The output of the algorithm is the encrypted version of the data. There is also a decryption algorithm, which takes the encrypted data and a decryption key as input and then returns the original data." Elmasri,Navathe(P709)
(4) Access Control
A database for an enterprise contains a great deal of information and usually has several groups of users. Most users need to access only a small part of the database to carry out their tasks. Allowing users unrestricted access to all the data can be undesirable, and a DBMS should provide mechanisms to control access to data. The main idea behind access control is to protect unauthorized persons from accessing the system.
Traditional Ways(70s~80s)
How it works?
1:Discretionary Access Control
Discretionary access control is based on the idea of access rights, or privileges, and mechanisms for giving users such privileges. A privilege allows a user to access some data object in a certain manner (e.g. to read or modify). A user who creates data object such as a table or a view automatically gets all applicable privileges on that object and the user can also propagate privileges using "Grant Option". The DBMS subsequently keeps track of how these privileges are granted to other users, and possibly revoked, and ensures that at all times only users with the necessary privileges can access an object.
SQL Syntax
SQL supports discretionary access control through the GRANT and REVOKE commands.
The GRANT command gives users privileges to base tables and views.
The REVOKE command cancels uses' privileges.
For example: GRANT privilege1, privilege2, ... ROVOKE privilege1, privilege2, ...
ON object_name ON object_name
TO user1, user2, ... ; FROM user1, user2, ... ;

GRANT SELECT, ALTER ROVOKE SELECT, ATLER
ON student ON student
TO db2_14 FROM db2_14
Example from Textbook (R.Elmasri, S. B. Navathe, Fundamentals of Database Systems, Ed.4, Addison-Wesley, 2003.Chapter 23)
Suppose that A1 creates the two base relations EMPLOYEE and DEPARTMENT
EMPLOYEE
NAME SSN BDATE ADDRESS SEX SALARY DNO
DEPARTMENT
DNUMBER DNAME MGRSSN
A1 is then the owner of these two relations and hence has all the relation privileges on each of them. A1 wants to grant to account A2 the privilege to insert and delete tuples in both of these relations
GRANT INSERT, DELETE ON EMPLOYEE, DEPARTMENT TO A2;
A2 cannot grant INSERT and DELETE privileges on the EMPLOYEE and DEPARTMENT tables, because A2 was not given the GRANT OPTION in the preceding command.
GRANT SELECT ON EMPLOYEE, DEPARTMENT TO A3 with GRANT OPTION;
The clause WITH GRANT OPTION means that A3 can now propagate the privilege to other accounts by using GRANT. For example, A3 can grant the SELECT privilege on the EMPLOYEE relation to A4 by issuing the following command:
GRANT SELECT ON EMPLOYEE TO A4;
Now suppose that A1 decides to revoke the SELECT privilege on the EMPLOYEE relation from A3; A1 then can issue this command:
REVOKE SELECT ON EMPLOYEE FROM A3;
The DBMS must now automatically revoke the SELECT privilege on EMPLOYEE from A4, too, because A3 granted that privileges to A4 and A3 does not have the privilege any more.

MySQL grant revoke syntax
Limits on propagation of privileges

The techniques to limit the propagation of privileges have been developed, but they have not been implemented in most DBMSs and are not a part of SQL.
Horizontal propagation limits:
An account B given the GRANT OPTION can grant the privilege to at most i other accounts.
Vertical Propagation limits:
It limits the depth to which an account can pass on the privilege in terms of levels.
Pros and Cons of discretionary access control
Advantages:
Being flexible and suitable for various types of systems and application like commercial and industrial environtments.
Disadvantages:
Not providing real assurance on the satisfaction of the protection requirements.
Not imposing any restriction on the usage of information once it is obtained by a user and makes system vulnerable to attacks.
2:Mandatory Access control
Mandatory access control are aimed at addressing such loopholes in discretionary access control. The popular model for mandatory access control called the Bell-LaPadula model, is described in terms of objects, subjects, security classes, and clearances. Each database object is assigned a security class, and each subject is assigned clearance for a security class.
The Bell-LaPadula model imposes two restrictions on all reads and writes of database objects:
1: Simple Security Property: Subject S is allowed to read object O only if class(S)≥ class(O). For example, a user with TS (top
secret) clearance can read a table with C (confidential) clearance, but a user with C(Confidential) clearance is not allowed to
read a table with TS (top secret) classification.
2. *-Property: Subject S is allowed to write object O only if class(S)≤ class(O). For example, a user with S (secret) clearance can
write only objects with S (secret) or TS (top secret) classification.
If discretionary access controls are also specified, these rules represent additional restrictions. Therefore, to read or write a database object, a user must have the necessary privileges and the security classes of the user and the object must satisfy the preceding restrictions.
Advantages: Mandatory policies ensure a high degree of protection.-->suitable for military types of applications, which require a
high degree of protection.
Disadvantages: Applicable to very few environment for being too rigid.
Current State and Future-->Role-Based Access Control
Role-Based Access Control emerged rapidly in the 1990s and it's adopted by most DBMS since then. Its basic concept is that privileges are associated with roles, and users are assigned to appropriate roles. Roles can then be granted to users and other roles. (Roles can be created and destroyed using the CREATE ROLE and DROP ROLE commands.) RBAC appears to be a viable alternative to traditional discretionary and mandatory access controls; it ensures that only authorized users given access to certain data or resources.
Advantages of RBAC
A properly-administered RBAC system enables users to carry out a broad range of authorized operations, and provides great flexibility and breadth of application. System administrators can control access at a level of abstraction that is natural to the way that enterprises typically conduct business. This is achieved by statically and dynamically regulating users' actions through the establishment and definition of roles, role hierarchies, relationships, and constraints. Thus, once an RBAC framework is established for an organization, the principal administrative actions are the granting and revoking of users into and out of roles. Role associations can be established when new operations are instituted, and old operations can be deleted as organizational functions change and evolve. This simplifies the administration and management of privileges; roles can be updated without updating the privileges for every user on an individual basis. With these outstanding features and the easier deployment over the Internet, Role-Based Access Control undoubtedly will continue to be dominant in the future.


1. f) Why does the need of encryption still require when a DBMS already supports discretionary and mandatory access control? [4]

Database data can be encrypted by a key that is generated by DBMS. Access to this key is protected by encryption and decryption keys that are provided by the client. This allows a client to change (for example) the user password without requiring the entire database to be re-encrypted.
It is not possible to turn a secure database into a plain-text database and vice-versa—this can only be done by copying the data from one database to another
It is important to note that encrypting a database has an impact on performance.
The RDbStoreDatabase and RDbNamedDatabase classes provide variants of the Create() and Open() functions that can create and open a secure database. The access key can be changed using the ChangeSecurity() member function of the RDbDatabase base class.
ENCRYPTION ISSUES
While there are many good reasons to encrypt data, there are many bad reasons to encrypt data.
Encryption does not solve all security problems, and may even make some problems worse. The
following section describes some of the misconceptions about encryption of stored data.
Issue 1: Encryption is not access control
Most organizations need to limit access to data to those who have a “need to know.” For example, a
human resources system may limit employees to reviewing only their own employment records, while
managers of employees may see the employment records of those employees working for them. Human
resources specialists may also need to see employee records for multiple employees.
This type of security policy  limiting data access to those with a need to see it  is typically
addressed by access control mechanisms. The Oracle database has provided strong, independently-
evaluated access control mechanisms for many years. Recently, Oracle8i has added the ability to
enforce access control to an extremely fine level of granularity, through its Virtual Private Database
capability.
Because human resources records are considered sensitive information, it’s tempting to think that this
information should all be encrypted “for better security.” However, encryption cannot enforce the type
of granular access control described above, and may actually hinder data access. In the human resources
example, an employee, his manager, and the HR clerk all need to access the employee’s record. If
employee data is encrypted, then each person also has to be able to access the data in unencrypted form.
Therefore, the employee, the manager and the HR clerk would have to share the same encryption key to
decrypt the data. Encryption would therefore not provide any additional security in the sense of better
access control, and the encryption might actually hinder the proper functioning of the application. There
is the additional issue that it is very difficult to securely transmit and share encryption keys among
multiple users of a system.
A basic principle behind encrypting stored data is that it must not interfere with access control. For
example, a user who has SELECT privilege on EMP should not be limited by the encryption mechanism
from seeing all the data he is otherwise allowed to see. Similarly, there is little benefit to encrypting,
(for example) part of a table with one key and part of a table with another key if users need to see all
encrypted data in the table; it merely adds to the overhead of decrypting data before users can read it.
Provided that access controls are implemented well, there is little additional security provided within the
database itself from encryption; any user who has privilege to access data within the database has no
more nor less privilege as a result of encryption. Therefore, encryption should never be used to solve
access control problems.

Issue 2: DBAs can access all data
Some organizations are concerned that database administrators (DBAs), because they typically have all
privileges, are able to see all data in the database. These organizations feel that the DBAs should
merely administer the database, but should not be able to see the data that the database contains. Some
organizations are also concerned about the concentration of privilege in one person, and would prefer to
partition the DBA function, or enforce two-person rules.
It’s tempting to think that encrypting all data (or significant amounts of data) will solve the above
problems, but there are better ways to accomplish these objectives. First of all, Oracle does support
limited partitioning of DBA privilege. Oracle9i provides native support for SYSDBA and SYSOPER
________________________________________
Page 5
Database Encryption in Oracle9I
3
February 2001
users. SYSDBA has all privileges, but SYSOPER has a limited privilege set (e.g. startup and shutdown
of the database). Furthermore, an organization can create smaller roles encompassing a number of
system privileges. A JR_DBA role might not include all system privileges, but only those appropriate to
a more junior database administrator (such as CREATE TABLE, CREATE USER, etc.) Oracle does
not audit the actions taken by SYS (or SYS-privileged users) but does audit startup and shutdown of the
database in the operating system records.
Furthermore, the DBA function by its nature is a trusted position. Even organizations with the most
sensitive data  such as intelligence agencies  do not typically partition the DBA function. Instead,
they vet their DBAs strongly, because it is a position of trust.
Encryption of stored data must not interfere with the administration of the database, or larger security
issues can result than you were attempting to address with encryption. For example, if by encrypting
data, you corrupt the data, you’ve created a security problem: data is not meaningful and may not be
recoverable.
Encryption can be used to mitigate the ability of a DBA  or other privileged user  to see data in the
database, but it is not a substitute for vetting a DBA properly, or for limiting the use of powerful system
privileges. If an untrustworthy user has significant privilege, there are multiple threats he can pose to an
organization, which may be far more significant than viewing unencrypted credit card numbers.
Issue 3: Encrypting everything does not make data secure
It’s a pervasive tendency to think that if storing some data encrypted strengthens security, then
encrypting everything makes all data secure.
We’ve already seen why encryption does not address access control issues well. Consider the
implications of encrypting an entire production database. All data must be decrypted to be read,
updated, or deleted, and, as discussed earlier, the encryption must not interfere with normal access
controls. Encryption is innately a performance-intensive operation; encrypting all data will significantly
affect performance. Availability is a key aspect of security and if, by encrypting data, you make data
unavailable, or the performance adversely affects availability, you have created a new security problem.
Encryption keys must be changed regularly as part of good security practice, which necessitates that the
database be inaccessible while the data is being decrypted and reencrypted with a new key or keys. This
also adversely affects availability.
While encrypting all or most data in a production database is clearly a problem, there may be
advantages to encrypting data stored off-line. For example, an organization may store backups for a
period of six months to a year off-line, in a remote location. Of course, the first line of protection is to
secure the data in a facility to which access is controlled, a physical measure. However, there may be a
benefit to encrypting this data before it is stored, and since it is not being accessed on-line, performance
need not be a consideration. While Oracle9i does not provide this facility, there are vendors who can
provide such encryption services. Organizations considering this should thoroughly test that data (that is
encrypted before off-line storage) can be decrypted and re-imported successfully before embarking on
large-scale encryption of backup data.
________________________________________
Page 6
Database Encryption in Oracle9I
4
February 2001
SOLUTIONS FOR STORED DATA ENCRYPTION IN ORACLE
Oracle9i Data Encryption Capabilities
While there are many security threats that encryption cannot address well, it is clear that one can obtain
an additional measure of security by selectively encrypting sensitive data before storage in the database.
Examples of such data could include:
• credit card numbers
• national identity numbers
• passwords for applications whose users are not database users
To address the above needs, Oracle8i (release 8.1.6) introduced a PL/SQL package to encrypt and
decrypt stored data. The package, DBMS_OBFUSCATION_TOOLKIT, is provided in both Standard
Edition and Enterprise Edition Oracle9i. The package is documented in the Oracle9i Supplied PL/SQL
Packages Reference Guide.
The package currently supports bulk data encryption using the Data Encryption Standard (DES)
algorithm, and includes procedures to encrypt (DESEncrypt) and decrypt (DESDecrypt) using DES.
The package does not currently support the Advanced Encryption Standard, the successor algorithm to
DES, though this is planned for a future release of Oracle9i.
Key management is programmatic, that is, the application (or caller of the function) has to supply the
encryption key, which means that the application developer has to find a way of storing and retrieving
keys securely. The relative strengths and weaknesses of various key management techniques are
discussed later in this paper. The DBMS_OBFUSCATION_TOOLKIT package, which can handle both
string and raw data, requires the submission of a 64-bit key. The DES algorithm itself has an effective
key length of 56-bits. The DBMS_OBFUSCATION_TOOLKIT package is granted to PUBLIC by
default.
Oracle has added support for triple DES (3DES) encryption in Oracle8i release 8.1.7. The
DBMS_OBFUSCATION_TOOLKIT package includes additional functions to encrypt and decrypt
using 2-key and 3-key 3DES, in outer cipher-block-chaining mode. They will require key lengths of 128
and 192 bits, respectively.
Oracle8i release 8.17 also added support for cryptographic checksumming using the MD5 algorithm
(using the MD5 procedure of the DBMS_OBFUSCATION_TOOLKIT package). Cryptographic
checksums can ensure data integrity; that is, that data has not been tampered with. For example, an
organization concerned that users not change salary values randomly could store a checksum of salary
values in a separate table. Only users changing the salary through an application (e.g. through executing
a procedure) would also have the privileges to insert a checksum for the new salary into a salary audit
table.
Partner Applications
Organizations seeking a more robust implementation of encrypting stored data in the Oracle database
can consider a product offering from Oracle partners such as Protegrity. Protegrity has provided
database encryption capabilities since Oracle8. In the Protegrity solution, key management is automatic.
There are multiple customers using it in production who are happy with the functionality.
________________________________________
Page 7
Database Encryption in Oracle9I
5
February 2001
Performance may be an issue in the Protegrity product, depending on how much data the customer wants
to encrypt. Since Protegrity has built their solution using the extensibility features of the server, their
“data cartridge” does not run in the server address space, and thus does not perform as well as a native
implementation of encryption in Oracle would. The tradeoff of more automatic key management may
well be worth it for Oracle customers, however.
CHALLENGES OF ENCRYPTION
This paper has already discussed why encryption should not be used to address threats better addressed
by access control mechanisms, and some of the reasons why encryption is not a security cure-all. Even
in cases where encryption can provide additional security, it is not without technical challenges, as
described in the following sections.
Encrypting Indexed Data
Special difficulties arise in handling encrypted data which is indexed. For example, suppose a company
uses national identity number (e.g. U.S. Social Security Number (SSN)) as the employee number for its
employees. The company considers employee numbers to be very sensitive data, and the company
therefore wants to encrypt data in the EMPLOYEE_NUMBER column of the EMPLOYEES table.
Since EMPLOYEE_NUMBER contains unique values, the database designers want to have an index
on it for better performance.
If the DBMS_OBFUSCATION_TOOLKIT (or another mechanism) is used to encrypt data in a
column, then an index on that column will also contain encrypted values. While the index can still be
used for equality checking (i.e. ‘SELECT * FROM emp WHERE employee_number = ‘123245’), the
index is essentially unusable for other purposes. Oracle therefore recommends that developers not
encrypt indexed data (and in fact, we do not support encrypting indexed data).
In the above example, a company that wants to encrypt social security number (or national identity
number) could create an alternate unique identifier for its employees, create an index on this employee
number, but retain the employee number in clear text. The national identity number could be a separate
column, and the values encrypted therein by an application, which would also handle decryption
appropriately. The national identity number could be obtained when necessary, but would not be used as
a unique number to identify employees.
Given the privacy issues associated with overuse of national identity numbers (for example, identity
theft), the fact that some allegedly unique national identity numbers have duplicates (US Social Security
Numbers), and the ease with which a sequence can generate a unique number, there are many good
reasons to avoid using national identity numbers as unique IDs.
Binary Large Objects (BLOBS)
Certain datatypes require more work to encrypt. For example, Oracle supports storage of binary large
objects (BLOBs), which lets users store very large objects (e.g. gigabytes) in the database. A BLOB
can be either stored internally as a column, or stored in an external file.
To use the DBMS_OBFUSCATION_TOOLKIT, the user would have to split the data into 32767
character chunks (the maximum that PL/SQL allows) and then would have to encrypt the chunk and
append it to the BLOB. To decrypt, the same procedure would have to be followed in reverse.
________________________________________
Page 8
Database Encryption in Oracle9I
6
February 2001
Key Management
Key management, including both generation of and secure storage of cryptographic keys, is arguably
one of the most important aspects of encryption. If keys are poorly-chosen or stored improperly, then it
makes it far easier for an attacker to break the encryption. Rather than using a “brute force” attack (that
is, cycling through all the possible keys in hopes of finding the correct decryption key), cryptanalysts
often seek weaknesses in the choice of keys, or the way in which keys are stored.
Key generation is an important aspect of encryption. Typically, keys are generated automatically
through a random-number generator, from a cryptographic seed. Provided that the random number
generation is sufficiently strong, this can be a secure form of key generation. However, if random
numbers are not well-formed, but have elements of predictability, the security of the encryption may be
easily compromised. Netscape had a well-publicized vulnerability in their SSL implementation several
years ago when it was discovered that two of the three elements of their random number generation
were not random (e.g. machine serial number and time of day). The encryption key for SSL sessions had
an effective key length of 9 bits, rather than the advertised 40 bits, because of the weakness of the key
generation. An SSL session key could be easily broken, not because the encryption algorithm was
weak, but because the key was easily derived.
To address the issue of secure cryptographic key generation, Oracle9i adds support for a secure random
number generation, the GetKey procedure of the DBMS_OBFUSCATION_TOOLKIT. The GetKey
procedure calls the secure random number generator (RNG) that has previously been certified against
the Federal Information Processing Standard (FIPS)-140 as part of the Oracle Advanced Security FIPS-
140 evaluation.
Developers should not use the DBMS_RANDOM package. The DBMS_RANDOM package generates
pseudo-random numbers; as RFC-1750 states, “The use of pseudo-random processes to generate secret
quantities can result in pseudo-security.”
Key Transmission
If the key is to be passed by the application to the database, then it must be encrypted. Otherwise, a
snooper could grab the key as it is being transmitted. Use of network encryption, such as that provided
by Oracle Advanced Security, will protect all data in transit from modification or interception, including
cryptographic keys.
Key Storage
Key storage is one of the most important, yet difficult, aspects of encryption. To recover data encrypted
with a symmetric key, the key must be accessible to the application or user seeking to decrypt data. The
key needs to be easy enough to retrieve that users can access encrypted data, without significant
performance degradation. The key needs to be secure enough that it’s not easily recoverable by someone
trying to maliciously access encrypted data he is not supposed to see.
The three basic options available to a developer are:
•
store the key in the database
•
store the key in the operating system
•
have the user manage the key
________________________________________
Page 9
Database Encryption in Oracle9I
7
February 2001
Storing the keys in the database cannot always provide “bullet-proof” security if you are trying to
protect data against the DBA accessing encrypted data (since an all-privileged DBA could access tables
containing encryption keys), but it can often provide quite good security against the casual snooper, or
against someone compromising the database file on the operating system.
As a trivial example, suppose you create a table (EMP) that contains employee data. You want to
encrypt each employee’s Social Security Number (one of the columns). You could encrypt each
employee’s SSN using a key which is stored in a separate column. However, anyone with SELECT
access on the entire table could retrieve the encryption key and decrypt the matching SSN.
While this encryption scheme seems easily defeatable, with a little more effort you can create a solution
that is much harder to break. For example, you could encrypt the SSN using a technique that performs
some additional data transformation on the employee_number before using it to encrypt the SSN,
something as simple as XORing the employee_number with the employee’s birthdate, for example.
As additional protection, a PL/SQL package body performing encryption can be “wrapped,” (using the
wrap utility) which obfuscates the code so that the package body cannot be read. For example, putting
the key into a PL/SQL package body and then wrapping it makes the package body — including the
embedded key — unreadable to the DBA and others. A developer could wrap a package body called
KEYMANAGE as follows:
wrap iname=/mydir/keymanage.sql
A developer can then have a function in the package call the DBMS_OBFUSCATION_TOOLKIT with
the key contained in the wrapped package.
While wrapping is not unbreakable, it makes it harder for a snooper to get the key. To make it even
harder, the key could be split up in the package and then have the procedure re-assemble it prior to use.
Even in cases where a different key is supplied for each encrypted data value, so that the value of the
key is not embedded within a package, wrapping the package that performs key management (i.e. data
transformation or padding) is recommended. Additional information about the Wrap Utility is available
in the PL/SQL User's Guide and Reference.
An alternative would be to have a separate table in which to store the encryption key, and envelope the
call to the keys table with a procedure. The key table can be joined to the data table using a primary
key-foreign key relationship; for example, EMPLOYEE_NUMBER is the primary key in the
EMPLOYEES table, that stores employee information and the encrypted SSN.
EMPLOYEE_NUMBER is a foreign key to the SSN_KEYS table, that stores the encryption keys for
each employee’s SSN. The key stored in the SSN_KEYS table can also be transformed before use (i.e.
through XORing), so the key itself is not stored unencrypted. The procedure itself should be wrapped, to
hide the way in which keys are transformed before use.
The strengths of this approach are:
• users who have direct table access cannot see the sensitive data unencrypted, nor can they retrieve
the keys to decrypt the data
• access to decrypted data can be controlled through a procedure that selects the (encrypted) data,
retrieves the decryption key from the key table, and transforms it before it can be used to decrypt
the data
________________________________________
Page 10
Database Encryption in Oracle9I
8
February 2001
• the data transformation algorithm is hidden from casual snooping by wrapping the procedure, which
obfuscates the procedure code
• SELECT access to both the data table and the keys table does not guarantee that the user with this
access can decrypt the data, because the key is transformed before use
The weakness in this approach is that a user who has SELECT access to both the key table and the data
table, who can derive the key transformation algorithm, can break the encryption scheme.
The above approach is not bullet-proof, but it is good enough to protect against easy retrieval of
sensitive information stored in clear (e.g. credit card numbers).
Storing keys in the operating system (e.g. in a flat file) is another option. Oracle9i allows you to make
callouts from PL/SQL, which you could use to retrieve encryption keys. However, if you store keys in
the operating system (O/S) and make callouts to it, then your data is only as secure as the protection on
the O/S. If your primary security concern driving you to encrypt data stored in the database is that the
database can be broken into from the operating system, then storing the keys in the operating system
arguably makes it easier for a hacker to retrieve encrypted data than storing the keys in the database
itself.
Having the user supply the key, assumes the user will be responsible with the key. Consider that 40%
of help desk calls are from users who have forgotten passwords, and you can see the risks of having
users manage encryption keys. In all likelihood, users will either forget an encryption key, or write the
key down, which then creates a security weakness. If a user forgets an encryption key or leaves the
company, then your data is unrecoverable.
If you do elect to have user-supplied or user-managed keys, then you need to make sure you are using
network encryption so the key is not passed from client to server in the clear. You also must develop key
archive mechanisms, which is also a difficult security problem. Arguably, key archives or ‘backdoors’
create the security weaknesses that encryption is attempting to address in the first place.
Changing Encryption Keys
Prudent security practice dictates that you periodically change encryption keys. For stored data, this
requires periodically unencrypting the data, and reencrypting it with another well-chosen key. This
would likely have to be done while the data is not being accessed, which creates another challenge,
especially so for a web-based application encrypting credit card numbers, since you do not want to
bring the entire application down while you switch encryption keys.
SUMMARY
The growth of e-business necessitates that some especially sensitive information be stored in encrypted
form. While encryption cannot address all security threats, the selective encryption of stored data can
add to the security of a well-implemented application that uses a well-configured Oracle9i database.
Oracle9i provides native encryption capabilities that enable application developers to provide additional
measures of data security through selective encryption of stored data.
________________________________________
Page 11
Database Encryption in Oracle9I
9
February 2001
Appendix A
ENCRYPTION EXAMPLE
Following is a sample PL/SQL program to encrypt data. Segments of the code are numbered and contain
narrative text explaining portions of the code.
DECLARE
input_string VARCHAR2(16) := ’tigertigertigert’;
key_string VARCHAR2(8) := ’scottsco’;
encrypted_string VARCHAR2(2048);
decrypted_string VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
’*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***’;
1. Test string data encryption and decryption-- The interface
for encrypting raw data is similar.
BEGIN
dbms_output.put_line(’> ========= BEGIN TEST =========’);
dbms_output.put_line(’> Input String :
’ ||
input_string);
BEGIN
dbms_obfuscation_toolkit. input_string => input_string,
key_string => key_string, encrypted_string =>
encrypted_string );
dbms_output.put_line(’> encrypted string : ’
||
encrypted_string);
dbms_obfuscation_toolkit.DESDecrypt(input_string =>
encrypted_string,
key => raw_key, decrypted_string =>
decrypted_string);
dbms_output.put_line(’> Decrypted output : ’
||
decrypted_string);
dbms_output.put_line(’> ’);
if input_string =
decrypted_string THEN
dbms_output.put_line(’> DES Encryption and Decryption
successful’);
END if;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line(’> ’ ||
INPUT_BUFFER_LENGTH_ERR_MSG);
END;