Setup SSO for APEX Builder + recovery on failure

I

I was very tired of filling out the forms for signing into the APEX Builder, so I decided to use the SSO there too. I wont cover how to get the credentials, but here is a great step by step article how to do that.

So assuming you did setup your SSO credentials, log into INTERNAL workspace as ADMIN user. This could be the last time you have to sign in with a password, isn't that cool?

At the top click on Manage Instance and Security and consider following changes:

• on Security tab change Allow Persistent Auth to Yes
• on Authentication Control tab under Development Environment Settings change Require User Account Expiration and Locking to No
• under Development Environment Authentication Schemes edit the SSO option

To configure Google SSO, fill the fields carefully like this (use your own credentials obviously):

• Scope = profile,email
• Username = #email#
• Additional User Attributes = email,name

For different providers parameters would vary.

If you screw this up like me, you wont be able to login back. And when you try too many times, you will also lock your account, so you would have to unlock it and change the password...

You can check your Admin account status:

SELECT
    d.workspace_id,
    d.workspace_name,
    d.user_name,
    d.is_admin,
    d.is_application_developer,
    d.account_locked,
    u.account_status
FROM apex_workspace_developers d
JOIN dba_users u
    ON u.username   = d.user_name
WHERE d.user_name   = 'ADMIN';

You should also check current authentication method:

SELECT t.value
FROM apex_instance_parameters t
WHERE t.name = 'APEX_BUILDER_AUTHENTICATION';

Now you need to unlock APEX Admin user and switch APEX Builder to use DB accounts:

BEGIN
    APEX_UNLOCK_ADMIN_USER(p_user_name => 'ADMIN');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('APEX_BUILDER_AUTHENTICATION', 'DB');     -- login with DB accounts
    COMMIT;
END;
/

And if you managed to forgot password for your Admin account (database schema/user), you can fix that by:

ALTER USER admin ACCOUNT UNLOCK;
ALTER USER admin IDENTIFIED BY "YOUR_NEW_PASSWORD";

Now you can login as Admin and change whatever you need.

Oh and one more important thing, your APEX accounts should be same as email address used by your SSO provider, or you would have to convert it to your user name during post_auth procedure.