Skip to main content

Oracle database tables and stored procedures configuration

Updated May 21, 2021

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 21.04, 22.02, or 22.12

  • 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

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

Access History table

This table contains Access History information for each user.

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)) ;

USERAH (with 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 (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)) ;

USERFP (with 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)) ;

IP Addresses table

This table contains the IP address information for each user.

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" ;

IPADDRESSES (with 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" ;

OATH Token table

This table contains OATH Token information for each user.

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 ) ;

USEROT (with 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 ) ;

PUSH Notification table

This table contains PUSH Notification token information for each user.

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)) ;

USERPN (with 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)) ;

User table

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

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),
              "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 (with 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),
	      "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" ;

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 (no company / domain)

create or replace PROCEDURE GETUSER
(
  USERID IN VARCHAR2
, RETVAL OUT VARCHAR2
, USERGROUPS OUT VARCHAR2
, PWDLASTSET OUT DATE
) AS
LOCKED NUMBER := 0;
BEGIN
  SELECT USERNAME, GROUPLIST, PASSWORDCHANGEDATE, LOCKED INTO RETVAL, USERGROUPS, PWDLASTSET, LOCKED FROM USERS WHERE UPPER(USERNAME) = UPPER(USERID);
   
  IF LOCKED = 1 THEN
    RETVAL := 'LOCKOUT';
  END IF;
END GETUSER;

GETUSER (with company / domain)

create or replace PROCEDURE GETUSER
(
  USERID IN VARCHAR2
, RETVAL OUT VARCHAR2
, USERGROUPS OUT VARCHAR2
, PWDLASTSET OUT DATE
) AS
LOCKED NUMBER := 0;
BEGIN
  SELECT USERNAME, GROUPLIST, PASSWORDCHANGEDATE, LOCKED INTO RETVAL, USERGROUPS, PWDLASTSET, LOCKED FROM USERS WHERE UPPER(USERNAME) = UPPER(USERID);
   
  IF LOCKED = 1 THEN
    RETVAL := 'LOCKOUT';
  END IF;
END GETUSER;

Create User

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

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;

CREATEUSER (with 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;

Update User

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

UPDATEUSER (no company / domain)

create or replace PROCEDURE UPDATEUSER
(
  USERID IN VARCHAR2
, SUCCESS IN NUMBER
, IPADDRESS IN VARCHAR2
) AS
 FAILCOUNT NUMBER := 0;
BEGIN
IF SUCCESS = 0 THEN
  SELECT FAILEDATTEMPTS+1 INTO FAILCOUNT FROM USERS
  WHERE UPPER(USERNAME)=UPPER(USERID);
ELSE
  FAILCOUNT := 0;
END IF;
  UPDATE USERS
  SET FAILEDATTEMPTS = FAILCOUNT
  WHERE UPPER(USERNAME)=UPPER(USERID);
  COMMIT;
  INSERT INTO IPADDRESSES (USER_ROWID,IPADDRESS,SUCCESS,TIMESTAMP) VALUES (USERID,IPADDRESS,SUCCESS,SYSDATE);
   
END UPDATEUSER;

UPDATEUSER (with 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 UPPER(USERNAME) = UPPER(UN) AND UPPER(COMPANY) = UPPER(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;

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 (no company / domain)

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

VALIDATEUSER (no company / domain)

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

GETPASSWORD (with 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 UPPER(USERNAME) = UPPER(UN) AND UPPER(COMPANY) = UPPER(CP);
END GETPASSWORD;

VALIDATEUSER (with 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 UPPER(USERNAME) = UPPER(UN) AND UPPER(COMPANY) = UPPER(CP) AND PASSWORD = PWD;
END VALIDATEUSER;

Reset Password

Stored procedure resets the password for the given user.

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 UPPER(USERNAME) = UPPER(USERID);
END RESETPASSWORD;

RESETPASSWORD (with 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 UPPER(USERNAME) = UPPER(UN) AND UPPER(COMPANY) = UPPER(CP);
END RESETPASSWORD;

Change Password

Stored procedure updates the password for the given user.

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 UPPER(USERNAME) = UPPER(USERID);
END CHANGEPASSWORD;

CHANGEPASSWORD (with 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 UPPER(USERNAME) = UPPER(UN) AND UPPER(COMPANY) = UPPER(CP);
END CHANGEPASSWORD;

Get User Profile

Stored procedure retrieves the profile of the given username.

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,
  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
   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
    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 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 (with 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,
  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
   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
    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 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 (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
) IS
 USER_ID ROWID;
BEGIN
  UPDATE USERS
  SET 
  FIRSTNAME=NVL(FN, FIRSTNAME), 
  LASTNAME=NVL(LN, LASTNAME), 
  PHONE1=NVL(PH1, PHONE1),
  PHONE2=NVL(PH2, PHONE2),
  PHONE3=NVL(PH3, PHONE3),
  PHONE4=NVL(PH4, PHONE4), 
  EMAIL1=NVL(EM1, EMAIL1), 
  EMAIL2=NVL(EM2, EMAIL2),
  QUESTIONS=NVL(QUES, QUESTIONS), 
  ANSWERS=NVL(ANS, ANSWERS),
  QUESTIONSCH=NVL(CHQUES, QUESTIONSCH), 
  ANSWERSCH=NVL(CHANS, ANSWERSCH),
  EMAIL3=NVL(EM3, EMAIL3),
  EMAIL4=NVL(EM4, EMAIL4),
  AUXID1=NVL(AUX1, AUXID1),
  AUXID2=NVL(AUX2, AUXID2),
  AUXID3=NVL(AUX3, AUXID3),
  AUXID4=NVL(AUX4, AUXID4),
  AUXID5=NVL(AUX5, AUXID5),
  AUXID6=NVL(AUX6, AUXID6),
  AUXID7=NVL(AUX7, AUXID7),
  AUXID8=NVL(AUX8, AUXID8),
  AUXID9=NVL(AUX9, AUXID9),
  AUXID10=NVL(AUX10, AUXID10),
  PINHASH=NVL(PIN, PINHASH),
  CERTRESETDATE=NVL(CRD, CERTRESETDATE),
  CERTCOUNT=NVL(CC, CERTCOUNT),
  MOBILERESETDATE=NVL(MRD, MOBILERESETDATE),
  MOBILECOUNT=NVL(MC, MOBILECOUNT),
  EXTSYNCPWDDATE=NVL(ESPD, EXTSYNCPWDDATE),
  CERTSERIALNUMBER=NVL(CSN, CERTSERIALNUMBER),
  HARDWARETOKEN=NVL(HT, HARDWARETOKEN),
  IOSDEVICES=NVL(IOS, IOSDEVICES),
  OATHSEED=NVL(OATH, OATHSEED),
  ONETIMEOATHLIST=NVL(OATHLIST, ONETIMEOATHLIST),
  COOKIEKEYS=NVL(COOKIEKEYS, USERS.COOKIEKEYS)
  WHERE UPPER(USERNAME) = UPPER(USERID);
  COMMIT;

  SELECT ROWID INTO USER_ID FROM USERS
  WHERE UPPER(USERNAME) = UPPER(USERID);

  IF DFP IS NOT NULL THEN
    DELETE FROM USERFP WHERE USER_ROWID = USER_ID;
    INSERT INTO USERFP (USER_ROWID,DIGITALFP) VALUES (USER_ID,DFP);
  END IF;

  IF AH IS NOT NULL THEN
    DELETE FROM USERAH WHERE USER_ROWID = USER_ID;
    INSERT INTO USERAH (USER_ROWID,ACCESSHISTORY) VALUES (USER_ID,AH);
  END IF;

  IF PN IS NOT NULL THEN
    DELETE FROM USERPN WHERE USER_ROWID = USER_ID;
    INSERT INTO USERPN (USER_ROWID,PNTOKEN) VALUES (USER_ID,PN);
  END IF;

  IF OT IS NOT NULL THEN
    DELETE FROM USEROT WHERE USER_ROWID = USER_ID;
    INSERT INTO USEROT (USER_ROWID,OATHTOKEN) VALUES (USER_ID,OT);
  END IF;
   
END UPDATEUSERPROFILE;

UPDATEUSERPROFILE (with 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
) IS
 USER_ID ROWID;
BEGIN
  UPDATE USERS
  SET 
  FIRSTNAME=NVL(FN, FIRSTNAME), 
  LASTNAME=NVL(LN, LASTNAME), 
  PHONE1=NVL(PH1, PHONE1),
  PHONE2=NVL(PH2, PHONE2),
  PHONE3=NVL(PH3, PHONE3),
  PHONE4=NVL(PH4, PHONE4), 
  EMAIL1=NVL(EM1, EMAIL1), 
  EMAIL2=NVL(EM2, EMAIL2),
  QUESTIONS=NVL(QUES, QUESTIONS), 
  ANSWERS=NVL(ANS, ANSWERS),
  QUESTIONSCH=NVL(CHQUES, QUESTIONSCH), 
  ANSWERSCH=NVL(CHANS, ANSWERSCH),
  EMAIL3=NVL(EM3, EMAIL3),
  EMAIL4=NVL(EM4, EMAIL4),
  AUXID1=NVL(AUX1, AUXID1),
  AUXID2=NVL(AUX2, AUXID2),
  AUXID3=NVL(AUX3, AUXID3),
  AUXID4=NVL(AUX4, AUXID4),
  AUXID5=NVL(AUX5, AUXID5),
  AUXID6=NVL(AUX6, AUXID6),
  AUXID7=NVL(AUX7, AUXID7),
  AUXID8=NVL(AUX8, AUXID8),
  AUXID9=NVL(AUX9, AUXID9),
  AUXID10=NVL(AUX10, AUXID10),
  PINHASH=NVL(PIN, PINHASH),
  CERTRESETDATE=NVL(CRD, CERTRESETDATE),
  CERTCOUNT=NVL(CC, CERTCOUNT),
  MOBILERESETDATE=NVL(MRD, MOBILERESETDATE),
  MOBILECOUNT=NVL(MC, MOBILECOUNT),
  EXTSYNCPWDDATE=NVL(ESPD, EXTSYNCPWDDATE),
  CERTSERIALNUMBER=NVL(CSN, CERTSERIALNUMBER),
  HARDWARETOKEN=NVL(HT, HARDWARETOKEN),
  IOSDEVICES=NVL(IOS, IOSDEVICES),
  OATHSEED=NVL(OATH, OATHSEED),
  ONETIMEOATHLIST=NVL(OATHLIST, ONETIMEOATHLIST),
  COOKIEKEYS=NVL(COOKIEKEYS, USERS.COOKIEKEYS)
  WHERE UPPER(USERNAME) = UPPER(USERID);
  COMMIT;

  SELECT ROWID INTO USER_ID FROM USERS
  WHERE UPPER(USERNAME) = UPPER(USERID);

  IF DFP IS NOT NULL THEN
    DELETE FROM USERFP WHERE USER_ROWID = USER_ID;
    INSERT INTO USERFP (USER_ROWID,DIGITALFP) VALUES (USER_ID,DFP);
  END IF;

  IF AH IS NOT NULL THEN
    DELETE FROM USERAH WHERE USER_ROWID = USER_ID;
    INSERT INTO USERAH (USER_ROWID,ACCESSHISTORY) VALUES (USER_ID,AH);
  END IF;

  IF PN IS NOT NULL THEN
    DELETE FROM USERPN WHERE USER_ROWID = USER_ID;
    INSERT INTO USERPN (USER_ROWID,PNTOKEN) VALUES (USER_ID,PN);
  END IF;

  IF OT IS NOT NULL THEN
    DELETE FROM USEROT WHERE USER_ROWID = USER_ID;
    INSERT INTO USEROT (USER_ROWID,OATHTOKEN) VALUES (USER_ID,OT);
  END IF;
   
END UPDATEUSERPROFILE;

Lock User

Stored procedure locks the account of the given username.

LOCKUSER (no company / domain)

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

LOCKUSER (with 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 UPPER(USERNAME) = UPPER(UN) AND UPPER(COMPANY) = UPPER(CP);
  COMMIT;
END LOCKUSER;

Unlock User

Stored procedure unlocks the account of the given username.

UNLOCKUSER (no company / domain)

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

UNLOCKUSER (with 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 UPPER(USERNAME) = UPPER(UN) AND UPPER(COMPANY) = UPPER(CP);
  COMMIT;
END UNLOCKUSER;
In this section:
See also: