Creating a JD Edwards Database User and Synonyms for Oracle Databases

You must create a JD Edwards database user for the RA JD Edwards Agent and grant it permissions on the required tables. Then you must log in as the database user you created, and create the required synonyms.

To create a JD Edwards database user for the RA JD Edwards Agent and grant it permissions on the required tables.

  1. Verify the owners of the required tables. Based on the JD Edwards version, the owners of the required tables may be different. If the JDE is installed on an Oracle 12c pluggable DB (pdb), you need to switch to that pluggable DB first.

    2. > alter session set container=pdb_name

    To verify the tables, from the SYS user:

    SQL> column owner format a20

    SQL> column table_name format a40

    SQL> Select owner, table_name from dba_tables where table_name in ('F9860','F983051','F986130','F0094','F986110','F986167','F986111');

    For example, a version 9.2 JD Edwards system will have:

    OWNER                TABLE_NAME

    -------------------- --------------

    PS920                F983051

    OL920                F9860

    SVM920               F986111

    SVM920               F986110

    SY920                F0094

    SY920                F986130

    SY920                F986167

    Sometimes there are multiple owners that have the same table. In that case, check the row count of that table for each owner like the following:

    SQL> select count(*) from owner.table_name;

    For example:

    SQL> select count(*) from PS920.F983051;

    Only use the table that is not empty in the next two steps to grant privileges and create synonyms.

  2. Create an AEJDE user and grant privileges to it. From SYS user.

    3. grant connect, resource to <AEJDE_User> identified by <password>;

    grant create synonym to &&AEJDE_User

    grant select on PS920.F983051 to &&AEJDE_User

    grant select on OL920.F9860 to &&AEJDE_User

    grant select on SVM920.F986111 to &&AEJDE_User

    grant select on SVM920.F986110 to &&AEJDE_User

    grant select on SY920.F0094 to &&AEJDE_User

    grant select on SY920.F986130 to &&AEJDE_User

    grant select on SY920.F986167 to &&AEJDE_User

    When prompted with the following, specify your user name.

    >Enter value for aejde_user

  3. Create required synonyms. From the newly created AEJDE user.

    4. create synonym F9860 for OL920.F9860;

    create synonym F983051 for PS920.F983051;

    create synonym F986130 for SY920.F986130;

    create synonym F0094 for SY920.F0094;

    create synonym F986110 for SVM920.F986110;

    create synonym F986167 for SY920.F986167;

    create synonym F986111 for SVM920.F986111;

  4. Verify the synonyms you just created with the following:

    5. SQL> column synonym_name format a20

    SQL> column table_owner format a20

    SQL> column table_name format a40

    SQL> set linesize 132

    SQL> select synonym_name, table_owner, table_name from USER_SYNONYMS where table_name in ('F9860','F983051','F986130','F0094','F986110','F986167','F986111');