You often read about FUSION and FUSION_RUNTIME users while dealing with Fusion Applications. There is one more called FUSION_DYNAMIC. Here are some details on the difference between these three and the purpose of each type of schema.

• FUSION: It can be considered as an Administrator of the Fusion Applications with all the corresponding rights and powers such as owning tables and objects, providing grants to FUSION_RUNTIME.  It is used for patching and has grants to many internal DBMS functions.
• FUSION_RUNTIME: Used to run the Applications.  Contains no DB objects.
• FUSION_DYNAMIC: This schema owns the objects that are created dynamically through ADM_DDL. ADM_DDL is a package that acts as a wrapper around the DDL statement. ADM_DDL support operations like truncate table, create index etc.

As the above statements indicate that FUSION owns the tables and objects including FND tables so using FUSION to run applications is insecure. It would be possible to modify security policies and other key information in the base tables (like FND) to break the Fusion Applications security via SQL injection etc. Other possibilities would be to write a logon DB trigger and steal credentials etc. Thus, to make Fusion Applications secure FUSION_RUNTIME is granted privileges to execute DMLs only on APPS tables. Another benefit of having separate users is achieving Separation of Duties (SODs) at schema level which is required by auditors. Below are the roles and privileges assigned to FUSION, FUSION_RUNTIME and FUSION_DYNAMIC schema:

It has the following privileges:

• Create SESSION
• Do all types of DDL owned by FUSION. Additionally, some specific priveleges on other schemas is also granted to FUSION.
• EXECUTE ON various EDN_PUBLISH_EVENT

It has the following roles:

• CTXAPP for managing Oracle Text Objects
• AQ_SER_ROLE and AQ_ADMINISTRATOR_ROLE for managing Advanced Queues (AQ)

It has the following privileges:

• CREATE SESSION
• CHANGE NOTIFICATION
• EXECUTE ON various EDN_PUBLISH_EVENT

It has the following roles:

• FUSION_APPS_READ_WRITE for performing DML (Select, Insert, Delete) on Fusion Apps tables
• FUSION_APPS_EXECUTE for performing execute on objects such as procedures, functions, packages etc.
• AQ_SER_ROLE and AQ_ADMINISTRATOR_ROLE for managing Advanced Queues (AQ)

It has following privileges:

• CREATE SESSION, PROCEDURE, TABLE, SEQUENCE, SYNONYM, VIEW
• UNLIMITED TABLESPACE
• ANALYZE ANY
• CREATE MINING MODEL
• EXECUTE on specific procedure, function or package and SELECT on specific tables. This depends on the objects identified by product teams that ADM_DDL needs to have access  in order to perform dynamic DDL statements.

There is one more role FUSION_APPS_READ_ONLY which is not attached to any user and has only SELECT privilege on all the Fusion objects. FUSION_RUNTIME does not have any synonyms defined to access objects owned by FUSION schema. A logon trigger is defined in FUSION_RUNTIME which sets the current schema to FUSION and eliminates the need of any synonyms.  

Fusion Application developers should be using FUSION_RUNTIME for testing and running Fusion Applications UI, BC and to connect to any SQL front end like SQL *PLUS, SQL Loader etc. For testing ADFbc using AM tester while using FUSION_RUNTIME you may hit the following error:

oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.sql.SQLException,
 msg=invalid name pattern: FUSION.FND_TABLE_OF_VARCHAR2_255

The fix is to add the below JVM parameter in the Run/Debug client property in the Model project properties

-Doracle.jdbc.createDescriptorUseCurrentSchemaForSchemaName=true

More details are discussed in this forum thread for it.