This post is also available in: Português

OraTOtP

OraTOtP ( Oracle Time-based One-time Password ) is a free tool that adds 2-Factor Authentication layer as an extra security when allowing users to execute anything inside your Oracle Database.

Usage examples of OraTOtP:

• Add an extra security layer for your database users, making the user password less powerful.
• If someone discovers any schema password, he will have limited or no access to the DB objects.
• You want to meet some compliance requirement (e.g.: PCI DSS Requirement 8.3)

Tested on all Oracle Database versions (SE and EE) from 10gR2 until latest 12c.

1. How it Works

After the user connects, his roles will be disabled and the only way to enable them is typing a correct 6-digits token that is generated using a mobile app. The DBA can easily define which roles are required to have 2-Factor Authentication before they get enabled by someone.

OraTOtP tool is intended to be very easy to setup and use, not requiring any major skills for anyone to enable it.

You should not enable 2-Factor Authentication for roles used by logins with unattended access (application, batch jobs, etc) unless there is some application interface to type the token. It is also not recommended to enable it on default roles (DBA, RESOURCE, etc.) as it may impact internal Oracle processes, the most appropriate is that clone these roles with another name and enable this protection.

2. Features

• If you always connect from the same application and machine, you can ask the tool to trust it for 7 days. So you won't need to type the token again if  you connect on the database from the exactly same place.
• User can reconfigure the 2-Factor if changing phone or app.
• Protection against tokens brute-force attacks.
• The token seed is stored encrypted inside the database. User can provide a password for the encryption seed to make it irreversible by anyone (not even me).

3. Instructions

1- Download an Authenticator app for your mobile. I recommend "Google Authenticator", as it is free, simple and stable:

• Android: https://play.google.com/store/apps/details?id=com.google.android.apps.authenticator2
• iOS: https://itunes.apple.com/br/app/google-authenticator/id388497605
• Windows Phone: https://www.microsoft.com/en-us/store/p/authenticator/9wzdncrfj3rj

There are also tons of other Third-Party apps that are based on the same TOTP algorithm.

2- Install OraTOtP tool in your database (check "Installation" section for more details).

3- CREATE or ALTER any existing ROLE to be only enabled after 2-Step Authentication is completed (check "Usage Example" section for other examples).

P.S.: Ensure your system clock is synchronized with UTC and in the correct timezone.

4. Created Objects

The OraTOtP tool will create 1 new schema (which name you can define during installation) to hold all the packages and tables that will have the Time-based One-time Password configuration. This user will be locked and expired and is not intended to be used by anyone.

It consists on:

• 1 New Schema with:
  • 3 Packages and their Bodys
  • 1 Procedure
  • 1 Trigger
  • 3 Tables and their Constraints and Indexes
• 2 Public Synonyms (to avoid schema name typing)
• 1 Context (to control 2-Factor authentication)

If you have Enterprise Edition DB, it will also create 1 function and 4 VPD policies to protect the schema tables.

If you have Database Vault Option, it will also create a realm to protect all the schema objects.

There are only 3 objects that need to be known:

1. Package TWOFACTOR (used to do all tasks related with the 2-Factor. Granted to PUBLIC).
2. Package TWOFACTOR_ADMIN (used to do all tasks admin tasks related with the 2-Factor. Must be granted to DBAs only).
3. Procedure ENABLE_ROLE (used to enable any 2-Factor protected role. Granted to PUBLIC).

Those 3 objects are more detailed on "Documentation" section.

5. Installation

Download ZIP file (check "Download" section for more information) and extract all the files.

Open SQL*Plus and run INSTALL.sql.

Example 1:

• Install for a local DB.

[oracle@mydbserver ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 18 13:54:08 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> @INSTALL
Schema Name for 2-Factor [TOTP]: TOTP
String to connect as SYS [/ as sysdba]: / as sysdba
Connected.
DB Vault Users script skipped - Database Vault not enabled.
Connected.
User created.
Connected.
User privs granted.
Connected.
Objects created.
Policies created.
Connected.
DB Vault Realms script skipped - Database Vault not enabled.
=> SCRIPT EXECUTED SUCCESSFULLY! <=

Example 2:

• Install for a remote database using TNS Names.
• Here the DB Vault is enabled on the Database. In that case the script requests extra information.

[oracle@mydbserver ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 18 11:20:13 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> @INSTALL
Schema Name for 2-Factor [TOTP]:
String to connect as SYS [/ as sysdba]: sys/Oracle.123@orcl as sysdba
Connected.
Oracle Database Vault Detected.
String to connect as DV Acct Mgr [/ as sysdba]: dvacctmgr/Oracle.123@orcl
String to connect as DV Owner [/ as sysdba]: dvowner/Oracle.123@orcl
String to connect as DBA [/ as sysdba]: system/Oracle.123@orcl
Connected.
User created.
Connected.
User privs granted.
Connected.
Objects created.
Policies created.
Connected.
Realm created.
=> SCRIPT EXECUTED SUCCESSFULLY! <=

Example 3:

• Install for a remote DB using EZ Connect.
• Setting the Schema Name as GAUTH instead of the default TOTP.

[oracle@mydbserver ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 18 11:20:13 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> @INSTALL
Schema Name for 2-Factor [TOTP]: GAUTH
String to connect as SYS [/ as sysdba]: sys/Oracle.123@10.1.1.5/orcl as sysdba
Connected.
DB Vault Users script skipped - Database Vault not enabled.
Connected.
User created.
Connected.
User privs granted.
Connected.
Objects created.
Policies created.
Connected.
DB Vault Realms script skipped - Database Vault not enabled.
=> SCRIPT EXECUTED SUCCESSFULLY! <=

6. Documentation

TWOFACTOR:

This package must be granted to every DB user and is responsible for allowing the user to setup and authenticate for the 2-Fator Auth. Only after that he will be able to ENABLE any role protected with 2-Factor.

SETUP (PPASS IN VARCHAR2 DEFAULT NULL);
Will setup the current logged in user and generate an URL with a QR Code. This is the first step that must be done by anyone. Scan the generated Code with your mobile app. Before running, don't forget to enable SERVEROUTPUT.

Parameter:

PPASS - Optionally you can add a password to make the code seed irreversible by anyone. You can define any password up to 30 characters. It has nothing to do with your user account password, this one is used to encrypt your code generation seed to protect it.

VALIDATE (PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL);
After the user is SETUP, you must validate it providing a code that is generated by your app. Validation proves that you have setup the mobile app correctly and the code being generated is valid. Only after user is validated that it can authenticate and enable roles that are protected with 2-Factor Authentication.

Parameter:

PCODE - Give the code generated by the mobile app.

PPASS - If you have done the setup with a password, type it here to decode the seed.

AUTHENTICATE (PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL);
Now that user is SETUP and the code generator is VALIDATED, after every new login you need to authenticate it to be able to enable and activate the role having your privileges.

Parameter:

PCODE - Give the code generated by the mobile app.

PPASS - If you have done the setup with a password, type it here to decode the seed.

DECONFIG (PCODE IN VARCHAR2 DEFAULT NULL, PPASS IN VARCHAR2 DEFAULT NULL);
Will undo and clean any user configuration. Useful if your changing your mobile app to another phone or you want to rekey your seed. You need to provide a CODE if the user is already validated. If you lost your code generation app and cannot DECONFIG, ask support for the admin can help.

Parameter:

PCODE - Give the code generated by the mobile app if the user is already validated.

PPASS - If you have done the setup with a password, type it here to decode the seed.

REMEMBER (PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL);
This procedure will remember the machine, application and connection credentials your are coming from for 7 days. This location will be configured as trusted. During that time, when you establish a new connection it will be automatically AUTHENTICATE. All you need is to enable the role.

Parameter:

PCODE - Give the code generated by the mobile app.

PPASS - If you have done the setup with a password, type it here to decode the seed.

FORGET;
This procedure will clean all the remembered connections sources linked to your user.

TWOFACTOR_ADMIN:

This package is very similar to TWOFACTOR package unless that you can also manage other users with it. Thus it must be granted only to 2-Factor Admins (or DBA's). Basically all the procedures are the same adding a "PUSER" parameter.

SETUP (PUSER IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL, PGAP IN NUMBER DEFAULT NULL);
Same as TWOFACTOR.SETUP, unless that it will setup the user specified by PUSER parameter (not the session user).

Parameter:

PUSER - Username affected by the procedure.

PPASS - Optionally you can add a password to make your code seed irreversible by any DB admin.

PGAP - Time gap in seconds to also accept codes based on time difference errors. One new code is generated every 30 seconds, however, to avoid problems, by default (if null input) we add a gap of 480 seconds (8 minutes) = 4 minutes up and down. That means that if your mobile clock is at 12h00 and server clock is at 12h04, your generated code will still be accessible. The accepted limit is 1200 seconds (20 minutes) = 10 minutes up and down.

VALIDATE (PUSER IN VARCHAR2, PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL);
Same as TWOFACTOR.VALIDATE, unless that it will validate the user specified by PUSER parameter (not the session user).

Parameter:

PUSER - Username affected by the procedure.

PCODE - Give the code generated by user mobile app.

PPASS - If user have done the setup with a password, type it here to decode the seed.

AUTHENTICATE (PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL);
Same as TWOFACTOR.AUTHENTICATE. You cannot authenticate for another user.

Parameter:

PCODE - Give the code generated by user mobile app.

PPASS - If you have done the setup with a password, type it here to decode the seed.

DECONFIG (PUSER IN VARCHAR2, PCODE IN VARCHAR2 DEFAULT NULL, PPASS IN VARCHAR2 DEFAULT NULL, PISADMIN IN BOOLEAN DEFAULT TRUE);
Same as TWOFACTOR.DECONFIG, unless that it will deconfigure the user specified by PUSER parameter (not the session user).

Parameter:

PUSER - Username affected by the procedure.

PCODE - Give the code generated by user mobile app. Ignored if PISADMIN is true (default behavior).

PPASS - If user have done the setup with a password, type it here to decode the seed.

PISADMIN - When this parameter is true (default behavior), you don't need to give a code to deconfigure already validated users.

REMEMBER (PCODE IN VARCHAR2, PPASS IN VARCHAR2 DEFAULT NULL, PINT IN INTERVAL DAY TO SECOND DEFAULT NULL);
Same as TWOFACTOR.REMEMBER. You cannot remember login specs for another user.

Parameter:

PCODE - Give the code generated by user mobile app.

PPASS - If user have done the setup with a password, type it here to decode the seed.

PINT - Interval of days to remember the credentials. If not specified, it defaults to 7 days.

FORGET (PUSER IN VARCHAR2);
Same as TWOFACTOR.FORGET, unless that it will forget the login specs of the user specified by PUSER parameter (not the session user).

Parameter:

PUSER - Username affected by the procedure.

ENABLE_ROLE:

This procedure is used to enable a protected role.

ENABLE_ROLE (ROLE_NAME IN VARCHAR2);
Will add the protected role named passed as parameter to your list of enabled roles, by running "SET ROLE". You must be authenticated in 2-Factor to be able to execute it.

Parameter:

ROLE_NAME - Protected role name that you want to enable in your session.

TABLES and COLUMNS:

Check tables and columns comments.

7. Usage Example

First step is to create a ROLE that you want to be protected by 2-Factor authenticaion:

SQL> CREATE ROLE APPOBJACCESS IDENTIFIED USING TOTP.ENABLE_ROLE;

Role created.

SQL>

P.S: You can optionally alter an existing roles to be only enabled after user is authenticated by 2-Factor.

Now, let's create a new user to show how it works:

SQL> CREATE USER USER1 IDENTIFIED BY "User1";

User created.

SQL> GRANT CREATE SESSION TO USER1;

Grant succeeded.

SQL> GRANT APPOBJACCESS TO USER1;

Grant succeeded.

SQL>

Role is created and granted to USER1, let's try to connect and enable the role:

SQL> conn User1/User1
Connected.
SQL> select * from session_roles;

no rows selected

SQL> set role APPOBJACCESS;
set role APPOBJACCESS
*
ERROR at line 1:
ORA-01924: role 'APPOBJACCESS' not granted or does not exist

SQL> exec enable_role('APPOBJACCESS');
BEGIN enable_role('APPOBJACCESS'); END;
*
ERROR at line 1:
ORA-20000: User not authenticated in 2Factor.
ORA-06512: at "TOTP.ENABLE_ROLE", line 14
ORA-06512: at line 1

SQL>

As you can see, role cannot be enabled via set command. The right way is to use the enable_role procedure, however, the user must authenticate first. As this user is not even yet configured, let's set it up:

SQL> set serveroutput on
SQL> set lines 1000
SQL> exec twofactor.setup;
https://www.google.com/chart?chs=200x200&chld=M|0&cht=qr&chl=%6F%74%70%61%75%74%68%3A%2F%2F%74%6F%74%70%2F%55%53%45%52%31%40%4F%52%43%4C%3F%73%65%63%72%65%74%3D%56%53%4D%34%50%52%35%41%4B%56%44%52%47%59%55%35%26%69%73%73%75%65%72%3D%44%42%20%53%65%72%76%65%72%20%2D%20%6F%72%63%6C%2E%75%73%2E%6F%72%61%63%6C%65%2E%63%6F%6D

PL/SQL procedure successfully completed.

SQL>

P.S: If you forget to enable serveroutput before running the SETUP procedure, just run the DECONFIG, enable it and then rerun setup.

Open the returned link address in a browser and scan the QR Code using "Google Authenticator" (or any other TOTP app that you prefer):

After your app is configured, it will start generating the codes as below:
You can now validate:

SQL> exec twofactor.validate(682286);

PL/SQL procedure successfully completed.

SQL>

With 2-Factor validated, from now on all you need to do after a creating a new connection is authenticate and enable the role:

SQL> select * from session_roles;

no rows selected

SQL> exec twofactor.authenticate(390564);

PL/SQL procedure successfully completed.

SQL> exec enable_role('APPOBJACCESS');

PL/SQL procedure successfully completed.

SQL> select * from session_roles;

ROLE
------------------------------
APPOBJACCESS

SQL>

Optionally, you can ask the 2-Factor authentication system to trust in your location for the next 7 days, so you won't need to re-authenticate after every new login coming from the same machine, terminal, IP, program and OS user:

SQL> conn User1/User1
Connected.
SQL> exec enable_role('APPOBJACCESS');
BEGIN enable_role('APPOBJACCESS'); END;
*
ERROR at line 1:
ORA-20000: User not authenticated in 2Factor.
ORA-06512: at "TOTP.ENABLE_ROLE", line 14
ORA-06512: at line 1

SQL> exec twofactor.authenticate(388648);

PL/SQL procedure successfully completed.

SQL> exec enable_role('APPOBJACCESS');

PL/SQL procedure successfully completed.

SQL> exec twofactor.remember(471508);

PL/SQL procedure successfully completed.

SQL> conn User1/User1
Connected.
SQL> exec enable_role('APPOBJACCESS');

PL/SQL procedure successfully completed.

SQL>

8. Download

https://github.com/dbarj/OraTOtP/archive/master.zip

---------------------------
Checksum information
---------------------------
Name: OraTOtP-1.00.zip
Size: 30248 bytes (0 MB)
CRC32: 9532060D
CRC64: F95F37109D5196B1
SHA256: 2339020753EE758969E2E500D5A4A29DD3D1FDFD9B14844EFD272567D4F54A4F
SHA1: C458801D32AC2B192ABD7AF2FC74099B08E5C269
BLAKE2sp: DF7E1399F2912244A02DEC6EFECEC617277321E0FD35AAB66AD7625F8DB3EA7F

0