From Release 9.2 Database Security has been changed. For an installation, all tables delivered by the Platform Pack installer are locked down ( Public Shutdown ) & For an upgrade, only NEW tables delivered by the installer are locked down.

 

Here I am using the Oracle Database , for explaining the concept.

 

Platform pack

 

When we run the 9.2 Platform pack for the installation/Upgrade , we will provide the Admin Role & End user Role as JDEADMIN & JDEUSER in the OUI.

Proxy user :  JDE

 

Platform Pack Installer assigned the Database Role JDE_ADMIN & JDE_ROLE to the Proxy User JDE.

 

For the Other Schema ( users )

 

Platform Pack  Installer assigned the JDEUSER and JDE_ROLE to the other DB schema/user ( DV920, SY920, OL920, SVM920, PS920,TESTDTA,TESTCTL …..etc)

JDE_ROLE  Permission in the Database

 

JDE_ROLE has the below permission in the Database, Because of that E1 Users are able to make the connection with Database and able create a Table & View in the Database.

 

Data Source DB Permission

The main change in the E1 system from  9.2  is below, For example :  Business Data – Test ( TESTDTA)

 

For an Install

 

JDEADMIN & JDEUSER has the Alter, Delete , Insert, Update, Select Permissions for the each table which was created by platform pack on TESTDTA. Because of this E1 users are able to fetch the data , add the data, update the data, delete the data in the Database.

 

All other E1 Data Sources (DV920, SY920, OL920, SVM920, PS920,TESTDTA,TESTCTl  …..etc) also having the same DB Permission.

 

For an Upgrade

 

For an upgrade, only NEW tables delivered by the installer are locked down. The installer does not lock down the logic artifacts during the Platform Pack execution - only the tables.

Note : This is called as Public shutdown. If other application is using your main Database other than E1 then that DB user will not be able to access the E1 Data.

 

What is benefit of the DB Security application ( P986117 ) ?

 

P986117 is simply used for record keeping and enables access to the database without having to ask the database administrator to create a database role and login credentials.

 

For Example : If we want to create a E1 User with read only access ,

 

Before 9.2

 

  • we  need  to create a DB user with Read only permission in DB with a help of DB admin
  • we need to create a System user in JDE .
  • Assign the system user to E1 user

 

From 9.2


Note : Above method also work for 9.2 but From E1 we can do this task with a help of DBA administrator

  • we need to open the application P986117 by login into JDEPLAN ( before workbench)
  • Create the Enter the DB user name ( For example : E1Read ) and assign the permission for all the Data Source

  •   Run the Workbench
  •   We need to create a System user in JDE .
  •   Assign the system user to E1 user

 

Installation Workbench  ( install/Upgrade) will take care the DB user creation and assigning the DB permission in the Database. No need of DB admin to do the task.

 

Record Keeping Purpose

 

For the default installation enter the below two records in P986117 for all the E1 Data Source in P986117 before running the workbench (As per the attached guide ) but I think its just for record keeping purpose so I entered the records using web client (once installation is completed )

For More information

 

Guide : https://docs.oracle.com/cd/E53430_01/EOTSC/datasource_security.htm#EOTSC987

 

Learn jde : https://apexapps.oracle.com/pls/apex/f?p=44785:141:15740944425770::::P141_PAGE_ID,P141_SECTION_ID:343,2574#DBSecurity

 

Impact of missing Enhanced Database Security

 

l have seen the below scenarios where customer faced the issue if this DB privilege were not set properly

 

Case 1 : During the Upgrade ( Install/Upgrade )

 

When customer upgrading the E1 from old release to new release using install/Upgrade method ( Opposite to supported same machine upgrade ) , customer have to refresh the TESTDTA & TESTCTL from old release DB to 9.2 DB .

 

In this activity, if customer drops the TESTCTL and TESTDTA in the 9.2 DB and created the schema from old release. DB Role JDEUSER & JDE_ROLE will not be assigned and missing this permission will leads to Table conversion failure.

 

E1: UPG : Table Conversion Fails While Running the Upgrade Workbench With Error - "OCI0000017 - Unable to execute statement for describe - SELECT * FROM SY920.F0093" (Doc ID 2182159.1)

 

Case 2 : Creating the custom environment ( Without using Environment master or E1 scripts )

 

If customer creating the custom environment by copying the Table, OCM ...etc and creating the custom environment. The Database privilege discussed above will not be applied for the custom environment. This will leads customer in a situation where they cant use the custom environment.