Oracle database tables and stored procedures configuration

Use this guide as a reference to create Oracle Database (DB) tables and stored procedures for SecureAuth® Identity Platform. This allows you to use Oracle DB for membership and/or profile information.

Prerequisites

Identity Platform release 23.07 or later
Oracle DB data store
Add an Oracle DB data store in the Identity Platform
Ensure ports are open to allow connection to the Oracle DB data store
Basic Oracle DB knowledge to deploy the scripts below. You can download the sample scripts: oracle2307.zip

CREATE TABLE samples

There are two separate scripts for the sample CREATE TABLES (as denoted in the title of the code script):

No company / domain – transmits only the userID and password through the Identity Platform
With company / domain – transmits more information like the company name or domain, in addition to the userID and password through the Identity Platform

User table

This table contains the User ID, Password, and Profile Information.

USERS (company / domain)

CREATE TABLE "ADMIN"."USERS" 
   (	"USERNAME" VARCHAR2(200 BYTE),
              "PASSWORD" VARCHAR2(200 BYTE),
              "EMAIL1" VARCHAR2(200 BYTE),
              "PHONE1" VARCHAR2(200 BYTE),
              "FIRSTNAME" VARCHAR2(200 BYTE),
              "LASTNAME" VARCHAR2(200 BYTE),
              "EMAIL2" VARCHAR2(200 BYTE),
              "PHONE2" VARCHAR2(200 BYTE),
              "ANSWERS" VARCHAR2(1000 BYTE),
              "QUESTIONS" VARCHAR2(1000 BYTE),
              "ANSWERSCH" VARCHAR2(1000 BYTE),
              "QUESTIONSCH" VARCHAR2(1000 BYTE),
              "FAILEDATTEMPTS" NUMBER DEFAULT 0,
              "LOCKED" NUMBER DEFAULT 0,
              "GROUPLIST" VARCHAR2(1000 BYTE),
              "PASSWORDCHANGEDATE" DATE,
              "EMAIL3" VARCHAR2(200 BYTE),
              "EMAIL4" VARCHAR2(200 BYTE),
              "PHONE3" VARCHAR2(200 BYTE),
              "PHONE4" VARCHAR2(200 BYTE),
              "AUXID1" VARCHAR2(512 BYTE),
              "AUXID2" VARCHAR2(512 BYTE),
              "AUXID3" VARCHAR2(512 BYTE),
              "AUXID4" VARCHAR2(512 BYTE),
              "AUXID5" VARCHAR2(512 BYTE),
              "AUXID6" VARCHAR2(512 BYTE),
              "AUXID7" VARCHAR2(512 BYTE),
              "AUXID8" VARCHAR2(512 BYTE),
              "AUXID9" VARCHAR2(512 BYTE),
              "AUXID10" VARCHAR2(512 BYTE),
              "PINHASH" VARCHAR2(120 BYTE),
              "CERTRESETDATE" VARCHAR2(50 BYTE),
              "CERTCOUNT" VARCHAR2(50 BYTE),
              "MOBILERESETDATE" VARCHAR2(50 BYTE),
              "MOBILECOUNT" VARCHAR2(50 BYTE),
              "EXTSYNCPWDDATE" VARCHAR2(50 BYTE),
              "CERTSERIALNUMBER" VARCHAR2(50 BYTE),
              "HARDWARETOKEN" VARCHAR2(1000 BYTE),
              "IOSDEVICES" VARCHAR2(1000 BYTE),
              "OATHSEED" VARCHAR2(1000 BYTE),
              "ONETIMEOATHLIST" VARCHAR2(1000 BYTE),
              "COOKIEKEYS" VARCHAR2(1000 BYTE),
              "MULTIFACTORTHROTTLE", VARCHAR2(512 BYTE),
			  "COMPANY" VARCHAR2(200 BYTE), 
			  "UUID" RAW(16)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

USERS (no company / domain)

CREATE TABLE "ADMIN"."USERS" 
   (	"USERNAME" VARCHAR2(200 BYTE),
              "PASSWORD" VARCHAR2(200 BYTE),
              "EMAIL1" VARCHAR2(200 BYTE),
              "PHONE1" VARCHAR2(200 BYTE),
              "FIRSTNAME" VARCHAR2(200 BYTE),
              "LASTNAME" VARCHAR2(200 BYTE),
              "EMAIL2" VARCHAR2(200 BYTE),
              "PHONE2" VARCHAR2(200 BYTE),
              "ANSWERS" VARCHAR2(1000 BYTE),
              "QUESTIONS" VARCHAR2(1000 BYTE),
              "ANSWERSCH" VARCHAR2(1000 BYTE),
              "QUESTIONSCH" VARCHAR2(1000 BYTE),
              "FAILEDATTEMPTS" NUMBER DEFAULT 0,
              "LOCKED" NUMBER DEFAULT 0,
              "GROUPLIST" VARCHAR2(1000 BYTE),
              "PASSWORDCHANGEDATE" DATE,
              "EMAIL3" VARCHAR2(200 BYTE),
              "EMAIL4" VARCHAR2(200 BYTE),
              "PHONE3" VARCHAR2(200 BYTE),
              "PHONE4" VARCHAR2(200 BYTE),
              "AUXID1" VARCHAR2(512 BYTE),
              "AUXID2" VARCHAR2(512 BYTE),
              "AUXID3" VARCHAR2(512 BYTE),
              "AUXID4" VARCHAR2(512 BYTE),
              "AUXID5" VARCHAR2(512 BYTE),
              "AUXID6" VARCHAR2(512 BYTE),
              "AUXID7" VARCHAR2(512 BYTE),
              "AUXID8" VARCHAR2(512 BYTE),
              "AUXID9" VARCHAR2(512 BYTE),
              "AUXID10" VARCHAR2(512 BYTE),
              "PINHASH" VARCHAR2(120 BYTE),
              "CERTRESETDATE" VARCHAR2(50 BYTE),
              "CERTCOUNT" VARCHAR2(50 BYTE),
              "MOBILERESETDATE" VARCHAR2(50 BYTE),
              "MOBILECOUNT" VARCHAR2(50 BYTE),
              "EXTSYNCPWDDATE" VARCHAR2(50 BYTE),
              "CERTSERIALNUMBER" VARCHAR2(50 BYTE),
              "HARDWARETOKEN" VARCHAR2(1000 BYTE),
              "IOSDEVICES" VARCHAR2(1000 BYTE),
              "OATHSEED" VARCHAR2(1000 BYTE),
              "ONETIMEOATHLIST" VARCHAR2(1000 BYTE),
              "COOKIEKEYS" VARCHAR2(1000 BYTE),
              "MULTIFACTORTHROTTLE", VARCHAR2(512 BYTE),
			  "UUID" RAW(16)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

Access History table

This table contains Access History information for each user.

USERAH (company / domain)

CREATE TABLE "ADMIN"."USERAH" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "ACCESSHISTORY" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 LOB ("ACCESSHISTORY") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

USERAH (no company / domain)

CREATE TABLE "ADMIN"."USERAH" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "ACCESSHISTORY" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 LOB ("ACCESSHISTORY") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

Fingerprint table

This table contains Browser / Device Fingerprinting information for each user.

USERFP (company / domain)

CREATE TABLE "ADMIN"."USERFP" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "DIGITALFP" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 LOB ("DIGITALFP") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

USERFP (no company / domain)

CREATE TABLE "ADMIN"."USERFP" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "DIGITALFP" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 LOB ("DIGITALFP") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

PUSH Notification table

This table contains PUSH Notification token information for each user.

USERPN (company / domain)

CREATE TABLE "ADMIN"."USERPN" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "PNTOKEN" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 LOB ("PNTOKEN") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

USERPN (no company / domain)

CREATE TABLE "ADMIN"."USERPN" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "PNTOKEN" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 LOB ("PNTOKEN") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

OATH Token table

This table contains OATH Token information for each user.

USEROT (company / domain)

CREATE TABLE "ADMIN"."USEROT" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "OATHTOKEN" CLOB
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" 
 LOB ("OATHTOKEN") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING ) ;

USEROT (no company / domain)

CREATE TABLE "ADMIN"."USEROT" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "OATHTOKEN" CLOB
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" 
 LOB ("OATHTOKEN") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING ) ;

IP Addresses table

This table contains the IP address information for each user.

IPADDRESSES (company / domain)

CREATE TABLE "ADMIN"."IPADDRESSES" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "IPADDRESS" VARCHAR2(100 BYTE), 
        "SUCCESS" NUMBER, 
        "TIMESTAMP" TIMESTAMP (6)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

IPADDRESSES (no company / domain)

CREATE TABLE "ADMIN"."IPADDRESSES" 
   (    "USER_ROWID" ROWID NOT NULL ENABLE, 
        "IPADDRESS" VARCHAR2(100 BYTE), 
        "SUCCESS" NUMBER, 
        "TIMESTAMP" TIMESTAMP (6)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

Stored Procedure samples

The following are sample stored procedures for use with the Identity Platform Oracle DB membership and profile providers.

There are two separate scripts for the sample stored procedures (as denoted in the title of the code script):

Without company / domain – for passing only the userID and password through the Identity Platform
With company / domain – for passing more information like the company name or domain, in addition to the userID and password through the Identity Platform

Get User

Stored procedure checks if the username exists, and returns that same username in the case that it does.

GETUSER (company / domain)

create or replace PROCEDURE GETUSER 
(
  USERID IN VARCHAR2 
, RETVAL OUT VARCHAR2 
, USERGROUPS OUT VARCHAR2
, PWDLASTSET OUT DATE
) IS
POS INTEGER;
CP VARCHAR2(50);
UN VARCHAR2(50);
BEGIN
POS := INSTR (USERID, '\', 1, 1);
CP := SUBSTR(USERID, 1, POS-1);
UN := SUBSTR(USERID, POS+1);
  SELECT USERNAME, GROUPLIST, PASSWORDCHANGEDATE INTO RETVAL, USERGROUPS, PWDLASTSET FROM USERS WHERE USERNAME = UN AND COMPANY = CP;
END GETUSER;

GETUSER (no company / domain)

create or replace PROCEDURE GETUSER 
(
  USERID IN VARCHAR2 
, RETVAL OUT VARCHAR2 
, USERGROUPS OUT VARCHAR2
, PWDLASTSET OUT DATE
) AS 
BEGIN
  SELECT USERNAME, GROUPLIST, PASSWORDCHANGEDATE INTO RETVAL, USERGROUPS, PWDLASTSET FROM USERS WHERE USERNAME = USERID;
END GETUSER;

Create User

Stored procedure inserts the username and password into the user table, and returns a MembershipCreateStatus enumeration.

CREATEUSER (company / domain)

create or replace PROCEDURE CREATEUSER 
(
  USERID IN VARCHAR2 
, PWD IN VARCHAR2 
) AS 
POS INTEGER;
CP VARCHAR2(50);
UN VARCHAR2(50);
BEGIN
POS := INSTR (USERID, '\', 1, 1);
CP := SUBSTR(USERID, 1, POS-1);
UN := SUBSTR(USERID, POS+1);
  INSERT INTO USERS (USERNAME,COMPANY,PASSWORD, UUID) VALUES (UN,CP,PWD, sys_guid());
END CREATEUSER;

CREATEUSER (no company / domain)

create or replace PROCEDURE CREATEUSER 
(
  USERID IN VARCHAR2 
, PWD IN VARCHAR2 
) AS 
BEGIN
  INSERT INTO USERS (USERNAME,PASSWORD, UUID) VALUES (USERID,PWD, sys_guid());
END CREATEUSER;

Update User

Stored procedure updates the user profile with the given profile information.

UPDATEUSER (company / domain)

create or replace PROCEDURE UPDATEUSER 
(
  USERID IN VARCHAR2 
, SUCCESS IN NUMBER
, IPADDRESS IN VARCHAR2
) AS 
 FAILCOUNT NUMBER := 0;
 USER_ID ROWID;
POS INTEGER;
CP VARCHAR2(50);
UN VARCHAR2(50);
BEGIN
POS := INSTR (USERID, '\', 1, 1);
CP := SUBSTR(USERID, 1, POS-1);
UN := SUBSTR(USERID, POS+1);

SELECT FAILEDATTEMPTS+1, ROWID INTO FAILCOUNT, USER_ID FROM USERS
  WHERE USERNAME = UN AND COMPANY = CP;

IF SUCCESS > 0 THEN
  FAILCOUNT := 0;
END IF;

  UPDATE USERS 
  SET FAILEDATTEMPTS = FAILCOUNT
  WHERE USERNAME = UN AND COMPANY = CP;
  COMMIT;

  INSERT INTO IPADDRESSES (USER_ROWID,IPADDRESS,SUCCESS,TIMESTAMP) VALUES (USER_ID,IPADDRESS,SUCCESS,SYSDATE);
  
END UPDATEUSER;

UPDATEUSER (no company / domain)

create or replace PROCEDURE UPDATEUSER 
(
  USERID IN VARCHAR2 
, SUCCESS IN NUMBER
, IPADDRESS IN VARCHAR2
) AS 
 FAILCOUNT NUMBER := 0;
 USER_ID ROWID;
BEGIN

SELECT FAILEDATTEMPTS+1, ROWID INTO FAILCOUNT, USER_ID FROM USERS
  WHERE USERNAME = USERID;

IF SUCCESS > 0 THEN
  FAILCOUNT := 0;
END IF;

  UPDATE USERS 
  SET FAILEDATTEMPTS = FAILCOUNT
  WHERE USERNAME = USERID;
  COMMIT;

  INSERT INTO IPADDRESSES (USER_ROWID,IPADDRESS,SUCCESS,TIMESTAMP) VALUES (USER_ID,IPADDRESS,SUCCESS,SYSDATE);
  
END UPDATEUSER;

Get / Validate Password

Stored procedure gets the password, password salt, and password format. Either stored procedure (GETPASSWORD or VALIDATEPASSWORD) can be used depending on the password format selected (Clear, Encrypted, or Hashed) in the Oracle DB data store properties.

Use VALIDATEUSER stored procedure if Clear is selected, otherwise, use the GETPASSWORD stored procedure.

GETPASSWORD (company / domain)

create or replace PROCEDURE GETPASSWORD 
(
  USERID IN VARCHAR2 
, RETVAL OUT VARCHAR2 
) IS
POS INTEGER;
CP VARCHAR2(50);
UN VARCHAR2(50);
BEGIN
POS := INSTR (USERID, '\', 1, 1);
CP := SUBSTR(USERID, 1, POS-1);
UN := SUBSTR(USERID, POS+1);
  SELECT PASSWORD INTO RETVAL FROM USERS WHERE USERNAME = UN AND COMPANY = CP;
END GETPASSWORD;

GETPASSWORD (no company / domain)

create or replace PROCEDURE GETPASSWORD 
(
  USERID IN VARCHAR2 
, RETVAL OUT VARCHAR2 
) AS 
BEGIN
  SELECT PASSWORD INTO RETVAL FROM USERS WHERE USERNAME = USERID;
END GETPASSWORD;

VALIDATEUSER (company / domain)

create or replace PROCEDURE VALIDATEUSER 
(
  USERID IN VARCHAR2
, PWD IN VARCHAR2
, RETVAL OUT VARCHAR2 
) IS
POS INTEGER;
CP VARCHAR2(50);
UN VARCHAR2(50);
BEGIN
POS := INSTR (USERID, '\', 1, 1);
CP := SUBSTR(USERID, 1, POS-1);
UN := SUBSTR(USERID, POS+1);
  SELECT USERNAME INTO RETVAL FROM USERS WHERE USERNAME = UN AND COMPANY = CP;
END VALIDATEUSER;

VALIDATEUSER (no company / domain)

create or replace PROCEDURE VALIDATEUSER 
(
  USERID IN VARCHAR2
, PWD IN VARCHAR2
, RETVAL OUT VARCHAR2 
) AS 
BEGIN
  SELECT USERNAME INTO RETVAL FROM USERS WHERE USERNAME = USERID AND PASSWORD = PWD;
END VALIDATEUSER;

Reset Password

Stored procedure resets the password for the given user.

RESETPASSWORD (company / domain)

create or replace PROCEDURE RESETPASSWORD 
(
  USERID IN VARCHAR2 
, NEWPWD IN VARCHAR2 
) AS 
POS INTEGER;
CP VARCHAR2(50);
UN VARCHAR2(50);
BEGIN
POS := INSTR (USERID, '\', 1, 1);
CP := SUBSTR(USERID, 1, POS-1);
UN := SUBSTR(USERID, POS+1);
  UPDATE USERS SET PASSWORD = NEWPWD, PASSWORDCHANGEDATE = SYSDATE WHERE USERNAME = UN AND COMPANY = CP;
END RESETPASSWORD;

RESETPASSWORD (no company / domain)

create or replace PROCEDURE RESETPASSWORD 
(
  USERID IN VARCHAR2 
, NEWPWD IN VARCHAR2 
) AS 
BEGIN
  UPDATE USERS SET PASSWORD = NEWPWD, PASSWORDCHANGEDATE = SYSDATE WHERE USERNAME = USERID;
END RESETPASSWORD;

Change Password

Stored procedure updates the password for the given user.

CHANGEPASSWORD (company /domain)

create or replace PROCEDURE CHANGEPASSWORD 
(
  USERID IN VARCHAR2 
, NEWPWD IN VARCHAR2 
, OLDPWD IN VARCHAR2 
) AS 
POS INTEGER;
CP VARCHAR2(50);
UN VARCHAR2(50);
BEGIN
POS := INSTR (USERID, '\', 1, 1);
CP := SUBSTR(USERID, 1, POS-1);
UN := SUBSTR(USERID, POS+1);
  UPDATE USERS SET PASSWORD = NEWPWD, PASSWORDCHANGEDATE = SYSDATE WHERE USERNAME = UN AND COMPANY = CP;
END CHANGEPASSWORD;

CHANGEPASSWORD (no company / domain)

create or replace PROCEDURE CHANGEPASSWORD 
(
  USERID IN VARCHAR2 
, NEWPWD IN VARCHAR2 
, OLDPWD IN VARCHAR2 
) AS 
BEGIN
  UPDATE USERS SET PASSWORD = NEWPWD, PASSWORDCHANGEDATE = SYSDATE WHERE USERNAME = USERID;
END CHANGEPASSWORD;

Get User Profile

Stored procedure retrieves the profile of the given username.

GETUSERPROFILE (company / domain)

create or replace procedure GETUSERPROFILE
(
  USERID IN VARCHAR2,
  CP IN VARCHAR2(50);
  FIRSTNAME OUT VARCHAR2,
  LASTNAME OUT VARCHAR2,
  PHONE1 OUT VARCHAR2,
  PHONE2 OUT VARCHAR2,
  PHONE3 OUT VARCHAR2,
  PHONE4 OUT VARCHAR2,
  EMAIL1 OUT VARCHAR2,
  EMAIL2 OUT VARCHAR2,
  EMAIL3 OUT VARCHAR2,
  EMAIL4 OUT VARCHAR2,
  AUXID1 OUT VARCHAR2,
  AUXID2 OUT VARCHAR2,
  AUXID3 OUT VARCHAR2,
  AUXID4 OUT VARCHAR2,
  AUXID5 OUT VARCHAR2,
  AUXID6 OUT VARCHAR2,
  AUXID7 OUT VARCHAR2,
  AUXID8 OUT VARCHAR2,
  AUXID9 OUT VARCHAR2,
  AUXID10 OUT VARCHAR2,
  GROUPLIST OUT VARCHAR2,
  QUESTIONS OUT VARCHAR2,
  ANSWERS OUT VARCHAR2,
  PINHASH OUT VARCHAR2,
  CERTRESETDATE OUT VARCHAR2,
  CERTCOUNT OUT VARCHAR2,
  MOBILERESETDATE OUT VARCHAR2,
  MOBILECOUNT OUT VARCHAR2,
  EXTSYNCPWDDATE OUT VARCHAR2,
  CERTSERIALNUMBER OUT VARCHAR2,
  HARDWARETOKEN OUT VARCHAR2,
  IOSDEVICES OUT VARCHAR2,
  OATHSEED OUT VARCHAR2,
  ONETIMEOATHLIST OUT VARCHAR2,
  COOKIEKEYS OUT VARCHAR2,
  MULTIFACTORTHROTTLE OUT VARCHAR2,
  QUESTIONSCH OUT VARCHAR2,
  ANSWERSCH OUT VARCHAR2,
  UUID OUT RAW,
  DFP OUT CLOB,
  AH OUT CLOB,
  PN OUT CLOB,
  OT OUT CLOB
) AS
USER_ID ROWID;
BEGIN

   UPDATE USERS SET UUID = SYS_GUID() WHERE UUID IS NULL AND USERNAME = USERID;

   SELECT U.FIRSTNAME,U.LASTNAME,U.PHONE1,U.PHONE2,U.EMAIL1,U.EMAIL2,U.GROUPLIST,U.QUESTIONS,U.ANSWERS,U.ROWID,U.EMAIL3,U.EMAIL4,U.PHONE3,U.PHONE4,U.AUXID1,U.AUXID2,U.AUXID3,U.AUXID4,U.AUXID5,U.AUXID6,U.AUXID7,U.AUXID8,U.AUXID9,U.AUXID10,U.PINHASH, U.CERTRESETDATE,U.CERTCOUNT,U.MOBILERESETDATE,U.MOBILECOUNT,U.EXTSYNCPWDDATE,U.CERTSERIALNUMBER,U.HARDWARETOKEN,U.IOSDEVICES,U.OATHSEED,U.ONETIMEOATHLIST,U.COOKIEKEYS,U.QUESTIONSCH,U.ANSWERSCH, U.UUID, U.MULTIFACTORTHROTTLE
    INTO FIRSTNAME,LASTNAME,PHONE1,PHONE2,EMAIL1,EMAIL2,GROUPLIST,QUESTIONS,ANSWERS,USER_ID,EMAIL3,EMAIL4,PHONE3,PHONE4,AUXID1,AUXID2,AUXID3,AUXID4,AUXID5,AUXID6,AUXID7,AUXID8,AUXID9,AUXID10,PINHASH,CERTRESETDATE,CERTCOUNT,MOBILERESETDATE,MOBILECOUNT,EXTSYNCPWDDATE,CERTSERIALNUMBER,HARDWARETOKEN,IOSDEVICES,OATHSEED,ONETIMEOATHLIST,COOKIEKEYS,QUESTIONSCH,ANSWERSCH, UUID, MULTIFACTORTHROTTLE FROM USERS U WHERE UPPER(U.USERNAME) = UPPER(USERID);
  BEGIN
    SELECT DIGITALFP INTO DFP FROM USERFP WHERE USER_ROWID = USER_ID;
  EXCEPTION
    WHEN no_data_found THEN
    DFP := '';
  END;
  BEGIN
    SELECT ACCESSHISTORY INTO AH FROM USERAH WHERE USER_ROWID = USER_ID;
  EXCEPTION
    WHEN no_data_found THEN
    AH := '';
  END;
  BEGIN
    SELECT PNTOKEN INTO PN FROM USERPN WHERE USER_ROWID = USER_ID;
  EXCEPTION
    WHEN no_data_found THEN
    PN := '';
  END;
  BEGIN
    SELECT OATHTOKEN INTO OT FROM USEROT WHERE USER_ROWID = USER_ID;
  EXCEPTION
    WHEN no_data_found THEN
    OT := '';
  END;
END GETUSERPROFILE;

GETUSERPROFILE (no company / domain)

create or replace procedure GETUSERPROFILE
(
  USERID IN VARCHAR2,
  FIRSTNAME OUT VARCHAR2,
  LASTNAME OUT VARCHAR2,
  PHONE1 OUT VARCHAR2,
  PHONE2 OUT VARCHAR2,
  PHONE3 OUT VARCHAR2,
  PHONE4 OUT VARCHAR2,
  EMAIL1 OUT VARCHAR2,
  EMAIL2 OUT VARCHAR2,
  EMAIL3 OUT VARCHAR2,
  EMAIL4 OUT VARCHAR2,
  AUXID1 OUT VARCHAR2,
  AUXID2 OUT VARCHAR2,
  AUXID3 OUT VARCHAR2,
  AUXID4 OUT VARCHAR2,
  AUXID5 OUT VARCHAR2,
  AUXID6 OUT VARCHAR2,
  AUXID7 OUT VARCHAR2,
  AUXID8 OUT VARCHAR2,
  AUXID9 OUT VARCHAR2,
  AUXID10 OUT VARCHAR2,
  GROUPLIST OUT VARCHAR2,
  QUESTIONS OUT VARCHAR2,
  ANSWERS OUT VARCHAR2,
  PINHASH OUT VARCHAR2,
  CERTRESETDATE OUT VARCHAR2,
  CERTCOUNT OUT VARCHAR2,
  MOBILERESETDATE OUT VARCHAR2,
  MOBILECOUNT OUT VARCHAR2,
  EXTSYNCPWDDATE OUT VARCHAR2,
  CERTSERIALNUMBER OUT VARCHAR2,
  HARDWARETOKEN OUT VARCHAR2,
  IOSDEVICES OUT VARCHAR2,
  OATHSEED OUT VARCHAR2,
  ONETIMEOATHLIST OUT VARCHAR2,
  COOKIEKEYS OUT VARCHAR2,
  MULTIFACTORTHROTTLE OUT VARCHAR2,
  QUESTIONSCH OUT VARCHAR2,
  ANSWERSCH OUT VARCHAR2,
  UUID OUT RAW,
  DFP OUT CLOB,
  AH OUT CLOB,
  PN OUT CLOB,
  OT OUT CLOB
) AS
USER_ID ROWID;
BEGIN

   UPDATE USERS SET UUID = SYS_GUID() WHERE UUID IS NULL AND USERNAME = USERID;

   SELECT U.FIRSTNAME,U.LASTNAME,U.PHONE1,U.PHONE2,U.EMAIL1,U.EMAIL2,U.GROUPLIST,U.QUESTIONS,U.ANSWERS,U.ROWID,U.EMAIL3,U.EMAIL4,U.PHONE3,U.PHONE4,U.AUXID1,U.AUXID2,U.AUXID3,U.AUXID4,U.AUXID5,U.AUXID6,U.AUXID7,U.AUXID8,U.AUXID9,U.AUXID10,U.PINHASH, U.CERTRESETDATE,U.CERTCOUNT,U.MOBILERESETDATE,U.MOBILECOUNT,U.EXTSYNCPWDDATE,U.CERTSERIALNUMBER,U.HARDWARETOKEN,U.IOSDEVICES,U.OATHSEED,U.ONETIMEOATHLIST,U.COOKIEKEYS,U.QUESTIONSCH,U.ANSWERSCH, U.UUID, U.MULTIFACTORTHROTTLE
    INTO FIRSTNAME,LASTNAME,PHONE1,PHONE2,EMAIL1,EMAIL2,GROUPLIST,QUESTIONS,ANSWERS,USER_ID,EMAIL3,EMAIL4,PHONE3,PHONE4,AUXID1,AUXID2,AUXID3,AUXID4,AUXID5,AUXID6,AUXID7,AUXID8,AUXID9,AUXID10,PINHASH,CERTRESETDATE,CERTCOUNT,MOBILERESETDATE,MOBILECOUNT,EXTSYNCPWDDATE,CERTSERIALNUMBER,HARDWARETOKEN,IOSDEVICES,OATHSEED,ONETIMEOATHLIST,COOKIEKEYS,QUESTIONSCH,ANSWERSCH, UUID, MULTIFACTORTHROTTLE FROM USERS U WHERE UPPER(U.USERNAME) = UPPER(USERID);
  BEGIN
    SELECT DIGITALFP INTO DFP FROM USERFP WHERE USER_ROWID = USER_ID;
  EXCEPTION
    WHEN no_data_found THEN
    DFP := '';
  END;
  BEGIN
    SELECT ACCESSHISTORY INTO AH FROM USERAH WHERE USER_ROWID = USER_ID;
  EXCEPTION
    WHEN no_data_found THEN
    AH := '';
  END;
  BEGIN
    SELECT PNTOKEN INTO PN FROM USERPN WHERE USER_ROWID = USER_ID;
  EXCEPTION
    WHEN no_data_found THEN
    PN := '';
  END;
  BEGIN
    SELECT OATHTOKEN INTO OT FROM USEROT WHERE USER_ROWID = USER_ID;
  EXCEPTION
    WHEN no_data_found THEN
    OT := '';
  END;
END GETUSERPROFILE;

Update User Profile

Stored procedure updates user profile with the given profile information.

UPDATEUSERPROFILE (company / domain)

create or replace PROCEDURE UPDATEUSERPROFILE
(
  USERID IN VARCHAR2
, CP IN VARCHAR2
, FN IN VARCHAR2
, LN IN VARCHAR2
, PH1 IN VARCHAR2
, PH2 IN VARCHAR2
, EM1 IN VARCHAR2
, EM2 IN VARCHAR2
, QUES IN USERS.QUESTIONS%TYPE
, ANS IN USERS.ANSWERS%TYPE
, CHQUES IN USERS.QUESTIONSCH%TYPE
, CHANS IN USERS.ANSWERSCH%TYPE
, DFP IN CLOB
, AH IN CLOB
, PN IN CLOB
, OT IN CLOB
, PH3 IN VARCHAR2
, PH4 IN VARCHAR2
, EM3 IN VARCHAR2
, EM4 IN VARCHAR2
, AUX1 IN VARCHAR2
, AUX2 IN VARCHAR2
, AUX3 IN VARCHAR2
, AUX4 IN VARCHAR2
, AUX5 IN VARCHAR2
, AUX6 IN VARCHAR2
, AUX7 IN VARCHAR2
, AUX8 IN VARCHAR2
, AUX9 IN VARCHAR2
, AUX10 IN VARCHAR2
, PIN IN VARCHAR2
, CRD IN VARCHAR2
, CC IN VARCHAR2
, MRD IN VARCHAR2
, MC IN VARCHAR2
, ESPD IN VARCHAR2
, CSN IN VARCHAR2
, HT IN VARCHAR2
, IOS IN VARCHAR2
, OATH IN VARCHAR2
, OATHLIST IN VARCHAR2
, COOKIEKEYS IN VARCHAR2
, MFATHROTTLE IN VARCHAR2
) IS
 USER_ID ROWID;
BEGIN
  UPDATE USERS
  SET 
  FIRSTNAME=FN,
  LASTNAME=LN,
  PHONE1=PH1,
  PHONE2=PH2,
  PHONE3=PH3,
  PHONE4=PH4,
  EMAIL1=EM1,
  EMAIL2=EM2,
  QUESTIONS=QUES,
  ANSWERS=ANS,
  QUESTIONSCH=CHQUES,
  ANSWERSCH=CHANS,
  EMAIL3=EM3,
  EMAIL4=EM4,
  AUXID1=AUX1,
  AUXID2=AUX2,
  AUXID3=AUX3,
  AUXID4=AUX4,
  AUXID5=AUX5,
  AUXID6=AUX6,
  AUXID7=AUX7,
  AUXID8=AUX8,
  AUXID9=AUX9,
  AUXID10=AUX10,
  PINHASH=PIN,
  CERTRESETDATE=CRD,
  CERTCOUNT=CC,
  MOBILERESETDATE=MRD,
  MOBILECOUNT=MC,
  EXTSYNCPWDDATE=ESPD,
  CERTSERIALNUMBER=CSN,
  HARDWARETOKEN=HT,
  IOSDEVICES=IOS,
  OATHSEED=OATH,
  ONETIMEOATHLIST=OATHLIST,
  COOKIEKEYS=COOKIEKEYS,
  MULTIFACTORTHROTTLE=MFATHROTTLE
  WHERE UPPER(USERNAME) = UPPER(USERID) AND UPPER(COMPANY) = UPPER(CP);
  COMMIT;

  SELECT ROWID INTO USER_ID FROM USERS
  WHERE UPPER(USERNAME) = UPPER(USERID) AND UPPER(COMPANY) = UPPER(CP);
  
  DELETE FROM USERFP WHERE USER_ROWID = USER_ID;
  INSERT INTO USERFP (USER_ROWID,DIGITALFP) VALUES (USER_ID,DFP);
    
  DELETE FROM USERAH WHERE USER_ROWID = USER_ID;
  INSERT INTO USERAH (USER_ROWID,ACCESSHISTORY) VALUES (USER_ID,AH);
    
  DELETE FROM USERPN WHERE USER_ROWID = USER_ID;
  INSERT INTO USERPN (USER_ROWID,PNTOKEN) VALUES (USER_ID,PN);
    
  DELETE FROM USEROT WHERE USER_ROWID = USER_ID;
  INSERT INTO USEROT (USER_ROWID,OATHTOKEN) VALUES (USER_ID,OT);

END UPDATEUSERPROFILE;

UPDATEUSERPROFILE (no company / domain)

create or replace PROCEDURE UPDATEUSERPROFILE
(
  USERID IN VARCHAR2
, FN IN VARCHAR2
, LN IN VARCHAR2
, PH1 IN VARCHAR2
, PH2 IN VARCHAR2
, EM1 IN VARCHAR2
, EM2 IN VARCHAR2
, QUES IN USERS.QUESTIONS%TYPE
, ANS IN USERS.ANSWERS%TYPE
, CHQUES IN USERS.QUESTIONSCH%TYPE
, CHANS IN USERS.ANSWERSCH%TYPE
, DFP IN CLOB
, AH IN CLOB
, PN IN CLOB
, OT IN CLOB
, PH3 IN VARCHAR2
, PH4 IN VARCHAR2
, EM3 IN VARCHAR2
, EM4 IN VARCHAR2
, AUX1 IN VARCHAR2
, AUX2 IN VARCHAR2
, AUX3 IN VARCHAR2
, AUX4 IN VARCHAR2
, AUX5 IN VARCHAR2
, AUX6 IN VARCHAR2
, AUX7 IN VARCHAR2
, AUX8 IN VARCHAR2
, AUX9 IN VARCHAR2
, AUX10 IN VARCHAR2
, PIN IN VARCHAR2
, CRD IN VARCHAR2
, CC IN VARCHAR2
, MRD IN VARCHAR2
, MC IN VARCHAR2
, ESPD IN VARCHAR2
, CSN IN VARCHAR2
, HT IN VARCHAR2
, IOS IN VARCHAR2
, OATH IN VARCHAR2
, OATHLIST IN VARCHAR2
, COOKIEKEYS IN VARCHAR2
, MFATHROTTLE IN VARCHAR2
) IS
 USER_ID ROWID;
BEGIN
  UPDATE USERS
  SET 
  FIRSTNAME=FN,
  LASTNAME=LN,
  PHONE1=PH1,
  PHONE2=PH2,
  PHONE3=PH3,
  PHONE4=PH4,
  EMAIL1=EM1,
  EMAIL2=EM2,
  QUESTIONS=QUES,
  ANSWERS=ANS,
  QUESTIONSCH=CHQUES,
  ANSWERSCH=CHANS,
  EMAIL3=EM3,
  EMAIL4=EM4,
  AUXID1=AUX1,
  AUXID2=AUX2,
  AUXID3=AUX3,
  AUXID4=AUX4,
  AUXID5=AUX5,
  AUXID6=AUX6,
  AUXID7=AUX7,
  AUXID8=AUX8,
  AUXID9=AUX9,
  AUXID10=AUX10,
  PINHASH=PIN,
  CERTRESETDATE=CRD,
  CERTCOUNT=CC,
  MOBILERESETDATE=MRD,
  MOBILECOUNT=MC,
  EXTSYNCPWDDATE=ESPD,
  CERTSERIALNUMBER=CSN,
  HARDWARETOKEN=HT,
  IOSDEVICES=IOS,
  OATHSEED=OATH,
  ONETIMEOATHLIST=OATHLIST,
  COOKIEKEYS=COOKIEKEYS,
  MULTIFACTORTHROTTLE=MFATHROTTLE
  WHERE UPPER(USERNAME) = UPPER(USERID);
  COMMIT;

  SELECT ROWID INTO USER_ID FROM USERS
  WHERE UPPER(USERNAME) = UPPER(USERID);
  
  DELETE FROM USERFP WHERE USER_ROWID = USER_ID;
  INSERT INTO USERFP (USER_ROWID,DIGITALFP) VALUES (USER_ID,DFP);
    
  DELETE FROM USERAH WHERE USER_ROWID = USER_ID;
  INSERT INTO USERAH (USER_ROWID,ACCESSHISTORY) VALUES (USER_ID,AH);
    
  DELETE FROM USERPN WHERE USER_ROWID = USER_ID;
  INSERT INTO USERPN (USER_ROWID,PNTOKEN) VALUES (USER_ID,PN);
    
  DELETE FROM USEROT WHERE USER_ROWID = USER_ID;
  INSERT INTO USEROT (USER_ROWID,OATHTOKEN) VALUES (USER_ID,OT);

END UPDATEUSERPROFILE;

Lock User

Stored procedure locks the account of the given username.

LOCKUSER (company / domain)

create or replace PROCEDURE LOCKUSER 
(
  USERID IN VARCHAR2 
) IS
POS INTEGER;
CP VARCHAR2(50);
UN VARCHAR2(50);
BEGIN
POS := INSTR (USERID, '\', 1, 1);
CP := SUBSTR(USERID, 1, POS-1);
UN := SUBSTR(USERID, POS+1);
  UPDATE USERS 
  SET LOCKED = 1
  WHERE USERNAME = UN AND COMPANY = CP;
  COMMIT;
END LOCKUSER;

LOCKUSER (no company / domain)

create or replace PROCEDURE LOCKUSER 
(
  USERID IN VARCHAR2 
) AS 
BEGIN
  UPDATE USERS 
  SET LOCKED = 1
  WHERE USERNAME=USERID;
  COMMIT;
END LOCKUSER;

Unlock User

Stored procedure unlocks the account of the given username.

UNLOCKUSER (company / domain)

create or replace PROCEDURE UNLOCKUSER 
(
  USERID IN VARCHAR2 
) AS 
POS INTEGER;
CP VARCHAR2(50);
UN VARCHAR2(50);
BEGIN
POS := INSTR (USERID, '\', 1, 1);
CP := SUBSTR(USERID, 1, POS-1);
UN := SUBSTR(USERID, POS+1);
  UPDATE USERS 
  SET LOCKED = 0
  WHERE USERNAME = UN AND COMPANY = CP;
  COMMIT;
END UNLOCKUSER;

UNLOCKUSER (no company / domain)

CREATE OR REPLACE PROCEDURE UNLOCKUSER 
(
  USERID IN VARCHAR2 
) AS 
BEGIN
  UPDATE USERS 
  SET LOCKED = 0
  WHERE USERNAME=USERID;
  COMMIT;
END UNLOCKUSER;

In this section: