| change password if new user [message #418040] |
Wed, 12 August 2009 12:31  |
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
|
Junior Member |
|
|
Hello Everyone,
I would like to force the new user to change his/her password if he/she login for the first time on the database. I have read that I could use EXPIRE the password but I don't have that privilege as a developer so I need to find another way. I have one incomplete solution but I am not sure if it is possible & hoping you can help me out analyze it.
I have a table for user that I think I could use to verify new user.
TBL_USER
USERNO, USERNAME
on my start up form Post-Logon Trigger I put an sql to check if the user exist.
declare
v_user varchar2(100);
begin
select username into v_user from tbl_user where username=user;
if v_user is null then
-- I want to put something here that will bring out a built in form to change password (if there is) or the PASSWORD command
end if;
end;
once I found out that they have change their password I will insert there username on the tbl_user as to identify that it is existing user.
If there is other way to this please help me out.
I'm new to oracle.
Thank you very much.
|
|
|
|
|
|
| Re: change password if new user [message #418053 is a reply to message #418051] |
Wed, 12 August 2009 14:40  |
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
|
Junior Member |
|
|
Hi,
First thank you for your reply. I don't actually have a form right now the you can change a password so the GO_ITEM doesn't seem the right solution. If ever I create one and will call the form for changing a password what will be the code behind it?
Example:
Block_Logon
Item
UserName
PW
Button_OK
so what will be the command to behind the Button_Ok?
Thank you
|
|
|
|
|
|
| Re: change password if new user [message #418058 is a reply to message #418055] |
Wed, 12 August 2009 15:06 |
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
|
Junior Member |
|
|
Hi,
I get your point in identifying new and old user account but my problem is to force to change the password when the user is newly created. The DBA will assign a username and temp/standard password for all new user and they have to change that password immediately when they login on the database. I know about Alter user <username> identified by <TheNewPassword> but I wanted to execute this on the form itself. Based on the example block I provided a while ago I can use the item username and PW to provide the information needed for the "Alter" but I'm getting an error when i do the compilation.
Error:
Encountered the symbol "Alter" when expecting one of the following.
This is the syntax:
DECLARE
V_USERNAME VARCHAR2(100);
V_PW VARCHAR2(100);
V_SQL VARCHAR2(100);
BEGIN
V_USERNAME := :BLOCK_LOGON.USERNAME;
V_PW:=:BLOCK_LOGON.PW;
ALTER USER'||V_USERNAME||'IDENTIFIED BY'||V_PW;
--I'm missing something in here
END;
Thank you,
|
|
|
|
|
|
| Re: change password if new user [message #418061 is a reply to message #418060] |
Wed, 12 August 2009 15:23 |
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
|
Junior Member |
|
|
Same Error, I'm pretty sure I'm missing something here because even I put the syntax Alter user myuserid identified by newpassword (w/o variable) I'm still getting the same error.
But thanks for helping me out.
|
|
|
|
| Re: change password if new user [message #418064 is a reply to message #418040] |
Wed, 12 August 2009 17:29 |
Only-Oracle
Messages: 53
Registered: June 2009
|
Member |
|
|
Hi everybody
May I contribute with you guys.
First of all, I just want to make sure that I understand your demand. You want some users who already expired their password should renew their password. But what is the reason and the benefit you want the users write again the password.
The second thing you have to do two forms, one form for user privilege and in this form you can put one column for expired date and you specify the date that the user should renew their password.
In the first form of login screen there should be two windows with specific canvas one windows with canvas for login entry and the second windows canvas will be transferred in case the user is being expired and they will have the ability and privilege for changing password.
I'll try my bust to do the forms 6i for you. And if anyone did something for forms like this I hope attache it here.
|
|
|
|
| Re: change password if new user [message #418233 is a reply to message #418064] |
Thu, 13 August 2009 09:20 |
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
|
Junior Member |
|
|
Hi again,
This is related to my problem but first I would like to emphasize that that user password is not actually expired they just need to require to change password on their first login because the password that been assigned to new user is only temporary.
On this trigger when button press, why am I getting the same error as not recognize alter?
DECLARE
V_USERNAME VARCHAR2(100);
V_PW VARCHAR2(100);
BEGIN
V_USERNAME := :TBL_SHN_USER.USERNAME;
V_PW:=:TBL_SHN_USER.PW;
ALTER USER ||V_USERNAME|| IDENTIFIED BY ||V_PW;
COMMIT;
END;
Thank you
|
|
|
|
| Re: change password if new user [message #418234 is a reply to message #418040] |
Thu, 13 August 2009 09:23 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You can't use alter and other DDL commands direct from PL/SQL.
You need to use dynamic SQL to do that.
Look up execute immediate.
And you don't need to commit DDL either.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
