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 user’s 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.
Checking for Weak or Default Passwords
Passwords are often the weak link in the security chain. A poorly chosen password, or well-known default password that has not been changed, is one of the greatest security risks to a database. To help manage this risk use the following program, which compares a list of known usernames and password verifiers to the users and verifiers actually being used in the database. The list is created by a helper program.
To start, a table is created that stores usernames, their plaintext passwords, and the respective computed password verifiers:
sec_mgr@KNOX10g> CREATE TABLE passwords 2 ( 3 username VARCHAR2(30), 4 passwd VARCHAR2(30), 5 verifier VARCHAR2(30) 6 ) 7 / Table created. sec_mgr@KNOX10g> ALTER TABLE PASSWORDS ADD ( 2 CONSTRAINT PWD_PK PRIMARY KEY (PASSWD, USERNAME)); Table altered. sec_mgr@KNOX10g> CREATE INDEX VERIFIER ON PASSWORDS 2 (USERNAME, VERIFIER); Index created.
To populate the table, a procedure is created that utilizes the identified by values clause introduced in the IS_AUTH_PASSWORD function shown earlier. The procedure takes a password parameter. The program iterates through the DBA_USERS view and sets the passwords for all the users to the value passed as the parameter. The program then inserts the resulting password verifier into the PASSWORDS table. When the parameter is null, the password is set to the username. This has the benefit of allowing you to check for passwords that are the same as the username.
sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE populate_passwords_tab ( 2 p_password IN VARCHAR2 DEFAULT NULL) 3 AS 4 l_new_password_verifier dba_users.PASSWORD%TYPE; 5 l_password dba_users.PASSWORD%TYPE 6 := UPPER (p_password); 7 BEGIN 8 FOR rec IN (SELECT username, password 9 FROM dba_users) 10 LOOP 11 IF (p_password IS NULL) 12 THEN 13 -- password is either passed as parameter 14 -- or set to user's name 15 l_password := rec.username; 16 END IF; 17 18 -- create new password verifier 19 EXECUTE IMMEDIATE 'alter user ' 20 || rec.username 21 || ' identified by ' 22 || l_password; 23 -- retrieve new verifier 24 SELECT password 25 INTO l_new_password_verifier 26 FROM dba_users 27 WHERE username = rec.username; 28 -- insert value into passwords table 29 INSERT INTO passwords 30 VALUES (rec.username, 31 l_password, 32 l_new_password_verifier); 33 -- set password back to its original value 34 EXECUTE IMMEDIATE 'alter user ' 35 || rec.username 36 || ' identified by values ''' 37 || rec.password 38 || ''''; 39 END LOOP; 40 END; 41 / Procedure created.
Next, execute the above procedure to seed the table first with usernames equal to passwords. Since the procedure modifies user passowords, you should consider running this on a non-production database so this process doesn’t interfere with your production applications. Then try some common passwords associated with the default Oracle accounts (there may be additional common and default usernames within your organization). Any standard applications you’ve installed on Oracle may also carry default schemas and well-known default passwords, so you should consider all default schemas and their passwords, too. Oracle Metalink (http://metalink.oracle.com) lists some of the default usernames and passwords in Note:160861.1.
sec_mgr@KNOX10g> -- sets all passwords to that of user's name sec_mgr@KNOX10g> EXEC populate_passwords_tab PL/SQL procedure successfully completed. sec_mgr@KNOX10g> -- check for manager, a common password for DBA accounts sec_mgr@KNOX10g> EXEC populate_passwords_tab ('manager') PL/SQL procedure successfully completed. sec_mgr@KNOX10g> -- check for your company's name here sec_mgr@KNOX10g> EXEC populate_passwords_tab ('oracle') PL/SQL procedure successfully completed. sec_mgr@KNOX10g> -- SCOTT's default password sec_mgr@KNOX10g> EXEC populate_passwords_tab ('tiger') PL/SQL procedure successfully completed. sec_mgr@KNOX10g> -- SYS' default password sec_mgr@KNOX10g> EXEC populate_passwords_tab ('change_on_install') PL/SQL procedure successfully completed. sec_mgr@KNOX10g> -- common password people use sec_mgr@KNOX10g> EXEC populate_passwords_tab ('password') PL/SQL procedure successfully completed. sec_mgr@KNOX10g> COMMIT ; Commit complete. Now that you have a data set of precomputed password verifiers, you’re ready to run checks against your production database.
| Note | Once the password table has been created, the same data can be used against any Oracle Database because the password verifiers for the usernames are always the same in every Oracle Database. |
For the procedure that actually performs the checking, simply iterate through the database users comparing the password verifier computed there with the one actually stored in the PASSWORDS table. When you find a match, print the matched value and the account’s status:
sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE check_passwords 2 AS 3 BEGIN 4 FOR rec IN (SELECT username, 5 PASSWORD, 6 account_status 7 FROM dba_users) 8 LOOP 9 FOR irec IN (SELECT * 10 FROM passwords 11 WHERE username = rec.username 12 AND verifier = rec.PASSWORD) 13 LOOP 14 DBMS_OUTPUT.put_line 15 ('------------------------------'); 16 DBMS_OUTPUT.put_line ( 'Password for ' 17 || rec.username 18 || ' is ' 19 || irec.passwd); 20 DBMS_OUTPUT.put_line 21 ( 'Account Status is ' 22 || rec.account_status); 23 END LOOP; 24 END LOOP; 25 END; 26 / Procedure created. sec_mgr@KNOX10g> set timing on sec_mgr@KNOX10g> SET serveroutput on sec_mgr@KNOX10g> EXEC check_passwords --Password for CTXSYS is CHANGE_ON_INSTALL Account Status is LOCKED --Password for DIP is DIP Account Status is LOCKED --Password for OLAPSYS is MANAGER Account Status is LOCKED PL/SQL procedure successfully completed. Elapsed: 00:00:00.03 The output from the procedure’s execution was truncated in the previous output. It simply shows that the procedure was successful in finding passwords (not all successful findings are printed). Because changing default passwords is a best practice, this program can assist you in ensuring your Oracle Databases are compliant with best practices and are thus better secured.
Impossible Passwords
The Oracle database user’s password verifier is stored as a 16-character hexadecimal string. If you query the DBA_USERS view, you’ll see some passwords that aren’t hexadecimals. For example, there’s a database user named ANONYMOUS with a password of anonymous. How can that be? If you created a user by the name of ANONYMOUS with a password of anonymous, the password verifier wouldn’t say anonymous; it would be a hexadecimal representation of a hash of the password, not a plaintext string. You can check the passwords table previously created to verify this:
sec_mgr@KNOX10g> select * from passwords 2 where username = 'ANONYMOUS' 3 and pwd = 'ANONYMOUS'; USERNAME PWD VERIFIER --------- --------- ------------- ANONYMOUS ANONYMOUS FE0E8CE7C92504E9
The reason the string anonymous is present, as opposed to the verifier you see above, is that the user wasn’t created with the standard CREATE USER syntax but with the identified by values clause, as shown here:
SQL> CREATE USER anonymous IDENTIFIED BY VALUES 'anonymous'; User created. SQL> select username, password 2 from dba_users 3 where username = 'ANONYMOUS' 4 / USERNAME PASSWORD ---------- -------------- ANONYMOUS anonymous
This is a simple trick you can use to ensure users don’t log in to an account. It’s similar to creating a very strong password, but it’s better because you can’t log in to the account with anonymous or any other string in the universe!
The reason that no password is possible is because Oracle, on authentication, will compute the password verifier, which will be some 16-character hexadecimal string. This is compared with the one stored for the user. Because you know the password verifiers are stored in hexadecimal format, any values outside of the hexadecimal set (0–9, A–F) will not match the one computed. The result: there is no password the user can provide that will allow them to log in.
Anytime you are creating a database schema to which no one should connect, you should use an impossible password. The account also should be locked and privileges to connect to the database should not be given.
Managing and Ensuring Good Passwords
Passwords are the most prevalent form of authentication to Oracle Databases. Oracle provides the ability to enforce the choice of good, strong passwords through the use of password complexity routines. Oracle also provides a way to ensure good password management practices are also being followed through password profile enforcement.
Password Complexity
Oracle supports user-defined password complexity routines that allow you to validate the strength of passwords when they are set. Password complexity routines are critical to ensuring that password best practices are obeyed. The complexity routine technically implements the official password policy in your organization (assuming you have such a policy, and you should). You can check for many things within the routine. The biggest exception is case-sensitivity. Database authenticated user passwords are case insensitive. Here are a few common best practice checks you can administer within the complexity routine:
-
Password isn’t the same as the username
-
Password contains at least one digit
-
Password is greater than some specified length
-
Password isn’t the same as the old password
-
Password isn’t an easy to guess word, such as manager, oracle, or your company’s name
The function that administers the password check has to be implemented in the SYS schema. The password complexity function returns a Boolean value. The value TRUE means the password is okay. However, a good trick is to raise an exception in the function to notify the user of exactly what condition failed during their password change. Otherwise, they will get a generic error.
A sample function that implements some of the above checks would look as follows:
sys@KNOX10g> CREATE OR REPLACE FUNCTION is_password_strong ( 2 p_username VARCHAR2, 3 p_new_password VARCHAR2, 4 p_old_password VARCHAR2) 5 -- return TRUE if password is strong enough 6 RETURN BOOLEAN 7 AS 8 l_return_val BOOLEAN := TRUE; 9 BEGIN 10 -- Check to be sure password is not the same as username 11 IF UPPER (p_new_password) = UPPER (p_username) 12 THEN 13 l_return_val := FALSE; 14 raise_application_error 15 (-20001, 16 'Password same as user name'); 17 END IF; 18 19 -- force user to change password to something new 20 IF UPPER (p_new_password) = 21 UPPER (p_old_password) 22 THEN 23 l_return_val := FALSE; 24 raise_application_error 25 (-20004, 26 'Password has to be different than old password'); 27 END IF; 28 29 -- Check for list of predictable passwords 30 IF LOWER (p_new_password) IN 31 ('manager', 32 'change_on_install', 33 'oracle', 34 'password') 35 THEN 36 l_return_val := FALSE; 37 raise_application_error 38 (-20002, 39 'Password is too predictable'); 40 END IF; 41 42 -- make sure password contains at least one digit 43 IF (regexp_like (p_new_password, '[0123456789]') = 44 FALSE) 45 THEN 46 l_return_val := FALSE; 47 raise_application_error 48 (-20003, 49 'Password needs at least one digit'); 50 END IF; 51 52 -- make sure password is at least six characters 53 IF LENGTH (p_new_password) <= 6 54 THEN 55 l_return_val := FALSE; 56 raise_application_error 57 (-20005, 58 'Password is too short'); 59 END IF; 60 61 RETURN l_return_val; 62 END; 63 / Function created. To enforce the password complexity routine, assign it to a Password Profile and then assign the profile to the user(s). Examples are shown in the following section.
Password Profiles
Oracle allows you to create Password Profiles that govern the behavior of the database with respect to passwords and authentication. To do this, create a profile with values set for the attributes you wish to use. The profile then can be enforced on your users. Oracle supports the following attributes for password profiles:
-
Password lifetime Allows a password to exist for a specific period of time
-
Grace period Time at which Database begins to warn users to change their password
-
Reuse time/max Supports password history and forces users to use new passwords
-
Failed login attempts Locks the account if the incorrect password is given after specified number of times
-
Account lockout Disables the account (combined with failed attempts to help prevent brute force attempts into user accounts)
-
Password Verify Function Defines the password complexity function that will be called when the user changes the password
Chapter 7 of the Oracle Database Security Guide gives more detailed explanations of these attributes. A sample profile that sets values for these attributes and assigns the complexity function defined previously would look as follows:
sec_mgr@KNOX10g> CREATE PROFILE strong_pwd LIMIT 2 PASSWORD_LIFE_TIME 90 3 PASSWORD_GRACE_TIME 15 4 PASSWORD_REUSE_TIME 180 5 PASSWORD_REUSE_MAX UNLIMITED 6 FAILED_LOGIN_ATTEMPTS 5 7 PASSWORD_LOCK_TIME .5 8 PASSWORD_VERIFY_FUNCTION is_password_strong; Profile created.
Now assign this profile to your users. Testing this on the user SCOTT yields the following results:
sec_mgr@KNOX10g> -- assign profile sec_mgr@KNOX10g> ALTER USER scott PROFILE strong_pwd; User altered. sec_mgr@KNOX10g> -- test profile sec_mgr@KNOX10g> -- reset scott's password sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY scott; ALTER USER scott IDENTIFIED BY scott * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20001: Password same as user name sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY manager; ALTER USER scott IDENTIFIED BY manager * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20002: Password is too predictable sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY nodigit; ALTER USER scott IDENTIFIED BY nodigit * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20003: Password needs at least one digit sec_mgr@KNOX10g> -- since this is not SCOTT, old password is null sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY tiger; ALTER USER scott IDENTIFIED BY tiger * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20003: Password needs at least one digit sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY short1; ALTER USER scott IDENTIFIED BY short1 * ERROR at line 1: ORA-28003: password verification for the specified password failed ORA-20005: Password is too short
Normally, after creating a password profile, you’ll force your users to change their passwords to ensure all passwords being used comply with the profile. To do this, you expire their existing password. Upon their next login, the database prompts them to reset their password. The new password is checked against the complexity routine and the other password profile values will also be enforced.
When administering this for SCOTT, set the new password to tiger, which you can’t see when looking at the output below. You may have noticed in the previous example that the complexity function didn’t indicate that the new tiger password matched the old password. The following output shows a different behavior when SCOTT changes his password. The complexity routine now informs him that the new password is the same as the old password. Note that this is done for security. If a user other than SCOTT received this message, then the database would have divulged the user’s password.
sec_mgr@KNOX10g> ALTER USER scott PASSWORD EXPIRE; User altered. sec_mgr@KNOX10g> conn scott/tiger ERROR: ORA-28001: the password has expired Changing password for scott New password: Retype new password: ERROR: ORA-28003: password verification for the specified password failed ORA-20004: Password has to be different than old password Password unchanged Warning: You are no longer connected to ORACLE.
Keep the Password Policies Practical
A password profile is a great way to ensure that good password management practices are being used. Once again, however, you have to balance security with usability. While using password profiles is generally a good idea, it can backfire. For example, forcing users to choose a new password each week (that is, expiring passwords too frequently) may in fact force the user to use easy passwords or worse, write down their passwords.
As another example, you may decide after three failed logins, you’ll lock the user account for a day. There are unintended consequences to this. The failed login and account locking can aid someone launching a denial of service (DoS) attack. The attack is made easy because a malicious person can intentionally lock all the database accounts by simply providing an incorrect password for each database user.
| ||
| ||
EAN: 2147483647
Pages: 111