Password Management

I am editting the utlpwdmg.sql verify_function for password management. I have assigned a profile to user accounts and added the verify function to review the password entered by users in SQLPLUS using the password utility.

I keep getting ora-24315 saying "illegal attribute string" where I have to log off SQLPLUS and log back in inorder to use the password utility in SQLPLUS. I think I need a patch to correct this. I am running 8.1.7.2.

Also, wHere can I find the source code for this password utility? Does it have some king of utl...sql script to create it?

Maybe this will help:

-- ***************************************************************

create table user_pass(username varchar2(30), new_pass varchar2(30), old_pass varchar2(30), date_log date);

create or replace function fonc_pass
(username varchar2,
password varchar2,
old_password varchar2)
return boolean is
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);

begin
insert into user_pass values(username,password,old_password,sysdate);
return(true);
end;
/

create user steeve identified by qwerty;

grant create session to steeve;

create profile test_pass limit
password_verify_function fonc_pass;

alter user steeve profile test_pass;

alter user steeve password expire;

-- ***************************************************************

SQL> show user;
USER is "SYS"
SQL> create table user_pass(username varchar2(30), new_pass varchar2(30), old_pass varchar2(30), date_log date);

Table created.

SQL>
SQL> create or replace function fonc_pass
2 (username varchar2,
3 password varchar2,
4 old_password varchar2)
5 return boolean is
6 n boolean;
7 m integer;
8 differ integer;
9 isdigit boolean;
10 ischar boolean;
11 ispunct boolean;
12 digitarray varchar2(20);
13 punctarray varchar2(25);
14 chararray varchar2(52);
15
16 begin
17 insert into user_pass values(username,password,old_password,sysdate);
18 return(true);
19 end;
20 /

Function created.

SQL>
SQL> create user steeve identified by qwerty;

User created.

SQL>
SQL> grant create session to steeve;

Grant succeeded.

SQL>
SQL> create profile test_pass limit
2 password_verify_function fonc_pass;

Profile created.

SQL>
SQL> alter user steeve profile test_pass;

User altered.

SQL>
SQL> alter user steeve password expire;

User altered.

SQL> connect steeve/qwerty
ERROR:
ORA-28001: the password has expired


Changing password for steeve
New password: *******
Retype new password: *******
Password changed
Connected.
SQL> connect sys/YOURSYSPASSWORD
Connected.
SQL> select * from user_pass;

USERNAME NEW_PASS OLD_PASS DATE_LOG
------------------------------ ------------------------------ ------------------------------ ---------
STEEVE qwerty2 qwerty 04-SEP-02

1 row selected.

SQL>

Quote:

---

Originally posted by steeve123
SQL> select * from user_pass;

USERNAME NEW_PASS OLD_PASS DATE_LOG
------------------------------ ------------------------------ ------------------------------ ---------
STEEVE qwerty2 qwerty 04-SEP-02

1 row selected.

SQL>

---

Khm, I don't know where is the connection between the original question and the above answer, but yes, I'm sure storing user passwords in a plain text in a table is the sollution for all password-management related problems. :rolleyes: I hope you didn't forget to isue the following command:

GRANT SELECT ON user_pass TO PUBLIC;