jilee/ 0000755 0000000 0000000 00000000000 13634360471 0005664 5 jilee/.baro_nurit 0000755 0000000 0000000 00000000131 13634360471 0010027 0 cjM3NH40Mz1TMDkFdDVBfEzGR0fKYUxjZEIha2JjlTIzczU2NVhQYEJ4eXphNHyyS2lyTm9CVVYo=SpvbNtUPgK=
key/ 0000755 0000000 0000000 00000000000 13744471246 0005371 5 ora/ 0000755 0000000 0000000 00000000000 13775207475 0005367 5 ora/sql/ 0000755 0000000 0000000 00000000000 13775207475 0006166 5 ora/sql/BAROPAM.sql 0000755 0000000 0000000 00000002623 13772226234 0007765 0 CREATE OR REPLACE LIBRARY libbarokey_verifykeyl AS '/usr/baropam/key/libbarokey.so' ;
/
CREATE OR REPLACE LIBRARY libbarokey_verifykeyp AS '/usr/baropam/key/libbarokey.so' ;
/
CREATE OR REPLACE FUNCTION TO_VERIFYKEYL (login_id VARCHAR2, phone_no VARCHAR2, cycle_time VARCHAR2, corr_time VARCHAR2, key_method VARCHAR2) return VARCHAR2
as external
language C
library libbarokey_verifykeyl
name "generateKEYL"
parameters (login_id STRING, phone_no STRING, cycle_time STRING, corr_time STRING, key_method STRING);
/
CREATE OR REPLACE FUNCTION TO_VERIFYKEYP (secure_key VARCHAR2, cycle_time VARCHAR2, corr_time VARCHAR2, key_method VARCHAR2) return VARCHAR2
as external
language C
library libbarokey_verifykeyp
name "generateKEYP"
parameters (secure_key STRING, cycle_time STRING, corr_time STRING, key_method STRING);
/
CREATE OR REPLACE LIBRARY libbarokey_encrypts AS '/usr/baropam/key/libbarokey.so' ;
/
CREATE OR REPLACE LIBRARY libbarokey_decrypts AS '/usr/baropam/key/libbarokey.so' ;
/
CREATE OR REPLACE FUNCTION TO_ENCRYPTS (input VARCHAR2) return VARCHAR2
as external
language C
library libbarokey_encrypts
name "baro_encrypts"
parameters (input STRING);
/
CREATE OR REPLACE FUNCTION TO_DECRYPTS (input VARCHAR2) return VARCHAR2
as external
language C
library libbarokey_decrypts
name "baro_decrypts"
parameters (input STRING);
/
ora/sql/CHECK_OPTIONS.sql 0000755 0000000 0000000 00000000642 13675775740 0010750 0 VARIABLE HASVPD VARCHAR2(5)
VARIABLE HASDBVAULT VARCHAR2(5)
BEGIN
SELECT VALUE INTO :HASVPD FROM V$OPTION WHERE PARAMETER = 'Fine-grained access control';
EXCEPTION
WHEN NO_DATA_FOUND THEN
:HASDBVAULT := 'FALSE';
END;
/
BEGIN
SELECT VALUE INTO :HASDBVAULT FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
EXCEPTION
WHEN NO_DATA_FOUND THEN
:HASDBVAULT := 'FALSE';
END;
/
ora/sql/CREATE_USER.sql 0000755 0000000 0000000 00000001201 13772003734 0010472 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
BEGIN
IF NOT (&_O_RELEASE LIKE '1002%' OR &_O_RELEASE LIKE '11%' OR &_O_RELEASE LIKE '12%')
THEN
RAISE_APPLICATION_ERROR(-20000, 'Database Version not supported');
END IF;
END;
/
-- Account is locked and expired, you may never use it.
-- SYSAUX is used because the tablespace must always be available and RW.
CREATE USER &_vUsername identified by "AS81...9jsa1Xj10jx" account lock password expire QUOTA 10M ON SYSAUX
/
BEGIN
IF &_O_RELEASE LIKE '12%'
THEN
EXECUTE IMMEDIATE 'REVOKE INHERIT PRIVILEGES ON USER &_vUsername FROM PUBLIC';
END IF;
END;
/
ora/sql/ENABLE_ROLE.sql 0000755 0000000 0000000 00000001600 13675775534 0010463 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
ALTER SESSION SET CURRENT_SCHEMA=&_vUsername
/
SET DEFINE OFF
CREATE OR REPLACE PROCEDURE ENABLE_ROLE(ROLE_NAME IN VARCHAR2) AUTHID CURRENT_USER AS
VROLES VARCHAR2(4000);
BEGIN
-- Created by Rodrigo Jorge - www.dbarj.com.br --
IF SYS_CONTEXT('TWOFACTOR_CTX', 'AUTHENTICATED') = 'TRUE'
THEN
$IF DBMS_DB_VERSION.VER_LE_10
$THEN SELECT WM_CONCAT(ROLE)
$ELSIF DBMS_DB_VERSION.VER_LE_11_1
$THEN SELECT WM_CONCAT(ROLE)
$ELSE SELECT LISTAGG(ROLE, ', ') WITHIN GROUP(ORDER BY ROWNUM)
$END
INTO VROLES
FROM (SELECT ROLE FROM SESSION_ROLES
UNION
SELECT ROLE_NAME FROM DUAL);
DBMS_SESSION.SET_ROLE(VROLES);
ELSE
RAISE_APPLICATION_ERROR(-20000, 'User not authenticated in 2Factor.');
END IF;
END ENABLE_ROLE;
/
ora/sql/GRANT_SYNONYM.sql 0000755 0000000 0000000 00000000462 13675776034 0011024 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
ALTER SESSION SET CURRENT_SCHEMA=&_vUsername
/
CREATE OR REPLACE PUBLIC SYNONYM TWOFACTOR FOR TWOFACTOR
/
CREATE OR REPLACE PUBLIC SYNONYM ENABLE_ROLE FOR ENABLE_ROLE
/
GRANT EXECUTE ON TWOFACTOR TO PUBLIC
/
GRANT EXECUTE ON ENABLE_ROLE TO PUBLIC
/
ora/sql/POLICIES.sql 0000755 0000000 0000000 00000004727 13313050060 0010101 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
ALTER SESSION SET CURRENT_SCHEMA=&_vUsername
/
SET DEFINE OFF
CREATE OR REPLACE FUNCTION FILTER_ACCESS(SCHEMA_VAR IN VARCHAR2, TABLE_VAR IN VARCHAR2) RETURN VARCHAR2 IS
V_USER VARCHAR2(30) := SYS_CONTEXT('USERENV', 'CURRENT_USER');
BEGIN
-- Created by Rodrigo Jorge - www.dbarj.com.br --
RETURN 'SYS_CONTEXT(''USERENV'',''CURRENT_USER'') = ''' || V_USER || '''';
END FILTER_ACCESS;
/
SET DEFINE ON
SET TERMOUT OFF
WHENEVER SQLERROR CONTINUE
EXEC DBMS_RLS.DROP_POLICY(OBJECT_SCHEMA => '&_vUsername', POLICY_NAME => 'FACKEYS_POLICY_1', OBJECT_NAME => 'FACKEYS');
EXEC DBMS_RLS.DROP_POLICY(OBJECT_SCHEMA => '&_vUsername', POLICY_NAME => 'FACKEYS_POLICY_2', OBJECT_NAME => 'FACKEYS');
EXEC DBMS_RLS.DROP_POLICY(OBJECT_SCHEMA => '&_vUsername', POLICY_NAME => 'TRUSTEDLOCS_POLICY', OBJECT_NAME => 'TRUSTEDLOCS');
EXEC DBMS_RLS.DROP_POLICY(OBJECT_SCHEMA => '&_vUsername', POLICY_NAME => 'BFORCEPROT_POLICY', OBJECT_NAME => 'BFORCEPROT');
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET TERMOUT ON
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => '&_vUsername',
object_name => 'FACKEYS',
policy_name => 'FACKEYS_POLICY_1',
function_schema => '&_vUsername',
policy_function => 'FILTER_ACCESS',
statement_types => 'SELECT',
enable => TRUE,
static_policy => TRUE,
sec_relevant_cols => 'KEY',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
);
END;
/
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => '&_vUsername',
object_name => 'FACKEYS',
policy_name => 'FACKEYS_POLICY_2',
function_schema => '&_vUsername',
policy_function => 'FILTER_ACCESS',
statement_types => 'INDEX, INSERT, UPDATE, DELETE',
enable => TRUE,
static_policy => TRUE
);
END;
/
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => '&_vUsername',
object_name => 'TRUSTEDLOCS',
policy_name => 'TRUSTEDLOCS_POLICY',
function_schema => '&_vUsername',
policy_function => 'FILTER_ACCESS',
statement_types => 'INDEX, INSERT, UPDATE, DELETE',
enable => TRUE,
static_policy => TRUE
);
END;
/
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => '&_vUsername',
object_name => 'BFORCEPROT',
policy_name => 'BFORCEPROT_POLICY',
function_schema => '&_vUsername',
policy_function => 'FILTER_ACCESS',
statement_types => 'INDEX, INSERT, UPDATE, DELETE',
enable => TRUE,
static_policy => TRUE
);
END;
/
prompt Policies created.
ora/sql/SET_TWOFACTOR_CTX_TRIG.sql 0000755 0000000 0000000 00000000515 13313050060 0012307 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
ALTER SESSION SET CURRENT_SCHEMA=&_vUsername
/
SET DEFINE OFF
CREATE OR REPLACE TRIGGER SET_TWOFACTOR_CTX_TRIG
AFTER LOGON ON DATABASE
BEGIN
-- Created by Rodrigo Jorge - www.dbarj.com.br --
TWOFACTOR_INTERNAL.CHECKANDAUTHUSER;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
ora/sql/SKIP.sql 0000755 0000000 0000000 00000000031 13313050060 0007420 0 SET DEFINE ON
prompt &1
ora/sql/TWOFACTOR.sql 0000755 0000000 0000000 00000006643 13774230731 0010261 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
ALTER SESSION SET CURRENT_SCHEMA=&_vUsername
/
SET DEFINE OFF
CREATE OR REPLACE PACKAGE TWOFACTOR AS
/************************************************************************
OraTOtP - Oracle Time-based One-time Password
Copyright 2016 Rodrigo Jorge
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
************************************************************************/
PROCEDURE SETUP(SECURE_KEY IN VARCHAR2, CYCLE_TIME IN VARCHAR2, CORR_TIME IN VARCHAR2, KEY_METHOD IN VARCHAR2);
PROCEDURE DECONFIG(PCODE IN VARCHAR2 DEFAULT NULL);
PROCEDURE VALIDATE(PCODE IN VARCHAR2);
PROCEDURE AUTHENTICATE(PCODE IN VARCHAR2);
PROCEDURE REMEMBER(PCODE IN VARCHAR2);
PROCEDURE GETSECUREKEY;
PROCEDURE FORGET;
PROCEDURE SETSECRETPASS(PPASS IN VARCHAR2);
END TWOFACTOR;
/
CREATE OR REPLACE PACKAGE BODY TWOFACTOR AS
/************************************************************************
OraTOtP - Oracle Time-based One-time Password
Copyright 2016 Rodrigo Jorge
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
************************************************************************/
VUSER CONSTANT VARCHAR2(30) := SYS_CONTEXT('USERENV', 'SESSION_USER');
VPASS VARCHAR2(30) := NULL;
PROCEDURE SETUP(SECURE_KEY IN VARCHAR2, CYCLE_TIME IN VARCHAR2, CORR_TIME IN VARCHAR2, KEY_METHOD IN VARCHAR2) IS
BEGIN
TWOFACTOR_ADMIN.SETUP(PUSER => VUSER, PPASS => VPASS, PGAP => NULL, SECURE_KEY => SECURE_KEY, CYCLE_TIME => CYCLE_TIME, CORR_TIME => CORR_TIME, KEY_METHOD => KEY_METHOD);
END;
PROCEDURE DECONFIG(PCODE IN VARCHAR2 DEFAULT NULL) IS
BEGIN
TWOFACTOR_ADMIN.DECONFIG(PUSER => VUSER, PCODE => PCODE, PPASS => VPASS, PISADMIN => FALSE);
END;
PROCEDURE VALIDATE(PCODE IN VARCHAR2) IS
BEGIN
TWOFACTOR_ADMIN.VALIDATE(PUSER => VUSER, PCODE => PCODE, PPASS => VPASS);
END;
PROCEDURE AUTHENTICATE(PCODE IN VARCHAR2) IS
BEGIN
TWOFACTOR_ADMIN.AUTHENTICATE(PCODE => PCODE, PPASS => VPASS);
END;
PROCEDURE REMEMBER(PCODE IN VARCHAR2) IS
BEGIN
TWOFACTOR_ADMIN.REMEMBER(PCODE => PCODE, PPASS => VPASS, PINT => NULL);
END;
PROCEDURE GETSECUREKEY IS
BEGIN
TWOFACTOR_ADMIN.GETSECUREKEY(PUSER => VUSER);
END;
PROCEDURE FORGET IS
BEGIN
TWOFACTOR_ADMIN.FORGET(PUSER => VUSER);
END;
PROCEDURE SETSECRETPASS(PPASS IN VARCHAR2) IS
BEGIN
VPASS := PPASS;
END;
END TWOFACTOR;
/
ora/sql/TWOFACTOR_ADMIN.sql 0000755 0000000 0000000 00000015153 13774230314 0011162 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
ALTER SESSION SET CURRENT_SCHEMA=&_vUsername
/
SET DEFINE OFF
CREATE OR REPLACE PACKAGE TWOFACTOR_ADMIN AS
/************************************************************************
OraTOtP - Oracle Time-based One-time Password
Copyright 2016 Rodrigo Jorge
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
************************************************************************/
PROCEDURE SETUP(PUSER IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL, PGAP IN NUMBER DEFAULT NULL, SECURE_KEY IN VARCHAR2, CYCLE_TIME IN VARCHAR2, CORR_TIME IN VARCHAR2, KEY_METHOD IN VARCHAR2);
PROCEDURE DECONFIG(PUSER IN VARCHAR2, PCODE IN VARCHAR2 DEFAULT NULL, PPASS IN VARCHAR2 DEFAULT NULL, PISADMIN IN BOOLEAN DEFAULT TRUE);
PROCEDURE VALIDATE(PUSER IN VARCHAR2, PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL);
PROCEDURE GETSECUREKEY(PUSER IN VARCHAR2);
PROCEDURE FORGET(PUSER IN VARCHAR2);
-- Can't run to another user:
PROCEDURE AUTHENTICATE(PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL);
PROCEDURE REMEMBER(PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL, PINT IN INTERVAL DAY TO SECOND DEFAULT NULL);
END TWOFACTOR_ADMIN;
/
CREATE OR REPLACE PACKAGE BODY TWOFACTOR_ADMIN AS
/************************************************************************
OraTOtP - Oracle Time-based One-time Password
Copyright 2016 Rodrigo Jorge
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
************************************************************************/
PROCEDURE ERRORIFUSERNOTSETUP(PUSER IN VARCHAR2) IS
BEGIN
IF TWOFACTOR_INTERNAL.ISUSERSETUP(PUSER) = FALSE
THEN
RAISE_APPLICATION_ERROR(-20000, 'Setup the user first.');
END IF;
END;
PROCEDURE ERRORIFUSERNOTAUTH(PUSER IN VARCHAR2) IS
BEGIN
ERRORIFUSERNOTSETUP(PUSER);
IF NOT TWOFACTOR_INTERNAL.ISUSERVALIDATED(PUSER)
THEN
RAISE_APPLICATION_ERROR(-20000, '2Factor not validated yet.');
ELSIF NOT TWOFACTOR_INTERNAL.ISUSERENABLED(PUSER)
THEN
RAISE_APPLICATION_ERROR(-20000, 'User is disabled from using 2Factor.');
ELSIF SYS_CONTEXT('TWOFACTOR_CTX', 'AUTHENTICATED') <> 'TRUE'
THEN
RAISE_APPLICATION_ERROR(-20000, 'Authenticate first.');
END IF;
END;
PROCEDURE SETUP(PUSER IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL, PGAP IN NUMBER DEFAULT NULL, SECURE_KEY IN VARCHAR2, CYCLE_TIME IN VARCHAR2, CORR_TIME IN VARCHAR2, KEY_METHOD IN VARCHAR2) IS
BEGIN
IF NOT TWOFACTOR_INTERNAL.ISUSERSETUP(PUSER)
THEN
TWOFACTOR_INTERNAL.ADDUSER(PUSER, PGAP, PPASS, SECURE_KEY, CYCLE_TIME, CORR_TIME, KEY_METHOD);
ELSE
RAISE_APPLICATION_ERROR(-20000, 'User already configured. Deconfig it to setup again.');
END IF;
END;
PROCEDURE DECONFIG(PUSER IN VARCHAR2, PCODE IN VARCHAR2 DEFAULT NULL, PPASS IN VARCHAR2 DEFAULT NULL, PISADMIN IN BOOLEAN DEFAULT TRUE) IS
BEGIN
ERRORIFUSERNOTSETUP(PUSER);
IF PISADMIN
THEN
TWOFACTOR_INTERNAL.REMUSER(PUSER);
ELSE
-- Only allow deconfig without code if not validated yet
IF NOT TWOFACTOR_INTERNAL.ISUSERVALIDATED(PUSER)
THEN
TWOFACTOR_INTERNAL.REMUSER(PUSER);
ELSIF PCODE IS NULL
THEN
RAISE_APPLICATION_ERROR(-20000, 'You need to type a code or ask an admin to Deconfig it.');
ELSIF TWOFACTOR_INTERNAL.CODECHECK(PUSER, PCODE, PPASS)
THEN
TWOFACTOR_INTERNAL.REMUSER(PUSER);
ELSE
RAISE_APPLICATION_ERROR(-20000, 'Code not valid.');
END IF;
END IF;
END;
PROCEDURE VALIDATE(PUSER IN VARCHAR2, PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL) IS
BEGIN
ERRORIFUSERNOTSETUP(PUSER);
-- First time Validation to enable and allow usage of 2Factor
IF TWOFACTOR_INTERNAL.ISUSERVALIDATED(PUSER)
THEN
RAISE_APPLICATION_ERROR(-20000, 'User already validated.');
END IF;
IF TWOFACTOR_INTERNAL.CODECHECK(PUSER, PCODE, PPASS)
THEN
TWOFACTOR_INTERNAL.SETVALIDATED(PUSER);
TWOFACTOR_INTERNAL.SETSTATUS(PUSER, 'ENABLED');
ELSE
RAISE_APPLICATION_ERROR(-20000, 'Code not valid.');
END IF;
END;
PROCEDURE AUTHENTICATE(PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL) IS
VUSER CONSTANT VARCHAR2(30 CHAR) := SYS_CONTEXT('USERENV', 'SESSION_USER');
BEGIN
ERRORIFUSERNOTSETUP(VUSER);
-- Daily Validation
IF NOT TWOFACTOR_INTERNAL.ISUSERVALIDATED(VUSER)
THEN
RAISE_APPLICATION_ERROR(-20000, '2Factor not validated yet.');
ELSIF NOT TWOFACTOR_INTERNAL.ISUSERENABLED(VUSER)
THEN
RAISE_APPLICATION_ERROR(-20000, 'User is disabled from using 2Factor.');
ELSIF TWOFACTOR_INTERNAL.CODECHECK(VUSER, PCODE, PPASS)
THEN
TWOFACTOR_INTERNAL.SETAUTHENTICATED;
ELSE
RAISE_APPLICATION_ERROR(-20000, 'Code not valid.');
END IF;
END;
PROCEDURE REMEMBER(PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL, PINT IN INTERVAL DAY TO SECOND DEFAULT NULL) IS
VUSER CONSTANT VARCHAR2(30 CHAR) := SYS_CONTEXT('USERENV', 'SESSION_USER');
BEGIN
ERRORIFUSERNOTAUTH(VUSER);
IF TWOFACTOR_INTERNAL.CODECHECK(VUSER, PCODE, PPASS)
THEN
IF NOT TWOFACTOR_INTERNAL.ADDMEMORY(VUSER, PINT)
THEN
RAISE_APPLICATION_ERROR(-20000, 'Max trusted locations exceeded. Clean first.');
END IF;
ELSE
RAISE_APPLICATION_ERROR(-20000, 'Code not valid.');
END IF;
END;
PROCEDURE GETSECUREKEY(PUSER IN VARCHAR2) IS
BEGIN
ERRORIFUSERNOTAUTH(PUSER);
DBMS_OUTPUT.PUT_LINE(TWOFACTOR_INTERNAL.GETSECUREKEY(PUSER));
END;
PROCEDURE FORGET(PUSER IN VARCHAR2) AS
BEGIN
ERRORIFUSERNOTAUTH(PUSER);
TWOFACTOR_INTERNAL.CLEANMEMORY(PUSER);
END;
END TWOFACTOR_ADMIN;
/
ora/sql/TWOFACTOR_INTERNAL.sql 0000755 0000000 0000000 00000046560 13774230274 0011561 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
ALTER SESSION SET CURRENT_SCHEMA=&_vUsername
/
SET DEFINE OFF
CREATE OR REPLACE PACKAGE TWOFACTOR_INTERNAL AS
/************************************************************************
OraTOtP - Oracle Time-based One-time Password
Copyright 2016 Rodrigo Jorge
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
************************************************************************/
-- Code Gen
TYPE CODEROW IS RECORD(
CODE VARCHAR2(6 CHAR)); --Only to give column a name
TYPE CODES IS TABLE OF CODEROW;
FUNCTION CODEGEN(PSECRET IN VARCHAR2, PGAP IN NUMBER) RETURN CODES
PIPELINED;
-- Support Functions
FUNCTION CODECHECK(PUSER IN VARCHAR2, PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN;
-- Sets
PROCEDURE SETSTATUS(PUSER IN VARCHAR2, PSTATUS IN VARCHAR2);
PROCEDURE SETVALIDATED(PUSER IN VARCHAR2);
PROCEDURE SETAUTHENTICATED;
-- Manipulate
PROCEDURE ADDUSER(PUSER IN VARCHAR2, PGAP IN NUMBER DEFAULT NULL, PPASS IN VARCHAR2 DEFAULT NULL, SECURE_KEY IN VARCHAR2, CYCLE_TIME IN VARCHAR2, CORR_TIME IN VARCHAR2, KEY_METHOD IN VARCHAR2);
PROCEDURE REMUSER(PUSER IN VARCHAR2);
PROCEDURE CLEANMEMORY(PUSER IN VARCHAR2);
FUNCTION ADDMEMORY(PUSER IN VARCHAR2, PINT IN INTERVAL DAY TO SECOND DEFAULT INTERVAL '7' DAY) RETURN BOOLEAN;
-- Checks
FUNCTION ISUSERSETUP(PUSER IN VARCHAR2) RETURN BOOLEAN;
FUNCTION ISUSERENABLED(PUSER IN VARCHAR2) RETURN BOOLEAN;
FUNCTION ISUSERVALIDATED(PUSER IN VARCHAR2) RETURN BOOLEAN;
FUNCTION GETSECUREKEY(PUSER IN VARCHAR2) RETURN VARCHAR2;
-- Login Refresh
PROCEDURE CHECKANDAUTHUSER;
END TWOFACTOR_INTERNAL;
/
CREATE OR REPLACE PACKAGE BODY TWOFACTOR_INTERNAL AS
/************************************************************************
OraTOtP - Oracle Time-based One-time Password
Copyright 2016 Rodrigo Jorge
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
************************************************************************/
CBASE32 CONSTANT VARCHAR2(32 CHAR) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
DEFAULT_SECRET_PASS CONSTANT VARCHAR2(30 CHAR) := 'DBA-RJ'; -- If changed, any generated Key w/o password may stop working.
FUNCTION ENCKEY(PDEC IN VARCHAR2, PPASS IN VARCHAR2) RETURN RAW IS
L_SEC_RAW RAW(16) := UTL_RAW.CAST_TO_RAW(PDEC); -- Key size = 16 characters = 16 bytes
L_PASS_RAW RAW(30) := UTL_RAW.CAST_TO_RAW(PPASS); -- Pass size = Max 30 characters = 30 bytes
L_HASH_KEY RAW(20); -- SHA1 = 160 bits = 20 bytes
L_ENCRYPTED_RAW RAW(24); -- FACKEYS.KEY%TYPE does not work for INVISIBLE cols
BEGIN
-- Target is not to protect, just make it not obvious
L_HASH_KEY := DBMS_CRYPTO.HASH(L_PASS_RAW, DBMS_CRYPTO.HASH_SH1);
L_ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(L_SEC_RAW, DBMS_CRYPTO.DES_CBC_PKCS5, L_HASH_KEY);
RETURN L_ENCRYPTED_RAW;
END ENCKEY;
FUNCTION DECKEY(PENC IN RAW, PPASS IN VARCHAR2) RETURN VARCHAR2 IS
L_PASS_RAW RAW(30) := UTL_RAW.CAST_TO_RAW(PPASS); -- Pass size = Max 30 characters = 30 bytes
L_HASH_KEY RAW(20); -- SHA1 = 160 bits = 20 bytes
L_UNENCRYPTED_RAW RAW(16); -- Key size = 16 characters = 16 bytes
BEGIN
-- Target is not to protect, just make it not obvious
L_HASH_KEY := DBMS_CRYPTO.HASH(L_PASS_RAW, DBMS_CRYPTO.HASH_SH1);
L_UNENCRYPTED_RAW := DBMS_CRYPTO.DECRYPT(PENC, DBMS_CRYPTO.DES_CBC_PKCS5, L_HASH_KEY);
RETURN UTL_RAW.CAST_TO_VARCHAR2(L_UNENCRYPTED_RAW);
END DECKEY;
FUNCTION REPLACEINENCSTR(PSRCSTR IN VARCHAR2, POLDSUB IN VARCHAR2, PNEWSUB IN VARCHAR2) RETURN VARCHAR2 IS
VSTRNOHEXA VARCHAR2(2000 CHAR) := REGEXP_REPLACE(PSRCSTR, '%[[:xdigit:]]{2}', '---');
VNTH NUMBER := 1;
VPOS NUMBER := 1;
VOUTPUT VARCHAR2(2000 CHAR) := PSRCSTR;
BEGIN
WHILE VPOS <> 0
LOOP
VPOS := INSTR(VSTRNOHEXA, POLDSUB, 1, VNTH);
IF VPOS <> 0
THEN
VOUTPUT := SUBSTR(VOUTPUT, 1, VPOS - 1 + ((LENGTH(PNEWSUB) - 1) * (VNTH - 1))) || PNEWSUB || SUBSTR(VOUTPUT, VPOS + 1 + ((LENGTH(PNEWSUB) - 1) * (VNTH - 1)));
VNTH := VNTH + 1;
END IF;
END LOOP;
RETURN VOUTPUT;
END REPLACEINENCSTR;
FUNCTION GETSECRET(PUSER IN VARCHAR2, PPASS IN VARCHAR2) RETURN VARCHAR2 IS
VKEY RAW(24); -- FACKEYS.KEY%TYPE does not work for INVISIBLE cols
BEGIN
SELECT KEY INTO VKEY FROM FACKEYS WHERE USERNAME = PUSER;
RETURN DECKEY(VKEY, PPASS);
END GETSECRET;
FUNCTION GETGAP(PUSER IN VARCHAR2) RETURN NUMBER IS
VGAP FACKEYS.TIME_GAP%TYPE;
BEGIN
SELECT TIME_GAP INTO VGAP FROM FACKEYS WHERE USERNAME = PUSER;
RETURN VGAP;
END GETGAP;
FUNCTION GETMEMORY(PUSER IN VARCHAR2) RETURN NUMBER IS
VMEM FACKEYS.MAX_TRUSTED_SOURCES%TYPE;
BEGIN
SELECT MAX_TRUSTED_SOURCES INTO VMEM FROM FACKEYS WHERE USERNAME = PUSER;
RETURN VMEM;
END GETMEMORY;
FUNCTION CANTRYNOW(PUSER IN VARCHAR2) RETURN BOOLEAN IS
VBFORCEPROT BFORCEPROT%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO VBFORCEPROT FROM BFORCEPROT WHERE USERNAME = PUSER FOR UPDATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN TRUE;
END;
IF VBFORCEPROT.FIRST_FAILED IS NULL
AND VBFORCEPROT.TRIES IS NULL
THEN
RETURN TRUE;
ELSIF SYSTIMESTAMP > VBFORCEPROT.FIRST_FAILED + NUMTODSINTERVAL(VBFORCEPROT.WAIT_DURATION, 'SECOND')
OR VBFORCEPROT.TRIES < VBFORCEPROT.MAX_TRIES
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END CANTRYNOW;
FUNCTION SECRETGEN RETURN VARCHAR2 IS
VSECRET VARCHAR2(16 CHAR) := '';
BEGIN
$IF DBMS_DB_VERSION.VER_LE_10 $THEN
SELECT REPLACE(WM_CONCAT(SUBSTR(CBASE32, TRUNC(DBMS_RANDOM.VALUE(1, 33)), 1)), ',', '')
$ELSIF DBMS_DB_VERSION.VER_LE_11_1 $THEN
SELECT REPLACE(WM_CONCAT(SUBSTR(CBASE32, TRUNC(DBMS_RANDOM.VALUE(1, 33)), 1)), ',', '')
$ELSE
SELECT LISTAGG(SUBSTR(CBASE32, TRUNC(DBMS_RANDOM.VALUE(1, 33)), 1)) WITHIN GROUP(ORDER BY ROWNUM)
$END
INTO VSECRET
FROM DUAL
CONNECT BY LEVEL <= 16;
RETURN VSECRET;
END SECRETGEN;
FUNCTION CODEGEN(PSECRET IN VARCHAR2, PGAP IN NUMBER) RETURN CODES
PIPELINED IS
VBITS VARCHAR2(80 CHAR) := ''; --16 char * 5 bits / Bits representing secret position on CBASE32
VHEXABITS VARCHAR2(500) := ''; -- VBITS in HEXA representation
VUTIME NUMBER(38); -- Unix time / POSIX Time / Epoch time
VUTIME30CHK VARCHAR2(16); -- Unix time in 30 secs chunks (Hexa)
VLUTIME30CHK VARCHAR2(16); -- Unix time in 30 secs chunks (Hexa) - Last Value used
VUTIMERANGE NUMBER(38); -- Unix time adjusted with Gap secs
VMAC RAW(100);
VOFFSET NUMBER;
VP1 NUMBER;
VP2 NUMBER := POWER(2, 31) - 1;
VOUTKEY CODEROW; -- Store the output
FUNCTION NUM_TO_BIN(PNUM NUMBER) RETURN VARCHAR2 IS
VBIN VARCHAR2(8);
VNUM NUMBER := PNUM;
BEGIN
IF VNUM = 0
THEN
RETURN '0';
END IF;
WHILE VNUM > 0
LOOP
VBIN := MOD(VNUM, 2) || VBIN;
VNUM := FLOOR(VNUM / 2);
END LOOP;
RETURN VBIN;
END NUM_TO_BIN;
FUNCTION BIN_TO_HEX(PNUM VARCHAR2) RETURN VARCHAR2 IS
VHEX VARCHAR2(20);
VHEXC VARCHAR2(1);
BEGIN
IF PNUM = 0
THEN
RETURN '0';
END IF;
FOR I IN 1 .. LENGTH(PNUM) / 4
LOOP
SELECT LTRIM(TO_CHAR(BIN_TO_NUM(TO_NUMBER(SUBSTR(PNUM, ((I - 1) * 4) + 1, 1)), TO_NUMBER(SUBSTR(PNUM, ((I - 1) * 4) + 2, 1)), TO_NUMBER(SUBSTR(PNUM, ((I - 1) * 4) + 3, 1)), TO_NUMBER(SUBSTR(PNUM, ((I - 1) * 4) + 4, 1))), 'x')) INTO VHEXC FROM DUAL;
VHEX := VHEX || VHEXC;
END LOOP;
RETURN VHEX;
END BIN_TO_HEX;
BEGIN
FOR C IN 1 .. LENGTH(PSECRET)
LOOP
VBITS := VBITS || LPAD(NUM_TO_BIN(INSTR(CBASE32, SUBSTR(PSECRET, C, 1)) - 1), 5, '0');
END LOOP;
VHEXABITS := BIN_TO_HEX(VBITS);
SELECT EXTRACT(DAY FROM(DIFF)) * 86400 + EXTRACT(HOUR FROM(DIFF)) * 3600 + EXTRACT(MINUTE FROM(DIFF)) * 60 + EXTRACT(SECOND FROM(DIFF)) N INTO VUTIME FROM (SELECT CURRENT_TIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 +00:00' DIFF FROM DUAL);
VUTIMERANGE := VUTIME - FLOOR(PGAP);
WHILE TRUE
LOOP
SELECT LPAD(LTRIM(TO_CHAR(FLOOR(VUTIMERANGE / 30), 'xxxxxxxxxxxxxxxx')), 16, '0') INTO VUTIME30CHK FROM DUAL;
IF VLUTIME30CHK = VUTIME30CHK -- If last run and code don't change
THEN
EXIT;
END IF;
VMAC := DBMS_CRYPTO.MAC(SRC => HEXTORAW(VUTIME30CHK), TYP => DBMS_CRYPTO.HMAC_SH1, KEY => HEXTORAW(VHEXABITS));
VOFFSET := TO_NUMBER(SUBSTR(RAWTOHEX(VMAC), -1, 1), 'x');
VP1 := TO_NUMBER(SUBSTR(RAWTOHEX(VMAC), VOFFSET * 2 + 1, 8), 'xxxxxxxx');
VOUTKEY.CODE := SUBSTR(BITAND(VP1, VP2), -6, 6);
PIPE ROW(VOUTKEY);
VLUTIME30CHK := VUTIME30CHK;
VUTIMERANGE := LEAST(VUTIMERANGE + 30, VUTIME + FLOOR(PGAP));
END LOOP;
RETURN;
END CODEGEN;
FUNCTION GET_GLOBAL_NAME RETURN VARCHAR2 IS
VGNAME GLOBAL_NAME.GLOBAL_NAME%TYPE;
BEGIN
SELECT GLOBAL_NAME INTO VGNAME FROM GLOBAL_NAME;
RETURN VGNAME;
END GET_GLOBAL_NAME;
PROCEDURE SETSTATUS(PUSER IN VARCHAR2, PSTATUS IN VARCHAR2) IS
BEGIN
UPDATE FACKEYS SET STATUS = PSTATUS WHERE USERNAME = PUSER;
COMMIT;
END SETSTATUS;
PROCEDURE SETVALIDATED(PUSER IN VARCHAR2) IS
BEGIN
UPDATE FACKEYS SET VALIDATED = 'VALIDATED' WHERE USERNAME = PUSER;
COMMIT;
END SETVALIDATED;
PROCEDURE SETAUTHENTICATED IS
BEGIN
DBMS_SESSION.SET_CONTEXT('TWOFACTOR_CTX', 'AUTHENTICATED', 'TRUE');
END SETAUTHENTICATED;
PROCEDURE INCLASTTRY(PUSER IN VARCHAR2, PCLEAN IN BOOLEAN DEFAULT FALSE) IS
VBFORCEPROT BFORCEPROT%ROWTYPE;
BEGIN
BEGIN
SELECT * INTO VBFORCEPROT FROM BFORCEPROT WHERE USERNAME = PUSER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;
IF PCLEAN -- If code is right, clean data
THEN
IF VBFORCEPROT.FIRST_FAILED IS NOT NULL
THEN
UPDATE BFORCEPROT
SET FIRST_FAILED = NULL,
TRIES = NULL
WHERE USERNAME = PUSER;
END IF;
ELSIF VBFORCEPROT.FIRST_FAILED IS NULL -- If code is wrong and this is the first wrong attempt
THEN
UPDATE BFORCEPROT
SET FIRST_FAILED = SYSTIMESTAMP,
TRIES = 1
WHERE USERNAME = PUSER;
ELSIF SYSTIMESTAMP > VBFORCEPROT.FIRST_FAILED + NUMTODSINTERVAL(VBFORCEPROT.WAIT_DURATION, 'SECOND') -- If code is wrong and this is already too far from the first wrong attempt
THEN
UPDATE BFORCEPROT
SET FIRST_FAILED = SYSTIMESTAMP,
TRIES = 1
WHERE USERNAME = PUSER;
ELSE
-- If code is wrong and we are still inside tries window
UPDATE BFORCEPROT SET TRIES = LEAST(TRIES + 1, MAX_TRIES) WHERE USERNAME = PUSER;
END IF;
COMMIT;
END INCLASTTRY;
FUNCTION CODECHECK(PUSER IN VARCHAR2, PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN IS
VPASS VARCHAR2(30 CHAR) := NVL(PPASS, DEFAULT_SECRET_PASS);
VFIND NUMBER;
VSECRET VARCHAR2(16 CHAR);
VGAP FACKEYS.TIME_GAP%TYPE;
TKEY VARCHAR2(06 CHAR);
BEGIN
-- To avoid BForce, check if user can try
IF NOT (CANTRYNOW(PUSER)) -- CANTRYNOW lock the user row to avoid parallel execution of this function = Parallel BForce Attack
THEN
ROLLBACK; -- Release Lock of "FOR UPDATE" inside CANTRYNOW
RETURN FALSE;
END IF;
VGAP := GETGAP(PUSER);
BEGIN
VSECRET := GETSECRET(PUSER, VPASS);
-- Wrong Password throughs exception on GETSECRET -> DECKEY
EXCEPTION
WHEN OTHERS THEN
INCLASTTRY(PUSER);
RETURN FALSE;
END;
--SELECT COUNT(*) INTO VFIND FROM TABLE(CODEGEN(VSECRET, VGAP)) WHERE CODE = PCODE;
SELECT TO_VERIFYKEYP(SECURE_KEY, CYCLE_TIME, CORR_TIME, KEY_METHOD) INTO TKEY FROM FACKEYS WHERE USERNAME = PUSER;
IF TKEY = PCODE
THEN
INCLASTTRY(PUSER, TRUE);
RETURN TRUE;
ELSE
INCLASTTRY(PUSER);
RETURN FALSE;
END IF;
END CODECHECK;
PROCEDURE ADDUSER(PUSER IN VARCHAR2, PGAP IN NUMBER DEFAULT NULL, PPASS IN VARCHAR2 DEFAULT NULL, SECURE_KEY IN VARCHAR2, CYCLE_TIME IN VARCHAR2, CORR_TIME IN VARCHAR2, KEY_METHOD IN VARCHAR2) IS
VPASS VARCHAR2(30 CHAR) := NVL(PPASS, DEFAULT_SECRET_PASS);
VKEY RAW(24) := ENCKEY(SECRETGEN, VPASS); -- FACKEYS.KEY%TYPE does not work for INVISIBLE cols
BEGIN
IF PGAP IS NULL -- This could be avoided on 12c+ with new "DEFAULT ON NULL" clause, but not on prior versions
THEN
INSERT INTO FACKEYS
(USERNAME,
KEY,
SECURE_KEY,
CYCLE_TIME,
CORR_TIME,
KEY_METHOD)
VALUES
(PUSER,
VKEY,
SECURE_KEY,
CYCLE_TIME,
CORR_TIME,
KEY_METHOD);
ELSE
INSERT INTO FACKEYS
(USERNAME,
KEY,
SECURE_KEY,
CYCLE_TIME,
CORR_TIME,
KEY_METHOD,
TIME_GAP)
VALUES
(PUSER,
VKEY,
SECURE_KEY,
CYCLE_TIME,
CORR_TIME,
KEY_METHOD,
PGAP);
END IF;
INSERT INTO BFORCEPROT (USERNAME) VALUES (PUSER);
COMMIT;
END ADDUSER;
PROCEDURE REMUSER(PUSER IN VARCHAR2) IS
BEGIN
DELETE FROM FACKEYS WHERE USERNAME = PUSER;
COMMIT;
END REMUSER;
FUNCTION HASMEMORY(PUSER IN VARCHAR2) RETURN BOOLEAN IS
VFIND NUMBER;
BEGIN
SELECT COUNT(*)
INTO VFIND
FROM TRUSTEDLOCS
WHERE USERNAME = PUSER
AND SYSTIMESTAMP BETWEEN START_DATE AND END_DATE;
IF VFIND >= GETMEMORY(PUSER)
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END HASMEMORY;
PROCEDURE REMMEMORY IS
BEGIN
DELETE TRUSTEDLOCS B
WHERE EXISTS (SELECT 1
FROM V$SESSION A
WHERE A.AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID')
AND A.SID = SYS_CONTEXT('USERENV', 'SID')
AND A.USERNAME = B.USERNAME
AND A.OSUSER = B.OSUSER
AND A.MACHINE = B.MACHINE
AND A.TERMINAL = B.TERMINAL
AND A.PROGRAM = B.PROGRAM
AND B.IP_ADDRESS = SYS_CONTEXT('USERENV', 'IP_ADDRESS')
AND SYSTIMESTAMP BETWEEN B.START_DATE AND B.END_DATE);
-- Do not COMMIT; --ADDMEMORY will commit
END REMMEMORY;
FUNCTION ADDMEMORY(PUSER IN VARCHAR2, PINT INTERVAL DAY TO SECOND DEFAULT INTERVAL '7' DAY) RETURN BOOLEAN IS
VREM TRUSTEDLOCS%ROWTYPE;
VINT INTERVAL DAY TO SECOND := NVL(PINT, INTERVAL '7' DAY);
CURSOR C1 IS
SELECT MAX_TRUSTED_SOURCES FROM FACKEYS WHERE USERNAME = PUSER FOR UPDATE; -- Just to get a user lock, nothing will be changed
BEGIN
-- Locks here are to avoid parallel memory attack
OPEN C1; -- Get lock for user
IF HASMEMORY(PUSER)
THEN
-- Module and Action removed as they are defined after login trigger from DBMS_APPLICATION_INFO.
SELECT USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
SYS_CONTEXT('USERENV', 'IP_ADDRESS') IP_ADDRESS
INTO VREM.USERNAME,
VREM.OSUSER,
VREM.MACHINE,
VREM.TERMINAL,
VREM.PROGRAM,
VREM.IP_ADDRESS
FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID')
AND SID = SYS_CONTEXT('USERENV', 'SID');
VREM.START_DATE := SYSTIMESTAMP;
VREM.END_DATE := VREM.START_DATE + VINT;
INSERT INTO TRUSTEDLOCS VALUES VREM;
COMMIT; -- Release lock
CLOSE C1;
RETURN TRUE;
ELSE
-- Memory is Full
ROLLBACK; -- Release lock
CLOSE C1;
RETURN FALSE;
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
REMMEMORY;
RETURN ADDMEMORY(PUSER, VINT);
END ADDMEMORY;
PROCEDURE CLEANMEMORY(PUSER IN VARCHAR2) IS
BEGIN
DELETE TRUSTEDLOCS WHERE USERNAME = PUSER;
COMMIT;
END CLEANMEMORY;
FUNCTION ISUSERSETUP(PUSER IN VARCHAR2) RETURN BOOLEAN IS
VFIND NUMBER;
BEGIN
SELECT COUNT(*) INTO VFIND FROM FACKEYS WHERE USERNAME = PUSER;
IF VFIND = 0
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END ISUSERSETUP;
FUNCTION ISUSERENABLED(PUSER IN VARCHAR2) RETURN BOOLEAN IS
VFIND NUMBER;
BEGIN
SELECT COUNT(*)
INTO VFIND
FROM FACKEYS
WHERE USERNAME = PUSER
AND STATUS = 'ENABLED';
IF VFIND = 0
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END ISUSERENABLED;
FUNCTION ISUSERVALIDATED(PUSER IN VARCHAR2) RETURN BOOLEAN IS
VFIND NUMBER;
BEGIN
SELECT COUNT(*)
INTO VFIND
FROM FACKEYS
WHERE USERNAME = PUSER
AND VALIDATED = 'VALIDATED';
IF VFIND = 0
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END ISUSERVALIDATED;
FUNCTION ISUSERREMEMBERED RETURN BOOLEAN IS
VFIND NUMBER;
BEGIN
SELECT COUNT(*)
INTO VFIND
FROM V$SESSION A,
TRUSTEDLOCS B
WHERE A.AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID')
AND A.SID = SYS_CONTEXT('USERENV', 'SID')
AND A.USERNAME = B.USERNAME
AND A.OSUSER = B.OSUSER
AND A.MACHINE = B.MACHINE
AND NVL(A.TERMINAL, ' ') = NVL(B.TERMINAL, ' ')
AND A.PROGRAM = B.PROGRAM
AND NVL(B.IP_ADDRESS, ' ') = NVL(SYS_CONTEXT('USERENV', 'IP_ADDRESS'), ' ')
AND SYSTIMESTAMP BETWEEN B.START_DATE AND B.END_DATE;
IF VFIND = 0
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END ISUSERREMEMBERED;
FUNCTION GETSECUREKEY(PUSER IN VARCHAR2) RETURN VARCHAR2 IS
SECUREKEY VARCHAR2(80 CHAR);
BEGIN
SELECT
'Secure key: ' || SECURE_KEY || '-' || CYCLE_TIME || '-' || CORR_TIME || '-' || KEY_METHOD
INTO
SECUREKEY
FROM
FACKEYS
WHERE
USERNAME = PUSER
;
RETURN SECUREKEY;
END GETSECUREKEY;
PROCEDURE CHECKANDAUTHUSER AS
VUSER CONSTANT VARCHAR2(30 CHAR) := SYS_CONTEXT('USERENV', 'SESSION_USER');
BEGIN
IF NOT ISUSERSETUP(VUSER) -- Not Setup
THEN
DBMS_SESSION.SET_CONTEXT('TWOFACTOR_CTX', 'AUTHENTICATED', 'NOT SETUP');
ELSIF NOT ISUSERVALIDATED(VUSER) -- Not Validated
THEN
DBMS_SESSION.SET_CONTEXT('TWOFACTOR_CTX', 'AUTHENTICATED', 'NOT VALIDATED');
ELSIF NOT ISUSERENABLED(VUSER) -- Not Enabled
THEN
DBMS_SESSION.SET_CONTEXT('TWOFACTOR_CTX', 'AUTHENTICATED', 'NOT ENABLED');
ELSIF ISUSERREMEMBERED() -- Is Remembered!
THEN
SETAUTHENTICATED;
ELSE
DBMS_SESSION.SET_CONTEXT('TWOFACTOR_CTX', 'AUTHENTICATED', 'FALSE');
END IF;
END CHECKANDAUTHUSER;
END TWOFACTOR_INTERNAL;
/
ora/sql/USER_OBJS.sql 0000755 0000000 0000000 00000012444 13771266012 0010276 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
ALTER SESSION SET CURRENT_SCHEMA=&_vUsername
/
--- CLEAN
SET TERMOUT OFF
WHENEVER SQLERROR CONTINUE
DROP TABLE FACKEYS PURGE
/
DROP TABLE BFORCEPROT
/
DROP TABLE TRUSTEDLOCS
/
DROP CONTEXT TWOFACTOR_CTX
/
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET TERMOUT ON
--- TABLE FACKEYS
CREATE TABLE FACKEYS
(
USERNAME VARCHAR2(30 CHAR) NOT NULL,
KEY RAW(24) NOT NULL,
SECURE_KEY VARCHAR2(32 CHAR) DEFAULT 'jIqIcHbVqdpj7b4PzBpM2DiIeBvmHFV/' NOT NULL,
CYCLE_TIME VARCHAR2(03 CHAR) DEFAULT '30' NOT NULL,
CORR_TIME VARCHAR2(05 CHAR) DEFAULT '0' NOT NULL,
KEY_METHOD VARCHAR2(10 CHAR) DEFAULT 'app512' NOT NULL,
STATUS VARCHAR2(08 CHAR) DEFAULT 'DISABLED' NOT NULL,
VALIDATED VARCHAR2(13 CHAR) DEFAULT 'NOT VALIDATED' NOT NULL,
TIME_GAP NUMBER(4,0) DEFAULT 240 NOT NULL,
MAX_TRUSTED_SOURCES NUMBER(2,0) DEFAULT 5 NOT NULL
) TABLESPACE SYSAUX
/
BEGIN
IF &_O_RELEASE LIKE '12%'
THEN
EXECUTE IMMEDIATE 'ALTER TABLE FACKEYS MODIFY KEY INVISIBLE';
END IF;
END;
/
COMMENT ON TABLE FACKEYS IS '2-Factor Auth control table.'
/
COMMENT ON COLUMN FACKEYS.USERNAME IS 'User schema name.'
/
COMMENT ON COLUMN FACKEYS.KEY IS 'Encrypted user secret.'
/
COMMENT ON COLUMN FACKEYS.SECURE_KEY IS 'Encrypted user secret.'
/
COMMENT ON COLUMN FACKEYS.STATUS IS 'Current user status: ENABLED/DISABLED.'
/
COMMENT ON COLUMN FACKEYS.VALIDATED IS 'Has user ever validated his code?'
/
COMMENT ON COLUMN FACKEYS.TIME_GAP IS 'Time difference in secs to avoid clock errors.'
/
COMMENT ON COLUMN FACKEYS.MAX_TRUSTED_SOURCES IS 'Max number of trusted sources.';
ALTER TABLE FACKEYS ADD CONSTRAINT CK_STATUS CHECK (STATUS IN ('DISABLED','ENABLED'))
/
ALTER TABLE FACKEYS ADD CONSTRAINT CK_VALIDATED CHECK (VALIDATED IN ('NOT VALIDATED','VALIDATED'))
/
ALTER TABLE FACKEYS ADD CONSTRAINT CK_STAT_VALID CHECK (NOT(VALIDATED = 'NOT VALIDATED' AND STATUS = 'ENABLED'))
/
ALTER TABLE FACKEYS ADD CONSTRAINT CK_TIME_GAP CHECK (TIME_GAP <= 600) -- Clock error. Max is ±10min = 20 min
/
ALTER TABLE FACKEYS ADD CONSTRAINT PK_FACKEYS PRIMARY KEY (USERNAME) USING INDEX TABLESPACE SYSAUX
/
--- TABLE BFORCEPROT
CREATE TABLE BFORCEPROT
(
USERNAME VARCHAR2(30 CHAR) NOT NULL,
MAX_TRIES NUMBER(2,0) DEFAULT 3 NOT NULL,
WAIT_DURATION NUMBER(3,0) DEFAULT 30 NOT NULL,
FIRST_FAILED TIMESTAMP(6) WITH TIME ZONE NULL,
TRIES NUMBER(2,0) NULL
) TABLESPACE SYSAUX
/
COMMENT ON TABLE BFORCEPROT IS 'Control user tries to avoid B. Force attacks.'
/
COMMENT ON COLUMN BFORCEPROT.USERNAME IS 'User schema name.'
/
COMMENT ON COLUMN BFORCEPROT.MAX_TRIES IS 'Max tries attempts allowed on "WAIT_DURATION" window.'
/
COMMENT ON COLUMN BFORCEPROT.WAIT_DURATION IS 'Number of seconds that "MAX_TRIES" are valid.'
/
COMMENT ON COLUMN BFORCEPROT.FIRST_FAILED IS 'First wrong attempt on this "WAIT_DURATION" window.'
/
COMMENT ON COLUMN BFORCEPROT.TRIES IS 'Number of guesses already tried in this "WAIT_DURATION" window.'
/
ALTER TABLE BFORCEPROT ADD CONSTRAINT CK_FAILED_TRIES CHECK ((FIRST_FAILED IS NULL AND TRIES IS NULL) OR (FIRST_FAILED IS NOT NULL AND TRIES IS NOT NULL))
/
ALTER TABLE BFORCEPROT ADD CONSTRAINT PK_BFORCEPROT PRIMARY KEY (USERNAME) USING INDEX TABLESPACE SYSAUX
/
ALTER TABLE BFORCEPROT ADD CONSTRAINT FK_BFORCEPROT FOREIGN KEY (USERNAME) REFERENCES FACKEYS (USERNAME) ON DELETE CASCADE
/
--- TABLE TRUSTEDLOCS
CREATE TABLE TRUSTEDLOCS
(
USERNAME VARCHAR2(30 CHAR) NOT NULL,
OSUSER VARCHAR2(30 CHAR) NOT NULL,
MACHINE VARCHAR2(64 CHAR) NOT NULL,
TERMINAL VARCHAR2(30 CHAR) NULL,
PROGRAM VARCHAR2(48 CHAR) NOT NULL,
IP_ADDRESS VARCHAR2(256 CHAR) NULL,
START_DATE TIMESTAMP(6) WITH TIME ZONE NOT NULL,
END_DATE TIMESTAMP(6) WITH TIME ZONE NOT NULL
) TABLESPACE SYSAUX
/
COMMENT ON TABLE TRUSTEDLOCS IS 'Control user trusted locations to automatically authenticate.'
/
COMMENT ON COLUMN TRUSTEDLOCS.USERNAME IS 'User schema name.'
/
COMMENT ON COLUMN TRUSTEDLOCS.OSUSER IS 'OS User from trusted location.'
/
COMMENT ON COLUMN TRUSTEDLOCS.MACHINE IS 'Machine from trusted location.'
/
COMMENT ON COLUMN TRUSTEDLOCS.TERMINAL IS 'Terminal from trusted location.'
/
COMMENT ON COLUMN TRUSTEDLOCS.PROGRAM IS 'Program from trusted location.'
/
COMMENT ON COLUMN TRUSTEDLOCS.IP_ADDRESS IS 'IP Address from trusted location.'
/
COMMENT ON COLUMN TRUSTEDLOCS.START_DATE IS 'Since when this location is valid.'
/
COMMENT ON COLUMN TRUSTEDLOCS.END_DATE IS 'Until when this location is valid.'
/
ALTER TABLE TRUSTEDLOCS ADD CONSTRAINT UK_TRUSTEDLOCS UNIQUE (USERNAME, OSUSER, MACHINE, TERMINAL, PROGRAM, IP_ADDRESS) USING INDEX TABLESPACE SYSAUX
/
ALTER TABLE TRUSTEDLOCS ADD CONSTRAINT FK_TRUSTEDLOCS FOREIGN KEY (USERNAME) REFERENCES FACKEYS (USERNAME) ON DELETE CASCADE
/
--- CONTEXT TWOFACTOR_CTX
CREATE CONTEXT TWOFACTOR_CTX USING TWOFACTOR_INTERNAL
/
ora/sql/USER_PRIVS.sql 0000755 0000000 0000000 00000000361 13675775056 0010457 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
GRANT EXECUTE ON DBMS_CRYPTO TO &_vUsername -- To encrypt user Keys
/
GRANT SELECT ON V_$SESSION TO &_vUsername -- For remembering user login data (machine name, os user, terminal, etc)
/
ora/sql/VAULT_REALM.sql 0000755 0000000 0000000 00000001126 13675774540 0010526 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE ON
SET TERMOUT OFF
WHENEVER SQLERROR CONTINUE
BEGIN
DVSYS.DBMS_MACADM.DELETE_REALM(REALM_NAME => '&_vUsername');
END;
/
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET TERMOUT ON
BEGIN
DVSYS.DBMS_MACADM.CREATE_REALM (REALM_NAME => '&_vUsername', DESCRIPTION => 'Control &_vUsername objects access', ENABLED => 'Y', AUDIT_OPTIONS => '1');
DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(REALM_NAME => '&_vUsername', OBJECT_OWNER => DBMS_ASSERT.ENQUOTE_NAME('&_vUsername', FALSE), OBJECT_NAME => '%', OBJECT_TYPE => '%');
END;
/
prompt Realm created.
ora/sql/VAULT_USERS.sql 0000755 0000000 0000000 00000000615 13675774474 0010577 0 WHENEVER SQLERROR EXIT SQL.SQLCODE
prompt Oracle Database Vault Detected.
ACCEPT _vDVAcctMgrstr CHAR DEFAULT '/ as sysdba' PROMPT "String to connect as DV Acct Mgr [/ as sysdba]: "
ACCEPT _vDVOwnerstr CHAR DEFAULT '/ as sysdba' PROMPT "String to connect as DV Owner [/ as sysdba]: "
ACCEPT _vDBAstr CHAR DEFAULT '/ as sysdba' PROMPT "String to connect as DBA [/ as sysdba]: "
ora/INSTALL.sql 0000755 0000000 0000000 00000005520 13774233327 0007215 0 /************************************************************************
OraTOtP - Oracle Time-based One-time Password
Copyright 2016 Rodrigo Jorge
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
************************************************************************/
WHENEVER SQLERROR EXIT SQL.SQLCODE
COLUMN SCRIPT_NAME NEW_VALUE V_SCRIPT_NAME
SET VERIFY OFF
SET DEFINE ON
SET FEEDBACK OFF
DEFINE _vUsername = "TOTP" (CHAR)
ACCEPT _vUsername CHAR DEFAULT '&_vUsername' PROMPT "Schema Name for 2-Factor [&_vUsername]: "
ACCEPT _vSYSDBAstr CHAR DEFAULT '/ as sysdba' PROMPT "String to connect as SYS [/ as sysdba]: "
DEFINE _vDVAcctMgrstr = "&_vSYSDBAstr" (CHAR)
DEFINE _vDVOwnerstr = "&_vSYSDBAstr" (CHAR)
DEFINE _vDBAstr = "&_vSYSDBAstr" (CHAR)
-- Check if the current database has Fine-grained access control (EE) and Oracle Database Vault (option) enabled.
conn &_vSYSDBAstr
@@sql/CHECK_OPTIONS
-- If has DB Vault option, ask for users credentials.
SET TERMOUT OFF
SELECT DECODE(:HASDBVAULT, 'TRUE', 'VAULT_USERS', 'SKIP') SCRIPT_NAME FROM DUAL;
SET TERMOUT ON
SET DEFINE ON
@@sql/&V_SCRIPT_NAME 'DB Vault Users script skipped - Database Vault not enabled.'
-- Create user.
conn &_vDVAcctMgrstr
@@sql/CREATE_USER
prompt User created.
-- Grant privs used by procedures.
conn &_vSYSDBAstr
@@sql/USER_PRIVS
prompt User privs granted.
-- Create objects.
conn &_vDBAstr
@@sql/USER_OBJS
prompt User objs create.
@@sql/BAROPAM
prompt BaroPAM create.
@@sql/TWOFACTOR_INTERNAL
prompt twofactor internal create.
@@sql/TWOFACTOR_ADMIN
prompt twofactor admin create.
@@sql/TWOFACTOR
prompt twofactor create.
@@sql/ENABLE_ROLE
prompt enable role.
@@sql/GRANT_SYNONYM
prompt grant synonym.
@@sql/SET_TWOFACTOR_CTX_TRIG
prompt Objects created.
-- If not SE, create Policies.
SET TERMOUT OFF
SELECT DECODE(:HASVPD, 'TRUE', 'POLICIES', 'SKIP') SCRIPT_NAME FROM DUAL;
SET TERMOUT ON
SET DEFINE ON
@@sql/&V_SCRIPT_NAME 'Policies script skipped - Oracle Standard Edition'
-- If has DB Vault option, create Realm to proctect schemas objects.
conn &_vDVOwnerstr
SET TERMOUT OFF
SELECT DECODE(:HASDBVAULT, 'TRUE', 'VAULT_REALM', 'SKIP') SCRIPT_NAME FROM DUAL;
SET TERMOUT ON
SET DEFINE ON
@@sql/&V_SCRIPT_NAME 'DB Vault Realms script skipped - Database Vault not enabled.'
prompt => SCRIPT EXECUTED SUCCESSFULLY! <=
SET FEEDBACK ON
SET VERIFY ON
EXIT
key/libbarokey.so 0000755 0000000 0000000 00000216650 14045631116 0010061 0 ELF > @ @ 8 @ 8 8 > > > > > > ` ` $ $ Ptd @3 @3 @3 Qtd Rtd > > > ` ` GNU %)
iT"m6l I B (
,@H$@ ! &