Home arrow Oracle arrow Page 3 - Securing the Database

Throw Out Anything Stale - Oracle

If you work with Oracle databases, you will want to know how to secure them. This article focuses on a number of steps you can take, representing the best practices used in organizations today, to secure an Oracle database. It is excerpted from chapter 2 of the book Effective Oracle Database 10g Security by Design, written by David C. Knox (McGraw-Hill/Osborne, 2004; ISBN: 0072231300).

  1. Securing the Database
  2. Securing Access to Application Schemas
  3. Throw Out Anything Stale
  4. Checking for Weak or Default Passwords
  5. Impossible Passwords
  6. Password Profiles
  7. Default Roles
  8. Oracle Supplied Objects
  9. Securing the Network
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 12
September 22, 2005

print this article



Over time, it often happens that users and applications no longer require access to the database. Perhaps the user got a new job and will no longer be logging in to your database--at least, not through legitimate means!

A security best practice for operating systems, networks, and databases is to remove unused or unneeded accounts. This is a simple concept that can be simply accomplished, yet it represents one of the most common and serious security risks and bad practices. Stale accounts should be locked at the very least, and preferably dropped. Ive seen many instances when former employees still had active accounts on production systems years after their termination. Whether this is due to laziness or simply a lack of a good process, it's a huge security risk!

To remove unused and unneeded accounts successfully, it's important to know who is and who should be accessing the database. The schemas may not belong to just end users. A previously installed application, used and then abandoned on your database, also creates a security risk.


Be careful dropping schemas even if no one has logged in to them in months. These schemas may hold data and procedures needed by an application.

This again emphasizes the importance of knowing which schemas are doing what. If you're not sure whether an account still has relevant information, at the very least, export the data and procedures first!

How do you know if the account is being accessed? Auditing. You can audit connections to user schemas and access to objects within the schemas. You can even run a batch job to query the audit trail to look for unauthorized accesses and notify an administrator immediately upon detection. For more ideas and auditing examples, see Chapter 8.

Oracle Passwords

The Oracle Database stores user passwords in the data dictionary. For database authenticated users, the values stored aren't actually the plaintext passwords themselves but the password verifiers. Password verifiers are hashed (see Chapter 13 for more details on hashing) representations of plaintext passwords. The value is stored in a hexadecimal representation (numbers 0--9 and letters A--F).

The authentication process is performed by computing a password verifier for the plaintext password a user has submitted for authentication and comparing the resulting value with the one stored in the data dictionary. If they match, the user has supplied the same password and is authenticated.

Application Password Authentication Using Oracle's Native Password Store

Application user authentication is an important step to ensuring security for database applications. There are times when the application requires authentication but a default mechanism isn't provided. There are three possible methods for implementing your own user authentication for your application. First, you could build, maintain, and/or synchronize your own password repository and authentication scheme. You'll see an example of how to implement a password authentication solution in Chapter 13, which discusses the use of the new DBMS_CRYPTO package.

Second, assuming the application users are also database users, the application could authenticate users by trying to connect to the database as the respective user(s) . This is a bad alternative because it can be costly--from a time and performance perspective--to create and destroy database connections just to authenticate.

The final method, which also assumes the application users are database users, utilizes the database's internal password store. Unfortunately, Oracle supplies no password verifier program for developers to use, so you must build your own interface.

You can do this with a simple trick, commonly referred to as identified by values, that uses a syntactical variation of the ALTER USER DDL. The algorithm is similar to the one the database uses to authenticate its users. You simply need to compute a password verifier for the plaintext password a user submits and compare that value to the one stored in the database.

The following function is based on a program originally written by Tom Kyte. To start the function, obtain the user's current password verifier from the data dictionary:

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION is_auth_password
 2    p_username  IN VARCHAR2,
 3    p_password  IN VARCHAR2)
 5  AS
 6    l_orig_password_verifier dba_users.PASSWORD%TYPE;
 7    l_new_password_verifier dba_users.PASSWORD%TYPE;
10      INTO l_orig_password_verifier
11      FROM dba_users
12     WHERE username = UPPER (p_username);

Next, alter the user's password and set it to the password you want to verify. When you do this, the database recomputes the password verifier based on this password. The result is stored in the database dictionary:

13   EXECUTE IMMEDIATE    'alter user '
14                     || p_username
15                     || ' identified by ' 16                     || p_password;

Select this second verifier out and compare it to the original that you've stored. If they match, the passwords must have been the same.

18    INTO l_new_password_verifier
19    FROM dba_users
20   WHERE username = UPPER (p_username);

This works well when the passwords are identical. However, the problem is that if the passwords don't match, you've changed the users password (perhaps to one that they don't know). To resolve this, set the password back to its original value. Since you don't have the original plaintext password, you can't use the traditional ALTER USER syntax. Instead, reset the password by issuing an ALTER USER <username> IDENTIFIED BY VALUES '<original password verifier'>. Passing the original password verifier in single quotes after the values clause resets the password back to the user's original password. The database sees the word "values" and doesn't recompute the password verifier, but it stores the value specified in the quotes directly in the password column for the user.

21  EXECUTE IMMEDIATE    'alter user '
22                    || p_username
23                    || ' identified by values '''
24                    || l_orig_password_verifier
25                    || '''';
26  RETURN l_orig_password_verifier =
27                 l_new_password_verifier; 28 END;
29 /
Function created.

To test the program, check the Boolean return value of the function:

sec_mgr@KNOX10g> BEGIN
  2    IF (is_auth_password ('scott', 'tiger') = TRUE)
  3    THEN
  4      DBMS_OUTPUT.put_line ('scott/tiger is valid');
  5    ELSE
  6      DBMS_OUTPUT.put_line
  7                        ('scott/tiger is NOT valid');
  8    END IF;
 10    IF (is_auth_password ('scott', 'lion') = TRUE)
 11    THEN
 12      DBMS_OUTPUT.put_line ('scott/lion is valid');
 13    ELSE
 14      DBMS_OUTPUT.put_line
 15                         ('scott/lion is NOT valid');
 16    END IF;
 17  END;
 18 /
scott/tiger is valid
scott/lion is NOT valid

This function allows you to authenticate users against the database password store without having to know or manage the user's actual password.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: