Exclamation Pointers

We recently made some more changes to our passwords policy. Most of the changes are in PL/SQL stored procedures or stand-alone functions. Our diligent test team came up with numerous test cases to verify our software.

Our policy allows entry of some special characters like the exclamation point. However a tester found that they could not change the password to one which includes an exclamation point. So I did some testing of my own to figure out what the problem was.

I could log in using SQL*Plus as myself, and choose a password with an exclamation point by issuing a password command. But I could not ALTER USER IDENTIFED BY .

This was strangely curious. I knew Oracle allows passwords with exclamation points. But ALTER USER was always responding with an ORA-00922 (illegal option). Google searches on the subject did not seem to shed much light on the subject.

Luckily I did stumble upon some knowledge but trying to search Google with different keywords. Turns out you can change the password to one with an exclamation point using ALTER USER. You just need to surround the password with quotes.

Password Verify

We updated the password verify function on the back end in our database. It was now returning some new errors based on new password rules. So our front end C++ developers had to trap these errors and display appropriate text.

There was a problem when the C++ applications forced users to change their password when their database accounts had expired. In this scenario they were executing the following Pro*C code.

EXEC SQL
CONNECT user
IDENTIFIED BY oldpwd
AT dbname
ALTER AUTHORIZATION newpwd;

This does allow the user to change thier password. And the new passwords were going through our password verify function on the server. However, when the passwords did not pass the verify function, Oracle was returning an error of ORA-28003.

Yes. We knew it was failing the password verify function. But the front end needed to know the specific rule that failed. This was not being passed back. I have seen some other code that first manually called the password verify function to get the specific error. However in this scenario the database account was expired. I did not think we could connect and execute this function.

For now our developer is rewriting the password verify function in C++ to know before hand whether the back end version will fail. This feels wrong. Any ideas?

Password Reuse

Oracle password reuse policy is controlled by database profiles. Profiles are administered with CREATE PROFILE or ALTER PROFILE. Profiles are assigned to users with CREATE USER or ALTER USER. To set up password reuse you must specify the PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX limits in the profile.

-- Can reuse after 5 days and 3 intermediate passwords
CREATE PROFILE reusable
LIMIT password_reuse_max 3
LIMIT password_reuse_time 5;
ALTER USER xero
PROFILE reusable;

If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to UNLIMITED, passwords can be reused freely. If one of these limits is set to UNLIMITED, and the other is set to an integer, the user cannot reuse passwords. If both limits are set to integers, then a password can be reused after the PASSWORD_REUSE_TIME number of days has expired and the password has been changed PASSWORD_REUSE_MAX number of times.

If either limit is set to DEFAULT, the limit value is taken from the DEFAULT PROFILE. The DEFAULT PROFILE initially has all limits set to UNLIMITED. Note that you can set the PASSWORD_REUSE_TIME to a time period less than a day by using fractions.

-- User must wait at least 1 hour before reusing password
ALTER PROFILE reusable
LIMIT password_reuse_time 1/24;

This information has been tested in Oracle 9i (release 9.2.0.8). It is also consistent with the official documentation for Oracle 10g (10.1).

Pro*C Tricks

Consider the following Pro*C snippet of code that retrieves all records via cursor.

EXEC SQL DECLARE tol_cursor FOR
SELECT tol_value
FROM tolerances;
EXEC SQL OPEN tol_cursor;
while (bMoreData)
{
EXEC SQL FETCH tol_cursor
INTO v_value;
}
EXEC SQL CLOSE tol_cursor;

Suppose you had a huge amount of data to retrieve. You can split the code into two Pro*C functions. One can initialize the cursor. Another can use the cursor previously initialized and retrieve the data.

void init_query()
{
EXEC SQL DECLARE tol_cursor FOR
SELECT tol_index, tol_value
FROM tolerances;
EXEC SQL OPEN tol_cursor;
}

int get_value()
{
EXEC SQL FETCH tol_cursor
INTO v_value;
return v_value;
}

In other words, you do not have to set up a Pro*C cursor in the same function where you use it. This can come in handy if you want to make multiple calls to a Pro*C function to get small chunks of a large data set.

No Data Found

Our testing team said too much was broken in our application for them to proceed. They are a pretty thorough bunch. A trouble ticket was submitted with detailed steps and screen shots of the problems.

I could not duplicate the first problem. So I asked a tester to show me the problem. She could not duplicate it either. But she gave me access to her environment. And I studied the data the application was using when the problem happened.

Next I went into our log database table. Sure enough I found an entry with this tester's user ID. The log entry gave me the stored procedure where the error occurred. I traced through each statement the stored proc executed. Every time I ran the SQL manually in the test environment. I finally got to a statement where I replicated the Oracle error. The source of the problem? Bad data. I like when it is not the application's fault.

Using this same technique I got down to the procedure where the other errors were occurring. This time I was not able to duplicate the problem by running the SQL manually. After challenging all my assumptions, I realized I was logging in as the schema owner. So instead I got the testers login and password and tried again. Bam. It was a permissions issue. I get to forward these to our DBA Team. Problem was corrected immediately.