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.

The Big SQL

Our client has an offical acceptance test team. They are all full-time employees of our client. Recenetly they found that one of the queries in our app was never producing results.

At first I helped the testers understand what inputs you need to give the query to get it to find records. But the testers said that they still were unable to get a single result record.

Unfortunately the test environment is on lock down. I cannot log in and run SQL queries myself to check up on the data. So I kept e-mailing the testers, trying to get them to run SQL for me to diagnose the problem.

After many queries I came to the conclusion that the application had to be able to get some results. So as a last effort I had them run the big SQL code that the app runs. That's when I found out the whole big query results in an ORA-01502. In other words "index unusable". I normally run the big SQL myself when diagnosing problems. But in this case I skipped it because I needed to get somebody else to run it. From now on I will just have to get them to key in or cut-n-paste all the SQL in the first place.

The good news is that broken indexes can be fixed the DBA Team. This leaves me free to get back to other tasks on hold. See you next time.

Database Assumptions

A developer got tasked with removing the hard coded references to database schema names in the code. Just delete the schema from the SQL was trivial. Testing the changes was a whole different problem.

I went to the developer to get help with my Clearcase problems. In return he asked if I could get him through his testing problems. The app kept giving him error messages stating that he did not have the appropriate database role. I had him trace the code that made the app display this error message.

One would think if you ran the same SQL as the app, you could determine the app results by interpreting the SQL results. This is not always so. I helped the developer make the decision to run all the SQL as himself (instead of doing it as the schema owner). As soon as he tried this, he could not even run the equivalent SQL. Turns out his database account does not even see the database tables used in the SQL.

Our task was not complete. I prodded the developer to delve a little deeper. So I had him check whether his database account could even see the stored procedure that executes the SQL. Now that he saw the pattern, I released him to compile a list of database objects he needed new synonyms created for. The DBAs could help create them. But we needed to do the analysis on which synonyms we needed.

This adventure was another example where you need to question all assumptions to get to the root cause of a problem.

Hard Coded Schema

Our development team has a common database schema where we can do unit testing. In addition, some of us have our own personal database schemas where we can do whatever we want. I needed to test out some performance enhancements. So I decided to create a lot of data in my personal schema.

It took about a half hour to run a script that created lots of records and set everything up. So I ran our app against this schema, hoping to first replicate the performance problem. Too bad everything ran pretty quick. Didn't take long for me to realize that the application was somehow running against the common schema.

So I dug into the code and found that our app hard codes the schema name. This is evil. It works because the offical quality and acceptance tests are run in an environment that have the same schema name. Production also has the same schema name. This only breaks down when we get to me using my personal schema to do really specialized testing.

Well I am a programmer. So I made a not to fix this hard-coded schema name everywhere in the app. But in the mean time, I guess I can do all my testing in the main common schema. Time is always of the essence for me on my project.

Using & In Scripts

A developer on my team tried to write a SQL script to populate a new database table for our system. One of the values in one of the columns needed an ampersand in the text. As you may already know, the ampersand is a special character in Oracle SQL scripts that represents a substitution variable and not the ampersand character itself.

There was a lot of commotion with this new script. It first got passed to the DBA Team for implementation. Of course it did not work. So the script came back to the developer. Another developer and a DBA tried to help get it to work with no success. Then they called in a consultant who recommended we do not use an ampersand.

I figured this had gone on long enough. So I suggested escaping the character by placing a backslash before it. I also said they needed to add a SET ESCAPE ON to the beginning of the script to ensure the backslash was treated as an escape character. This got them past the ampersand problem. But this only brought them to the next problem with the script that prevented it from running.

The lesson from this exercise was that it is acceptable to write and debug scripts using advanced tools such as PL/SQL Developer. However when you perform your unit tests, you better run the scripts with the same program that will be used in Production (Oracle SQL*Plus). Otherwise you have no way to tell whether your tests are valid.

Password Policy

Our system at work already has a policy for password use. There is a database profile which is common to all database users. This profile has a PASSWORD_VERIFY_FUNCTION set to a stand-alone function owned by sys. Trouble is this function is not complete. It does not implement all of our customer's security requirements. I am a programmer. So the DBAs turned to me to fix this problem.

Some of the changes were trivial. Ensure the password has certain characteristics. I just mowed through these requirements by writing PL/SQL code in the password verify function. Then came a troubling requirement - the password had to have upper and lower case characters. You would think this was no trouble for an experienced PL/SQL programmer. But coding these requirements into the function caused a lot of things to break.

To better understand the problem I traced how our applications actually changed the user passwords. Turns out they just issue an ALTER USER IDENTIFIED BY . I sprinkled debug statements all throughout the password verify function, writing the output to a database table. And the output showed me exactly what was wrong. Turns out that when Oracle calls the password verify function while processing the ALTER USER command, it converts the password to upper case first! Damn.

I googled the web for information on this suspicious behavior. Could not really find much info on it. I already knew that Oracle passwords were not case sensitive. But who knew they would be doing an UPPER behind the scenes before my password verify function got called. Note that this did not happen when trying to change the password in SQL*Plus by executing the PASSWORD command. Go figure.

In the end I talked this over with our database manager. He looked at some password verify function samples from Oracle. None of them did upper/lower case enforcement. So we decided to skip implementation of this requirement. Normally I would protest, since us developer should be able to do anything. But I am on a tight deadline at work, and researching this problem has already taken too much time.

P.S. "Oracle PL/SQL Programming" written by Feurstein and published by O'Reilly is excellent . I have around 50 books on my bookcase at work. But I keep this book on a separate bookshelf reserved for the best.

Client Version Matters

Previously I had problems using my Oracle 8 client to connect to an Oracle 9i database as sysdba. But a DBA told me they had no problem doing this. The only difference seemed to be that the DBA was using the Oracle 10g client. It was worth a try.

I was shocked to see the Oracle 10g client download was 475 Meg. What the heck do they got in there? Had to leave the download running overnight. Normally I choose custom installs to ensure I only get what I want. I was able to choose things I thought I would need from the Oracle 10g client. But a post-installation configuration was hard to bypass. Had to kill the Oracle installer process using Task Manager.

So I fired up the Oracle 10g SQL*Plus. Voila. I could successfully log into our Oracle 9i database as sysdba. So I guess the Oracle 9i database does not support you logging in as sysdba with an Oracle 8 client. I imagine this is documented somewhere in the Oracle docs. But I would have liked something other than an "ORA-01017: invalid username/password; logon denied" to let me know that.

I guess I can't complain too much. The Oracle 10g client is a free download. Heck. Even the Oracle 10g database is a free download for non-commercial uses. And if I did not encounter weird Oracle problems, I would not have anything to write about. So now that I could log in as sysdba, it was time to get my password policy changes working.

Client Server Mismatch

I needed to modify some of our system's password policies. So I updated the password verify function which our users' common profile points to. Unfortunately this function must be compiled under sys. So I started handing it off to our DBAs to compile for me. That got old after the 3rd or 4th time I needed to make change during debugging.

So I talked our DBA manager to give me the sys password to a local Oracle 9 database running on one of our Windows servers. Since we use a lot of legacy tools on our project, I fired up the version of SQL*Plus that comes with our ancient Oracle 8.1.6 client. I was surprised at the result.

SQL> connect sys@the_database as sysdba
Enter password: *******
ERROR:
ORA-01017: invalid username/password; logon denied

At first I thought I had the wrong password. But a DBA came to my desk and had the same problem. The database is Oracle 9i (Release 9.2.0.8). Maybe you can't mix an Oracle 8 client with an Oracle 9i client when you log on as sysdba. I don't know.

The next thing I am going to try is to install the Oracle 10g client to see it makes a difference. Stay tuned and I will fill you in with my results.

Database Access

Our team came to the realization that we were not going to make our first delivery of the software. A quick analysis determined the problem holding us back was the code to retrieve and store data to the database. Current efforts were being done using the Active Template Library (ATL) database classes. The developer doing the work had never used ATL before. Thus the delay.

My team lead asked me for suggestions. I said that my plate was full so I could not take over the task. And even though I had done ATL programming before, it has been a long time. ATL was the initial choice because the work is an addition to a small app that was written with ATL. A couple other choices were MFC database access and Pro*C. My recommendation was to go with Pro*C because that was the second most used database access technology in our application suite.

There is potentially a huge amount of data being retrieved from the database in the new app. So the function written to get this data could not get it all at once. We ended up having one function to initialize the data retrieval, and another function which got one record at a time. The back end Pro*C code used a trick to make the retrieval code easy with only one open cursor. I have provided the details in a later post on Pro*C Cursors.

Password Reuse

Our client has a detailed set of security requirements for all applications. When my team's application suite was developed long ago, they implemented a subset of these requirements. Recently this has come up as part of an audit. So our new directive is to get in compliance with the security requirements.

One area the requirements cover is that of password reuse. I thought this would be easy to implement since all our users already have a database profile which we manage. This should have been a matter of setting some limits in the profile. Unfortunately nothing is ever as easy as it seems.

Some web sites recommended setting password reuse limits like this.

ALTER PROFILE common LIMIT password_reuse_max 5,
password_reuse_time UNLIMITED;

The idea is that users could reuse their passwords after they chang their password 5 times. But this would not work. After much investigation and pain, I finally got the 411 on password reuse.