X

The Integration blog covers the latest in product updates, best practices, customer stories, and more.

  • April 12, 2019

Managing the Database Passwords for SOA Cloud Service

Robert Wunderlich
Product Strategy Director

Oracle SOA Cloud Service is a customer-managed service which means that after provisioning, the customer has complete control and responsibility for the VMs.  This is different from service offerings like Autonomous Transaction Processing services or Oracle Integration Cloud where Oracle fully manages the service.  Administrators need to remain acutely aware that customer-managed services require regular monitoring and maintenance. 

A common issue faced by many customers are the default database user password expiration policies.  SOA Suite uses a number of schemas during startup and runtime.  Failure to maintain the passwords on these schemas can result in a sudden failure where SOA Suite/SOA Cloud Service cannot connect to the database.  This can result in a sudden service loss, so it is critical to ensure you are regularly changing the schema passwords.

For more details, see https://docs.oracle.com/en/cloud/paas/soa-cloud/csbcs/change-database-schema-password.html which discusses the process of updating the password in the database, as well as updating the data-source definitions in the SOA Suite Administration server.

An option which is not necessarily recommended since it relaxes security is to update the password lifetime to unlimited.  This removes the requirement to change the password at regular intervals.  Again, be warned that regularly changing passwords helps to increase security, taking this step will remove that protection.

Learn more about securing your database at https://blogs.oracle.com/database/securing-the-oracle-database-ebook-second-edition-now-available

If you absolutely must update the lifetime to unlimited, you should go ahead and create a separate profile and move the SOA Schemas to that profile.  This way, if any other users are using the database, the standard lifetime would still remain for all other users, except your SOA Servers.

Here is an example of how this would be done:

DECLARE
profile_cnt number;
user_profile varchar2(25) := '<PROFILE>';
schema_prefix varchar2(25) := '<SCHEMA_PREFIX>';
BEGIN
SELECT count(*) INTO profile_cnt FROM dba_profiles WHERE profile = upper(user_profile);
if profile_cnt >= 1 then
sys.dbms_output.put_line( 'Profile ' || user_profile || ' already exists');
else
sys.dbms_output.put_line( 'Creating ' || user_profile || ' with password life time of unlimited');
EXECUTE IMMEDIATE 'CREATE PROFILE ' || user_profile || ' LIMIT PASSWORD_LIFE_TIME UNLIMITED';
end if;
FOR rcu_user IN SELECT username FROM dba_users WHERE username LIKE UPPER( schema_prefix || '%'))
LOOP
sys.dbms_output.put_line( 'Changing profile for schema user = ' || rcu_user.username);
EXECUTE IMMEDIATE 'ALTER USER ' || SYS.DBMS_ASSERT.NOOP(rcu_user.username) || ' PROFILE ' || user_profile;
END LOOP;
END;
/

You need to replace the above tokens in the sql script to the actual values before executing:

  1. <PROFILE>  →  Name of the profile to be created
  2. <SCHEMA_PREFIX> → Common prefix for the RCU Schemas of the SOACS instance.How to get Schema Prefix

In the above PL/SQL, we create a profile with the unlimited password lifetime.  We then select all of the users based on the schema prefix for our SOA Suite/SOA Cloud Service environment.  We then move all of those users into the new profile.

The ideal approach is to regularly update your passwords but there is an option for relaxing your password expiration requirements to prevent a service disruption.

 
 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.