Enabling MFA for IDCS User Accounts in OCI

OCI has two types of user accounts IAM native and IDCS (also refered to as federated user accounts). For IAM accounts user has to enable MFA for their own account. Admin cannot enable MFA for another account. For federated accounts either tenancy admin or a user in IDCS_Administrators group must first setup MFA. Afterwards users can enroll themselves for MFA. This post shows steps for setting up MFA on IDCS.
The steps beging with the assumption that user (tenancy admin or user in IDCS_Administrators group) has already login to the IDCS console.
First step is to configure the factors used for MFA. This include deciding on the hashing algorithm used for passcode generation, how long a passcode is valid and etc. During the initial setup MFA with mobile app is setup (it's best practice to have two different type of MFA. Email based MFA will be setup afterwards).
On the IDCS console select the top left menu and expand Security and select Factors menu item. Select the mobile app tab and configure mobile app MFA related properties.

Next select MFA menu item under security menu and check the mobile app passcode. Both mobile app passcode and notification are selected by default. Click save to confirm the changes.

Third step is to enforce the MFA. This is done via a Sign-On policies. There's a default sign-on policy already created. It is possible to edit the existing one or add a new one.

For this post a new policy is created for MFA. Click add to begin the wizard. First item is the policy name. Click next to proceed to creating a sign-on rule.

A sign-on rule is created such that if a login is done using username and password then additional authentication factors are prompted. Since currently only mobile app passcode and notification are setup these options are selected as additonal factors. MFA prompt is set for everytime a login is done and enrollment is set for optional. This is useful until MFA setup is completed and verified.

The saved sign-on rule will be listed as below.

Next step is to add an app to the sign-on policy. For this select the automatically created SAML app which has the OCI-V2-App"tenancy name" as the name. Oracle doc describe this as

In order for MFA to be prompted this app must be selected. Without it no MFA will be prompted. In the apps tab select assign and search for the above app (applicable to the current tenancy) and select.

At this stage the sign-on rule is in inactive state. Select the menu next to the rule and activate it.

Next login to OCI console as a federated user. Even though enrollment was set to optional the console does prompt user to enable MFA (it is always possible to skip this and enable MFA later via IDCS console. Shown later in the post). To enable MFA enable secure verification button.

This will present a QR code that is compatible with Oracle mobile authenticator (OMA) app. If not alreayd done, download OMA app to phone and scan the QR code to add the account (i.e enroll to MFA). This QR code will not work with google authenticator. If google authenticator is desired then select use another authenticator app.

As mentioned earlier it is possible to enroll to MFA later on via IDCS console. Below screenshots show these steps. Login to IDCS console as the user that need MFA enabling. Select My Profile and then the security tab. Click enable button for 2-step verification.

Since only mobile app is enabled for MFA, the 2-step vericiation list mobile app. Click the mobile app button and scan the QR code as mentioned before.

Once done the device will be listed under mobile app.

Next time when the IDCS (federated) user login the MFA is prompted. Could use both mobile app notification (select allow on the mobile phone) or entering the passcode (alternative method).

As mentioned in the begining it is considered best practice to have two MFA methods. If only mobile app based MFA is configured then loss of the phone means unable to access the account and have to go through the recovery option. Below steps shows how to add email based MFA after adding the mobile app bsaed MFA (it is possible to setup both these options at the same time as well. No need to do one after another).
In the IDCS console expand security and then select factors. In the email tab configure MFA related properties.

Select MFA and check the email checkbox. Since mobile app is already configured those would have been already checked.

Next need to edit the sign-on policy created earlier. Select the policy menu and click on edit.

Select the sign-on rule inside the policy and select edit.

In the rule select email as an additional factor for authentication. This will already have mobile app selected.

To enroll email based MFA login as the user enabling MFA to the IDCS console (My profile -> security) and select configure on email option.

This will send a passcode to the email in the account. Enter the passcode to verify the email address. Once verified email will be added to MFA.

Next time when user login email is also presented as an alternative login method. When selected passcode is sent to the email.

Update 26 September 2023
With the recent changes to IDCS and IAM domains the above MFA setup will not work. To get MFA working again enable the "Security Policy for OCI Console" policy. This is provided by Oracle and will be in disable state.

Inside are two rules, one for admins and one for all users. If needed tweak them if not leave as it is.

This policy has the "OCI Console" as the app. No need to change it leave it as it is. "MFA Policy" created earlier could be deactivated now. All the previously registered MFA methods (mobile app, email) will work as before without re-regise

PL/SQL Blocks, Roles and Definer's and Invoker's Rights

As per Oracle security doc "definer's rights and invoker's rights are used to control access to the privileges necessary during the execution of a user-created procedure, or program unit.
In a definer's rights procedure, the procedure executes with the privileges of the owner. The privileges are bound to the schema in which they were created. An invoker's rights procedure executes with the privileges of the current user, that is, the user who invokes the procedure."
However, due to how roles are treated in pl/sql blocks an odd behaviour could be observed as below. The test case involves creating a user with a custom role that has few privileges.

SQL> create user testuser identified  by test321 default tablespace users quota unlimited on users;

User created.

SQL> create role testrole;

Role created.

SQL> grant create session, create table, create procedure to testrole;

Grant succeeded.

SQL> grant testrole to testuser;

Grant succeeded.

Connect as the testuser and verify user can execute actions using each of the privileges.

sqlplus testuser/test321@devpdb

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 23 20:49:03 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> create table x  (a number);

Table created.

SQL> create or replace function t return number as
  2  begin
  3  return 1;
  4  end;
  5  /

Function created.

So user can connect, create a table and a function.

Next create a function that dynamically creates a table.

SQL> create or replace function testfunc return number as
begin
    execute immediate 'create table  testtable as select * from x';
    return 1;

end;
/
Function created.

This function executes a creat table as statement. However, calling the function result in an error.

SQL> declare
  2  i number;
  3  begin
  4  i := testfunc;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TESTUSER.TESTFUNC", line 3
ORA-06512: at line 4

Intresting that it says insufficient privileges. If the create table as statement is run from a sql prompt it runs fine.

SQL> create table  testtable as select * from x;

Table created.

The issue here is that "all roles are disabled in any named PL/SQL block that executes with definer's rights". By default, each procedure is a definer's rights unit.
In this case both the executor of the procedure (invoker) and owner (definer) of the objects referenced by it are the same. Nontheless, because roles are disabled in the named PL/SQL block the function fails as it can't see the create table privilege needed.
This seems strange error to have since failure only happens when invovked through the function. Each statement inside the function would execute without any error when run outside the function on its own.

There are two ways to fix this.
One is to grant the create table privilege directly to the user rather than to the role.

grant create table to testuser;

SQL> declare
    i number;
begin
   i := testfunc;
end;
/

PL/SQL procedure successfully completed.

Second method is to run the function with invoker's right (even though in this case both definer and invoker are the same). Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles (Remember to revoke the create table from user if it was granted in previous method).

create or replace function testfunc return number authid current_user as
begin
    execute immediate 'create table  testtable as select * from x';
    return 1;

end;
/

SQL> declare
    i number;
begin
   i := testfunc;
end;
/

PL/SQL procedure successfully completed.

Another odd behaviour is when PL/SQL object references other objects. Using the same example as above grant execute on dbms_lob to role.

SQL> grant execute on dbms_lob to testrole;

Grant succeeded.

Assuming there's a table with a blob column, the dbms_lob package could be used to get the length of the data in lob column.

SQL> select DBMS_LOB.GETLENGTH(b) from blob_table;

DBMS_LOB.GETLENGTH(B)
---------------------
                    2

But if the call to dbms_lob is included in a function it will fail to compile. Complain is it cannot identify the dbms_lob package.

SQL> CREATE OR REPLACE FUNCTION blobsize RETURN NUMBER AS
i number;
BEGIN
    select DBMS_LOB.GETLENGTH(b) into i from blob_table;
    RETURN i;
END;
/  
Warning: Function created with compilation errors.

SQL> show errors;
Errors for FUNCTION BLOBSIZE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5      PL/SQL: SQL Statement ignored
4/12     PL/SQL: ORA-00904: "DBMS_LOB"."GETLENGTH": invalid identifier

This situation is different to earlier one. Oracle development guide states that "if the subprogram or package that you create references schema objects, then you must have the necessary object privileges for those objects. These privileges must be granted to you explicitly, not through roles".
Even though it may seems odd, but the behaviour is by design. To fix this grant exeucte on dbms_lob direct to user.

SQL>  grant execute on dbms_lob to testuser;

Grant succeeded.

Afterwards package compiles without error.

SQL> alter function blobsize compile;

Function altered.

Using UR=A to Connect to Databases in Nomount Mode

Time to time there are situation where connecting to a database in nomount mode is needed. Most noteably is the data guard standby creation. When a database is started in nomount mode

startup nomount;

the service on the listener would have a blocke status.

Service "devcdb" has 1 instance(s).
  Instance "devcdb", status BLOCKED, has 1 handler(s) for this service...

Any attempt to connect to the instance using service name will fail.

sqlplus sys@devcdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 14 20:27:16 2021
Version 19.12.0.0.0

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

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

The TNS entry used is shown below.

DEVCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test-4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devcdb)
    )
  )

One solution is to create a static listener entries as done when creating data guard configurations.



Other options is to use UR=A in the TNS entry. With the use of UR=A in TNS entry there's no need to create a static listener entry. Dynamic listener would allow connection without any issue.

DEVCDBUR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test-4-254.company.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devcdb)
      (UR = A)
    )
  )
  
sqlplus sys@devcdbur as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 14 20:47:13 2021
Version 19.12.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>

Even during normal DG operation use of UR=A in TNS entries used for DG traffic could be useful. During switchover primary could attmept to connect to standby which is in the nomount state (progressing to mount or read only). Without UR=A the primary connection would fail (and succeed later after reattempt, once standby has transitioned out of nomount). To reduce the false alerts occuring in these situation UR=A could be used.
For example without UR=A entry it took nearly 15s for DG broker to connect to the new standby during a switchover (output on a 19.13 system).

DGMGRL> switchover to dgtest
Performing switchover NOW, please wait...
Operation requires a connection to database "dgtest"
Connecting ...
Connected to "dgtest"
Connected as SYSDBA.
New primary database "dgtest" is opening...
Oracle Clusterware is restarting database "dgtest3" ...
[W000 2021-10-25T13:49:46.227+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:47.231+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:48.234+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:49.237+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:50.240+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:51.242+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:52.245+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:53.248+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:54.250+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:55.252+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:56.255+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:57.257+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:58.260+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:59.262+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:50:00.265+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:50:01.274+00:00] Failed to attach to dgtest3tns.
Connected to "dgtest3"
Connected to "dgtest3"
Switchover succeeded, new primary is "dgtest"

However, with UR=A in the TNS entry it was able to connect sooner.

DGMGRL> switchover to dgtest
Performing switchover NOW, please wait...
Operation requires a connection to database "dgtest"
Connecting ...
Connected to "dgtest"
Connected as SYSDBA.
New primary database "dgtest" is opening...
Oracle Clusterware is restarting database "dgtest3" ...
[W000 2021-10-25T13:55:24.705+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:25.708+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:26.710+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:27.714+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:28.716+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:29.718+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:30.722+00:00] Failed to attach to dgtest3tns.
Connected to "dgtest3"
Connected to "dgtest3"
Switchover succeeded, new primary is "dgtest"

UR=A is used in the TNS entries created in DBCS VM DBs that also has data guard enabled.

Related Metalink note
Connections to NOMOUNT/MOUNTED or RESTRICTED Databases Fail [ID 362656.1]

Update 28 January 2022
Entries similar to "[W000 2021-10-25T13:55:24.705+00:00] Failed to attach to dgtest3tns" are due to bug 30870248. Apply the patch if available for the RU.