Securing the Database - Throw Out Anything Stale
(Page 3 of 9 )
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.
CAUTION 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)
4 RETURN BOOLEAN
5 AS
6 l_orig_password_verifier dba_users.PASSWORD%TYPE;
7 l_new_password_verifier dba_users.PASSWORD%TYPE;
8 BEGIN
9 SELECT PASSWORD
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.
17 SELECT PASSWORD
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;
9
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.
Next: Checking for Weak or Default Passwords >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article 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). Check it out at your favorite bookstore. Buy this book now.
|
|
/ 9 
