• Home
• App Integrations
• Single Sign-On Integrations
• PL/SQL SSO

PL/SQL Application SSO (Header based SSO) Integration

---

PL/SQL Application SSO (Single Sign-On) integration enables seamless authentication in PL/SQL-based applications using header-based SSO. Users can log in with their existing credentials through LDAP, AD passwords, or other Identity Providers (IDPs). By configuring an appropriate IDP such as miniOrange, Azure AD, Okta, or a custom solution, the authentication process is handled, and an authentication token is issued. This token is passed in the headers of each request and undergoes validation to ensure its authenticity and integrity. This extracts user information from the validated token, including user ID, roles, and permissions which enables the implementation of access control mechanisms. By following this process, SSO can be effectively implemented in PL/SQL applications, delivering a streamlined and secure authentication experience while simplifying login, reducing credential management, and enhancing overall application security.

Follow the Step-by-Step Guide given below for Oracle PL/SQL SSO (Single Sign-On)

1. Configure PL/SQL in miniOrange

• Login into miniOrange Admin Console.
• Go to Apps and click on Add Applicaton button.



• In Choose Application Type click on Create App button in OAUTH/OIDC application type.



• Search for PL/SQL in the list, if you don't find PL/SQL in the list then, search for OAuth2/OpenID Connect and you can set up your application via OAuth2/OpenID Connect App.



• Enter the Client Name of your app.
• Copy the Redirect-URL from miniOrange SSO connector.
• Adjust time validity for the tokens.
• Then, click on Save.



• To get the Client id,Client secret, OAuth endpoints and scope, you need to edit the application which you have created in the previous steps.

You can edit Application by using the following steps:

• Go to Apps.
• Search for your app and Click on the edit in Action menu against your app.



• Click on Click here to get the miniOrange endpoints.



• Note down all the parameters (Client id,Client secret,OAuth endpoints and scopes). You will need this info while configuring PL/SQL with miniOrange.

OAuth endpoints:

Authorization Endpoint:	https://login.xecurify.com/moas/idp/openidsso (Note: Use this endpoint only if you want to use miniorange as oauth identity server.) https://login.xecurify.com/moas/broker/login/oauth/260174 (Note: Use this enpoint only if you are configuring any Identity Provider in Identity Providers Menu and not using miniorange as IDP.)
Token Endpoint:	https://login.xecurify.com/moas/rest/oauth/token
User Info Endpoint:	https://login.xecurify.com/moas/rest/oauth/getuserinfo
Introspection Endpoint:	https://login.xecurify.com/moas/rest/oauth/introspect
Revoke Endpoint:	https://login.xecurify.com/moas/rest/oauth/revoke
OpenID Single Logout Endpoint:	https://login.xecurify.com/moas/idp/oidc/logout?post_logout_redirect_uri=




OAuth Scopes:

email:	View email address of the user
profile:	View profile attributes of the user account
openid:	Retrieve JWT token for OpenID Connect

2. PL/SQL SSO Configuration

• Create an account for PLSQL Database.
• Edit the dads.conf under Oracle_domain/base_domain/config/fmwconfig/components/OHS/ohs1/mod_plsql. Use the login credentials for <admin_username> & <admin_password>.
  

      dads.conf 
  
  <Location /pls/first>
  SetHandler pls_handler
  Order allow,deny
  Allow from All
  AllowOverride None
  PlsqlDatabaseUsername <admin_username>
  PlsqlDatabasePassword <admin_password>
  PlsqlDatabaseConnectString localhost:1521:orcl SIDFormat
  PlsqlAuthenticationMode CustomOwa
  PlsqlCGIEnvironmentList MO_REMOTE_USER
  </Location>
  
      

• Edit OWA_CUSTOM_BODY in the database under Orcl → packages → owa_custom

          OWA_Custom_body
  
  create or replace package body OWA_CUSTOM is
  
  function authorize return boolean is
  begin
  owa_sec.set_protection_realm('first');
  return TRUE;
  end;
  
  begin 
  owa_sec.set_authorization(OWA_SEC.CUSTOM);
  end;
  
          

• Replace all instances of REMOTE_USER within your PL/SQL stored procedures with MO_REMOTE_USER

3. Configure Your User Directory (Optional)

miniOrange provides user authentication from various external sources, which can be Directories (like ADFS, Microsoft Active Directory, Azure AD, OpenLDAP, Google, AWS Cognito etc), Identity Providers (like Okta, Shibboleth, Ping, OneLogin, KeyCloak), Databases (like MySQL, Maria DB, PostgreSQL) and many more. You can configure your existing directory/user store or add users in miniOrange.

• Setup miniOrange as IDP
• Setup AD as User Directory
• Setup External IDP

• To add your users in miniOrange there are 2 ways:

1. Create User in miniOrange

2. Bulk Upload Users

1. Create User in miniOrange

• Click on Users >> Add User.



• Here, fill the user details without the password and then click on the Create User button.



• After successful user creation a notification message "An end user is added successfully" will be displayed at the top of the dashboard.



• Click on On Boarding Status tab. Check the email, with the registered e-mail id and select action Send Activation Mail with Password Reset Link from Select Action dropdown list and then click on Apply button.



• Now, Open your email id. Open the mail you get from miniOrange and then click on the link to set your account password.
• On the next screen, enter the password and confirm password and then click on the Single Sign-On (SSO) reset password button.



• Now, you can log in into miniOrange account by entering your credentials.

2. Bulk Upload Users in miniOrange via Uploading CSV File.

• Navigate to Users >> User List. Click on Add User button.



• In Bulk User Registration Download sample csv format from our console and edit this csv file according to the instructions.



• To bulk upload users, choose the file make sure it is in comma separated .csv file format then click on Upload.



• After uploading the csv file successfully, you will see a success message with a link.
• Click on that link you will see list of users to send activation mail. Select users to send activation mail and click on Send Activation Mail. An activation mail will be sent to the selected users.

• Click on User Stores >> Add User Store in the left menu of the dashboard.



• Select User Store type as AD/LDAP.



1. STORE LDAP CONFIGURATION IN MINIORANGE: Choose this option if you want to keep your configuration in miniOrange. If active directory is behind a firewall, you will need to open the firewall to allow incoming requests to your AD.
2. STORE LDAP CONFIGURATION ON PREMISE: Choose this option if you want to keep your configuration in your premise and only allow access to AD inside premises. You will have to download and install miniOrange gateway in your premise.




• Enter LDAP Display Name and LDAP Identifier name.
• Select Directory Type as Active Directory.
• Enter the LDAP Server URL or IP Address against LDAP Server URL field.
• Click on Test Connection button to verify if you have made a successful connection with your LDAP server.



• In Active Directory, go to the properties of user containers/OU's and search for Distinguished Name attribute.



• Enter the valid Bind account Password.
• Click on Test Bind Account Credentials button to verify your LDAP Bind credentials for LDAP connection.



• Search Base is the location in the directory where the search for a user begins. You will get this from the same place you got your Distinguished name.



• Select a suitable Search filter from the drop down menu. To use custom Search Filter select "Custom Search Filter" option and customize it accordingly.



• You can also configure following options while setting up AD. Enable Activate LDAP in order to authenticate users from AD/LDAP. Click on the Save button to add user store.




Here's the list of the attributes and what it does when we enable it. You can enable/disable accordingly.

Attribute	Description
Activate LDAP	All user authentications will be done with LDAP credentials if you Activate it
Sync users in miniOrange	Users will be created in miniOrange after authentication with LDAP
Backup Authentication	If LDAP credentials fail then user will be authenticated through miniOrange
Allow users to change password	This allows your users to change their password. It updates the new credentials in your LDAP server
Enable administrator login	On enabling this, your miniOrange Administrator login authenticates using your LDAP server
Show IdP to users	If you enable this option, this IdP will be visible to users
Send Configured Attributes	If you enable this option, then only the attributes configured below will be sent in attributes at the time of login



• Click on Save. After this, it will show you the list of User stores. Click on Test Configuration to check whether you have enter valid details. For that, it will ask for username and password.



• On Successful connection with LDAP Server, a success message is shown.
• Click on Test Attribute Mapping.



• Enter a valid Username. Then, click on Test. Mapped Attributes corresponding to the user are fetched.



• After successful Attribute Mapping Configuration, go back to the ldap configuration and enable Activate LDAP in order to authenticate users from AD/LDAP.

Refer our guide to setup LDAPS on windows server.

User Import and Provisioning from AD

• Go to Settings in the Customer Admin Account.



• Enable the "Enable User Auto Registration" option and click Save.



• (Optional) To send a welcome email to all the end users that will be imported, enable the "Enable sending Welcome Emails after user registration" option and click Save.



• From the Left-Side menu of the dashboard select Provisioning.



• In Setup Provisioning tab select Active Directory in the Select Application Drop Down.
• Toggle the Import Users tab, click on Save button.



• On the same section, switch to Import Users section.
• Select Active Directory from the dropdown and click on the Import Users tab, to import all the users from Active Directory to miniOrange.



• You can view all the Users you have imports by selecting Users >> User List from Left Panel.



• All the imported users will be auto registered.
• These groups will be helpful in adding multiple 2FA policies on the applications.

miniOrange integrates with various external user sources such as directories, identity providers, and etc.

• Okta
• ADFS
• Ping
• Many more

Not able to find your IdP or Need help setting it up?

Contact us or email us at idpsupport@xecurify.com and we'll help you setting it up in no time.

4. Adaptive Authentication with PL/SQL

A. Restricting access to PL/SQL with IP Blocking

You can use adaptive authentication with PL/SQL Single Sign-On (SSO) to improve the security and functionality of Single Sign-On. You can allow a IP Address in certain range for SSO or you can deny it based your requirements and you can also challenge the user to verify his authenticity. Adaptive authentication manages the user authentication bases on different factors such as Device ID, Location, Time of Access, IP Address and many more.

You can configure Adaptive Authentication with IP Blocking in following way:

• Login to Self Service Console >> Adaptive Authentication.
• Add a Policy Name for your Adaptive Authentication Policy.



• Select your Action for behaviour Change and Challenge Type for user from the Action for behaviour Change Section.





Action for behaviour Change Options :




Attribute	Description
Allow	Allow user to authenticate and use services if Adaptive authentication condition is true.
Challenge	Challenge users with one of the three methods mentioned below for verifying user authenticity.
Deny	Deny user authentications and access to services if Adaptive authentication condition is true.




Challenge Type Options :




Attribute	Description
User second Factor	The User needs to authenticate using the second factor he has opted or assigned for such as OTP over SMS PUSH Notification OTP over Email And 12 more methods.
KBA (Knowledge-based authentication)	The System will ask user for 2 of 3 questions he has configured in his Self Service Console. Only after right answer to both questions user is allowed to proceed further.
OTP over Alternate Email	User will receive a OTP on the alternate email he has configured threw Self Service Console. Once user provides the correct OTP he is allowed to proceed further.



• Now Enable Enable IP Restriction option from the IP RESTRICTION CONFIGURATION section to configure custom IP range.



• Select the Action you want to perform if the IP address is out of the range. i.e. Allow, Challenge & Deny.
• Specify the IP Address range for which you want above setting to reflect. You can add more than one IP Address ranges by clicking on following button +.
• Scroll to the end and click on save.

B. Adaptive Authentication with Limiting number of devices.

Using Adaptive Authentication you can also restrict the number of devices the end user can access the Services on. You can allow end users to access services on a fixed no. of devices. The end users will be able to access services provided by us on this fixed no. of devices.

You can configure Adaptive Authentication with Device Restriction in following way


• Login to Self Service Console >> Adaptive Authentication.
• Add a Policy Name for your Adaptive Authentication Policy.
• Select your Action for behaviour Change and Challenge Type for user from the Action for behaviour Change Section.




• Scroll down to Device Configuration section and enable Allow User to Register Device option to allow users to register their devices.
• Enter the Number of Devices which are allowed to register in field next to Number of Device Registrations Allowed
• Choose Action if number of devices exceeded (This will override your setting for Action for Behaviour Change.)
1. Challenge: The user needs to verify himself using any of the three methods mentioned in table in step 4.1
2. Deny : Deny users access to the system
• Enable option Send email alerts to Users if number of Device registrations exceeded allowed count if you want to alert the user about no of devices exceeding the limit. Save the configuration.

C. Add Adaptive Authentication policy to PL/SQL.

• Login to Self Service Console >> Policies.
• Click on Edit option for predefined app policy.



• Set your application name in the Application and select password as Login Method.
• Enable Adaptive Authentication on Policy page and select the required restriction method as an option.
• From Select Login Policy dropdown select the policy we created in last step and click on save.

D.Notification and Alert Message.

This section handles the notifications and alerts related to Adaptive Authentication.It provides the following options :

• Get email alerts if users login from unknown devices or locations : Admin need to enable this option to enable receiving alerts for different alert options.
  
  
  

Option	Description
Challenge Completed and Device Registered	Enabling this option allows you to send an email alert when an end-user completes a challenge and registers a device.
Challenge Completed but Device Not Registered	Enabling this option allows you to send an email alert when an end-user completes a challenge but do not registers the device.
Challenge Failed	Enabling this option allows you to send an email alert when an end-user fails to complete the challenge.






• Next subsection is Send email alerts which allows us to enable or disable alerts for admin and end-users. To enable alerts for admins, you can enable the “Administrators” switch button.




• In case you want multiple admins accounts to receive alerts then you can enable the option for admin and then enter the admin emails separated by a ‘,’ in the input field next to Administrators email to receive alerts label. To enable alerts for the end-users, you can enable the “End Users” switch button.
• In case you want to customize the deny message that end user receive in case his authentication denied due to adaptive policy, you can do this by entering the message inside “Deny message for Adaptive Authentication” textbox.


How to add a trusted Device

1. When End-user log in to the self service console after the policy for device restriction is on, he is provided the option to add the current device as a trusted device.

External References

• miniOrange SSO Solution
• SSO Integration for other Oracle Applications