Database Security: Beyond the Password

by George Jucan


Keep your confidential data secure from (internal or external) intruders--even if passwords have been compromised--with Oracle Database built-in features.

Published 200

Recently we've seen a dramatic increase in public and business awareness of safeguarding private and personal information. With specific legislation now in place in many countries, it is now not only a public relations matter but also a legal obligation to protect identifiable data about individuals.

Regardless, the protection of confidential and sensitive data in IT systems--whether in transaction-processing (OLTP) or data warehousing environments--is of paramount importance for business operations. For example, can you imagine a sales system without customer names, addresses, and credit card numbers stored in the database? Personal data is a strategic asset in today's systems, so companies should take a proactive, robust, and holistic approach to privacy protection through security enforcement. From this perspective, your organization's strategic and tactical decisions must target the end result, rather than focus on specific projects or immediate business needs to avoid costly reworking or even the eventual loss of customer confidence.

Many sophisticated measures are usually in place to prevent unauthorized access at the network and operating-system levels and are incorporated in off-the-shelf or custom-built application systems. But too often the database, where the information actually resides, is protected only through the standard username/password mechanism. Oracle Database 10g has one of the most advanced implementations of this mechanism, but if the password is compromised, that protection is gone. Oracle Database can provide even more protection through the use of Oracle Virtual Private Database, Oracle Label Security, and so on, but these mechanisms are still insufficiently used in production implementations.


In this Technical Article, I will describe (and illustrate via demo) the implementation of a security mechanism that assumes that one or more database passwords have been compromised. This approach offers a simple way to combine Oracle Database 10g Release 1 security features (some of which are available in Oracle9i) to easily achieve a significant level of protection even if the intruder establishes a database connection. The main purpose is to protect sensitive data from any unauthorized user, whether that person is an outside hacker or even the company's DBA. The examples provided are specific to a transactional environment, but the same principles will apply in business intelligence and data warehousing environments as well.

Database Security Objectives


The Oracle Database is the key component of real security enforcement. Generally speaking, the servers running the Oracle Database engine are well protected behind firewalls, but this fact does not preclude the possibility of unauthorized access attempts, including internal ones by employees. The Oracle Database engine provides self-contained security mechanisms, in addition to the traditional username/password method, to protect its data content even if all other blocks have been bypassed. The security measures defined in the following sections assume penetration up to the database level and are intended as the last line of defense in the database itself, not as a total replacement for external protection.

Starting from the premise that all other security measures have been bypassed and unauthorized access to the database can be initiated, the solution defined in the following sections involves building database defense features to ensure that:

  • Oracle Application Server—being a secure client for the database—can read, insert, and update all data as needed. Oracle Application Server will use its internal and application-specific security mechanisms to protect private data from unauthorized users in the presentation layer.
  • Secure database access is available for the error-resolution process, using SQL*Plus, including the ability to see private information.
  • No other database access can retrieve private client information.


Demo Setup


This exercise includes a classic sales-type data model in which the data to be protected is stored in a CUSTOMER database, in particular in the CARD_NO column. The example makes the entire table look empty for an unauthorized request, so a SELECT * from CUSTOMER; will retrieve no records. A table that seems to have no records at all will likely be less attractive to an intruder (maybe the table is not even used?) than a table that has records but that hides or masks "interesting" columns.

However, with a minor change in the DBMS_RLS.ADD_POLICY call, the solution will hide (display as NULL) or mask (display as ****) the values of the protected column CARD_NO but display all the records with the values for the other columns. You can enable capability by specifying the sec_relevant_cols and sec_relevant_cols_opt parameters in the DBMS_RLS.ADD_POLICY call. The initial_setup.sql iscript in this article's support files creates a very basic CUSTOMER table to use as an example in this process.


It is best practice to avoid using the schema owner to access data; rather, a different account, such as AppSvr, should be shared by all client connections and handled by Oracle Application Server. The AppSvr database user does not own any object and has only a CREATE SESSION system privilege but receives SELECT, INSERT, UPDATE, and DELETE privileges on all tables containing application data from the schema owner, such as the owner of the SHIP2004 schema.


The enable_connection.sql script in the support files creates a user that would typically be used by an application running on Oracle Application Server as described above.


Security Implementation


To achieve the stated security objectives a database policy is used to hide the records from CUSTOMER table unless the connection is "authorized" (initiated by Oracle Application Server, running at a predefined IP address, and so on). The policy is implemented under a Security Manager database user, such as Sec_Manager, so it is not visible even from the SHIP2004 or AppSvr schema.


Determining what environment variables to use and the specific values to be checked by the security predicate are questions of implementation. The very large number of potential combinations and particular site details will create a significant block to an intrusion attempt. .

It is a good idea to create a separate schema, such as Sec_Manager, without any privileges—not even CONNECT—as a placeholder for all definitions used in a security implementation. All objects will be created by a DBA account in the Sec_Manager schema. Having no privileges, this username cannot be used even to log in to the database, so the owned security definitions will be well protected. (Nobody can see even the definitions of security-related objects.)

However, one of our initial objectives here is to enable SQL*Plus-level access for a few maintenance and support staff members. This emergency access requires a "secret passage" that is easily remembered by the authorized people but too long to be written on a sticky note (visible to anyone), which is unfortunately how many passwords are kept. This example uses the CLIENT_IDENTIFIER environment variable, but it can be any environment variable or combination of environment variables of your choosing.

The create_setup.sql script (in the support files) demonstrates how to create the security implementation schema, predicate function, and security policy according to the description above. It also produces several data listings, using different database login privileges to demonstrate what different connections will see (or not) in the CUSTOMER table. It also demonstrates the use of the dbms_session.set_identifier function to open the secret door for accessing the data through a SQL*Plus connection.

Direct SQL*Plus Access


Because Oracle Application Server has strong built-in security features for authenticating and authorizing requests, direct SQL*Plus access is the usual entry point for intruders. Implementing the security policy described above has already achieved the following:
  • Even if the AppSvr password is compromised and someone uses the AppSvr login for unauthorized access through SQL*Plus, the CUSTOMER data will not be displayed, because the IP address and/or the external session name will not be the one expected by the security predicate—the system will not even show that there are any records in the protected table.
  • The schema-owner login will not be used by online applications. It will be used only for maintenance purposes and therefore given to a strictly controlled number of people. Moreover, they will have to set one or more environment settings ( CLIENT_IDENTIFIER in this case) appropriately to see CUSTOMER data. Even if the password is compromised (for example, someone spots a sticky note on a desk), the protected table will look empty to an unauthorized person accessing it, as long as the hidden backdoor settings from the security predicate are not disclosed. Not even knowing that there is any data in the table, the intruder is quite unlikely to look further into it.
  • No other database user, even with the DBA privilege, can see the records in the protected table. However, even if another database user somehow gets access to SHIP2004 tables, the considerations described above are valid. (The person has to know about the security feature to see private data.)
The data listings throughout the sample scripts demonstrate the above statements.

Encrypt Data and Packages

Encrypting the CARD_NO data ensures another layer of data protection for sensitive data. You can do the encryption with a static key defined in the external process or stored in a column in the database. It is a good practice to split the encryption components (key and function) onto two separate servers, to increase the environment's complexity and the effort required from potential intruders to retrieve all needed information to decrypt protected data.

If the key is defined in the application, a hacker will have to break not only into the database server but also into the application server to get the key to decrypt the data. Even if somebody cracks the access protection described in the previous sections, that person would still have to crack the package code (encoded as described in the "Secure the Security Environment" section below) to know the encryption function applied. The intruder would also have to crack the compiled application code located in the application servers to identify the key used. If the key is not stored in any clear text file, such as a parameter file or source code, but only in a compiled version, it increases the difficulty and skill set required to retrieve the real private data through an unauthorized access.

However, to be application independent the example demonstrated in the support files uses another table column as an encryption key. The value stored in the key column has to be static, because if the value changes, the CARD_NO data cannot be decrypted anymore. In this case, the column CREATED_BY was chosen for the key, because it should never be updated after record creation.

The most convenient solution for minimizing the additional work required by encryption is to create a package that basically hides the call to Oracle's encryption utilities. The developers will simply make a function call instead of directly using the protected column, which is a minimal inconvenience for the gained security benefits. This example uses the ENCRYPT and DECRYPT functions from the DBMS_CRYPTO package, which offers many cryptographic choices (see the Oracle documentation for additional details). The large combination of options, with the choice of encryption key, adds to the complexity of breaking the presented solution, especially once the source code of the custom package is wrapped as described below. (The create_packages.sql script presents a sample setup for the encryption/decryption functions described here.)

Oracle Database 10g Release 2 provides transparent data encryption out-of-the-box, enabling the ability to transparently encrypt any regular database column (dates, strings, numbers) and automatically decrypt it when a user has passed necessary access control checks. The Oracle engine itself, without control from the database user, handles the encryption keys, so the application or SQL access to the table does not have to manage the keys anymore. By extension, a DBA can manage the table but not see the actual data values, which will resolve part of the concerns the above setup is addressing.

Manipulate Encrypted Data


Oracle Application Server applications use routines from the Sec_Manager.Secure_Package package to store personal data in an encrypted format, such as Secure_Package.Secure_Data to store CARD_NO data. According to the definition of the custom encryption package described in create_packages.sql (in the support files), an access to the CARD_NO column is replaced with a function call whose parameters are the value to be stored in the column and the key used for data encryption

For example, to use 'a1b2c3d4' as your encryption key, a typical INSERT statement that initially looked like

insert into CUSTOMER (NAME, CARD_NO) 
values ('Jane Doe', '1234123412341234'); 
must be transformed into:
insert into CUSTOMER (NAME, CARD_NO) 
values ('Jane Doe', Sec_Manager.Secure_Package.Secure_Data('1234123412341234','a1b2c3d4')); 
Similarly, Oracle Application Server applications also use routines from Sec_Manager.Secure_Package to read data in decrypted format, such as Secure_Package.Clear_Data for CARD_NO data. The same encryption key used to insert the values is now used to retrieve the protected information in a clear form. In this case a typical SELECT statement that initially looked like
select NAME, CARD_NO 
will have to be modified to:
select NAME, Sec_Manager.Secure_Package.Clear_Data(CARD_NO,'a1b2c3d4') 
Secure the Environment

When all the development has been done (hopefully by a trustworthy person), the promoted code can be encrypted as well, so even a DBA who promotes the scripts cannot see exactly how the security is implemented. You implement the encryption with an Oracle-supplied utility, using an operating system command such as

wrap iname=Secure_Package.sql oname=Secure_Package.sec
When wrapped, Secure_Package.sec can be executed at the SQL*Plus prompt just like any clear text script and the Oracle engine will interpret it just as well. The same concept can be applied to any other security-related PL*SQL script. This approach not only prevents the persons involved in code promotion (DBAs, developers, support and admin staff) from seeing the package content but it is deployed in encrypted form in the database as well—so even a later attempt to decipher the content of those packages is quite difficult.

Even if an intruder with the DBA privilege were to grant the CONNECT privilege to the security objects holder Sec_Manager in order to see the contents of the protection and encryption packages, no clear text will be stored in the database for these objects. Because Oracle does not provide any "unwrap" utility, the intruder would have to crack Oracle's encryption algorithms to be able to see the package contents.


Audit Access to Sensitive Data Regardless of Access


Even with all these security measures in place, it is still important to know if any unauthorized access was performed on sensitive data. The simplest way is to use the built-in database audit functionality to monitor access ( SELECT, INSERT, UPDATE, DELETE) to protected data at the table level, regardless of the database connection requesting the transaction, through
                                                                  audit insert, update, select on SHIP2004.CUSTOMER;                               
However, with Oracle Fine Grained Auditing (FGA), you can further refine access monitoring to minimize processing overhead and provide meaningful information. The example presented in enable_fga.sql utilizes the DBMS_FGA package to enable a basic auditing policy. A built-in audit mechanism in the database prevents users from bypassing the audit, thus ensuring its accuracy. The audit records can be viewed in DBA_FGA_AUDIT_TRAIL view as well as in the DBA_COMMON_AUDIT_TRAIL view and could even include SQLBIND and SQLTEXT information if the policy specifies audit_trail = DBMS_FGA.DB_EXTENDED.


The example presented here can be easily enhanced with Oracle-provided functionality to include email or pager notifications and activation conditions to generate audit records only for specific events. For additional information on database audit policies and implementation, see the Oracle documentation.



As intrusion attempts become more and more sophisticated, system architects and administrators have to define better ways to protect their database contents. Combining a series of usual methods and techniques in an unusual way can make intruder attempts to navigate the maze too complicated for the trouble.


The method I've described here has proved effective even when the passwords for the AppSvr and SHIP2004 schemas were "accidentally" disclosed, so it can be used as a component in your security environment to protect strategic data assets and enhance customer confidence in disclosing private data. In the long run, this contributes to a better client relationship and enhanced business opportunities.

Next Steps:
See Oracle Platform Security technical information
Read Technical Articles about Security
Get Security Alerts


George Jucan ) is the founder and acting CEO of Open Data Systems. Founded in 1998 as a Toronto-based consulting services company, Open Data Systems specializes in Oracle technologies, with a particular emphasis on database design, setup, and administration in OLAP and OLTP environments. Jucan is an OCP with more than 12 years of experience with Oracle products, serving over the years as DBA, architect, and project manager.