Oracle Database Configuration Guide

Introduction

Use this guide along with the Data Tab Configuration guide to configure an Oracle Database-integrated SecureAuth IdP realm.

Prerequisites

1. Have an on-premises Oracle Database with which SecureAuth IdP can integrate

SecureAuth IdP supports versions 11.2 and 12.1, and supports with limitations versions 11.1 and 10.2

2. Create or enable an admin account that has read access (and optional write access) to the tables and stored procedures

Write access is required to update user profile information, such as creating new users and changing passwords

3. Contact SecureAuth Support to download the Oracle Data Access Components and install it onto the SecureAuth IdP appliance

More download information found here

Oracle Database Configuration Steps

Notice

The tables and stored procedures below are examples, and can be used or modified based on the existing environment

Scripts without Company / Domain

If passing only the userID and password through SecureAuth IdP, then use the tables and stored procedures below

See the next section for the tables and stored procedures that pass the company or domain name in addition to the userID and password

Tables

Note

If these tables already exist in the environment, then creating a new one is not required, but changes may be required

Users Table

  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), 
        "FAILEDATTEMPTS" NUMBER DEFAULT 0, 
        "LOCKED" NUMBER DEFAULT 0, 
        "GROUPLIST" VARCHAR2(1000 BYTE), 
        "PASSWORDCHANGEDATE" DATE
   ) 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" ;

User Access Histories Table

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

User Fingerprints Table

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

User Push Notification Table

  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 Tokens Table

  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

  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 Procedures

Notice

The PROCEDURE name (e.g. GETUSER) is defaulted in the SecureAuth IdP Web Admin

If the names are changed in the stored procedure, then be sure to copy them into the Web Admin (see SecureAuth Configuration Steps below for more information)

Get User Stored Procedure

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;

Get Password / Validate User Stored Procedure

Either stored procedure can be utilized depending on the password format selected in the Web Admin

If Clear or MD5 is selected, then use the Validate User stored procedure; if SHA1 or SHA2 is selected, then use the Get Password stored procedure

Refer to the SecureAuth IdP Configuration Steps below for more information

Get Password Stored Procedure

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

Validate User Stored Procedure

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

Update User Stored Procedure

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 USERNAME=USERID;
ELSE
  FAILCOUNT := 0;
END IF;
  UPDATE USERS 
  SET FAILEDATTEMPTS = FAILCOUNT
  WHERE USERNAME=USERID;
  COMMIT;
  INSERT INTO IPADDRESSES (USERNAME,IPADDRESS,SUCCESS,TIMESTAMP) VALUES (USERID,IPADDRESS,SUCCESS,SYSDATE);
  
END UPDATEUSER;

Reset Password Stored Procedure

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

  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;

Create User Stored Procedure

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

Lock User Stored Procedure

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

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

Get Profile Stored Procedure

create or replace PROCEDURE GETUSERPROFILE 
(
  USERID IN VARCHAR2,
  FIRSTNAME OUT VARCHAR2,
  LASTNAME OUT VARCHAR2,
  PHONE1 OUT VARCHAR2,
  PHONE2 OUT VARCHAR2,
  EMAIL1 OUT VARCHAR2,
  EMAIL2 OUT VARCHAR2,
  GROUPLIST OUT VARCHAR2,
  QUESTIONS OUT VARCHAR2,
  ANSWERS OUT VARCHAR2,
  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
    INTO FIRSTNAME,LASTNAME,PHONE1,PHONE2,EMAIL1,EMAIL2,GROUPLIST,QUESTIONS,ANSWERS,USER_ID FROM USERS U WHERE U.USERNAME = 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 Profile Stored Procedure

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
, DFP IN CLOB
, AH IN CLOB
, PN IN CLOB
, OT IN CLOB
) IS 
 USER_ID ROWID;
BEGIN
  UPDATE USERS 
  SET FIRSTNAME=FN, LASTNAME=LN, PHONE1=PH1,
  PHONE2=PH2, EMAIL1=EM1, EMAIL2=EM2, QUESTIONS=QUES, ANSWERS=ANS 
  WHERE USERNAME = USERID;
  COMMIT;
  
  SELECT ROWID INTO USER_ID FROM USERS
  WHERE USERNAME = 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;

Scripts with Company / Domain

If passing more than just the userID and password through SecureAuth IdP (such as userID, password, and company), then use the tables and stored procedures below

See the previous section for the tables and stored procedures that do not pass the company or domain name

Tables

Note

If these tables already exist in the environment, then creating a new one is not required, but changes may be required

Users Table

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), 
        "COMPANY" VARCHAR2(200 BYTE), 
        "FAILEDATTEMPTS" NUMBER DEFAULT 0, 
        "LOCKED" NUMBER DEFAULT 0, 
        "GROUPLIST" VARCHAR2(1000 BYTE), 
        "PASSWORDCHANGEDATE" DATE
   ) 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" ;

User Access Histories Table

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

User Fingerprints Table

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

User Push Notification Table

  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 Tokens Table

  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

  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 Procedures

Notice

The PROCEDURE name (e.g. GETUSER) is defaulted in the SecureAuth IdP Web Admin

If the names are changed in the stored procedure, then be sure to copy them into the Web Admin (see SecureAuth Configuration Steps below for more information)

Get User Stored Procedure

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;

Get Password / Validate User Stored Procedure

Either stored procedure can be utilized depending on the password format selected in the Web Admin (step 4)

If Clear or MD5 is selected, then use the Validate User stored procedure; if SHA1 or SHA2 is selected, then use the Get Password stored procedure

Refer to the SecureAuth IdP Configuration Steps below for more information

Get Password Stored Procedure

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;

Validate User Stored Procedure

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 AND PASSWORD = PWD;
END VALIDATEUSER;

Update User Stored Procedure

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;

Reset Password Stored Procedure

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;

Change Password Stored Procedure

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;

Create User Stored Procedure

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) VALUES (UN,CP,PWD);
END CREATEUSER;

Lock User Stored Procedure

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;

Unlock User Stored Procedure

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;

Get Profile Stored Procedure

create or replace PROCEDURE GETUSERPROFILE 
(
  USERID IN VARCHAR2,
  FIRSTNAME OUT VARCHAR2,
  LASTNAME OUT VARCHAR2,
  PHONE1 OUT VARCHAR2,
  PHONE2 OUT VARCHAR2,
  EMAIL1 OUT VARCHAR2,
  EMAIL2 OUT VARCHAR2,
  GROUPLIST OUT VARCHAR2,
  QUESTIONS OUT VARCHAR2,
  ANSWERS OUT VARCHAR2,
  DFP OUT CLOB,
  AH OUT CLOB,
  PN OUT CLOB
) AS 
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 U.FIRSTNAME,U.LASTNAME,U.PHONE1,U.PHONE2,U.EMAIL1,U.EMAIL2,U.GROUPLIST,U.QUESTIONS,U.ANSWERS,U.ROWID
    INTO FIRSTNAME,LASTNAME,PHONE1,PHONE2,EMAIL1,EMAIL2,GROUPLIST,QUESTIONS,ANSWERS,USER_ID FROM USERS U WHERE U.USERNAME = UN AND U.COMPANY = CP;
  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;  
END GETUSERPROFILE;

Update Profile Stored Procedure

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
, DFP IN CLOB
, AH IN CLOB
, PN IN CLOB
, OT IN CLOB
) IS 
 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);
  UPDATE USERS 
  SET FIRSTNAME=FN, LASTNAME=LN, PHONE1=PH1,
  PHONE2=PH2, EMAIL1=EM1, EMAIL2=EM2, QUESTIONS=QUES, ANSWERS=ANS 
  WHERE USERNAME = UN AND COMPANY = CP;
  COMMIT;
  
  SELECT ROWID INTO USER_ID FROM USERS
  WHERE USERNAME = UN AND COMPANY = 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;

SecureAuth IdP Configuration Steps

Data

Datastore Type

1. Select Oracle from the Type dropdown

Datastore Connection and Credentials

Note

If using CyberArk Vault for credentials, enable Use CyberArk Vault for credentials and follow the steps in CyberArk Password Vault Server and AIM Integration with SecureAuth IdP

2. Provide or modify the given Connection String in the following format:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=[DBName]))); User Id=[username];Password=[password]

Replace localhost (after HOST) with the Server Name or IP Address of the Oracle Database if it is not on the same server

Replace 1522 with the appropriate PORT number if using a different port

Replace [DBName] with the name of the database

Replace [username] with the username of the service account

Replace [password] with the password of the service account

3. Select the Password Format from the dropdown

This setting dictates which Stored Procedure to use in the Oracle Database (Validate Password or Get Password)

4. Provide a unique string of text to append to passwords before they are hashed in the Password Salt field (not necessary for Clear Password Format)

Group Permissions

5. Provide a list of Allowed Groups and / or Denied Groups to restrict access to the realm

Stored Procedure Configuration

6. Leave the Stored Procedure names as the default, or modify them to reflect the names in the Oracle Database Stored Procedures

If using the provided names in the Stored Procedures above, then no changes are required

7. Click Test Connection to ensure that the connection is successful

Profile Provider Settings

Datastore Type

If using Oracle Database as the Profile Provider as well as the main directory integration, then follow these steps:

8. Select Oracle from the Data Server dropdown

Note

If using CyberArk Vault for credentials, enable Use CyberArk Vault for credentials and follow the steps in CyberArk Password Vault Server and AIM Integration with SecureAuth IdP

9. Leave the Get Profile SP and Update Profile SP fields as the default, or modify them to reflect the names in the Oracle Database Stored Procedures

If using the provided names in the Stored Procedures above, then no changes are required

Warning

Click Save once the configurations have been completed and before leaving the Data page to avoid losing changes

Notice

Refer to Data Tab Configuration to complete the configuration steps

In this section:

Oracle Database Configuration Guide

Introduction

Prerequisites

Oracle Database Configuration Steps

Notice

Scripts without Company / Domain

Note

Notice

Scripts with Company / Domain

Note

Notice

SecureAuth IdP Configuration Steps

Data

Datastore Type

Datastore Connection and Credentials

Note

Group Permissions

Stored Procedure Configuration

Profile Provider Settings

Datastore Type

Note

Warning

Notice

Search results