Chris,
Here is an alternate method to check password validity. [And like your code, above,
even if someone wants to hack passwords, this code does not tell you what the password is (unless one already knows the password)...it simply confirms what the password isn't.]
First (as you also mentioned earlier), this function depends upon use by a "highly privileged user", specifically one that has, at least, the "
ALTER USER" privilege. (In your coded example, the use must have the "
CREATE DATABASE LINK" privilege for success.) And, (
as our brilliant Tek-Tips colleague, Sem (Dima), reminded me within the last hour):
Sem(Dima) said:
...roles are disabled in stored procedures (and functions)
Therefore, the using user must have (in your case, the "CREATE DATABASE LINK" privilege and) in my case, the "ALTER USER" privilege from an
explicit GRANT. This means that despite the using user being a member of the DBA role, the user must also have explicit use of the "ALTER USER" privilege. To make this happen, you can:
Code:
SQL> connect system/<password>@<tns alias>
SQL> grant ALTER USER to <using user>;
SQL> -- or, in your earlier case: [i]grant CREATE DATABASE LINK to <using user>;[/i]
Then, connect as the user that you want to own the user-defined function that checks for password validity, and compile the password-checking function:
Code:
****************************************************************************************
create or replace function check_password
(user_in varchar2, password_in varchar2)
return varchar2
is
hold_existing_pw_value varchar2(30);
hold_check_pw_value varchar2(30);
sql_stm varchar2(500);
pragma autonomous_transaction;
begin
begin
select password
into hold_existing_pw_value
from dba_users
where username=upper(user_in);
exception
when no_data_found then
return 'User ['||user_in||'] does not exist.';
end;
sql_stm := 'alter user '||user_in||' identified by '||password_in;
execute immediate sql_stm;
select password
into hold_check_pw_value
from dba_users
where username=upper(user_in);
sql_stm := 'alter user '||user_in||' identified by values '||hold_existing_pw_value;
if hold_existing_pw_value = hold_check_pw_value then
return '['||password_in||'] is a valid password for user ['||user_in||'].';
else
return '['||password_in||'] is an invalid password for user ['||user_in||'].';
end if;
end;
/
Function created.
You may also want to create a PUBLIC synonym for any function that multiple users will access so that they do not need to qualify the function name with a schema name:
Code:
conn system/<password>@<tns alias>
create public synonym check_password for <owning_schema>.check_password;
Synonym created.
Then, we can test the function.
Test 1 -- Use by non-privileged user:
Code:
SQL> conn fred/fred
Connected.
SQL> select check_password('test','test') from dual;
select check_password('test','test') from dual
*
ERROR at line 1:
ORA-01031: insufficient privileges
Test 2 -- Use by privileged user for valid username/password:
Code:
conn dhunt/<password>
col x heading "User/Password Check" format a50
select check_password('test','test') x from dual;
User/Password Check
-------------------------------------------
[test] is a valid password for user [test].
Test 3 -- ...Valid user, bad password:
Code:
select check_password('test','blah') x from dual;
User/Password Check
----------------------------------------------
[blah] is an invalid password for user [test].
Test 4 -- ...Invalid user:
Code:
select check_password('blah','blah') x from dual;
User/Password Check
---------------------------
User [blah] does not exist.
Let us know if this is an interesting alternative.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
[
Providing low-cost remote Database Admin services]
Click here to join
Utah Oracle Users Group on Tek-Tips if you use
Oracle in
Utah USA.
![[banghead]](/data/assets/smilies/banghead.gif "[banghead] [banghead]")
