I am currently designated as Principal Presales Consultant in Oracle, Sydney, Australia. Having 20 + years professional experience. Currently Looking after Presales activities for iPaaS related Cloud Offering e.g. Oracle SOA Cloud Services (OSCS), Oracle Integration Cloud Service (OIC), MFT and Oracle API Platform etc. In past I have worked for many small to large companies. I have played various roles such as Integration Solution Architect / Integration Technical Architect / Integration Team Lead / Integration Specialist / SOA Infrastructure Admin / SOA Designer and SOA Developer in multiple companies. I have worked in various OFMW products such as Weblogic, OSB, BAM, SOA Suite, OWSM and Mediator etc. I have good hands-on experience in SOA Administration as well. In addition to that, have handful experience of SOA Architecture, Analysis, Design, Development, SIT Testing, Performance and Load Testing, Production and Post-Production Support for SOA projects.
Recently, I have been come across scenario where one of my Customer, building a VBCS application which needs a combobox to be populated with all IDCS Users name as approver. Customer builds the solution but was having issue with existing solution. Problem was when One user e.g. Sys Admin User ID (Administrator role) login using his credentials, he can see combobox populated with IDCS users name. However, when another business user when they login they can’t see combobox fetching IDCS User list. Obliviously, its permission issue. Sys Admin being part of IDCS Admin group has all privilege but other users in his tenancy are not, hence problem was coming.
Now, VBCS has two mechanisms for Identity propagation. Please read this section for more info. a) login user identity gets propagated to invoke REST API as part of service connection b) Developer can use fixed credentials to invoke backend REST API using service connection.
Security is the key aspect in any implementation, specially when it comes to publish your API/Integration to external consumer outside of your organization.
Oracle Integration Cloud has capability to design an Integration which could be shared across internal/external Organisation. In this scenario security is paramount to protect that endpoint which will be published through OIC. By default, if your OIC Integration has REST Endpoint with Trigger role, it’s offering Basic Authentication Or OAuth 2.0 Or both Security policy.
With Invoke role there are many other flavours of OAuth grant type which I won’t be covering in this blog. I am more focused on this blog for OAuth 2.0 for OIC Rest Adapter for trigger role only.
Recently came across interesting use case, so thought to cover blog on that topic. One of my Customer was having issue to connect OIC to Oracle Autonomous Database (ADB) which is sitting in private subnet via Oracle Integration Cloud connectivity agent. So, I did some debugging and fix the issue and that learning I am going to cover here. I will explain here how we can setup an ATP Database in private subnet, setup all required security/firewall rules, setup OIC connectivity agent, bastion compute in public subnet and then connecting OIC to ATP DB sitting in private subnet via Connectivity agent.
In this fictitious scenario, customer HR division representative sending employee code to OIC REST endpoint to retrieve detailed information for an employee record which is been stored in Oracle ADB instance located in private subnet. Since, ADB is in private subnet it’s not accessible to outside world even app tier, and that’s where OIC Connectivity agent comes into picture to provide connectivity between OIC which is public internet and ADB database which is in private subnet.
Oracle Fusion Applications are a suite of applications built on Oracle Cloud that include cloud-based applications for enterprise resource planning, enterprise performance management, supply chain management and manufacturing, human capital management, and customer experience.
There are numerous use cases where standard object resides inside Fusion ERP needs to be extended to include more fields or completely new Custom object needs to be created.
In this blog, I will be covering how you can create a new Custom Object inside Fusion ERP and trigger transactions for that Custom object.
Oracle Visual Builder is a cloud based UX development Platform as a Service (PaaS) offering from Oracle Cloud. It provides an open-source standards-based solution to develop, collaborate on, and deploy Web and Mobile applications within Oracle Cloud.
VBCS also provide another awesome feature called Business Objects. A business object is a resource, such as an invoice or purchase order, like a database table; it has fields that hold the data for your application. Like a database table, a business object provides the structure for data used with business processes. Business objects are stored in a database.
Business Object also get exposed through REST Endpoint out-of-box, user doesn’t need to install/configure anything other than just creating a Business Object. However, those REST API are protected and need authentication when someone want to access from outside word.
In this blog, I am listing very simple steps what’s needed if developers want to expose Business Object REST API to outside world as anonymous users.
This blog title seems quite easy and simple but there are bit of steps to manage SOA Market Place Image (SOA MP) Database password reset configuration within application tier which I will discuss in this blog.
There could be multiple situations when user needs to change SOA MP Database password e.g. SOA MP DB password might got expired, or about to expire hence that’s must need to reset to new password which should get further updated all relevant places inside SOA application tier.
In my case it was for SOA dev/test environment eventually SOA MP DB password has default expiry set for 6-month time and it got expired, due to which SOA application was not coming up and constantly keep throwing below error-
Caused by: java.sql.SQLException: ORA-28001: the password has expired
Note:
In my case below software version were used.
SOA Suite 12.2.1.4
Oracle Autonomous Database 19c
Instruction given here may not work exactly for another SOA and DB version.
To fix above problem. We need to perform below steps –
Need to find out how many places within SOA Application tier DB password been used?
Are the SOA MP DB users locked? or Password expired? or both
Reset & unlock the SOA MP DB user’s password
Create encrypted value of new password for WebLogic Data source & update all Data source files with this new encrypted password and restart all WLS Admin/SOA servers.
Note: If step 4 doesn’t work (In my case, it hasn’t worked but as per Oracle documentation it should work. I may be doing some mistake which hard to troubleshoot so I took alternate path as workaround)5
5. (Optional) force WebLogic to start in Development mode instead of Production mode as clear text password not accepted in Production mode inside JDBC configuration files.
6. (Optional) Use new password as clear string inside JDBC configuration file, bring up WebLogic server and again update new password from WebLogic console for all Data sources, this will encrypt database password inside data source files.
7. Create new DB profile with UNLIMITED limit and assigning to each SOA DB users to avoid password expiry in future.
Need to find out how many places within SOA Application tier DB password been used? –
SOA interact with Database using WebLogic Data source which act as bridge between application tier to Database tier. So, we need to know how many WebLogic Data sources using the SOA Database. There are two ways you can validate this –
If WebLogic Admin server up and running then go to console>>data sources >> and make a note of all Data source except your Custom one which you might have created for some other purpose. E.g.
b) If WebLogic server not up and running (which most likely the case) then ssh to server where SOA binaries and domain reside and find out ‘jdbc’ folder, which will list out all Data sources which exists within WebLogic.
E.g.
/u01/data/domains/mgusoa_domain/config/jdbc/
So, now first challenge is sorted out, now we know what are all Data sources which are using SOA MP database, so if we change SOA MP database password all these Data source files needs to be updated with new password.
Are the SOA MP DB users locked? or Password expired? or both
Now, let’s move to next step and check whether SOA MP Database DB users are locked, or password expired or both.
In order to do that, first you need to know DB users name used for SOA. One quick and easy way to open one of Data source files and find out DB prefix e.g. “SP1603934449”
Once you know DB prefix, run below query in SOA Database to know all SOA DB users.
select username from dba_users where username like 'SP1603934449%';
So, now you know all the SOA DB users within SOA Database which are used by SOA application tier. Next step to check each of them whether they are locked, or password expired.
Run below sql query to know their status –
select username, account_status from dba_users where username like 'SP1603934449%';
You should be able to see “account_status” column value either “LOCKED” or “EXPIRED & LOCKED”
In my case most of them with status “EXPIRED & LOCKED”
Reset & unlock the SOA MP DB user’s password
ALTER USER SP1603934449_IUA identified by Lxxx#1xxxxx;
ALTER USER SP1603934449_SOAINFRA ACCOUNT UNLOCK;
commit;
Once password reset and unlocked, run the below SQL query again and account status should be “OPEN” now
select username, account_status from dba_users where username like 'SP1603934449%';
Create encrypted value of new password for Weblogic Datasource & update all datasource files with this new encrypted password and restart all WLS Admin/SOA servers.
WebLogic data source files doesn’t except password in clear text if, WebLogic running in “Production” mode. If WebLogic running in “Development” mode, then it can accept clear text password. In my case, although it was dev/test environment but unfortunately it was set for “Production” mode, so I have to generated encrypted value of new password.
Run the WebLogic Encryption Utility and enter the password you set for the database schemas:
/u01/jdk/bin/java weblogic.security.Encrypt
password: new_password
Once you run “/u01/jdk/bin/java weblogic.security.Encrypt” command it will ask to type new password, type the new password. It will further generate encrypted password value as highlighted in above screen shot.
Once you know the new encrypted value for new password, update all the JDBC data sources files one by one with this new encrypted password and restart the WLS admin/SOA Servers
Your WebLogic server should be up and running after this change.
Note: If step 4 doesn’t work (In my case, it hasn’t worked but as per Oracle documentation it should work. I may be doing some mistake which hard to troubleshoot so I took alternate path as workaround)
(Optional) force WebLogic to start in Development mode instead of Production mode as clear text password not accepted in Production mode inside JDBC configuration files.
You must update multiple configuration file to force WebLogic to start in “Development” mode.
I have updated below 4 configuration files. The full path is just for reference, you must use your environment path to find out these files. Make Production value either “false” or “” depending up on file existing value.
Once above configuration is done, now you can update all JDBC data source files with clear text password
Once all JDBC data source xml files got updated with new password in clear text format.
Restart the WLS admin and SOA server using out of box script available in SOA Market Place Image
/opt/scripts/restart/restart_12c_servers.sh
Admin server should go in running mode
Now you can access WebLogic Admin console and start remaining servers if needed.
Last steps to update all data source from Weblogic console again, this will update all JDBC data source files in backend with encrypted password instead of clear text password. Use the same clear text password from WebLogic console for all Data source files and click save button, this will replace clear text password in backend to encrypted password.
Create new DB profile with UNLIMITED limit and assigning to each SOA DB users to avoid password expiry in future.
Above command should create a database profile with password expiry set to unlimited and can be verified by running below command
select * from dba_profiles where resource_name = 'PASSWORD_LIFE_TIME';
Now, set all SOA DB Users to use that profile.
Run below command to check whether profile for each SOA Database user been changed or not?
Above step hopefully will force SOA DB users password not to expire in future and will help to avoid all above steps. However, that’s not recommend for Production environment.
I have been recently engaged in one assignment where I was expected to make connectivity with NetSuite to create Customer inside NetSuite. However, condition was to connect NetSuite using “Token based Authentication” only. That was Customer’s key requirement to establish secure connectivity to NetSuite.
Token based authentication needs many input parameters such as WSLD URL, Consumer Key, Consumer Secret, Token, Token Secret and Account ID.
I had to spent bit of time to work-out how to get all above parameters values and in this blog I just want to share that learning.
There is already NetSuite Connector Documentation available which describe the instructions about Token Based Authentication. This blog is just expanding that document with some additional info and screenshots.
So, let’s get started-
Prerequisite
Before you establish connectivity from OIC to NetSuite using Token Based Policy there are certain prerequisite which you need to accomplish as listed below-
Enable Client/Server SuiteScript, REST/SOAP Suite Talk and Token based Authentication
To connect to Oracle NetSuite, you must have registered with Oracle NetSuite and enabled key features (such as SOAP and REST web services) on your Oracle NetSuite instance.
1. Visit http://www.netsuite.com to register with Oracle NetSuite. Ensure that you obtain an account with administrator privileges.
2. Enable connection-related features on your Oracle NetSuite instance.
a. On your NetSuite home page, select Setup, then Company, and then Enable Features.
b. Click the SuiteCloud subtab.
c. In the SuiteScript section, check the following boxes:
i. CLIENT SUITESCRIPT. Click I Agree on the SuiteCloud Terms of Service page.
ii. SERVER SUITESCRIPT. Click I Agree on the SuiteCloud Terms of Service page.
d. In the SuiteTalk section, check the following boxes:
i. SOAP WEB SERVICES. Click I Agree on the SuiteCloud Terms of Service page.
ii. REST WEB SERVICES. Click I Agree on the SuiteCloud Terms of Service page.
e. In the Manage Authentication section, check the TOKEN-BASED AUTHENTICATION box. Click I Agree on the SuiteCloud Terms of Service page.
You must enable the TBA feature if you want to use the TBA authentication policy to connect to Oracle NetSuite from external applications.
f. Click Save.
Create an Integration Role with Token-Based Authentication (TBA) Permissions
Create a new role and assign TBA permissions along with other necessary permissions (specific to your integration) to it. You’ll assign the Oracle Integration user account—which you’ll subsequently create—to this role.
Note:
As a best practice, avoid using the Administrator and Full Access roles/users in Oracle NetSuite connections that use the TBA security policy.
To create a new role:
1. On the NetSuite home page, select Setup, then User/Roles, then Manage Roles, and then New.
2. On the Role page:
a. Enter a name for the role, for example, Oracle Integration Role.
b. In the CENTER TYPE drop-down field, select System Administrator Center.
c. In the Subsidiary Restrictions section, select All. For information on subsidiary restrictions, see Restricting Role Access to Subsidiaries.
d. On the Permissions tab, To provide TBA permissions to the new role, you must add the User Access Token permission to the role with full access. This permission is present on the Setup subtab under the Permissions tab.
You can add other permissions to the role depending on the tasks you want to allow the users assigned this role to perform. For any custom role, you must specifically add the SOAP web services permission with the Full level. See Assigning the SOAP Web Services Permission to a Role.
e. After you’ve added all the necessary permissions, click Save to create the new role.
Create a User Account for Oracle Integration
Create a user account for Oracle Integration and assign this account to the Token Base Authentication role “OracleIntegrationRole” you created previously. You’ll use the credentials associated with this user account to connect to NetSuite from Oracle Integration.
follow the procedure provided here:
1. On the NetSuite home page, select Lists, then Employees, then Employees, and then New.
2. On the Employee page:
a. In the NAME fields, enter a first name and last name for the user, for example, Integration User05.
b. In the EMAIL field, enter a valid email address.
c. In the SUBSIDIARY drop-down field, select a subsidiary of your choice.
d. Scroll down and click the Access tab to perform additional configurations.
i. Select the GIVE ACCESS and MANUALLY ASSIGN OR CHANGE PASSWORD check boxes.
ii. In the PASSWORD field, enter a password for the user account.
iii. Re-enter the password in the CONFIRM PASSWORD field.
iv. To assign this user to the “OracleIntegrationRole“ TBA role created previously:
• With the Roles subtab selected, select the TBA role from the ROLE drop-down field; for example, Oracle “OracleIntegrationRole”.
• Click Add.
e. Click Save to create the new user record.
Create an Integration Record for Oracle Integration
Before you can create and assign API tokens (for TBA) to a user account, you must create an integration record for the application that will use this user account to access NetSuite.
Create an integration record for the Oracle Integration application.
1. On the NetSuite home page, select Setup, then Integration, then Manage Integrations, and then New.
2. On the Integration page:
Enter a name for the integration record, for example, “ExtIntegrationApp”
a. Optionally, enter a description for the record.
b. Leave the Enabled option selected in the STATE drop-down field.
c. On the Authentication tab:
i. Leave the TOKEN-BASED AUTHENTICATION check box selected.
ii. Deselect the TBA: AUTHORIZATION FLOW and AUTHORIZATION CODE GRANT check boxes.
d. Click Save.
The confirmation page displays the client credentials for this integration record or application.
Create an Access Token for the User Account
Create and assign an access token to the Oracle Integration user account.
1. On the NetSuite home page, select Setup, then User/Roles, then Access Tokens, and then New.
Note: “Access Tokens” page was not appearing before, only when I executed “Enable Client/Server SuiteScript, REST/SOAP Suite Talk and Token based Authentication” section as per this document then only “Access Token” page appears.
2. On the Access Token page:
a. In the APPLICATION NAME field, select the integration record created previously e.g. “ExtIntegrationApp”
b. In the USER field, select already existed Oracle Integration’s user account e.g. “rn13manish”
c. In the ROLE field, select the appropriate Token Base Authentication role e.g. “OracleIntegrationRole”
d. Leave the TOKEN NAME field unchanged.
e. Click Save.
The confirmation page displays the token values for the user account.
3. Note down the Token ID and Token Secret values. You’ll use these credentials to connect to NetSuite from Oracle Integration.
Make a Note of the NetSuite Account ID
Along with other credentials, you’ll require the NetSuite Account ID to connect to NetSuite from Oracle Integration.
To view your account ID:
1. On the NetSuite home page, select Setup, then Integration, and then SOAP Web Services Preferences.
2. Note down the Account ID displayed at the top of the page.
3. Click Cancel to exit the page.
Assemble the Oracle NetSuite WSDL URL
You need to draft the NetSuite WSLD using below technique –
Once you WSDL are ready, make sure you test in browser and it should open –
OIC Connection to NetSuite Using Token Based Authentication
Once all parameters values such as WSLD URL, Consumer Key, Consumer Secret, Token, Token Secret and Account ID are ready then make connection to NetSuite using NetSuite Adapter from OIC Home Page >> Integration >> Connection
Once connection is established, you can perform any CRUD operation for any business object as per your option available. In my case I did create Customer inside NetSuite by posting a JSON payload via REST Adapter to NetSuite Adapter.
Note: The role what you using to communicate to NetSuite e.g. in my case it was “OracleIntegrationRole” must have given permission to particular business object such as “Customers”, otherwise your integration will be keep failing and will give below error message –
{
“Status” : {
“IsSuccess” : “true”,
“Type” : “ERROR”,
“Code” : “INSUFFICIENT_PERMISSION”,
“Message” : “Permission Violation: You need the ‘Lists -> Customers’ permission to access this page. Please contact your account administrator.”,
“FaterSubmittedFailed” : “”
},
“ContactRef” : {
“InternalId” : “”,
“ExternalId” : “”,
“Name” : “”,
“Status” : “false”
}
}
In order to fix above error. Make sure you give “Customer” and “Customer Profile” permission to “OracleIntegrationRole” role.
Here is my OIC Integration.
Here is my mapping.
Note:
There could be some mandatory field for the object what you trying to create inside NetSuite, so first try creating object directly using NetSuite frontend with minimal fields which will give you idea what are the mandatory fields needed for that object.
Once above JSON got posted and I got success reply, you can further login into NetSuite to validate whether that particular Customer got created or not. In my case its absolutely got created.
Oracle Integration Cloud (OIC) is Oracle’s next generation modern Integration solution Platform as a Service (PaaS) offering. The core purpose of this product to integrate various SaaS and On-prem systems real time. In addition to Integration capability, it also provides Process Automation and Visual Builder Capability. Details docs are available here.
OIC has concept of Adapters. There are huge range of adapters available and documented here.
One of the Adapter REST Adapter been used to expose an Integration to outside world for consumption. In order word, it’s an entry point for most of Integrations what we developed using OIC. It also gets used to invoke any external REST based endpoint.
REST Adapter support Basic Auth and various flavour of OAuth as security mechanism to protect the Integration access.
However, not all OAuth flavour supported for Trigger Role (Used as Entry point of Integration) vs Invoke Role (Used for invoking third party REST endpoint).
REST APIs exposed using the REST Adapter (Trigger Role) are protected using Basic Authentication and OAuth token-based authentication.
REST API consumed using the REST Adapter (Invoke Role) Support HTTP Basic Authentication, OAuth Client Credentials (two-legged flow), OAuth Resource Owner Password Credentials (two-legged flow), OAuth Authorization Code Credentials (three-legged flow), OAuth Custom Three Legged Flow, OAuth Custom Two Legged Flow, OAuth 1.0a One Legged Authentication, Amazon Web Services (AWS) Signature Version 4, and Oracle Cloud Infrastructure (OCI) Signature Version 1. There is also support for consuming APIs that are unprotected.
Now, majority of Customers chose Basic AUTH while publishing an Integration because it’s very simply to implement but has limitation because the user password gets expired in every 3 month which result changing all Integrations configuration again in 3 month of time. We can very well avoid this problem by Implementing OAuth token which never gets expired.
Oracle has official document for setting up Service Account without expiry but it’s quite difficult to follow instructions from that document. Hence, I thought to publish more user friendly instructions to achieve the same outcome.
In this blog, I will be covering how we can invoke an Integration exposed using REST Adapter (Trigger role) using OAuth token which doesn’t get expired.
Customisation is essential part of any SaaS implementation to capture unique business needs. In Salesforce SaaS application also, there could be several use-cases where user might need to create a new Custom Object or add custom fields into existing Standard Object such as Contact, Account and Organisation etc. In this blog I will be showing how can we add Custom Object e.g. CochOrder which can have multiple Custom Fields e.g. Order Number, Shipping Cost, Source Region, Target Region and Total Amount etc. and can update that Custom Object fields using Oracle Integration Cloud (OIC) Salesforce adapter. I must recommend you to read my other blog which I have wrote to cover adding Custom Fields to existing Standard Object such as Contact, Account and Organisation etc. Most of the steps is going to same as previous blogs, so I am not going to repeat them here, instead will be only focusing only new changes related to Custom Objects.
Before, I go into deep drive, just want to highlight the core objective of this blog to show Salesforce configuration and OIC Salesforce adapter configuration, I am assuming reader has already basis understanding of OIC product features such as Connection, Integration, mapping and deployment.
My colleague had already covered Salesforce Inbound and Outbound integration using Oracle Integration Cloud Salesforce Adapter. So, I might not be repeating few steps which already been covered in this blog as well. if you doing Salesforce Integration first time, then its recommended to review these blogs before you proceed to read this blog.
So let’s do deep dive now. Below are the high levels flow and steps which needs to be performed to achieve desired result.
Oracle’s two major ground breaking innovation last year were Autonomous Data warehouse (ADW) and Autonomous Database Transaction processing (ATP) both are database offering suitable for different workload and are self-driving, self-securing, and self-repairing in nature. If you want to read more about these services then please go through above links.
ADW/ATP both can be quickly provisioned on Oracle Cloud Infrastructure, it’s take less than 5 minute to spin ADW/ATP instance and database is ready to connect.
User can use Oracle SQL Developer to connect to ADW/ATP database as long as they are supported version. These DBaaS services also offers out-of-box browser based SQL Developer tool which can be used to run any kind of SQL statements.
Here is sample snap of browser based SQL Developer capabilities –
Once user has Database ready, obviously there could be requirement to access data residing inside ADW/ATP instances.
Fortunately, Oracle Integration Cloud provide Adapter for connecting ADW/ATP instance, click here to know more about ATP Adapter capabilities –
In this blog I will be covering simple steps how you can connect to ADW/ATP instances using OIC Autonomous Transaction Processing Adapter (ATP) Adapter.
I made assumption that ADW/ATP instance already exists. if you not sure how to create ADW/ATP instance then refer this blog which was written by my colleague who already explained how to create ADW/ATP database instance and connect from SQL developer.
So, let move forward. Login to your Oracle Integration Cloud (OIC) home page >> Integration >> Connection >> Create >> search for “Oracle ATP” >> select the same
RedThunder.Blog 