rem ----------------------------------------------------------------------------
rem File:        $RCSfile: wsgsso.sql $
rem Author:      $Author: bcopy $
rem Date:        $Date: 2001/10/30 11:40:37 $
rem Version:     $Revision: 1.4 $
rem Status:      $State: Exp $
rem Locked_by:   $Locker:  $
rem Description: <Enter global description here>
rem Notes:       <Global notes>
rem RealAuth:    <Full real name of code author>
rem IncepDate:   <nnth Xxxember 1998>
rem Copyright:   (c) Oracle Corporation 1998.  All Rights Reserved.
rem ----------------------------------------------------------------------------
rem Log of Changes from RCS
rem ----------------------------------------------------------------------------
rem $Log: wsgsso.sql $
rem Revision 1.4  2001/10/30  11:40:37  bcopy
rem B1956579 : Added comments to update wsgp.pkb when session table changes.
rem
rem Revision 1.3  2001/04/19  16:30:37  rclewley
rem Username should be 100, not 1000. doh.
rem
rem Revision 1.2  2001/04/19  11:31:39  rclewley
rem Increased user_name column to 1000 to macth portal username length.
rem
rem Revision 1.1  2001/04/03  09:12:50  rclewley
rem Initial revision
rem
rem Revision 1.1  1998/11/06  14:54:36  ipenning
rem Initial revision
rem
rem ----------------------------------------------------------------------------
rem  

PROMPT
PROMPT Creating wsgsso_constants package
PROMPT Cancel URL is the URL that the user is redirected to
PROMPT when clicking the cancel button on the SSO Login screen.

create or replace package wsgsso_constants is

g_cancel_url constant varchar2(1000) := '&cancel_url';

end wsgsso_constants;
/
SHOW ERROR

PROMPT Creating Table 'WSGSEC_USERRESP' 
CREATE TABLE WSGSSO_USERRESP
 (RESPONSIBILITY_NAME  VARCHAR2(30) NOT NULL
 ,USER_NAME  VARCHAR2(100) NOT NULL
 )
/

PROMPT Creating Table 'WSGSSO_USER' 
CREATE TABLE WSGSSO_USER
 (USER_NAME  VARCHAR2(100) NOT NULL
 ,PASSWORD  VARCHAR2(30) NOT NULL
 )
/

PROMPT Creating Table 'WSGSSO_PERM' 
CREATE TABLE WSGSSO_PERM
 (RESPONSIBILITY_NAME  VARCHAR2(30) NOT NULL
 ,PACKAGE_NAME  VARCHAR2(30) NOT NULL
 )
/

rem B1956579 : Update WSGP.PKB when changing CLIENT_ID data type
PROMPT Creating Table 'WSGSSO_SESSIONS' 
CREATE TABLE WSGSSO_SESSIONS
 (CLIENT_ID  VARCHAR2(240) NOT NULL
 ,USER_NAME  VARCHAR2(100) NOT NULL
 ,IP_ADDRESS  VARCHAR2(30) NOT NULL
 ,EXPIRES  DATE
 )
/

PROMPT Creating Table 'WSGSSO_RESP' 
CREATE TABLE WSGSSO_RESP
 (RESPONSIBILITY_NAME  VARCHAR2(30) NOT NULL
 )
/

PROMPT Creating Primary Key on 'WSGSSO_USERRESP'
ALTER TABLE WSGSSO_USERRESP ADD CONSTRAINT
 WSGSSO_USERRESP_PK PRIMARY KEY 
  (RESPONSIBILITY_NAME
  ,USER_NAME)
/

PROMPT Creating Primary Key on 'WSGSSO_USER'
ALTER TABLE WSGSSO_USER ADD CONSTRAINT
 WSGSSO_USER_PK PRIMARY KEY 
  (USER_NAME)
/

PROMPT Creating Primary Key on 'WSGSSO_PERM'
ALTER TABLE WSGSSO_PERM ADD CONSTRAINT
 WSGSSO_PERM_PK PRIMARY KEY 
  (PACKAGE_NAME
  ,RESPONSIBILITY_NAME)
/

PROMPT Creating Primary Key on 'WSGSSO_SESSIONS'
ALTER TABLE WSGSSO_SESSIONS ADD CONSTRAINT
 WSGSSO_SESSIONS_PK PRIMARY KEY 
  (CLIENT_ID)
/

PROMPT Creating Primary Key on 'WSGSSO_RESP'
ALTER TABLE WSGSSO_RESP ADD CONSTRAINT
 WSGSSO_RESP_PK PRIMARY KEY 
  (RESPONSIBILITY_NAME)
/
 

PROMPT Creating Foreign Keys on 'WSGSSO_USERRESP'
ALTER TABLE WSGSSO_USERRESP ADD CONSTRAINT
 USERNAME_FK FOREIGN KEY 
  (USER_NAME) REFERENCES WSGSSO_USER
  (USER_NAME) 
 ADD CONSTRAINT
 RESPONSIBILITY_FK FOREIGN KEY 
  (RESPONSIBILITY_NAME) REFERENCES WSGSSO_RESP
  (RESPONSIBILITY_NAME)
/
 

PROMPT Creating Foreign Keys on 'WSGSSO_PERM'
ALTER TABLE WSGSSO_PERM ADD CONSTRAINT
 RESP_PERM_FK FOREIGN KEY 
  (RESPONSIBILITY_NAME) REFERENCES WSGSSO_RESP
  (RESPONSIBILITY_NAME)
/

PROMPT
PROMPT Installing Single Sign-On security package
@@wsgsso.pks
@@wsgsso.pkb
rem  
rem ----------------------------------------------------------------------------
rem $$Header_is_done
rem  End of file $RCSfile: wsgsso.sql $
rem ----------------------------------------------------------------------------






