Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Madan .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: July 15, 2008 - 8:07 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

Hello Tom,
here I have created a table as shown below,can you please
give me a procedure for the auto system generation of password
and want to know the probabality of that password being occured.

CREATE TABLE USER_INFO
(Title varchar2(9),
First_Name Varchar2(30),
Last_Name Varchar2(30),
Sex Varchar2(7) check (sex in('MALE', 'FEMALE')),
Date_Of_Birth Date,
Age Number(2),
Address1 Varchar2(60),
Address2 Varchar2(55),
Address3 Varchar2(55),
City Varchar2(30),
PIN_ZIP Number(8),
State Varchar2(35),
Country Varchar2(50),
Phone_Home Number(12),
Phone_Work Number(12),
e_Mail_ID Varchar2(75),
PKN Varchar2(25) Primary Key,
COMPUTER_STATUS VARCH(8),check (COMPUTER_STATUS
in('Y', 'N')));









and Tom said...



I use the following routine to generate random passwords for people:

create or replace function generate_password (
in_userid in varchar2, l_date in date
)
return varchar2
is
j number := 0;
k number;
str varchar2(30);
result varchar2(30);
begin
if in_userid is null then
return null;
end if;
str := substr (in_userid, 1, 4) || to_char (l_date, 'SSSS');
for i in 1 .. least (length (str), 8)
loop
j := mod (j + ascii (substr (str, i, 1)), 256);
k := mod (bitand (j, ascii (substr (str, i, 1))), 74) + 48;
if k between 58 and 64 then
k := k + 7;
elsif k between 91 and 96 then
k := k + 6;
end if;
result := result || chr (k);
end loop;
result := replace (result, '1', '2');
result := replace (result, 'l', 'L');
result := replace (result, '0', '9');
result := replace (result, 'O', 'P');
result := 'A' || substr (result, 2);
return result;
end generate_password;
/


You'd have to have the same first 4 digits and do it at the same time of day to get a dup.




Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

another way of doing this

Jay, April 15, 2002 - 6:44 am UTC

Depending on your version of Oracle (this works in 9i) is to use DBMS_RANDOM package

EG dbms_random.string('x',6)

Returns a 6 character alpha-numeric random string:

SQL> BEGIN
  2  DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.STRING('X',6));
  3  END;
  4  /
J3C3BF

PL/SQL procedure successfully completed. 

Tom Kyte
April 16, 2002 - 7:15 am UTC

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.STRING('X',6));
D=MDO3

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter user ops$tkyte identified by D=MDO3;
alter user ops$tkyte identified by D=MDO3
                                    *
ERROR at line 1:
ORA-00922: missing or invalid option

I wouldn't use that.  It'll generate invalid passwords. 

2 quick ones

J, April 16, 2002 - 1:33 pm UTC

1. Why 4 S's?  why not 5 ?ie: sysdate,'SSSS' 

2 How many times did you have to run the 
DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.STRING('X',6));
before you got the invalid one ?

and
SQL> select to_date(sysdate,'SSSS') from dual;
ERROR:
ORA-01810: format code appears twice

3 I now know to_date should be to_char but why the 
format code appears twice?

Thanks for all this good stuff!! 

Tom Kyte
April 16, 2002 - 9:38 pm UTC

1) it is just a seed. it needs 8 bytes, you can use sssss if you like.

to_date expects a character string. so that is like:

to_date( '01-jan-02', 'ssss' )

ss is the input format for seconds, sssss would be the input format for seconds since midnight (you see ssss is seconds || seconds).

you have the ss format twice. don't confuse that with sssss



Duplicite values

Stano, April 17, 2002 - 12:28 pm UTC

Hi Tom,

you said:

"You'd have to have the same first 4 digits and do it at the same time of day to get a dup."

Even I think it is not necessary to avoid duplicate passwords at all costs, using your method you'll will get the same password if you have the same first 4 digits and you are doing it at the same SECOND OF A MINUTE (e.g. if you have 61 users with the same first 4 digits, at least two of them will have the same password). Thus, it is perhaps really better to use something like

str := substr (in_userid, 1, 4) || substr (to_char (l_date, 'SSSSS'), 2);

in your code.

Tom Kyte
April 17, 2002 - 12:57 pm UTC

Fine, use whatever algorithm you like. It is that easy. See it however you choose. Seed it with random if you like. seed it with the first three characters and sssss. whatever...

I think you meant substr (to_char (l_date, 'SSSSS'), 4)
^^^ not two tho.



John, January 09, 2003 - 4:32 pm UTC

Tom,  

What is wrong with dbms_random.string?
 
My version is Oracle 8.1.7.3.4 on Windows.

The code for the function says:

------------------------------------------------
 -- Random string.  Pilfered from Chris Ellis.
 FUNCTION string (opt char, len NUMBER)
     RETURN VARCHAR2 is      -- string of <len> characters
     optx char (1)      := lower(opt);
     lo   NUMBER;
     rng  NUMBER;
     n    NUMBER;
     xstr VARCHAR2 (60) := NULL;
 BEGIN
     IF    optx = 'u' THEN    -- upper case alpha characters only
         lo := 65; rng := 26; -- ASCII 41 to 5A (hex)
     ELSIF optx = 'l' THEN    -- lower case alpha characters only
         lo := 97; rng := 26; -- ASCII 61 to 7A (hex)
     ELSIF optx = 'a' THEN    -- alpha characters only (mixed case)
         lo := 65; rng := 52; -- ASCII 41 to 5A and 61 to 7A (see below)
     ELSIF optx = 'x' THEN    -- any alpha-numeric characters (upper)
         lo := 48; rng := 36; -- ASCII 30 to 39 and 41 to 5A (see below)
     ELSIF optx = 'p' THEN    -- any printable characters
         lo := 32; rng := 95; -- ASCII 20 to 7E (hex)
     ELSE
         lo := 65; rng := 26; -- default to upper case
     END IF;
     FOR i IN 1 .. least(len,60) LOOP
         /* Get random ASCII character value in specified range */
         n := lo + TRUNC(rng * value); -- between lo and (lo + rng -1)
         /* Adjust FOR split range */
         IF    optx = 'A' AND n > 90 THEN
             n := n+6;               -- exclude ASCII characters 5B to 60
         ELSIF optx = 'X' AND n > 57 THEN
             n := n+7;               -- exclude ASCII characters 3A to 40
         END IF;
         xstr := xstr||chr(n);       -- Append character to string
     END LOOP;
     RETURN xstr;
 END string;
----------------------------------

I have a junk table with 6 rows for testing.

SQL> select dbms_random.string('u',40) from junk;

DBMS_RANDOM.STRING('U',40)
------------------------------------------------------
GCJFNGYGROJUGWAJWQAODDBBZKSVEJUYYSMCZKPV
SYGATQNJZZVTNNSGNBBRQSFPHWWGHQEPPHPWUPLY
KNPSXMEMUOTQCULZVDGAHRYOHLQICDEYPRJNEJCB
KQWKLPOKLWWBGKNRZFAZQJKJFDDHENAGTCKSHNZS
HMJJNRPOWJNUTMKPTZRQCPQEZGUOXMKGSJJDBAXC
MRCYZLWNCCIKDPXGTDXLEZVPTWYUMISFRUOCBMSC

6 rows selected.

SQL> select dbms_random.string('l',40) from junk;

DBMS_RANDOM.STRING('L',40)
------------------------------------------------------
jcybbhnlnhpfatrfnbsmuostwwmfcpjcgwnfhjbg
enzmbjlevgovalwfzlgexwoteclhcsmrpsywxeys
dbacafifitkefpaiqluecxwkznsypuxcnhhxcwym
vvmihmzfdtazcvcdpsbqafiwarcilzulggmkkwpd
hsyrqafdaiocchjwmpqtlurkankmrsgwlziqgozb
aqkogditwcdvrhnhfieitmaliwsivixmgahryzew

6 rows selected.

SQL> select dbms_random.string('a',40) from junk;

DBMS_RANDOM.STRING('A',40)
------------------------------------------------------
a\QSAiIdRZrtAtZRZGnCZMMEHX[aNdImmZdgNIGc
RdRlQVlgIJfWMUrhsksOYQ\JsagOHatlkLnjbEkQ
aLC[EGalQcAfZcmQQEFYBl^sU`MnZiD`CBi]WHBO
PhtaOlHGPsNdpUOBkOT^odXTdiOpjn_`lEEQdfFe
^Z]UJSbnQIGRlYcs]N]DfggGDSt_FTfUiqsECENt
qVNaRN_TSbTUXlkbm]tOgTOEEFVn]psZdjeCtQ[W

6 rows selected.


The "u" and "l" work fine but "a" does not.  It should return just alpha characters only mixed case.



I would like to have something like this that returns both alpha and numeric without any special characters.  

Can you help? 

Thanks
 

Tom Kyte
January 09, 2003 - 7:02 pm UTC

...
V
W
X
Y
Z
[
\
]
^
_
`
a
b
c
d
...

[\]^_`

are in the middle, they are there by design apparently. You'll have to take their algorithm and DIY if you don't want those.

Found the Problem

John, January 10, 2003 - 9:39 am UTC

In the DBMS_RANDOM.STRING FUNCTION 
------------------------------------------------
 FUNCTION string (opt char, len NUMBER)
        RETURN VARCHAR2 is    -- string of <len> characters
        optx char (1)      := lower(opt);   
        lo   NUMBER;
        rng  NUMBER;
        n    NUMBER;
        xstr VARCHAR2 (60) := NULL;
    BEGIN

--------------------------------------------------
Notice optx is set to always lower case.


Now look at the part that adjust for split range it has the following:

--------------------------------------------------
            /* Adjust FOR split range */
            IF    optx = 'A' AND n > 90 THEN
                n := n+6;               -- exclude ASCII characters 5B to 60
            ELSIF optx = 'X' AND n > 57 THEN
                n := n+7;               -- exclude ASCII characters 3A to 40
            END IF;
            xstr := xstr||chr(n);    -- Append character to string
        END LOOP;
        RETURN xstr;
    END string;
-----------------------------------------------

I created a new package called my_random and modified the string function to compare optx = 'a' and optx = 'x'.  It now works.  

SQL> select my_random.string('a',40) from junk;

MY_RANDOM.STRING('A',40)
-----------------------------------------------------
lEoApcGblPocdAMHvNnKbnxkmvhnjlbDWqEJGASq
svrVJGmrrkapVqfXzVoZOieIGtlkBfxoJfglukcd
fIdFcHbWLtiVixyypdFLmkgAPHaQaIBfeSerpFeR
mUFjdvnyImwhUIeuPEVCQcBpLMMXDdQbxLuPfTyX
uXxuRgatWzuFifHygRHhYDaGxqwAbdSJcSxhJbji
LGdmIYaecBwEWYesOuvtJsCyRrnsHrhsXkThcAka

6 rows selected.

SQL> 
SQL> select my_random.string('x',40) from junk;

MY_RANDOM.STRING('X',40)
-----------------------------------------------------
VV3Z1X1T8VICEQNKYBLBB5F0QBQZH9C5R0U8S3SL
P97JKAFF0YYV54IL7UZI7EY0VDK6TF5OLSUVFVFR
APVNEVQZUTR0HRLKNSQDPL3MRTQKZYDHJU5M9PZP
6C7A6R88LK95GIE2IM7ET4HEOXMQGLAU4XOWCCUC
E0PGHRK2GD13UG3U9T1NCJ2PIX2SV8FWMXUAW1G4
ANUA2DPDRPKPVX2WYR9C0ZZ2BWEQHKZJHVXEXR39



Thanks  

John, January 10, 2003 - 10:43 am UTC

Tom, Here is an updated function. I is has the bug fix from above. I have also added a option to get a numeric and alpha (mixed case) string.


FUNCTION string (opt char, len NUMBER)
RETURN VARCHAR2 is -- string of <len> characters
optx char (1) := lower(opt);
lo NUMBER;
rng NUMBER;
n NUMBER;
xstr VARCHAR2 (60) := NULL;
BEGIN
IF optx = 'u' THEN -- upper case alpha characters only
lo := 65; rng := 26; -- ASCII 41 to 5A (hex)
ELSIF optx = 'l' THEN -- lower case alpha characters only
lo := 97; rng := 26; -- ASCII 61 to 7A (hex)
ELSIF optx = 'a' THEN -- alpha characters only (mixed case)
lo := 65; rng := 52; -- ASCII 41 to 5A and 61 to 7A (see below)
ELSIF optx = 'x' THEN -- any alpha-numeric characters (upper)
lo := 48; rng := 36; -- ASCII 30 to 39 and 41 to 5A (see below)
ELSIF optx = 'r' THEN -- any alpha-numeric characters (upper and lower)
lo := 48; rng := 68; -- ASCII 30 to 39 and 41 to 5A and 61 to 7A(see below)
ELSIF optx = 'p' THEN -- any printable characters
lo := 32; rng := 95; -- ASCII 20 to 7E (hex)
ELSE
lo := 65; rng := 26; -- default to upper case
END IF;
FOR i IN 1 .. least(len,60) LOOP
/* Get random ASCII character value in specified range */
n := lo + TRUNC(rng * value); -- between lo and (lo + rng -1)
/* Adjust FOR split range */
IF optx = 'a' AND n > 90 THEN
n := n+6; -- exclude ASCII characters 5B to 60
ELSIF optx = 'x' AND n > 57 THEN
n := n+7; -- exclude ASCII characters 3A to 40
ELSIF optx = 'r' AND (n between 58 and 64
OR n between 91 and 96) THEN
n := n+7; -- exclude ASCII characters 3A to 40
-- and ASCII characters 5B to 60
END IF;
xstr := xstr||chr(n); -- Append character to string
END LOOP;
RETURN xstr;
END string;




Preceding code still has a bug

Daryl, February 21, 2004 - 5:42 pm UTC

John's code is pretty nice. Instead of VALUE within the FOR loop, though, it needs DBMS_RANDOM.VALUE, as show below:

CREATE OR REPLACE FUNCTION string(opt char, len NUMBER)
RETURN VARCHAR2 is -- string of <len> characters
optx char (1) := lower(opt);
lo NUMBER;
rng NUMBER;
n NUMBER;
xstr VARCHAR2 (60) := NULL;
BEGIN
IF optx = 'u' THEN -- upper case alpha characters only
lo := 65; rng := 26; -- ASCII 41 to 5A (hex)
ELSIF optx = 'l' THEN -- lower case alpha characters only
lo := 97; rng := 26; -- ASCII 61 to 7A (hex)
ELSIF optx = 'a' THEN -- alpha characters only (mixed case)
lo := 65; rng := 52; -- ASCII 41 to 5A and 61 to 7A (see below)
ELSIF optx = 'x' THEN -- any alpha-numeric characters (upper)
lo := 48; rng := 36; -- ASCII 30 to 39 and 41 to 5A (see below)
ELSIF optx = 'r' THEN -- any alpha-numeric characters (upper and lower)
lo := 48; rng := 68; -- ASCII 30 to 39 and 41 to 5A and 61 to 7A(see below)
ELSIF optx = 'p' THEN -- any printable characters
lo := 32; rng := 95; -- ASCII 20 to 7E (hex)
ELSE
lo := 65; rng := 26; -- default to upper case
END IF;
FOR i IN 1 .. least(len,60) LOOP
/* Get random ASCII character value in specified range */
n := lo + TRUNC(rng * dbms_random.value); -- between lo and (lo + rng -1)
/* Adjust FOR split range */
IF optx = 'a' AND n > 90 THEN
n := n+6; -- exclude ASCII characters 5B to 60
ELSIF optx = 'x' AND n > 57 THEN
n := n+7; -- exclude ASCII characters 3A to 40
ELSIF optx = 'r' AND (n between 58 and 64
OR n between 91 and 96) THEN
n := n+7; -- exclude ASCII characters 3A to 40
-- and ASCII characters 5B to 60
END IF;
xstr := xstr||chr(n); -- Append character to string
END LOOP;
RETURN xstr;
END string



Are Oracle Passwords Case-Insensitive?

Jack, March 31, 2005 - 11:01 am UTC

Tom,

Are Oracle passwords case-insensitive?  See example below.

If so, any reason why?  Doesn't that greatly simplify password guessing (the number of possible values in each position is nearly half what it could be)?

You showed above that an equal sign (=) is off limits in a password.  Can you point to documentation that shows what characters are legal?  Thanks.

- Jack


SQL> create user scot identified by "TiGeRs#3";

User created.

SQL> grant create session to scot;

Grant succeeded.

SQL> connect scot/tigers#3
Connected.
 

Tom Kyte
March 31, 2005 - 11:25 am UTC

they are not case sensitive, for "why" we'd have to go back to 1980 or thereabouts.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_84a.htm#2081150 <code>



Any ideas?

A reader, March 03, 2006 - 5:22 pm UTC

Tom,
any ideas of what's going on??

Record 4: Rejected - Error on table table_1, column OUT_DATE. ORA-01861: literal does not match format string

and my date in the file I want to slqldr is in
this format 040109 1032

Thanks!

Tom Kyte
March 03, 2006 - 8:09 pm UTC

nope, why not? because NO EXAMPLE OF ANYTHING is provided.


I would say "your data doesn't match your format" based on the error message.

I have no idea what the number 040109 1032 represents
I have no idea what control file you used
I have no idea what the record looks like.

nothing.

follow up

A reader, March 06, 2006 - 9:49 am UTC

040109 1032 is yymmdd 10:32 time

control file read as follows:

...
date_1 date 'yyyy/mm/dd hh24:mi:ss'
....

Tom Kyte
March 08, 2006 - 4:03 pm UTC

full example, you know, something I can easily, quickly, rapidly cut and paste and run.

create table
full ctl file with a begindata

sort of like I give you. (funny, now that I look at the question, I'm wondering why we are talking about sqlldr here at all?)

YossiN, July 14, 2008 - 1:42 am UTC

Dear Daryl,
I don't think numbers can the first character of a password

SQL> select strings('x',8) from dual;

STRINGS('X',8)
----------------------------------------------
9WHB17MQ

SQL> alter user system identified by 9WHB17MQ;
alter user system identified by 9WHB17MQ
                                *
ERROR at line 1:
ORA-00988: missing or invalid password(s)

Tom Kyte
July 15, 2008 - 9:24 am UTC

ops$tkyte%ORA10GR2> alter user scott identified by "42"
  2  /

User altered.

ops$tkyte%ORA10GR2> connect scott/42
Connected.
scott%ORA10GR2> 

YossiN, July 14, 2008 - 1:57 am UTC

Found a simple solution:
SQL> select strings('u',1)||strings('x',7) password from dual;

PASSWORD
--------------------------------------------------------------
BJY8K4Q1


strings ??

Narendra, July 15, 2008 - 11:19 am UTC

Yossin,

What is this "strings" function ? Is it part of oracle or a custom function ?
Tom Kyte
July 15, 2008 - 8:07 pm UTC

ops$tkyte%ORA10GR2> select dbms_random.string('X',8) from dual;

DBMS_RANDOM.STRING('X',8)
-------------------------------------------------------------------------------
CSXEGWI6



looks like they just wrote a wrapper function to invoke the supplied dbms_random package.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library