Minor Bug in Supplied Password Function?

I get the feeling that most people will see this as ridiculously trivial, but it’s the sort of thing that my attention gets drawn to 🙂

When I first spotted the double “backtick” in the punctarray within the VERIFY_FUNCTION created using the supplied (10.2) password function script (utlpwdmg.sql) I thought it was a bug introduced by me during my improving/modifying the script months earlier.

I’d already identified that there were punctuation characters missing from the array that needed to be added as their absence had caused a bit of confusion for our end users. Having been told that they needed to include at least one punctuation character they were discovering that some punctuation characters were not being seen as valid punctuation. As a result I was already motivated to “fix” the function. The punctarray I ended up with is provided later in this post.

After coming up with the new punctarry I then gave the double backticks no more thought, apart from kicking myself for making such a simple error.

… Then I recently saw the double backticks again in a VERIFY_FUNCTION at a new site… This couldn’t have been my bug, I’d been no where near this system until that day! This prompted me to dig a bit deeper.

First up, here is a demonstration of why I think this is a (very minor) bug:

“punctarray” in supplied utlpwdmg.sql


Creating a user with ` in the password and demonstrating that the password must be in double quotation marks (or set using the “password” command):

SQL> create user a identified by abcde`12345;
create user a identified by abcde`12345
ERROR at line 1:
ORA-00911: invalid character
SQL> create user a identified by "abcde`12345";

User created.

SQL> grant create session to a;

Grant succeeded.</div>

SQL> conn a/abcde`12345

So that demonstrates the creation of a user with a password containing ` using the supplied password verification function, but what happens if we remove one of the backticks…

The punctarray was modified in utlpwdmg.sql to be:


The function was created and a new user with a password containing a backtick was created:

SQL> create user b identified by "abcde`12345";

User created.

SQL> grant create session to b;

Grant succeeded.

SQL> conn b/abcde`12345

As demonstrated, the use of double backticks appears to be completely unnecessary, hence the suggestion that it is a bug (admittedly a harmless one, but a bug nonetheless). I can only assume that this is the result of a simple typing error.

Interestingly for 11g the new function, VERIFY_FUNCTION_11G, does not check for punctuation characters without modification, but VERIFY_FUNCTION is included in the same script for use or reference.

The point of this post is not just to point out the “bug”, but rather to share what I had previously arrived at for punctarray, which includes a wider range of characters and no duplicates.:

punctarray := '!#$%^()`.|*+,-:;<=>?_{}[]~\';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s