powerrbuilder logiin

I have a powerbuilder application that connects to oracle 9i db using one db account/schma. All users have one account in an application table and user enters the userid/password into pb screen which  connects to DB using one master account to validate the userid/password.

A new security requirement forbids storing userids/passwords in client software and mandates audit trails. WE are thinking of creating one database account per user and have each user connect to his own schema which will be assigned privileges/role to the application schema.

If i create one db account per user is it possible to let user log in his username/password and then pass that to the DB for access.  how is it implemented?

2.  The new rules require us to enforce several password rules like
- minimum 8 characters
-  no repeating characters
-  one underscore, one upper case, on lowercase character
- change password every 30 days.
etc.

Can i create an oracle function to enforce those rules so when user enters the info, i check if he needs to change the password and prompt him with some screen to change it or retunr in valid message? or do we need some coding in PB.

thank you,
sam15Asked:
Who is Participating?
 
themdxConnect With a Mentor Commented:
You can leave blank username & password when you run aplication. Your application should have a logon screen, which allows end users put their username and password.
After Scott enters SCOTT/TIGER and press login, your source code should change properties like that:
sqlca.DBMS="O90 Oracle9i (9.0.1)"
sqlca.database   = "XXXXXXX"
sqlca.LogId      = sle_login.Text
sqlca.LogPass    = sle_password.Text
sqlca.ServerName = sle_server.Text
0
 
themdxCommented:
1.) You can't do that!
Solution:
- Create public synonym for each your table, sequence.
- Create a role
- Grant access right to above public synonym
- Create for each staff a Oracle Account, and grant the role that you just create
So your staff can connect to App via Oracle username/passwd and you don't have to store password in client side.
2.) Modify the role with your password rules, it will effects to all users
0
 
sam15Author Commented:
thanks for you response.

I am trying to see how will this work.  I can do all the things you described in oracle. But what are the changes that need to be done in PB to accomplish this. My understanding is PB has a connection profile where you define the Data source, userid and password for database. Would you leave those blank when you create application?

Let us say I created an oracle account for Scott/Tiger. Scott wants to access the application. He clicks on the app.exe which opens up the main windows with userid/password prompt. So now he enters Scott/Tiger and press "login". What happens next? How would PB connect to database and validate that Scott/Tiger is a valid user or no valid user before he gives him access to application.

THank you,
0
Improved Protection from Phishing Attacks

WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!

 
sam15Author Commented:
themdx:

If scott entered wrong password, connection will not occur to DB. so you show the user the DB message that password is not valid?

Let me confirm two more things with you:
1. if I want to Allow user to change his password, I assume you will create a menu item in PB, which opens a data window with old password and new password and then connect to DB with old password, run an oracle "change_user_password" function to validate if new password meets security rules and then confirm back to user (accpetance or rejection). is this correct?

2. After each user login, I want to check  when was last time he logged in (ie over 6o days) and force him to change the pasword. I assume you do an oracle system level trigger for this.

thanks a lot
0
 
themdxCommented:
That's easy to show message when wrong password, such as:

CONNECT;
IF sqlca.SQLCode = -1 THEN
      MessageBox("SQL Error " + String(sqlca.SQLDBCode), sqlca.SQLErrText + &
            "~n~rCould not login to Oracle!")

1. Change password:
- Check the maching new password 1 and new password 2 (confirm new password)
- Connect to database with old password. If sucess, change the password by following Oralce SQL Command:
ALTER USER scott IDENTIFIED BY new_password;

2. You can record the time when user login, and the next time, you can compare the current time with last time that user logon to the system. If the period is over 60 day, you can display changing password screen, and user need change password before doing any thing.


0
 
sam15Author Commented:
thanks, great info

it seems in PB you can have many data windows created and you can display any window based on the result coming back from DB function/procedure? is this correct.

For example,
user enters correct userid/password--->display application main window
user enters incorrect userid/passwrod---->display PB message that it is invalid based on (-1) sql code.
user enters correct userid/pass but his password expire---->display PB change password screen
etc.
0
 
themdxCommented:
correct
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.