Z-Archive » Maybank Projects » M2U UPASS Project

Rem =====================================================================

Rem   NAME

Rem     <tb_am_tac.sql>

Rem   DESCRIPTION

Rem     <UPASS TAC Table>

Rem   MODULE

Rem     <Authentication Module>

Rem   MODIFIED  (MM/DD/YYYY)

Rem        Danniell   03/12/2010 - Creation

Rem        xxxxxx           mm/dd/yyyy - Modify

Rem =====================================================================

CREATE TABLE TB_AM_TAC

(

  T_USER_ID          NUMBER(9) CONSTRAINT NN_TAC_USER_ID NOT NULL,

  T_TAC_CIPHERTEXT   VARCHAR2(64 BYTE),

  T_TAC_STATE        NUMBER(1)                  DEFAULT 0 CONSTRAINT NN_TAC_TAC_STATE NOT NULL,

  T_DATE_GENERATED   TIMESTAMP(6)               DEFAULT SYSTIMESTAMP CONSTRAINT NN_TAC_DATE_GENERATED NOT NULL,

  T_DATE_FIRST_USED  TIMESTAMP(6),

  T_DATE_LAST_USED   TIMESTAMP(6),

  T_USE_COUNT        NUMBER(6)                  DEFAULT 0 CONSTRAINT NN_TAC_USE_COUNT NOT NULL,

  T_ERROR_COUNT      NUMBER(2)                  DEFAULT 0 CONSTRAINT NN_TAC_ERROR_COUNT NOT NULL,

  T_SESSION_ID       VARCHAR2(32 BYTE)

)

TABLESPACE USERS

PCTUSED    0

PCTFREE    20

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          16K

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

CREATE UNIQUE INDEX PK_TAC_USER_ID ON TB_AM_TAC

(T_USER_ID)

LOGGING

TABLESPACE USERS

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          16K

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

NOPARALLEL;

ALTER TABLE TB_AM_TAC ADD (

  CONSTRAINT PK_TAC_USER_ID

 PRIMARY KEY

 (T_USER_ID)

    USING INDEX

    TABLESPACE USERS

    PCTFREE    10

    INITRANS   2

    MAXTRANS   255

    STORAGE    (

                INITIAL          16K

                MINEXTENTS       1

                MAXEXTENTS       UNLIMITED

                PCTINCREASE      0

               ));

ALTER TABLE TB_AM_TAC ADD (

  CONSTRAINT FK_TAC_USER_ID

 FOREIGN KEY (T_USER_ID)

 REFERENCES TB_AM_USER (U_USER_ID)

    ON DELETE CASCADE);