Blog

Complete Guide to Setting Up Custom Authentication Learn how to set up Custom authentication. To create a custom authentication scheme: On the Workspace home page, click the App Builder icon. Select an application. On the Application home page, click Shared Components. The Shared Components page appears. Under Security, select Authentication Schemes. On the Authentication Schemes page, click Create. Select Based on a pre-configured scheme from the gallery and click Next. Under Name: Name – Enter the name used to reference the authentication scheme by other application developers. Scheme Type – Select Custom. Fill in the appropriate fields. To learn more about a specific field, see field-level Help. Click Create Authentication Scheme. Creating a custom authentication scheme alone is not sufficient for building a fully tailored authentication system. There are many additional, actionable, and important considerations involved. In this article, we’ll dive deep into this complex subject and provide you with practical, step-by-step guidance to help you implement a complete custom authentication solution in your Oracle APEX application. Here are the key topics we’ll cover to help you gain a thorough understanding of the subject. Create a custom authentication scheme – covered above. Create Custom Logic – a PL/SQL code Password Security Register a User Authenticate User Using Username and Password Using Username and OTP (optional) Password Reset Role Base Access Control – RBAC Writing a separate article on RBAC. Lets start writing our custom logic for our Oracle APEX Application’s custom authentication. Login to your APEX instance as developer. Navigation: Home > SQL Workshop > Object Browser On the left panel, click on + sign and choose Package to create Give a name – LAMCX_AUTH Enter the name of your choice for the Package to be created. The name must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore. Click on Create Package Now refresh page and check in left panel, you will find your newly created package. Navigate to Package Specification Create function – HASH_PASSWORD FUNCTION hash_password ( p_user_name IN VARCHAR2, p_password IN VARCHAR2 ) RETURN VARCHAR2; Create procedure – SIGNUP PROCEDURE signup ( p_first_name IN VARCHAR2, p_last_name IN VARCHAR2, p_user_name IN VARCHAR2, p_email IN VARCHAR2, p_app_id NUMBER, p_company_id NUMBER, p_referral varchar2, p_password IN VARCHAR2 DEFAULT NULL ); Create procedure – AUTHENTICATE_USER FUNCTION authenticate_user ( p_username IN VARCHAR2, p_password IN VARCHAR2 DEFAULT NULL, p_app_id IN NUMBER DEFAULT NULL, p_by_otp IN VARCHAR2 DEFAULT 'N', p_otp IN NUMBER DEFAULT NULL ) RETURN BOOLEAN; Create procedure – RESET_PASSWORD PROCEDURE reset_password ( p_user_name IN VARCHAR2, p_password IN VARCHAR2, p_password_new IN VARCHAR2 ); Create function – IS_ACTIVE FUNCTION is_active( p_username varchar2 default null ) return boolean; Click on button – Save and Compile You must check and confirm, if package is compiled successfully Create database table – LAMCX_USERS, to store user data. CREATE TABLE "LAMCX_USERS" ( "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(2000 CHAR), "LAST_NAME" VARCHAR2(2000 CHAR), "USER_NAME" VARCHAR2(100), "START_DATE" DATE, "END_DATE" DATE, "STATUS" VARCHAR2(30 CHAR), "REMARKS" VARCHAR2(4000 CHAR), "ATTRIBUTE1" VARCHAR2(150 CHAR), "ATTRIBUTE2" VARCHAR2(150 CHAR), "ATTRIBUTE3" VARCHAR2(150 CHAR), "ATTRIBUTE4" VARCHAR2(150 CHAR), "ATTRIBUTE5" VARCHAR2(150 CHAR), "PASSWORD_HASH" VARCHAR2(2000 CHAR), "PASSWORD_RAW" VARCHAR2(30 CHAR), "MOBILE" VARCHAR2(30 CHAR), "EMAIL" VARCHAR2(100 CHAR), "SOURCE" VARCHAR2(30 CHAR), "USER_TYPE" VARCHAR2(30 CHAR), "PWD_UPDATE_STATUS" VARCHAR2(10 CHAR), "PWD_UPDATE_DATE" DATE, "PWD_UPDATED_BY" NUMBER, "PWD_OTP" VARCHAR2(10 CHAR), "PWD_OTP_REQ_START" DATE, "PWD_OTP_REQ_END" DATE, "EMP_IMG" BLOB, "COMPANY_ID" NUMBER, "CREATED" DATE, "CREATED_BY" VARCHAR2(255 CHAR), "UPDATED" DATE, "UPDATED_BY" VARCHAR2(255 CHAR), "ACCESS_ROLES" VARCHAR2(500), "FULL_NAME" VARCHAR2(150), "APPLICATION_ID" NUMBER, "BUYER" VARCHAR2(1) DEFAULT 'N', "AGENT" VARCHAR2(1) DEFAULT 'N', "LINKEDIN" VARCHAR2(200), "LAST_LOGGED_METHOD" VARCHAR2(30), "LAST_LOGGED" DATE, "DISPLAY_IMAGE" VARCHAR2(4000), "EMAIL_VERIFIED" VARCHAR2(10) DEFAULT 'N', "COUNTRY" VARCHAR2(100), "STATE" VARCHAR2(100), "CITY" VARCHAR2(100), "ADDRESS_1" VARCHAR2(100), "ADDRESS_2" VARCHAR2(100), "POSTAL_CODE" VARCHAR2(15), "ORG_TAX_ID" VARCHAR2(20), "ORG_INCOME_ID" VARCHAR2(20), "CONNECT_REQ" VARCHAR2(10) DEFAULT 'N', "REQUESTED_BY" NUMBER, "REQUEST_DATE" DATE, "ISD" NUMBER, "REQUESTED_BY_COMP" NUMBER, "ACCOUNT_TYPE" VARCHAR2(20), "RELEASE_REQUEST" VARCHAR2(10) DEFAULT 'N', "RELEASE_REQUEST_DATE" DATE, "RELEASE_REQUEST_BY" NUMBER, "RELEASE_REQUEST_REM" VARCHAR2(1000), "WF_ID" NUMBER, "ACCEPT_PRIVACY_POLICY" VARCHAR2(10) DEFAULT 'N', "ACCEPT_PRIVACY_POLICY_DATE" DATE, "REFERRAL_USER" NUMBER, "OTP_LOGIN" NUMBER, "OTP_LOGIN_DATE" DATE, "PARSE_COUNT" NUMBER DEFAULT 0, "PARSE_COUNT_MAX" NUMBER DEFAULT 3, CONSTRAINT "LAMCX_USERS_ID_PK" PRIMARY KEY ("ID") USING INDEX ENABLE ) ; Create an Index on the table CREATE OR REPLACE EDITIONABLE TRIGGER "LAMCX_USERS_BIU" BEFORE INSERT OR UPDATE ON LAMCX_USERS FOR EACH ROW BEGIN IF inserting THEN — Make sure to save the full name — Make sure to save the username in upper case :new.user_name := upper(:new.user_name); — Hash the password so we are not saving clear text — :new.password_hash := lams_auth.hash_password(upper(:new.user_name), :new.user_name||'@123'); :new.password_hash := lamcx_auth.hash_password(upper(:new.user_name), nvl(:new.password_raw,:new.user_name||'@123')); :new.created := sysdate; :new.created_by := e(coalesce(sys_context('APEX$SESSION', 'APP_USER'), user)); END IF; :new.first_name := initcap(substr(:new.first_name,1,100)); :new.last_name := initcap(substr(:new.last_name,1,100)); :new.full_name := trim(:new.first_name||' '||:new.last_name); :new.updated := sysdate; :new.updated_by := e(coalesce(sys_context('APEX$SESSION', 'APP_USER'), user)); END LAMCX_USERS_biu; CREATE TABLE "LAMCX_APEX_WORKSPACE_SESSIONS" ( "WORKSPACE_ID" NUMBER NOT NULL ENABLE, "WORKSPACE_NAME" VARCHAR2(255) NOT NULL ENABLE, "WORKSPACE_DISPLAY_NAME" VARCHAR2(4000), "APEX_SESSION_ID" NUMBER, "USER_NAME" VARCHAR2(255), "REMOTE_ADDR" VARCHAR2(255), "SESSION_CREATED" DATE NOT NULL ENABLE, "SESSION_IDLE_TIMEOUT_ON" DATE NOT NULL ENABLE, "SESSION_LIFE_TIMEOUT_ON" DATE NOT NULL ENABLE, "SESSION_MAX_IDLE_SEC" NUMBER(5,0) NOT NULL ENABLE, "SESSION_TIME_ZONE" VARCHAR2(255), "SESSION_LANG" VARCHAR2(5), "SESSION_TERRITORY" VARCHAR2(255), "SESSION_DEBUG_LEVEL" NUMBER(1,0), "SESSION_TRACE_MODE" VARCHAR2(5), "SESSION_AUTHENTICATION_ID" NUMBER, "SESSION_TENANT_ID" VARCHAR2(32), "SESSION_AUTHENTICATION_NAME" VARCHAR2(255), "APPLICATION_ID" NUMBER, "SESSION_END" DATE ) ; As now we have created our package specification, ready with our database table, now we proceed to create our package body. Inside package, select Body tab and start writing your actual logic for the process. Function – HASH_PASSWORD FUNCTION hash_password ( p_user_name IN VARCHAR2, p_password IN VARCHAR2 ) RETURN VARCHAR2 IS l_user lamcx_users.user_name%TYPE := upper(p_user_name); l_password VARCHAR2(1000); BEGIN — — The following encrypts the provided password and returns the encrypted string. — This is a one-way encryption using SHA512 — SELECT standard_hash(p_user_name || p_password, 'SHA512') INTO l_password FROM dual; RETURN l_password; END hash_password; Procedure – SIGNUP PROCEDURE signup ( p_first_name IN VARCHAR2, p_last_name IN VARCHAR2, p_user_name IN VARCHAR2, p_email IN VARCHAR2, p_app_id NUMBER, p_company_id NUMBER, p_referral varchar2, p_password IN VARCHAR2 DEFAULT NULL ) IS l_status VARCHAR2(1000) := NULL; l_status2 VARCHAR2(1000) := NULL; l_id NUMBER; x NUMBER := 0; cnt NUMBER := 0; — e_oldpass EXCEPTION; — PRAGMA exception_init ( e_oldpass, -20001 ); BEGIN BEGIN SELECT COUNT(1) INTO x FROM lamcx_users WHERE user_name = p_user_name AND (company_id is null or company_id = p_company_id); IF x > 0 THEN cnt

When creating an authentication scheme from the gallery, you can choose from a set of preconfigured options that follow standard authentication and session management practices. To link an authentication scheme to your application, start by selecting the application and then creating a new authentication scheme. Note that a newly created scheme is not automatically activated. To enable it, simply edit the scheme and click Make Current Scheme to set it as the active authentication method for your application. This section describes all preconfigured authentication schemes that ship with Oracle APEX. 1. Oracle APEX Accounts Oracle APEX Accounts are user accounts that are created within and managed in the APEX user repository. When you use this method, your application is authenticated against these accounts. Oracle APEX Accounts authentication requires that a database user (schema) exists in the local database. When using this method, the user name and password of the database account is used to authenticate the user. Oracle APEX Accounts is a good solution when: You want control of the user account repository. User name and password-based approach to security is sufficient. You do not need to integrate into a single sign-on framework. Oracle APEX Accounts is an especially good approach when you must get a group of users up and running on a new application quickly. 2. Custom Authentication Creating a Custom Authentication scheme from scratch to have complete control over your authentication interface. Custom authentication is the best approach for applications when any of the following is true: Database authentication or other methods are not adequate. You want to develop your own login form and associated methods. You want to control security aspects of session management. You want to record or audit activity at the user or session level. You want to enforce session activity or expiry limits. You want to program conditional one-way redirection logic before Oracle APEX page processing. You want to integrate your application with non-APEX applications using a common session management framework. Your application consists of multiple applications that operate seamlessly (for example, more than one application ID). When you want your users to be able to register their own accounts. For detailed understanding, refer Custom Auth article by Bharat. 3. Database Accounts Database Account Credentials authentication utilizes database schema accounts to authenticate users. 4. HTTP Header Variable Authenticate users externally by storing the username in a HTTP Header variable set by the web server. HTTP Header Variable supports the use of header variables to identify a user and to create an Oracle APEX user session. Use HTTP Header Variable authentication scheme if your company employs a centralized web authentication solution like Oracle Access Manager which provides single sign-on across applications and technologies. User credential verification is performed by these systems and they pass the user’s name to APEX using a HTTP header variable such as “REMOTE_USER” 5. Open Door Credentials Enable anyone to access your application using a built-in login page that captures a user name. 6. No Authentication (using DAD) Adopts the current database user. This approach can be used in combination with a mod_plsql Database Access Descriptor (DAD) configuration that uses basic authentication to set the database session user. 7. LDAP Directory Authenticate a user and password with an authentication request to a LDAP server. 8. Oracle Application Server Single Sign-On Server Delegates authentication to the Oracle AS Single Sign-On (SSO) Server. To use this authentication scheme, your site must have been registered as a partner application with the SSO server. 9. SAML Sign-In Delegates authentication to the Security Assertion Markup Language (SAML) Sign In authentication scheme. 10. Social Sign-In Social Sign-In supports authentication with Google, Facebook, and other social networks and enterprise identity providers that support OpenID Connect or OAuth2 standards. 2 0

Table of Contents 1. Why Modernise Oracle Forms? 2. Why Oracle APEX? 3. The Oracle RAD Stack 4. Why should you modernise your Forms apps with APEX? 5. Migration Strategy 6. How APEX accelerates the modernisation of your Forms apps? 7. Summary Oracle Forms has been a reliable platform for enterprise applications for decades. However, with the shift towards cloud computing, web-first, and mobile-friendly architectures, modernising legacy Forms applications has become a necessity. Why Modernise Oracle Forms? Modernising improves UX, lowers TCO, and ensures cloud-readiness. APEX is Oracle’s low-code development platform offering scalability, rapid development, and full cloud support. Why Oracle APEX? Oracle APEX is the ideal application platform for modernising your existing Forms based apps. It is the world’s most popular enterprise low-code application platform and enables you to build scalable, secure enterprise apps, with world-class features, that can be deployed anywhere – cloud or on-premises. Oracle APEX follows a straightforward 3-tier architecture where requests are routed from the browser via a web server to the database. All processing, data handling, and business logic are carried out within the database. This design ensures zero-latency data access, exceptional performance, and scalability by default. The Oracle RAD Stack The Oracle RAD stack is an inclusive technology stack based on three core components: Oracle REST Data Services (ORDS), Oracle APEX, and Oracle Database. This stack provides all the necessary components to develop and deploy world-class, powerful, beautiful, and scalable apps. In addition, both Oracle APEX and ORDS are no-cost features of Oracle Database, meaning if you have Oracle Database, you already have this Oracle RAD stack. Why should you modernise your Forms apps with APEX? Modernising Oracle Forms with APEX helps retain business logic while moving to a modern platform. Here’s why it’s a smart move: Future-Proof Technology Oracle Forms is legacy: While still supported, Oracle Forms is not the focus of Oracle’s innovation efforts. APEX is actively developed: APEX evolves rapidly with modern web standards and is tightly integrated with Oracle Database and Oracle Cloud Infrastructure. Improved User Experience APEX apps offer modern, responsive, mobile-friendly interfaces out-of-the-box. Forms apps tend to be desktop-only and dated in appearance and functionality. No Middleware Dependency Oracle Forms requires Oracle Fusion Middleware (WebLogic Server), which adds complexity and cost. APEX runs natively within the Oracle Database, reducing the tech stack and simplifying architecture. Cloud Readiness APEX is cloud-native, with full support on Oracle Cloud (including Autonomous Database). Migrating to APEX positions your app for easy cloud deployment, scalability, and managed infrastructure. Lower Development & Maintenance Costs APEX is low-code, so development is faster and easier, especially for database-centric applications. Maintenance is simpler and less resource-intensive compared to managing legacy Forms environments. Easy Integration and Extensibility APEX provides REST and web service support for easy integration with third-party systems, APIs, and mobile apps. JavaScript, CSS, and plug-ins allow for custom UI/UX enhancements. Smooth Migration Tools and Methodology Oracle provides APEX Forms Migration Toolkits and services to assess, extract, and rebuild Forms logic in APEX. Many PL/SQL procedures and business logic can be reused, speeding up the modernisation process. Community and Ecosystem Support APEX has a growing, vibrant developer community and broad industry adoption. Availability of skilled resources and open-source tools makes it easier to find support and accelerate innovation. Modernising Forms to APEX empowers you to preserve your existing Oracle investment while embracing a flexible, web-based, cloud-ready platform. It boosts developer productivity, reduces TCO, and delivers a modern experience to end users. Migration Strategy There is no silver bullet that will magically transform a complex Oracle Forms application into a beautiful, completely modern, intuitive Web app. Modernising Oracle Forms applications using Oracle APEX is a strategic way to preserve business logic while moving to a web-based, low-code environment. Here’s a step-by-step approach to guide the modernisation process: The diagram below shows the high-level modernisation architecture to modernise your Forms apps. How APEX accelerates the modernisation of your Forms apps? There is no silver bullet that will magically transform a complex Oracle Forms application into a beautiful, completely modern, intuitive Web app. 1. Assess the Existing Oracle Forms Application  Inventory Forms and Reports: List all Forms (.fmb), Libraries (.pll), and Reports (.rdf). Identify Business Logic: Determine where key logic resides – in Forms, libraries, or the database. Understand Dependencies: Check for integration with other systems, external libraries, or JavaBeans. Classify Complexity: Categorise Forms (simple data entry, complex logic, or custom UI behaviour). 2. Prepare the Oracle APEX Environment  Provision Infrastructure: Use Oracle APEX on Autonomous Database, Oracle Cloud Infrastructure, or on-premises. Set Up Workspace & Schema: Match the schema used in Oracle Forms for a smoother transition. Install APEX Utilities: Use Forms Migration Workbench (FMWB) for metadata extraction Install any needed APEX plug-ins for advanced UI components. 3. Extract and Reuse Business Logic  Leverage PL/SQL: Reuse stored procedures and packages from the Forms backend in APEX. Refactor Code: Move Forms-based logic (e.g., triggers, program units) to database packages where necessary. Avoid Hardcoding in APEX: Keep logic in PL/SQL to ensure maintainability and reusability.   4. Rebuild UI in APEX Use APEX Components: Rebuild Forms using interactive Forms, grids, charts, and calendars. Modernise UX: Take advantage of APEX’s responsive, accessible, and theme-based UI. Mimic Critical Functionality: Ensure that key features (like LOVs, validations, navigation) from Forms are implemented in APEX. 5. Integrate Custom Features JavaScript for Client-side Interactions: Replace JavaBeans and client-side triggers with JavaScript and Dynamic Actions. Plug-ins: Use or build APEX plug-ins for advanced UI components or third-party integrations. 6. Migrate Reports Use APEX Reporting Features: Interactive Reports, Classic Reports, and BI Publisher integration. Convert RDFs to APEX: Rebuild as needed; complex Reports may require rethinking design using APEX features. 7. Test & Validate User Acceptance Testing: Validate business process equivalence between Forms and APEX. Performance Testing: Ensure APEX apps perform well, especially for heavy transactions. Security Testing: Configure roles, authorisations, and session management. 8. Deploy & Monitor Gradual Rollout: Start with a pilot module and gradually replace Forms. User Training: Provide end-user training on the APEX-based interface. Use APEX Monitoring Tools: Monitor usage, errors, and performance with APEX Activity Reports. 9. Application Programming Interfaces (APIs): APEX provides these APIs to allow experienced

In today’s fast-paced business environment, efficiency is key. The ability to automate processes, streamline workflows, and enhance decision-making is crucial for staying competitive. Oracle ERP (Enterprise Resource Planning) is a powerful tool that enables businesses to achieve just that. With a comprehensive suite of integrated applications, Oracle ERP transforms how organizations manage their financials, supply chain, human resources, and more. In this blog, we explore how Oracle ERP can optimize business operations, reduce costs, and drive growth. What Is Oracle ERP? Oracle ERP is a cloud-based suite of applications designed to streamline business operations by providing a single, unified system for managing core business functions. Unlike traditional on-premise solutions, Oracle ERP is designed to be flexible, scalable, and highly customizable to meet the unique needs of any organization, regardless of size or industry. With features that span financial management, procurement, inventory management, project management, human resources, and more, Oracle ERP connects every department of your business, allowing for seamless data flow and enhanced collaboration. Real-World Case Studies Oracle ERP in Action A major retail chain implemented Oracle ERP to streamline its supply chain management. By integrating procurement, inventory management, and sales data, the company reduced stockouts, optimized inventory levels, and improved its forecasting capabilities. As a result, the retailer increased its on-time delivery rate by 20% and reduced excess inventory by 15%, leading to significant cost savings. Getting Started with Oracle ERP Implementing Oracle ERP is a significant step for any business, but with the right approach, it can lead to tremendous improvements in efficiency and growth. The first step is to assess your business needs and determine which modules of Oracle ERP are most relevant to your organization. Then, a customized implementation plan can be developed, with guidance on data migration, system integration, and employee training. 1 0

In today’s fast-paced digital world, businesses must adapt quickly to new technologies to stay competitive. Cloud computing is one of the most transformative tools available, and Oracle Cloud is leading the way by offering an all-in-one solution that goes beyond just infrastructure. With its comprehensive suite of services, Oracle Cloud enables businesses to optimize operations, reduce costs, and enhance decision-making processes. Whether you’re a small startup or a large enterprise, Oracle Cloud has the tools to help you innovate, scale, and stay secure in the ever-evolving tech landscape. Why Oracle Cloud? Oracle Cloud offers far more than traditional cloud services. It provides a full range of applications, platforms, and infrastructure services designed to meet the needs of businesses in all industries. From automating workflows to improving data management and enhancing business intelligence, Oracle Cloud provides end-to-end solutions that can transform how businesses operate. How to Get Started with Oracle Cloud The first step toward leveraging Oracle Cloud is understanding how it aligns with your business goals. Techno Mind can guide you through this process by offering customized Oracle Cloud solutions that are tailored to your specific needs. Whether you’re looking for a cloud-based CRM solution, a full-fledged ERP system, or a more efficient data analytics platform, our experts can help you design and implement the right cloud strategy. Transform Your Business with Oracle Cloud Oracle Cloud isn’t just about upgrading your infrastructure; it’s about transforming the way your business operates, innovates, and grows. By integrating Oracle Cloud services into your business processes, you can stay agile, responsive, and ready to meet the challenges of the modern business landscape. Whether you’re improving internal processes, enhancing customer interactions, or driving smarter business decisions, Oracle Cloud has everything you need to succeed. 1

In today’s fast-evolving digital landscape, businesses need to stay ahead of the curve to remain competitive. At Techno Mind, we understand the challenges and opportunities that technology presents. Through our blog, we strive to deliver the most relevant, insightful, and actionable content that can guide you on your journey to digital transformation. Whether you’re a startup or an established enterprise, we believe that the right technology, when implemented strategically, can unlock endless growth and innovation. A Comprehensive Resource for Digital Transformation We know that navigating the digital world can feel overwhelming. With new technologies and trends emerging regularly, it’s important to have a trusted partner who can break down complex concepts and provide clear, practical advice. Our blog covers everything from foundational concepts for newcomers to deep dives for advanced professionals. Whether you’re starting your journey with cloud computing or looking to optimize existing systems, you’ll find valuable resources to help you move forward. How to Get the Most Out of Oracle APEX Oracle APEX is a powerful low-code platform that enables businesses to develop scalable web and mobile applications quickly and securely. With its intuitive drag-and-drop interface, users can create robust applications without extensive coding knowledge, reducing development time and costs. APEX seamlessly integrates with Oracle’s powerful database, ensuring high performance and data security. Whether you are building internal tools, customer-facing applications, or enterprise-level solutions, Oracle APEX allows for rapid deployment and easy scalability to meet evolving business needs. Its flexibility and ease of use make it the ideal platform for businesses looking to innovate and stay competitive. Mastering Oracle Cloud for Your Business Oracle Cloud is more than just a cloud infrastructure—it’s a comprehensive suite of services that can transform how businesses operate. From improving data management to enhancing business intelligence, our blog guides you on how to unlock the full potential of Oracle Cloud. With its robust security features, cost-efficient solutions, and scalability, Oracle Cloud empowers businesses to innovate and scale seamlessly. Whether you’re looking to streamline operations, enhance customer experiences, or drive smarter decision-making, Oracle Cloud offers the tools to help you achieve your goals.