Home » RDBMS Server » Security » User unable to change password with "password" command (Oracle Release 9.2.0.1.0)
User unable to change password with "password" command [message #472793] Mon, 23 August 2010 23:29 Go to next message
rudee
Messages: 5
Registered: August 2010
Junior Member
We haave enble the alter log for audit purpose so the password will be display in the log which is not security.

I try to use "password" to change password but very user got the error below. please help. Thank you in advance.


SQL> password
Changing password for RUDEE
Old password:
New password:
Retype new password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20014: -6502 ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 27

Password unchanged
Re: User unable to change password with "password" command [message #472802 is a reply to message #472793] Tue, 24 August 2010 00:22 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

ORA-00604: error occurred at recursive SQL level 1


It says there is an error. If you can fix .Fix it or else contact oracle support.
Quote:

ORA-20014: -6502 ORA-06502: PL/SQL: numeric or value error


Says..
You are trying to assign non numeric value to a variable that is of numeric data type or numeric or value error: character string buffer too small ( you are assigning a value to a variable where length is exceeding)


Is there any trigger that not allowing to change the password?

Could you please show us the code?

Regards
Ved

[Updated on: Tue, 24 August 2010 01:10]

Report message to a moderator

Re: User unable to change password with "password" command [message #472808 is a reply to message #472802] Tue, 24 August 2010 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is an error at line 27 of the password verify function associated to the user profile.

Regards
Michel
Re: User unable to change password with "password" command [message #472835 is a reply to message #472793] Tue, 24 August 2010 02:42 Go to previous messageGo to next message
rudee
Messages: 5
Registered: August 2010
Junior Member
The trigger are below. I could not find out the line 27.

=====
CREATE OR REPLACE FUNCTION sys.verify_pswd (
username VARCHAR2,
PASSWORD VARCHAR2,
old_password VARCHAR2
)
RETURN BOOLEAN
IS
n BOOLEAN;
m INTEGER;
differ INTEGER;
isdigit BOOLEAN;
islchar BOOLEAN;
isuchar BOOLEAN;
ispunct BOOLEAN;
digitarray VARCHAR2 (20);
punctarray VARCHAR2 (25);
lchararray VARCHAR2 (52);
uchararray VARCHAR2 (52);
BEGIN
digitarray := '0123456789';
uchararray := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
lchararray := 'abcdefghijklmnopqrstuvwxyz';
punctarray := '!"#$%&()``*+,-/:;<=>?_';

-- 1. Check if the password is same as the username
------------------------------------------------------
IF PASSWORD = username
THEN
raise_application_error (-20001, 'Password same as or similar to user');
END IF;


.....
....
...
-- 8. Everything is fine; return TRUE;
------------------------------------------------------
RETURN (TRUE);
exception
when others then
raise_application_error
(-20009,
sqlerrm
);
END;
/

Re: User unable to change password with "password" command [message #472842 is a reply to message #472835] Tue, 24 August 2010 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you can't with the code how can we without the code?

Regards
Michel
Re: User unable to change password with "password" command [message #472844 is a reply to message #472793] Tue, 24 August 2010 03:50 Go to previous messageGo to next message
rudee
Messages: 5
Registered: August 2010
Junior Member
Here are the code.

===
CREATE OR REPLACE FUNCTION sys.verify_pswd (
username VARCHAR2,
PASSWORD VARCHAR2,
old_password VARCHAR2
)
RETURN BOOLEAN
IS
n BOOLEAN;
m INTEGER;
differ INTEGER;
isdigit BOOLEAN;
islchar BOOLEAN;
isuchar BOOLEAN;
ispunct BOOLEAN;
digitarray VARCHAR2 (20);
punctarray VARCHAR2 (25);
lchararray VARCHAR2 (52);
uchararray VARCHAR2 (52);
BEGIN
digitarray := '0123456789';
uchararray := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
lchararray := 'abcdefghijklmnopqrstuvwxyz';
punctarray := '!"#$%&()``*+,-/:;<=>?_';

-- 1. Check if the password is same as the username
------------------------------------------------------
IF PASSWORD = username
THEN
raise_application_error (-20001, 'Password same as or similar to user');
END IF;

-- 2. Check for the minimum length of the password
------------------------------------------------------
IF LENGTH (PASSWORD) < 7
THEN
raise_application_error (-20002, 'Password length less than 7');
END IF;

-- 3. Check if the old password is null
------------------------------------------------------
IF old_password = ''
THEN
raise_application_error (-20003, 'Old password is null');
END IF;

-- 4. Check for the digit
------------------------------------------------------
isdigit := FALSE;
m := LENGTH (PASSWORD);

FOR i IN 1 .. 10
LOOP
FOR j IN 1 .. m
LOOP
IF SUBSTR (PASSWORD, j, 1) = SUBSTR (digitarray, i, 1)
THEN
isdigit := TRUE;
GOTO findlchar;
END IF;
END LOOP;
END LOOP;

IF isdigit = FALSE
THEN

-- 4.1. Check for the punctuation
------------------------------------------------------
<<findpunct>>
ispunct := FALSE;

FOR i IN 1 .. LENGTH (punctarray)
LOOP
FOR j IN 1 .. m
LOOP
IF SUBSTR (PASSWORD, j, 1) = SUBSTR (punctarray, i, 1)
THEN
ispunct := TRUE;
GOTO findlchar;
END IF;
END LOOP;
END LOOP;

IF ispunct = FALSE
THEN
raise_application_error
(-20004,

--'Password should contain at least one digit, one character and one punctuation'
'Password should contain at least one digit or one punctuation'
);
END IF;
END IF;

-- 5. Check for the lower character
------------------------------------------------------
<<findlchar>>
isLchar := FALSE;

FOR i IN 1 .. LENGTH (lchararray)
LOOP
FOR j IN 1 .. m
LOOP
IF SUBSTR (PASSWORD, j, 1) = SUBSTR (lchararray, i, 1)
THEN
islchar := TRUE;
GOTO findUchar;
END IF;
END LOOP;
END LOOP;

IF islchar = FALSE
THEN
raise_application_error
(-20005,
'Password should contain at least one character '
);
END IF;

-- 6. Check for the upper character
------------------------------------------------------
<<finduchar>>
isUchar := FALSE;

FOR i IN 1 .. LENGTH (uchararray)
LOOP
FOR j IN 1 .. m
LOOP
IF SUBSTR (PASSWORD, j, 1) = SUBSTR (uchararray, i, 1)
THEN
isUchar := TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;

IF isuchar = FALSE
THEN
raise_application_error (-20006, 'Password must be of mixed case');
END IF;

<<endsearch>>

-- 7. Check if the password differs from the previous password by at least 3 letters
------------------------------------------------------
differ := LENGTH (old_password) - LENGTH (PASSWORD);

IF ABS (differ) < 3
THEN
IF LENGTH (PASSWORD) < LENGTH (old_password)
THEN
m := LENGTH (PASSWORD);
ELSE
m := LENGTH (old_password);
END IF;

differ := ABS (differ);

FOR i IN 1 .. m
LOOP
IF SUBSTR (PASSWORD, i, 1) != SUBSTR (old_password, i, 1)
THEN
differ := differ + 1;
END IF;
END LOOP;

IF differ < 3
THEN
raise_application_error
(-20007,
'Password should differ by at least 3 characters'
);
END IF;
END IF;

-- 8. Everything is fine; return TRUE;
------------------------------------------------------
RETURN (TRUE);
exception
when others then
raise_application_error
(-20009,
sqlerrm
);
END;
/
Re: User unable to change password with "password" command [message #472845 is a reply to message #472844] Tue, 24 August 2010 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 13643
Registered: September 2008
Location: Rainy Manchester
Senior Member
The error isn't coming from that function (which isn't a trigger despite what you said).
Error no is -20014 - nothing in that code raises that error.

So find the code that raises that error.
And can you please read the orafaq forum guide and follow it's instructions on formatting posts.
Re: User unable to change password with "password" command [message #472846 is a reply to message #472844] Tue, 24 August 2010 04:00 Go to previous messageGo to next message
ThomasG
Messages: 3209
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
- Don't create objects in the SYS schema, the sys schema works differently from every other schema.
- get rid of the "when others then" exception handler to see the real error that is happening.
- Select from the user_source view to see which line really is line 27
Re: User unable to change password with "password" command [message #472851 is a reply to message #472793] Tue, 24 August 2010 04:47 Go to previous messageGo to next message
rudee
Messages: 5
Registered: August 2010
Junior Member
Thank you very much for all.
Re: User unable to change password with "password" command [message #472852 is a reply to message #472851] Tue, 24 August 2010 05:06 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Was the issue been resolved?
It would have help others including me if you post how you solved the problem.

Thanks
Ved
Re: User unable to change password with "password" command [message #472887 is a reply to message #472846] Tue, 24 August 2010 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Don't create objects in the SYS schema, the sys schema works differently from every other schema.

Except the password verify function that can't be created in another schema (for security purpose).

Regards
Michel

[Updated on: Tue, 24 August 2010 10:15]

Report message to a moderator

Re: User unable to change password with "password" command [message #472913 is a reply to message #472887] Tue, 24 August 2010 13:10 Go to previous messageGo to next message
ThomasG
Messages: 3209
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Michel Cadot wrote on Tue, 24 August 2010 17:15

Except the password verify function that can't be created in another schema (for security purpose).


Sounds like an interesting concept to know about. Can you give me a clue in which part of the documentation is described how it works?

A search for both "verify_pswd" or "custom password verification" comes up with nothing on Tahiti, "password verify function" only brings up matches to the EXP-00058 error description of 8i and 9i.

[Updated on: Tue, 24 August 2010 13:12]

Report message to a moderator

Re: User unable to change password with "password" command [message #472920 is a reply to message #472913] Tue, 24 August 2010 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it:
SQL> CREATE OR REPLACE FUNCTION michel.verify_function
  2  (username varchar2,
  3    password varchar2,
  4    old_password varchar2)
  5    RETURN boolean IS 
  6  begin
  7    return true;
  8  end;
  9  /

Function created.

SQL> create profile test limit  PASSWORD_VERIFY_FUNCTION verify_function;
create profile test limit  PASSWORD_VERIFY_FUNCTION verify_function
*
ERROR at line 1:
ORA-07443: function VERIFY_FUNCTION not found


SQL> sho user
USER is "MICHEL"

It searches the function in SYS schema and you cannot specify a schema for the function:
SQL> create profile test limit  PASSWORD_VERIFY_FUNCTION michel.verify_function;
create profile test limit  PASSWORD_VERIFY_FUNCTION michel.verify_function
                                                          *
ERROR at line 1:
ORA-02376: invalid or redundant resource

SQL> @sys
Connected.
SYS> CREATE OR REPLACE FUNCTION sys.verify_function
  2  (username varchar2,
  3    password varchar2,
  4    old_password varchar2)
  5    RETURN boolean IS 
  6  begin
  7    return true;
  8  end;
  9  /

Function created.

SYS> connect michel/michel
Connected.
SQL> drop function michel.verify_function;

Function dropped.

SQL> create profile test limit  PASSWORD_VERIFY_FUNCTION verify_function;

Profile created.

Regards
Michel

[Updated on: Tue, 24 August 2010 13:30]

Report message to a moderator

Re: User unable to change password with "password" command [message #472929 is a reply to message #472920] Tue, 24 August 2010 13:49 Go to previous messageGo to next message
ThomasG
Messages: 3209
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, thanks. Profile. Haven't thought to look in that direction.
Re: User unable to change password with "password" command [message #472933 is a reply to message #472929] Tue, 24 August 2010 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But I never found it in the documentation, just experiment it.
Maybe some day I will submit to Oracle a documentation enhancement request but I have so many enhancement requests in progress...

Regards
Michel
Re: User unable to change password with "password" command [message #472937 is a reply to message #472933] Tue, 24 August 2010 15:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
The 11g documentation refers you to the ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql file, which tells you that the verify_function must be in the sys schema. The documentation for 10g, 9i, and 8i included the function as part of the documentation and also mentioned that it must be in the sys schema.

11g:
http://download.oracle.com/docs/cd/E11882_01/network.112/e10574/authentication.htm#DBSEG33224

10g:
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/policies.htm#sthref831

9i:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/secure.htm#8366

8i:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/secure.htm#2950

Re: User unable to change password with "password" command [message #472938 is a reply to message #472937] Tue, 24 August 2010 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for making the documentation search for us.
I didn't search so hard after all. Smile

Regards
Michel

[Updated on: Tue, 24 August 2010 15:13]

Report message to a moderator

Re: User unable to change password with "password" command [message #472941 is a reply to message #472793] Tue, 24 August 2010 19:30 Go to previous message
rudee
Messages: 5
Registered: August 2010
Junior Member
First of all I like to thank you you all for the support.

The problem is not sloving yet.

1. The English is not my first language. I am Thai people.
2. I think I am the begining of oracle but have to take care as DB admin.
3. I will take sometime to read all answers also the documents from link. And after I understand then I will try to fix it and will go back to tell the result.

I hope to have the website like this in Thailand because I would like to or have to learn more about oracle admin especially security. The company has to comply with SOX.

Smile

Previous Topic: v$xml_audit_trail return no rows
Next Topic: Create or modify user information in OID using DBMS_LDAP.
Goto Forum:
  


Current Time: Tue Jul 23 19:07:55 CDT 2019