Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Implementing web sessions and password protection under OAS

Implementing web sessions and password protection under OAS

From: Foo Man Choo SE <foomanchoose_at_yahoo.com>
Date: 26 Sep 2001 12:16:57 -0700
Message-ID: <424fd2be.0109261116.2de3cc41@posting.google.com>


We are using OAS 4.0.8 under Oracle 8.

We have a need to password protect access to PL/SQL generated web pages. Since OAS doesn't directly support the concept of browser sessions and session variables like ASP and JSP, we are being forced to implement our own scheme using browser cookies and a "session" database table.

I would appreciate any advise or comments on the validity of the design.

The general design is as follows:

Assuming there are three PL/SQL procs to generate pages: LOGIN.sql, LOGIN_PROC.sql and FOO.sql. The idea is that the user is forced to enter a valid userid and password via LOGIN before they can visit page FOO. The logic in the pages is as follows:

LOGIN.sql



-Gets URL of target page passed in as a parameter
  1. Simple html form to prompt for userid/password
  2. The URL parameter is saved as a hidden form field
  3. Upon form submit via Post, LOGIN_PROC.sql is invoked

LOGIN_PROC.sql



-URL of target page is passed in as a form field from LOGIN and saved
  1. Validate userid/password using separate authentication external proc.
  2. If validation fails, redisplay LOGIN page with a "invalid login" message
  3. If validation is successful:
    • Add new row to SESSION table (see below)
    • A new unique SessionId key (sequence field) is generated by DB
    • Userid, password are saved in the corresponding fields
    • The current date/time is saved in TimeStamp field
    • The status flag is set to "ACTIVE"
  4. The SessionId is saved in a browser cookie
  5. The page pointed to by the URL parameter (e.g. FOO.sql) is invoked

FOO.sql


  1. Retrieve SessionId from browser cookie
  2. If SessionId doesn't exist, the LOGIN page is invoked with parm FOO.sql
  3. If SessionId exists:
    • The row with this key is retrieved from the SESSION table
    • If the status flag is not set to "ACTIVE", the LOGIN page is invoked
    • If the status flag is "ACTIVE", the TimeStamp field is updated
  4. The FOO page is generated and displayed normally

SESSION TABLE



-consists of 5 fields:
 SessionID (generated sequence field)
 Userid
 Password
 TimeStamp (successful access to a secondary page results in an update)
 Status ("ACTIVE" or "INACTIVE")

Other Background Functionality


  1. A stored procedure runs every 5 minutes and sets the status field to "INACTIVE" for every row with a timestamp value older than 20 minutes ago.
  2. An independent stored procedure runs nightly to delete all records with a status of "INACTIVE"

Hope this provides enough of an idea of what we are trying to achieve.  Does it appear to be a workable approach of implementing the rough equivalent of sessions? Are there any security holes (I'm only talking rudimentary security here) in this approach? Thanks!

--Rob Received on Wed Sep 26 2001 - 14:16:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US