Solved

powerrbuilder logiin

Posted on 2008-10-17
7
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In this blog we highlight approaches to managed security as a service.  We also look into ConnectWise’s value in aiding MSPs’ security management and indicate why critical alerting is a necessary integration.
This article is written by John Gates, CISSP. Gates, the SNUG President-Elect, currently holds the position of Manager of Information Systems at Lake Park High School in Roselle, Illinois.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
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…

635 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