Skip navigation.

APPS Blogs

UTL_FILE_DIR Security Weakness: Why and How To Use Oracle Directories

UTL_FILE_DIR is the database initialization parameter the Oracle Database uses to determine what operating system directories and files PL/SQL packages, functions, and procedures may read from or write to when using the standard UTL_FILE database package.  The directories specified in the UTL_FILE_DIR parameter may be accessed by any database user, which can be a security issue.  In Oracle 9iR2, Oracle released new functionality called “Directories” that provides a more secure and robust capability to access operating system directories and files.  The advantages of using Directories over UTL_FILE_DIR are –

  • Read and/or Write access to a Directory can be granted to individual database accounts or roles
  • A Directory can be added without having to bounce the database
  • Directory name is used in the UTL_FILE.FOPEN statement rather than the directory path, which allows changes to the directory path without modification to the PL/SQL source code
Securing UTL_FILE

The UTL_FILE database package is used to read from and write to operating system directories and files.  By default, PUBLIC is granted execute permission on UTL_FILE. Therefore, any database account may read from and write to files in the directories specified in the UTL_FILE_DIR database initialization parameter.

Oracle usually assumes that PUBLIC has execute permission on UTL_FILE, therefore, many Oracle product installations do not specifically grant execute permission on UTL_FILE to Oracle installed database accounts.  Consequently, revoking execute permission on UTL_FILE from PUBLIC will result in errors in a number of standard Oracle database utilities and The Oracle E-Business Suite.  Also, some Oracle products and third party products will grant execute on UTL_FILE to PUBLIC during the installation of the product.

We do not recommend revoking execute permission on UTL_FILE from PUBLIC in database instances running the Oracle E-Business Suite and other complex applications (i.e., SAP, Peoplesoft, Oracle Clinical, etc.) due to the possibility of encountering errors in the application and third party products.  Only revoke execute permission from PUBLIC in database instances where the application, third party products, and all database management tools can be thoroughly tested.  All Oracle delivered products must be tested since Oracle often assumes UTL_FILE is granted to PUBLIC and does not provide the necessary grants when any products are installed – this includes products like Enterprise Manager Grid Control and Apex.

Security considerations with UTL_FILE can be mitigated by removing all directories from UTL_FILE_DIR and using the Directory functionality instead.

Oracle E-Business Suite and UTL_FILE_DIR

The combination of UTL_FILE being granted to PUBLIC and UTL_FILE_DIR being publicly accessible creates a significant security issue for the Oracle E-Business Suite.  The Oracle E-Business Suite uses UTL_FILE_DIR to read and write concurrent manager request temporary files.  Also, UTL_FILE_DIR is extensively used by most organizations to access interface and conversion data files from PL/SQL interface programs.

In the Oracle E-Business Suite, UTL_FILE_DIR is usually set to include at least the directories specified in $APPLPTMP and $APPLTMP – in the default installation this will include at least “/usr/tmp”.  Frequently, additional custom directories will be included for custom interfaces and other custom programs.

By accessing the APPLSYSPUB database account, an attacker can easy read and write interface data files.  Depending on the exact configuration, implemented modules, and custom interfaces, this could allow access to sensitive information including social security numbers and credit card numbers.

Migrating From UTL_FILE_DIR

For Oracle E-Business Suite customers, migrating from UTL_FILE_DIR to Directories requires only minimal changes and may require no source code changes depending on the design of the interfaces and other custom programs. The steps are as follows -

  1. Identify where UTIL_FILE is used
  2. Create Directories
  3. Change FOPEN calls to use Directories
  4. Edit UTL_FILE_DIR to remove physical Directories

Step One – Identify Where UTIL_FILE Is Used

The most difficult issue is identifying the packages, functions, and procedures using the physical directories in UTL_FILE_DIR.  The UTL_FILE_DIR physical directories are only directly referenced by the UTL_FILE.FOPEN function.  The FOPEN specifies the operating system directory and file name to open.  All subsequent read, write, and close function calls use the file handle returned by FOPEN.

The following SQL may assist in identifying uses of UTL_FILE_DIR in FOPEN statements –

SELECT * FROM dba_source 

WHERE upper(text) like '%FOPEN%' 

AND name like '%<custom prefix>%' 

AND owner = 'APPS' 

 If the calls to FOPEN are not in a common function and are not accessed through some other indirection, it should be fairly straightforward to find and change the necessary FOPEN references in any custom PL/SQL packages, functions, and procedures.  If the physical directory paths are stored in a table or in a concurrent program definition, then no changes to the source code are required.

At this time, converting the standard the Oracle E-Business Suite directories ($APPLPTMP and $APPLTMP) is not recommended as this is not supported by Oracle.  Theoretically it should work without any issues, however, the Oracle E-Business Suite references the directories in multiple places including the $APPLPTMP and $APPLTMP environmental variables, system profile options (e.g., “ECX: XSLT File Path”), and potentially in some configuration files.

Step Two – Create Directories

The following general steps are required to change the references from UTL_FILE_DIR to Directories. Please note that the directory name MUST always be in uppercase in all UTL_FILE.FOPEN statements, otherwise errors may be encountered

For each custom directory in UTL_FILE_DIR, execute the following SQL statements in each development, test, and production database instance –

 
CREATE OR REPLACE DIRECTORY <name> AS '<physical directory path>'; 
GRANT READ, WRITE ON DIRECTORY <name> TO APPS;            

as an example –

CREATE OR REPLACE DIRECTORY TMP_DIR AS '/usr/tmp'; 
GRANT READ, WRITE ON DIRECTORY TMP_DIR TO APPS; 

 

The directories “/usr/tmp” and “../comn/temp” and any other directories specified in $APPLPTMP and $APPLTMP should remain in UTL_FILE_DIR, since these directories are required by Oracle.

Step Three – Change FOPEN Calls to Use Directories

Once directories have been created the next step is to edit your code to use them. The process is straightforward. If a physical directory is specified in the UTL_FILE.FOPEN statement, change the hard-coded path to the Directory name. 

As an example –

FILE_ID := UTL_FILE.FOPEN('/usr/tmp', 'dummy.txt', 'W'); 

 change to –

FILE_ID := UTL_FILE.FOPEN('TMP_DIR', 'dummy.txt', 'W'); 

 Two pointers to keep in mind:

  1. Always be sure to use the directory name in uppercase and it must be enclosed in single quotes  
  2. If the physical directory is specified in a table or as a parameter in a Concurrent Program definition, then just specify the Directory name rather than a physical path – /usr/tmp becomes TMP_DIR

Step Four – Edit UTL_FILE_DIR to Remove Physical Directories

Remove all the custom physical directories from UTL_FILE_DIR.  The standard Oracle directories of ‘/usr/tmp’, ‘../comn/temp’, etc. should not be removed.  The database must be bounced for the change to take effect.

 

If you have questions, please contact us at info@integrigy.com

References Tags: Information DisclosureOracle DatabaseOracle E-Business Suite
Categories: APPS Blogs, Security Blogs

SYNC 2014 !

Bas Klaassen - Thu, 2014-07-24 12:15
Vanuit Proact organiseren wij het kennisplatform SYNC 2014 op 17 september in de Rotterdam Cruise Terminal. Alle hedendaagse IT-infrastructuurontwikkelingen in 1 dag: • Een interactief programma o.l.v. dagvoorzitter Lars Sørensen o.a. bekend van BNR • Een keynote van Marco Gianotten van Giarte, de Nederlandse “Gartner” op het gebied van Outsoucing/Managed Services • Huisman Equipment over de Bas Klaassenhttp://www.blogger.com/profile/04080547141637579116noreply@blogger.com0
Categories: APPS Blogs

R12.2 :Modulus Check Validations for Bank Accounts

OracleApps Epicenter - Sat, 2014-07-12 08:45
The existing bank account number validations for domestic banks only check the length of the bank account number. These validations are performed during the entry and update of bank accounts.With R12.2 the account number validations for United Kingdom are enhanced to include a modulus check alongside the length checks. Modulus checking is the process of [...]
Categories: APPS Blogs

Oracle E-Business Suite Security - Signed JAR Files - What Should You Do – Part II

In our blog post on 16-May, we provided guidance on Java JAR signing for the E-Business Suite. We are continuing our research on E-Business Suite Java JAR signing and will be presenting it in a forthcoming educational webinar. Until then we would like to share a few items of importance based on recent client conversations -

  • Apply latest patches - The latest patches for Oracle E-Business Suite JAR signing are noted in 1591073.1. There are separate patches for 11i, 12.0.x, 12.1.x and 12.2.x. To fully take advantage of the security features provided by signing JAR files the latest patches need to be applied.
  • Do not use the default Keystore passwords - Before you sign your JAR files change the keystore passwords.  The initial instructions in 1591073.1 note that a possible first step before you start the JAR signing process is to change the keystore passwords. Integrigy recommends that changing the keystore passwords should be mandatory. The default Oracle passwords should not be used. Follow the instructions in Appendix A of 1591073.1 to change both keystore passwords. Each password must be at least six (6) characters in length. If you have already signed your JAR files, after changing the keystore passwords you must create a new keystore and redo all the steps in 1591073.1 to create a new signed certificate (it is much easier to change the keystore passwords BEFORE you sign your JAR files).
  • The keystore passwords are available to anyone with the APPS password - Using the code below anyone with the APPS password can extract the keystore passwords. Ensure that this fact is allowed for in your polices for segregation of duties, keystore management and certificate security.

SQL> set serveroutput on
declare 
spass varchar2(30); 
kpass varchar2(30); 
begin 
ad_jar.get_jripasswords(spass, kpass); 
dbms_output.put_line(spass); 
dbms_output.put_line(kpass); 
end; 
/

This will output the passwords in the following order:

store password (spass) 
key password (kpass)

If you have questions, please contact us at info@integrigy.com

References Tags: Security Strategy and StandardsOracle E-Business Suite
Categories: APPS Blogs, Security Blogs

Oracle E-Business Suite Security, Java 7 and Auto-Update

Maintaining a secure Oracle E-Business Suite implementation requires constant vigilance. For the desktop clients accessing Oracle E-Business Suite, Integrigy recommends running the latest version of Java 7 SE.  Java 7 is fully supported by Oracle with Public Updates through April 2015 and is patched with the latest security fixes. Most likely in late 2014 we anticipate that Oracle will have released and certified Java 8 with the Oracle E-Business Suite.

Most corporate environments utilize a standardized version of Java, tested and certified for corporate and mission critical applications. As such the Java auto-update functionality cannot be used to automatically upgrade Java on all desktops. These environments require new versions of Java to be periodically pushed to all desktops. For more information on how to push Java updates through software distribution see MOS Note 1439822.1. This note also describes how to download Java versions with the Java auto-update functionality disabled.

Keep in mind too that the version of Java used with the E-Business Suite should be obtained from My Oracle Support. Your Desktop support teams may or may not have Oracle support accounts.

Other points to keep in mind:

  • To support Java 7, the Oracle E-Business Suite application servers must be updated per the instructions in MOS Note 393931.1
  • “Non-Static Versioning” should be used the E-Business Suite to allow for later versions of the JRE Plug-in to be installed on the desktop client. For example, with Non-Static versioning JRE 7 will be invoked instead of JRE 6 if both are installed on a Windows desktop. With Non-Static versioning, the web server’s version of Java is the minimum version that can be used on the desktop client.
  • You will need to implement the Enhanced JAR File signing for the later versions of Java 7 (refer to Integrigy blog posting for more information)
  • Remember to remove all versions of Java that are no longer needed – for example JIinitiator

You may continue using Java 6.  As an Oracle E-Business Suite customer, you are entitled to Java 6 updates through Extended Support.  The latest Java 6 update (6u75) may be downloaded from My Oracle Support. This version (6u75) is equal to 7u55 for security fixes.

If you have questions, please contact us at info@integrigy.com

References

 

Tags: Security Strategy and StandardsOracle E-Business SuiteIT Security
Categories: APPS Blogs, Security Blogs

Register OAM WebGate from WebGate host

Online Apps DBA - Thu, 2014-06-26 15:05
Hi All, In this post I will explain how one can register a webgate from webgate host rather than registering the webgate from OAM Admin Console or OAM Admin Host. Refer these posts 1, 2 to understand concepts of WebGate registration in OAM 11g. Inband registration mode is used when Web Server Administrator and OAM [...]

This is a content summary only. Visit my website http://onlineAppsDBA.com for full links, other content, and more!
Categories: APPS Blogs

Trusting Privileged Users, DBMS_SQLHASH, and Three Misconceptions about Encryption

Clients often contact Integrigy requesting assistance to protect their sensitive data. Frequently these are requests for assistance to locate and then encrypt sensitive data. While encryption  offers protection for sensitive data, it by no means solves all security problems. How to protect sensitive data (and how to verify the trust of privileged users such as database administrators with sensitive data) requires more than just encryption.

The Oracle Database Security Guide (a great read for anyone interested in Oracle database security) makes three key points in Chapter Eight about encryption:

  1. Encryption does not solve access control problems - A user who has privileges to access data within the database has no more nor any less privileges as a result of encryption
  2. Encryption does not protect against a malicious database administrator - If untrustworthy users have significant privileges, then they can pose multiple threats to an organization, some of them far more significant than viewing unencrypted credit card numbers
  3. Encrypting everything does not make data secure – Data must be available when needed as well as backups and DR solutions considered. Moreover, encrypting all data will significantly affect performance.
     
DBMS_SQLHASH

Besides encryption, one of the security tools that Oracle provides is the DBMS_SQLHASH package. Hash values are similar to data fingerprints and can be used to validate if data has been changed (referred to as data integrity). Hashing is different from encryption and it is important to know the difference. If you need to know more about hashing, see the reference section below.  

The DBMS_SQLHASH package has been delivered since 10g and provides an interface to generate the hash value of the result set returned by a SQL query and provides support for several industry-standard hashing algorithms including SHA-1.

DBMS_SQLHASH.GETHASH(sqltext IN varchar2,

                                                digest_type IN BINARY_INTEGER,

                                                chunk_size IN number DEFAULT 134217728)

 

sqltext

The SQL statement whose result is hashed

digest_type

Hash algorithm used: HASH_MD4, HASH_MD5 or HASH_SH1

Use 3 for HASH_SH1, Use 2 for HASH_MD5 and 1 for HASH_MD4

chunk_size

Size of the result chunk when getting the hash

When the result set size is large, the GETHASH function will break it into chunks having a size equal to chunk_size. It will generate the hash for each chunk and then use hash chaining to calculate the final hash. The default chunk_size is 128 MB.

 

How Can Auditors use DBMS_SQLHASH

One use case for DBMS_SQLHASH is to help auditors trust-but-verify the actions of privileged users such as database administrators. By hashing key tables an auditor can quickly determine if the database administrator has made changes – either authorized or unauthorized. An auditor can do this by recording hashes at the start of an audit period for comparison to hashes at the end of the period. If the hashes at the end of the audit period match the hashes at the beginning of the period, no changes have been made. If there are a large number of databases and/or tables to audit, this approach is a very beneficial means of identifying what requires additional review – assuming sufficient logging has been configured to capture the details of the changes.

For example, to determine if there have been changes to Oracle database users and their associated privileges over a period of time, such as granting access to sensitive data, an auditor can hash the following Dictionary tables:

  • SYS.DBA_USERS
  • SYS.DBA_ROLES
  • SYS.DBA_TAB_PRIVS
  • SYS.DBA_SYS_PRIVS
  • SYS.DBA_ROLE_PRIVS

Examples

Note: to call the SYS.DBMS_SQLHASH package, the user will need execute rights granted from SYS.

Control

DBA_USERS

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH ('SELECT * FROM SYS.DBA_USERS ORDER BY USER_ID', 3) sh1_dba_user_hash FROM DUAL;

Sample

Result

Sh1_dba_user_hash

7BD61E22E35FA2F95035E6A794F5B8CF0E37FDF6

 

Control

SYS.DBA_ROLES

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH('SELECT * FROM SYS.DBA_ROLES ORDER BY ROLE', 3) sh1_dba_roles_hash FROM DUAL;

Sample

Result

sh1_dba_roles_hash

C80D69048D613E926E95AF77B627D9B5D6CB20C8

 

Control

SYS.DBA_TAB_PRIVS

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH('SELECT * FROM SYS.DBA_TAB_PRIVS ORDER BY OWNER,TABLE_NAME', 3) sh1_dba_tab_privs_hash FROM DUAL;

Sample

Result

sh1_dba_tab_privs_hash

53FBDBDBF95186400A4DEEE611F51CD0B1E998DF

 

Control

SYS.DBA_SYS_PRIVS

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH('SELECT * FROM SYS.DBA_SYS_PRIVS ORDER BY GRANTEE, PRIVILEGE', 3) sh1_dba_sys_privs_hash FROM DUAL;

Sample

Result

sh1_dba_sys_privs_hash

A27E8C71AD0CAEFB94AFEAB5DB108871F09BC281

 

Control

SYS.DBA_ROLE_PRIVS

SQL

SELECT SYS.DBMS_SQLHASH.GETHASH('SELECT * FROM SYS.DBA_ROLE_PRIVS ORDER BY GRANTEE, GRANTED_ROLE', 3) sh1_dba_role_privs_hash FROM DUAL;

Sample

Result

sh1_dba_role_privs_hash

5715D1B2C2A775D579B36DEBD2C2F1F608762AEC

 

Key Point

The order by clause will change the HASH. Oracle Support Note 1569256.1 explains this in more detail.  To guarantee the same HASH for a SQL issued at different times, the same ordering of the data must be used.

If you have questions or are interested in Integrigy's hashing methodology for Oracle and the Oracle E-Business Suite, please contact us at info@integrigy.com

References Tags: AuditingSecurity Strategy and StandardsSensitive DataOracle DatabaseAuditor
Categories: APPS Blogs, Security Blogs

Apache 11g WebGate for OAM 11gR2

Online Apps DBA - Tue, 2014-06-17 13:07
Apache WebGate is one of the widely used webgates in most of the enterprises. Oracle has been releasing OHS 11g webgates for OAM 11gR1, R2 PS1 and R2 PS2 releases. However Oracle has released Apache 11g R2 PS1 webgate with OAM 11g R2 PS1 release and it can be downloaded from edelivery.oracle.com -> Oracle Fusion [...]

This is a content summary only. Visit my website http://onlineAppsDBA.com for full links, other content, and more!
Categories: APPS Blogs

Splunk DB Connect Tail for Oracle E-Business Sign-on Audit

Integrigy has received a lot of great feedback about our Framework for logging and auditing the Oracle E-Business Suite.  The Framework is posted here.  The Framework is a direct result of our consulting experience and clients have found it equally useful to both those wanting to improve their auditing capabilities as well as those just starting to implement logging and auditing.  Our goal with the Framework is to provide a clear explanation of the native auditing and logging features available, present an approach and strategy for using these features and a straight-forward configuration steps to implement the approach.

The Framework is also specifically designed to help clients meet compliance and security standards such as Sarbanes-Oxley (SOX), Payment Card Industry (PCI), FISMA, and HIPAA. The foundation of the Framework is PCI DSS requirement 10.2.

Splunk DB Connect

The Framework defines three levels of maturity. Level one identifies basic logging, level two calls for passing log data to a centralized log management solution and level three is a continuous improvement loop where increasingly more data is correlated. Level two is the key step. Given the complexity of the Oracle E-Business Suite and compliance requirements for protection and non-repudiation of log data, a centralized logging solution is required.

Splunk, ArcSight, Envision and the Oracle Audit Vault all offer solutions for centralized logging. Recently a client was asking for assistance to implement our Framework using Splunk. Splunk has a native parser for Oracle Syslog as well as a free application to import data directly from tables. Splunk’s DB Connect provides real-time integration is an ideal solution to pull data from the E-Business Suite’s Sign-On Audit tables.

Sign-On Audit

Sign-On Audit is optional functionality to track end-user navigation activity in the professional forms (not Web or HTML forms).  It has three levels: Login, What Responsibility was used, and What Forms were visited.  For each option, the length of time is captured.  Only Navigation activity is a captured – it is important to understand that what the end-user did in the form, be it viewed a record or updated a record, is not captured.  If the requirement is to capture the end-user actions in the form, auditing must be enabled using Oracle E-Business Suite AuditTrail or third-party tools are required.

Sign-On Audit is turned off/on by the system profile option “Sign-On: Audit Level.”  If enabled, Sign-On Audit needs to regularly purge the data it collects.  This can be done using the Purge Concurrent Request and/or Manager Data concurrent program.

Sign-On Audit data is collected in real-time and can be viewed through standard reports, a Form, or by using SQL. The following are the tables for Sign-On audit data that can be used by Splunk’s DB Connect:

  • APPLSYS.FND_SIGNON
  • APPLSYS.FND_LOGIN_RESPONSIBILITIES
  • APPLSYS.FND_LOGIN_RESP_FORMS
  • APPLSYS.FND_UNSUCCESSFUL_LOGINS
How to Tail Sign-On Audit activity using Splunk DB Connect

Below is a description of how to get starting using Splunk and DB Connect to implement Integrigy’s Framework for logging and auditing for the Oracle E-Business Suite. The sample is for how to tail the table APPLSYS.FND_LOGINS such that every hour Splunk will log into the E-Business Suite’s database and check if there are any new rows in the table. The high-level summary is a follows:

  1. Do this first in a development or test instance, do not attempt first in production.
  2. Obtain the documentation for the Splunk DB Connector and Integrigy’s Framework whitepaper.
  3. For this example, enable Sign-On audit if you have done so already.
  4. Install the Splunk DB Connector. To finish the installation you will need to install Java 1.6 (or greater) and/or reference the location of the Java Home. You will also need the Oracle JDBC driver. The installation of the Oracle JDBC driver for Splunk is well documented in the DB Connector instructions. The JAR file must be placed within the Splunk file system.
  5. Within Splunk create a database connection to the E-Business Suite. Integrigy’s recommendation is to create an appropriately privileged account (do not use APPS).
  6. Create an input to the Splunk database. These are referred to as ‘Database Inputs’. This is a key step. As a quick note be sure to reference all Oracle objects in UPPER CASE:
    1. Choose “Tail”.
    2. Select the database connection you defined earlier.
    3. For the table APPLSYS.FND_LOGINS, the following Specific SQL can be used to ignore scheduled concurrent program activity. Copy the following SQL exactly, including the last line with Splunk’s syntax for the rising column:

SELECT  U.USER_NAME,U.PERSON_PARTY_ID, LI.*

from APPLSYS.FND_LOGINS LI, APPLSYS.FND_USER U

WHERE LI.TERMINAL_ID IS NULL

AND LI.USER_ID = U.USER_ID

{{AND $rising_column$ > ?}}

  1. Identify the rising column, enter: LOGIN_ID
  2. Identify the index, as a quick demo to get going just use the default, enter: default
  3. Identify a Host Field value, you can enter the database SID, for example, VIS121
  4. Select the output format, use: multi-line key-value format
  5. Identify the timestamp column, enter: START_TIME
  6. Set the polling frequency or interval to hourly (default if left blank will be auto): 1h
  1. Test by logging into the Oracle E-Business Suite and then looking at Splunk.
  2. To fully implement the Integrigy Framework for logging and auditing the Oracle E-Business Suite, database auditing well as E-Business Suite auditing and Page Access Tracking  will need to be enabled, but you can repeat step five above for each table identified for logging E-Business Suite end-user navigation. The SQL used will differ from the above but should be straight forward. Keep in mind too that you will need enable both Sign-On Audit and Page Access Tracking in order to log end-user navigation within the Oracle E-Business Suite.

 

Figure 1 – Example of Searching Splunk for Oracle E-Business Suite Sign-On for the User SYSADMIN

 

If you have questions, please contact us at info@integrigy.com.

References Tags: AuditingComplianceOracle E-Business Suite
Categories: APPS Blogs, Security Blogs

Guest Post: Custom Balancing by 2 segments in EBS R12

David Haimes - Mon, 2014-06-02 07:15
Sangeeta

Sangeeta Sameer, IT Leader – Finance, Renewables ERP at GE Power & Water

This is a guest post from Sangeeta Sameer, who worked for Oracle for 6 years before moving on to work for GE where has held a number of senior positions.  She has also joined the board of the OAUG Multinational Special Interest Group.

At the Collaborate 2014 Conference, David Haimes and I presented a custom solution to enable balancing by 2 segments of the Chart of Accounts in Release 12.1.3 of Oracle e-Business Suite. The attached PowerPoint and Paper that we presented describes in detail the requirements and the custom solution.

So when David asked me to write a guest post about this topic, I thought about what else I would include in the paper if we were writing it again. While the paper talks about the requirement to create a balanced Trial Balance by 2 segments, it does not expand on why we need a balanced Trial Balance by 2 segments. Global corporations like General Electric (GE) need better granularity and accountability for the financial performance of their geographical divisions and lines of businesses by being able to create a Balance Sheet at both a Legal Entity/Statutory level and Business Unit/Management Entity level. GE is a Global conglomerate that has diverse businesses operating in a large number of countries. GE needs the ability to create a balanced Trial Balance by the Legal Entity (LE) and the Management Entity (ME) segments in the Chart of Accounts.

The ability to automatically balance by more than one segment is not a new requirement for GE, and I think it would be a requirement for other multinational companies as well. This requirement is explained quite well in Oracle Fusion Applications Enterprise Structures Concepts Guide 11g Release 5 (11.1.5) – Part # E22899-05. See the section titled “Legal Entity and Its Relationship to Balancing Segments”. While Fusion Applications are able to balance by up to 3 segments in the Chart of Accounts, Oracle e-Business Suite (EBS) Applications are able to balance by one segment only.

In prior EBS Release 11i implementations at GE, the requirement to balance by 2 segments was satisfied by developing custom solutions that needed to be re-visited for Release 12. For example, one of the solutions in 11i was for a custom program to bring balancing entries into the Interface Table. With the introduction of SLA in Release 12, this 11i solution was no longer a good one for R12. The solution described in the attached Paper and PowerPoint is a much more robust solution, and is live in Production for 2 different ERP Projects at GE.

The OAUG Multinational Special Interest Group (SIG) is organizing a webinar on June 11th at 10.30 am Pacific (1.30 pm Eastern) on this topic. David and I will repeat the presentation from Collaborate 2014, and look forward to sharing this solution again. The webinar will be one hour long, including 20-25 minutes for Q&A.

The link to register for this webinar is:
https://www4.gotomeeting.com/register/790696327


Categories: APPS Blogs

OIM Integration with GRC/OAACG for EBS SoD

Online Apps DBA - Sat, 2014-05-31 13:59
We recently implemented Oracle Identity Manager (OIM) integration with Oracle E-Business Suite (R12) using EBS-UM connector. We also integrated Oracle E-Business Suite (R12) with GRC/OAACG for Segregation of Duties (SoD). Oracle Identity Manager (OIM) was also integrated with GRC/OAACG for SoD where on provisioning EBS roles & responsibilities in EBS (from OIM), OIM was doing [...]

This is a content summary only. Visit my website http://onlineAppsDBA.com for full links, other content, and more!
Categories: APPS Blogs

Kerberos Authentication for Oracle - Benefits and Recommendations

Kerberos authentication support in the Oracle Database is now included with all editions of the Oracle Database.  Previously, Kerberos authentication required an Oracle Advanced Security Option license.  Since this licensing change, we have been working with our clients to design and implement database user authentication using Kerberos and Active Directory.  This allows for authentication and verification of database users using Active Directory without implementing other identify management products or servers.  Although, it does require both server and client-side configuration.

First, we need to note that there are three options for creation of users in an Oracle database - users can be identified as:

  • Locally – accounts and passwords are defined with the local database.
  • Externally – accounts and passwords are defined locally but authenticated by an external service, such as an operating system or third party service (e.g.  Active Directory or LDAP).  This includes Kerberos.
  • Globally – accounts and passwords are both defined outside the local database.  Authentication must be done through an external service.  This Oracle feature is called Enterprise User Security (EUS).
What is Kerberos?

Kerberos is not OS authentication.  Remote OS authentication is a security option where the Oracle database allowed a connection if the user has an open session within the operating system.  Remote OS Authentication is now obsolete and is no longer supported after Oracle 11gR1, however, it remains a feature only for backwards compatibility.

Kerberos is a network authentication protocol originally developed by the Massachusetts Institute of Technology (MIT).  Kerberos has for years been built into Microsoft Active Directory and is designed to authenticate users to network resources, such as Oracle databases. Kerberos uses tickets and symmetric-key cryptography to eliminate the need to transmit passwords over the network.

Previously, Oracle Kerberos Authentication was a component of Advanced Security Option (ASO) - Kerberos Authentication required an ASO license per database server. As of Fall 2013, Oracle Kerberos Authentication is no longer part of ASO and it can be used with any database edition for all supported versions of the database without additional licensing. (See Note 1375853.1 for further information.)

What does this mean?

Using Kerberos can improve security and save time and money. For Kerberos authenticated users, database administrators will still need to create accounts and assign roles, but they will no longer need to worry about password resets, nor will need they need to close accounts upon termination of employment (assuming the AD account is closed).

The benefits of using Kerberos will differ per client and the identity management strategy being pursued.

Recommendations
  • Consider Kerberos for named user accounts (end-users), not service accounts.
  • Existing users will need to be altered from locally defined to external.
  • Case sensitivity of user names can be an issue. Oracle by default creates usernames in upper case. AD is case in-sensitive. Kerberos authentication requires uppercase.
  • The Oracle database server must be in the AD domain or, if not, the krb5.conf file needs to explicitly include it in the realm mapping.
  • Oracle Kerberos authentication does not require any external Kerberos libraries to be added.
  • Be sure to log Kerberos authentication events in Splunk, ArcSight, or whatever your centralized logging solution may be

If you have questions, please contact us at info@integrigy.com.

References

 

Tags: Security Strategy and StandardsOracle DatabaseIT Security
Categories: APPS Blogs, Security Blogs

Participate in our QUIZ to win monthly prize

Online Apps DBA - Thu, 2014-05-29 12:03
Where are OID ports configured (In xml file under ORACLE_INSTANCE or in OID Database) ? Can you keep ORACLE_HOME (for IAM) outside Middleware Home (MW_HOME) ? What is Relative Distinguished Name (RDN) of entry with Distinguished Name (DN) “uid=jbloggs, cn=Users,dc=mydomain,dc=com” ?  For some of interesting QUIZ like above and chance to win monthly prize, LikeUs on [...]

This is a content summary only. Visit my website http://onlineAppsDBA.com for full links, other content, and more!
Categories: APPS Blogs

Does my GoPro Qualify as a Wearable Device?

David Haimes - Thu, 2014-05-29 07:44
Is this small camera a wearable?

Is this small camera a wearable?

Last week I attended a Wearables Design Jam organized by Ultan (@ultan) from the Oracle UX team (a future post is coming on the event) and since then I have been thinking about what makes a successful wearable device.  The most successful devices in the marketplace seem to be the fitness trackers and I have had great benefit from my fitbit, not least of all the inspiration for walking meetings.  A fitness tracker is not a new idea (I joke that a fitbit is just a $100 pedometer), but the wearable devices today make tracking your fitness so much easier.  No need to keep diaries of your activities, everything is there for you in a dashboard with all sorts of interesting analytics, goal setting, alerts etc.

The GoPro has similarities, it is a small personal camcorder with only memory, no tape and no screen for viewing, similar to the Flip camera which Cisco bought and then killed and many other cameras.  However it takes action videos really well, with very little effort and is pretty much indestructible so you can take it  anywhere.  They produce professional quality video and photos including time lapse videos, fast frame rates for smooth slow motion and have an ultra wide field of view so you rarely miss any action.  You can also attach them to your body, bike and other helmets, cars and of course surfboards. My model (Hero 3+ Black edition) has WiFi so I can control it from my iPhone or iPad and grab content from it easily, although that does drain the battery fast.

If you haven’t already seen it, you must check out the Time Lapse video I took with the GoPro of the Team USA Americas Cup yacht being installed at Oracle HQ.  This was around 9,000 photos taken over 50+ hours compressed into a 2 minute video.  I have also strapped it to my car to capture a journey down the highway 1, strapped it to the outside of my car, strapped it to a remote control car, strapped it to myself and my kids in a swimming pool, to my Son’s bike and to my daughter’s wheeled shoes.  Now I am trying to think of some enterprise applications of this amazing device, watch this space.

Finally, I’m embedding a shot from my car, especially for Jake (@jkuramot) who always likes to know how my car is doing, plus a few other interesting (IMHO) shots.

Good for 100 mph plus

Good for 100 mph plus

 

UPDATE (5/28/14):

I was reading a post from my colleague John Cartan this morning about his experience with the the Narrative Clip which is a smaller device, intended to be used for life logging.  It is worn all the time and captures a picture every 30 seconds.  The experience did not seem to be great, but I think a GoPro, altho a little bigger would be much better for this purpose.  I could imagine wearing it all day at a conference say, or in a series of meetings, brainstorming sessions etc. it would give a really good replay of events.  I will probably try some of this in the coming weeks, just to see how interesting it is.


Categories: APPS Blogs

Maker Faire, Java and the Internet of Things

David Haimes - Wed, 2014-05-28 10:00

makerpassMaker Faire is a popular event here in the San Francisco Bay Area, I’ve been going with my kids for several years now and all of us look forward to it every year.  I describe it as everything from embroidery to fire breathing robots, anything you can make is there and everyone is passionate about making stuff and everyone enjoys sharing their passion.

However this year I was there as staff with the Apps Lab who were providing a DIY activity for the Java team who are a big supporter of Maker Faire, Jake explains in detail in his blog.  My summary is we showed use of embedded java on a Raspberry Pis to control a variety of devices with input from other devices, for example I could use a motion sensor to turn on a fan, or send a tweet or send an SMS message.  It was an interesting experience showing this off to people, explaining what we mean by the internet of things and seeing people’s reaction.  There are a huge range of ages and types of people at Maker Fairs, so it was cool to see young and old alike enjoying making things happen.  The most interesting thing to see first hand is how young children (as young as 4 or 5) can just figure out how to do things with minimal guidance and in fact seem to prefer to explore on their own, older people are much quicker to get help or listen to me explain rather than do it.  I have no idea what this means, if anything, but it was interesting to me at least.


Categories: APPS Blogs

12.2.3 Online Patching – Importance Of Database Flashback

Online Apps DBA - Mon, 2014-05-26 09:37
  Online Patching feature of Oracle E-Business Suites 12.2.x allows patching activity, while users are working on the System. The Online Patching cycle consists of several phases (i.e prepare, apply, finalize, cutover and cleanup etc.), with the critical phase where the changes are committed being called cutover. Up to this phase, you can run a special [...]

This is a content summary only. Visit my website http://onlineAppsDBA.com for full links, other content, and more!
Categories: APPS Blogs

Oracle GRC integration with OIM for SoD Error: String index out of range: -7

Online Apps DBA - Sun, 2014-05-25 08:55
Oracle Identity Manager (OIM) can be integrated Oracle Identity Analytics (OIA) or Oracle/SAP GRC (Governance, Risk, and Compliance) for SoD (Segregation of Duties). We recently implemented OIM integration with Oracle GRC (OAACG - Oracle Application Access Control Governor) for Oracle E-Business Suite (EBS R12) for SoD. EBS Responsibility Provisioning from OIM to EBS were failing [...]

This is a content summary only. Visit my website http://onlineAppsDBA.com for full links, other content, and more!
Categories: APPS Blogs

Report from Collaborate 14, Las Vegas

David Haimes - Fri, 2014-05-23 22:54
David in a Suit

Talking Fusion Accounting Hub in a suit

Last month I attended and presented at Collaborate, the largest Oracle conference outside of Open World and one where the agenda is determined by the user community.

I was well organized this year; before I attended the conference I had downloaded the mobile App and started creating a conference agenda, I was a little bit disappointed that I had to manually add sessions I was presenting to my agenda, seems like a missing feature and it did not create the best first impression.  However, I then found the mobile app really good, a lot of discussions, the ability to comment on sessions and all the good social features you would expect.  When the conference got started the gamification they had added (a simple leader board of activity) helped to drive adoption and also caused some controversy, I’ll stay out of that but I would recommend reading Debra Lilley’s thoughts on it.

Throughout the week many people were talking about how several sessions they wanted to attend seemed to be scheduled at the same time. My first thought about this was that they were just making a polite excuse for not attending some of my sessions, but then I did observe the same thing myself.  It is hard having to choose between two or often three very interesting and useful sessions which are on at the same time, it is also tough when attendance at some sessions is lower than they deserve.  However, as I thought more about it, I feel it should be seen as a positive that the quality of the sessions and the presenters was very high.

My session on Fusion Accounting Hub and GLobal Charts of Accounts and Consolidation was on Monday and I had a decent crowd, got through a lot of material and had some really good questions (and I hope the answers were good).  I mentioned on my blog that FAH was the talk of the town based on the number of sessions that were covering it, I certainly spent  lots of time discussing it with people over the course of the conference.  We had a good GL SIG group meeting, I did a few minutes about the management segment and Data Access Sets in EBusiness Suite GL Release 12.  I was surprised that more people were not aware of this very useful feature, maybe I should blog about it (he says, adding it to a long to do list).  My third session was co-presented with Sangeeta Sameer from GE, I have asked Sangeeta to write a guest blog post introducing it, sharing the paper and announcing a webinar repeat of it – watch this space.

Exciting that all the things were, probably the part I enjoyed most was talking to so many people who had implemented or were implementing Cloud ERP Apps.  I had a chance to hear the positive comments and excitement that customers and partners had, but equally chat about where the y had suggestions for improvements.  We have a lot of very experienced, motivated and very smart partners out there and I enjoy every minute talking with them and learning from them, hopefully helping them out with a few tips too.

So next up for me is Open World, I did not make KScope this year, but one day I would like to go there, but for my particular field Collaborate is a must conference and if you have never been I would highly recommend you consider it next year, I will certainly be submitting a paper.

 

 

 


Categories: APPS Blogs

Oracle E-Business Suite Denial of Service Attacks and Locking the APPS Password

My wake-up call one day last week came from an acquaintance. Somebody at his company typed the APPS password in wrong too many times and locked the APPS database account. This caused the Oracle E-Business Suite to lock-out ALL users from accessing the application and concurrent processing to stop. Since it was production, excitement ensued. By the time he had called me, the APPS password had been reset and the Oracle E-Business Suite was back up. The question was what do to prevent it from occurring in the future?

In order to provide a more secure default configuration, Oracle began setting the default profile FAILED_LOGIN_ATTEMPTS in 11g to 10 (failed logins). This profile is assigned to all database accounts by default, including the APPS account in Oracle E-Business Suite environments.  Thus, most are vulnerable to a very simple to execute denial of service attack. The risk of allowing the APPS password to be easily locked is essentially risking an intentional or unintentional denial-of-service attack.

As part of Integrigy’s standard security assessment checks for the Oracle E-Business Suite, we recommend that a custom database password profile be created for key service accounts such as APPS. In this custom profile for service accounts, FAILED_LOGIN_ATTEMPTS should be set high value or UNLIMITED for key application service accounts. To mitigate any risk of brute force attempts against these accounts, failed login attempts should be monitored and a PASSWORD_VERIFY_FUNCTION set to require password complexity and a minimum password length.

The default password profile should not be used. Integrigy recommends a set of custom profiles be developed and segment accounts into interactive service accounts, other service accounts, and named users.

What might be a few other denial-of-service attacks for the E-Business Suite? This not inclusive, but a few of them are:

  • If the profile option ‘Upload File Size Limit’ is not set, a user could potentially upload an inordinately large document or a number of large attached documents sufficient to consume storage to the point that the database would become inoperable. The file size limit should be set and be set appropriately small for your business processes.
  • If you are Internet facing, such as running iRecruitment, there is another profile option, ‘IRC: Document Upload Count Limit’ which limits the total number of documents that can be uploaded per user.
  • As well, if you are Internet facing, such as running iRecruitment or any other module that allows self-registration and creation of accounts, you should consider implementing CAPTCHA – these are the hard-to-read random words you need to retype that are used by web sites to prove you are a human. Someone with nefarious intent could create numerous bogus E-Business Suite user accounts (most likely through automation) sufficient to interfere with the normal operation of the Suite.  See the reference below for the Oracle Support note for how to implement CAPTCHA.
  • Not directly related to the E-Business Suite, but those accounts used in OBIEE data source connections (defined in the RPD) should certainly be treated the same as what is suggested above for the APPS account. Locking the accounts used for data connections will render OBIEE unusable for users.

 If you have questions, please contact us at info@integrigy.com

References Tags: DMZ/ExternalOracle E-Business Suite
Categories: APPS Blogs, Security Blogs

How To Stop Insiders from Stealing Your Secrets – Recommendations on Rings of Security

For those of you who attended our webinar on 15-May-2014 on how to secure privileged users, Bruce Schneier’s blog post on 5/16/2014 will be of interest. The post was titled “How to Stop an Insider from Stealing All Your Secrets”. In the post he referenced a magazine article by Bob Toxen in the Communications of the ACM.

Bob Toxen’s article is here and it is a fascinating read on Edward Snowden’s exploits at the NSA and what should have been in place to stop him. The article reviews best practices for “Rings of Security” with which to protect against insider threats from privileged users. Of particular interest were Bob’s comments about the NSA’s use of SharePoint and his recommended best practices for the implementation and use of SSH (Secure Shell).

If you have questions or comments on this topic, please contact us at info@integrigy.com. We would enjoy hearing from you.

References Tags: AuditingSecurity Strategy and StandardsSensitive Data
Categories: APPS Blogs, Security Blogs