Solved

powerrbuilder logiin

Posted on 2008-10-17
7
438 Views
Last Modified: 2013-12-26
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,
0
Comment
Question by:sam15
  • 4
  • 3
7 Comments
 
LVL 1

Expert Comment

by:themdx
Comment Utility
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
 

Author Comment

by:sam15
Comment Utility
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
 
LVL 1

Accepted Solution

by:
themdx earned 500 total points
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:sam15
Comment Utility
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
 
LVL 1

Expert Comment

by:themdx
Comment Utility
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
 

Author Comment

by:sam15
Comment Utility
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
 
LVL 1

Expert Comment

by:themdx
Comment Utility
correct
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now