Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

powerrbuilder logiin

Posted on 2008-10-17
7
Medium Priority
?
446 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
ID: 22747634
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
ID: 22748133
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 1500 total points
ID: 22755123
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:sam15
ID: 22759045
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
ID: 22793528
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
ID: 22795875
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
ID: 22801472
correct
0

Featured Post

New Tabletop Appliances Blow Competitors Away!

WatchGuard’s new T15, T35 and T55 tabletop UTMs provide the highest-performing security inspection in their class, allowing users at small offices, home offices and distributed enterprises to experience blazing-fast Internet speeds without sacrificing enterprise-grade security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing emails are a popular malware delivery vehicle for attack.  While there are many ways for an attacker to increase the chances of success for their phishing emails, one of the most effective methods involves spoofing the message to appear to …
Your business may be under attack from a silent enemy that is hard to detect. It works stealthily in the shadows to access and exploit your critical business information, sensitive confidential data and intellectual property, for commercial gain. T…
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

972 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