Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> Valid characters for Oracle passwords?...
        
(Message started by: Pete Finnigan on Nov 9^th, 2005, 5:19pm)

Title: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Nov 9^th, 2005, 5:19pm

In many Oracle security documents, there is a suggestion to use long, complex passwords.

The suggestion to use long, complex passwords begs the question: what characters can be included in an Oracle password?

We have run into problems when digits are used as the first character of the password (don't remember the specifics; it might have been an arbitrary decision). I haven't tried punctuation characters (using them could be tricky, because some punctuation characters can be interpretted by UNIX shells to have special meaning). I'm also not sure if non-ASCII characters can be used for passwords. Then, there is the problem about special characters getting from the UI (web, GUI tool, whatever) to the Oracle API login call without getting altered in transmission...

Is there a definative document that describes what characters can be present in a valid Oracle database password? I've not seen anything from Oracle about this.

Thank you for your thoughts on the matter.

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Nov 9^th, 2005, 8:23pm

Hi,

basically you can use any ASCII character, they are always UPPERED so there is no case sensitivity. You can also use digits 0-9 and three special characters _#$. You have to start a password with an ASCII character and then you can use any of the others. These are the same rules for naming any object in Oracle such as a table. These rules are well documented in the Oracle docs.

Now it gets better if you use double quotes. Basically then you can use any character in the keyspace. The same ASCII UPPER rule works though so instead of 256 possible characters its 230 as the ASCII characters are not case sensitive. If the password is encased in double quotes then you can start it with any character. The rules about using an ASCII character first go out of the window.

As you noted you can use special characters and control characters as well. For me these only really have one possible use. That is to set a password that is much harder for someone to login manually as. You can add the password to a script for instance. I have created a password with a carraige return in it before for instance.

Hope this helps

cheers

Pete

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Nov 9^th, 2005, 11:04pm

Thanks, Pete. That helps. Sorry for pestering you about it...

Jkayser

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 5^th, 2005, 5:31pm

on 11/09/05 at 20:23:43, Pete Finnigan wrote:

The same ASCII UPPER rule works though so instead of 256 possible characters its 230 as the ASCII characters are not case sensitive.

not sure if I am agreeing with that one

SQL> create user "user1" identified by "éèëê";

User created.

SQL> create user "USER1" identified by "ÉÈËÊ";

User created.

SQL> select username,password from dba_users where username in ('USER1','user1');
USERNAME PASSWORD
------------------------------ ------------------------------
USER1 912BB2D0E222EA9A
user1 912BB2D0E222EA9A

there 31 accentuated characters that are have an upper form.

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 6^th, 2005, 8:18pm

Hi,

Look at the password column in your final select statement. The value of the password is the same therefore the upper case and lower case characters generate the same password, therefore there is no case sensitivity backing up what i said.

cheers

Pete

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 11^th, 2005, 10:54pm

I don't think he was saying it was case-sensitive, I think he was saying that it isn't just the 26 lowercase characters of the 'normal' alphabet that are translated.

The following indicates that there are 60 such characters, and therefore only 196 in the pool of characters really used for the password.

SELECT COUNT(CASE WHEN charx != UPPER(charx) THEN 1 ELSE NULL END) lower_ne_upper,
COUNT(DISTINCT UPPER(charx)) num_dist
FROM
(SELECT CHR(ROWNUM-1) charx FROM dba_tables WHERE ROWNUM <= 256)

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 12^th, 2005, 8:43am

well, in 9iR2 and 10gR2, I have got 31 accentuated characters, that is 199 distinct upper character out of the 256 set.

in 8i, I have got a different result,

SQL> create user u1 identified by "!";

User created.

SQL> grant create session to u1;

Grant succeeded.

SQL> connect u1/!
Connected.
SQL> connect u1/¡
Connected.

so the upper of ! is the inversed explanation mark (¡), so it remains only 102 distinct characters in 8i

the scenario above does not work in 9i and 10g, which means that some 8i exotic passwords may stop working in 9i :-[

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 14^th, 2005, 6:25pm

Thanks for the correction Gary, you are absolutely right. This is actually very intersting work. It means that the total keyspace is in fact much smaller than was thought so weakening the potential strength of passwords. It is still possible to create passwords of sufficient length and complexity that should not be cracked by any normal computing resource in any short timescale. It does weaken the choice from the keyspace though quite a lot. nice research.

cheers

Pete

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 16^th, 2005, 11:40am

in 8i, it is quite weird... it seems it was designed by american used to use 7-bits ascii

It has very strange effects... Uppercase c-cedille (Ç) is 11000111. It accepts 3 passwords !
Lowercase ç (11100111), Uppercase G (01000111) and Uppercase Ç (11000111)

SQL> alter user G identified by "Ç";

User altered.

SQL> CONNECT g/Ç
Connected.
SQL> CONNECT G/G
Connected.
SQL> CONNECT g/g
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> CONNECT G/ç
Connected.

Cheers
Laurent Schneider

:o it is almost a case sensitive password, since G works, but g does not

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 16^th, 2005, 9:16pm

Hi Laurent,

Thanks for your post. This is interesting. I am intrigued as to why it does not accept a lower case "g" but accepts an upper case one. It seems like a bit of a mess really.

cheers

Pete

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 20^th, 2005, 6:20pm

Hello, all.

I ran some tests on RDBMS 10.1 on Sun SPARC Solaris 8. Here are the results of the test:

1) Only ASCII characters are acceptable (chars in the range 0-127). If you try passwords with characters out of this range, you get "ORA-01040: invalid character in password; logon denied".
2) Object naming rules apply to passwords. Double quoting object names results in the largest number of valid characters. However, NULL characters (chr(0)) and double quotes are not allowed.
3) Upper and lower case alphabetic characters create the same hash values.

For a 1 character password, I was able to get 100 different hashes:
a) ASCII characters only: 128 possible characters.
b) Disallow NULL and double quotes: this leaves 126 possible characters.
c) Ignore case: this leave 100 possible characters.

My test code is here:

declare
ichar integer;
iascii integer;
short_passwd nvarchar2(10);
sql_stmt varchar2(200);
my_hash varchar2(40);
begin
dbms_output.disable;
dbms_output.enable(1000000);
execute immediate 'drop user dummy';
execute immediate 'create user dummy identified by dummy';
for ichar in 0..65536 loop
short_passwd := nchr(ichar);
if short_passwd != chr(0) and short_passwd != '"' then
sql_stmt := 'alter user dummy identified by "' || short_passwd || '"';
--dbms_output.put_line( sql_stmt );
iascii := ascii(substr(sql_stmt,33,1));
if ichar != iascii then
dbms_output.put_line('ERROR: ichar: ' || ichar || ', iascii: ' || iascii );
end if;
begin
execute immediate sql_stmt;
select password into my_hash from dba_users where username = 'DUMMY';
dbms_output.put_line( 'Hash: ' || my_hash || ', iascii: ' || iascii || ': ' || short_passwd );
exception
when others then
dbms_output.put_line( 'ERROR: ichar: ' || ichar || ' ' || sqlcode );
end;
end if;
end loop;
end;
/

Based on this test, there are 100 possible characters in each byte of password, although this result may be platform dependent.

Can anyone find any more possible password characters?

Jeff Kayser
jeff.kayser@dbdr.com

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 21^st, 2005, 7:22am

SQL> create user u identified by "ÿ";

User created.

SQL> grant create session to u;

Grant succeeded.

SQL> connect u/ÿ
Connected.

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 21^st, 2005, 4:24pm

10gR2 sqlplus
103 characters are unique : ex : "<"
118 characters are double : ex : "x" and "X"
3 characters are triple : "y" and "Y" and "ÿ"
32 characters seems to be common, or hard to test, ascii 128-159

total (103+118+3+32) 256 valid char, (103+59+1+1) 164 character space

regards
laurent

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 21^st, 2005, 4:26pm

on 12/20/05 at 18:20:45, jkayser wrote:

nvarchar2(10)

nvarchar seems not to be accepted

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Dec 21^st, 2005, 4:44pm

Hello, all.

Does anyone know how NLS_LANG settings may influence this discussion? I'm wondering specifically, if client-side NLS_LANG settings will effect:

1) Anonymous PL/SQL blocks entered into SQL*Plus.
2) SQL commands entered into SQL*Plus.
3) SQL*Plus commands like "connect <username>/<passwork>"

In other words, there is lots of Oracle code between the UI and the RDBMS engine. How much of what we type is transmitted unaltered to the RDBMS engine, and how much is transformed somehow?

I'm wondering if some of the special characters are getting transformed before they are interpretted...

Jeff Kayser
jeff.kayser@dbdr.com

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Jan 18^th, 2006, 9:13pm

Hello All:

I'm brand new to this forum, so I apologize if this thread has ben beaten to death already. I was thinking exactly the same thing as jkayser.
When we converted our SAP system to Unicode (specifically UTF8) we discovered all sorts of character manipluations that Oracle had done under the covers.

Cheers,
Mike

Title: Re: Valid characters for Oracle passwords?...
Post by Pete Finnigan on Oct 26^th, 2009, 4:53pm

The most important character to avoid, I would have to say would be the @ symblol. I had a user attempt to use a password like 'p@ssw0rd' they could log into OEM just fine. . .but everytime they went to log into isql or sqlplus, they got an ORA-12154 tns error. . .
:-/ this was a fun one to debug. . . . ::)