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.