| User unable to change password with "password" command [message #472793] |
Mon, 23 August 2010 23:29  |
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  |
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 #472835 is a reply to message #472793] |
Tue, 24 August 2010 02:42 |
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 #472844 is a reply to message #472793] |
Tue, 24 August 2010 03:50 |
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 #472846 is a reply to message #472844] |
Tue, 24 August 2010 04:00 |
ThomasG
Messages: 3212
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 #472913 is a reply to message #472887] |
Tue, 24 August 2010 13:10 |
ThomasG
Messages: 3212
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 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #472941 is a reply to message #472793] |
Tue, 24 August 2010 19:30 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
