Hi anybody,
how can I execute a command like ‘connect user/pass@dbname’ from within a stored procedure? It works directly entered into the editor screen of toad, but it doesn’t work when placed between a begin…end block.
It seems to be necessary to open temporarily another session since the verify_function_11g doesn’t check the old password which ist askin for.
thanks in advance
Hi,
I think you are somehow in wrong street.
Why would you do that? Please explain your problem … if usera is OK then give grants to that user to run statements (as it will be user connected in your ask). This is how Oracle works.
Going to open new session from PLSQL is not a good way (imagine what will be if password change?).
Cheers,
Damir
Hi Damir,
thanks for going into discussion on my problem. As I mentioned before, the use of verify_function_11g is not modern security handling since the validation rule “new password must differ from old password by at least three characters” is not honestly enforced. You can provide any string as old password and function passes by if you do so while new password is exactly the old. The reason is: verify_function doesn’t check, if “old password” given by parameter is actually the valid password.
Depending on security purposes it is exactly what has to be done: check if old password is the real one and only if it is true, you can enforce the “differ rule”.
Acting from outside the server there is no problem. Within certain programming environments you oftenly open two sessions for the same user and old password can be prechecked. But if dba is signing on as psql-user, compiler regets all my attempts in creating a short-term second connection, which could validate the given old password.
Hi,
Your initial question was:
how can I execute a command like 'connect user/pass@dbname' from within a stored procedure?
And I answered it.
Now you are mentioning something else (password verify function).
Please make up your mind.
Brg
Damir
Hi,
I think, you already lost the road …
Please read my first question carefully. It is built from several sentences … There you can find everyting.
Maybe you feel a little better, if you can explain to somebody “how oracle works” instead of trying to get into his problem? Real world is much more interesting and - of course - more complicated than most of database providers anticipate. Please go and look into verify_function_10g and try to recognize, how oracle tests if new password differs from old password. It’s a joke.
Your reply to my question has been a reprehension, not an answer and no solution to my problem.
And please realize: I have been very polite after getting your first reply.
regards
Wilko
You mentioned the password verify function not working. There’s information on why that happens at http://qdosmsq.dunbar-it.co.uk/blog/2013/11/so-how-do-you-change-a-users-password/ which might help.
Cheers,
Norm [ TeamT ]
–
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Hi Norm,
thanks for your response. I read your link. As I understand, it is even worse. You can give any old password you want. If it is different by three characters from the new one, you will get a password accepted, which is exactly the old one. Therfore I asked for a way to temporarily create another session with logging in by given old password. That seemed to be the only way to verify the old pwd. I can do that in any programming environment like c++, vb etc. But I’m forced to provide a pl/sql to do the verification rule.
regards
Wilko
Evening Wilko,
On 02/04/15 07:32, wilko.helms_796 wrote:
thanks for your response. I read your link. As I understand, it is even
worse. You can give any old password you want.If it is different by
three characters from the new one, you will get a password accepted,
which is exactly the old one.
I'm possibly not understanding you there. Are you saying that you can give ant old password at all, and the password verification will get a new password accepted?
So, you can change the old password to exactly the same and Oracle accepts it?
If so, how are you changing the password. There are only two ways to get a NOT NULL into the old_password that is passed to the validation PL/SQL code:
When the user whose password is needing changed, calls the "PASSWORD" function.
When the user whose password needs changing calls "ALTER USER me IDENTIFIED BY my_new_password REPLACE old_password;".
There is no other time when Oracle will pass the old password to the verification function I'm afraid. So, if the SYS user is running an "ALTER USER xxx IDENTIFIED BY new_password;" or "ALTER USER xxx IDENTIFIED BY new_password REPLACE anything_here;" it makes no difference, there will never be an old_password in the verification code, it will be NULL.
That's just the way it is I'm afraid.
Therfore I asked for a way to temporarily
create another session with logging in by given old password. That
seemed to be the only way to verify the old pwd. I can do that in any
programming environment like c++, vb etc. But I'm forced to provide a
pl/sql to do the verification rule.
You could, in the old days, get the old password (pre 10g) from SYS.USER$.PASSWORD but it was hashed. I wrote something about that years ago for Jonathan Lewis it's over at http://www.jlcomp.demon.co.uk/faq/changepassword.html but is well out of date for 10g upwaqrds. There they password is stored in the SYS.USER$.SPARE_4 column and is 64 hex digits long, if I remember.
You could work with those perhaps? Maybe! Good luck.
As far as I'm aware there isn't a way you can easily connect from PL/SQl to another session, but maybe you could create an external DLL (or lib file on the server) and create an external C function that does create a separate connection. Then you define a PROC or Function in the database(s) affected which call out to that C code and return a value indicating if the connection worked.
You might need an EXTPROC listener setting up though, and they come with some security issues.
Using Toad, see if you can F4 (describe) on the SYS.DBMS_STANDARD package, then look at something like the DATABASE_NAME function for an example of creating an Oracle function that calls out to a C code external routine.
I believe similar things can be done with Java also.
-- Cheers,
Norm. [TeamT]
Hi Wilko,
here you are, details on calling external C or Java routines, might be helpful for what you are after:
http://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_ex.htm
– Cheers,
Norm. [TeamT]
Hi Norm,
thanks for your help. It seems to be the only way calling an external program just to open another connection. Is little too much stuff, since I hoped, could be done within my oracle procedures.
Client side is VB and there I can run two connections concurrently while one of them is just used, to open a second short session for the already logged in user. The outcome would verify the “old password” is valid ( i.e. the right user is sitting in front of screen ) and then let user introduce a new password. Cost is one of the grace sessions, user can utilize for signon, before user’s account will be locked because of password expiration. I will hold the old password until the second login validates, cancel second session and then call oracle function to change password. This is due to increased security demands to hinder a unauthorized person changing somebody’s password who left the screen without having screen locked.
In my opinion the better way would hve been to do the check inside oracle.
Nevertheless, thanks for your efforts and have a nice day ( in old germany it’s now exactly midnight )
Wilko