Create Read-only database user for your JD Edwards setup in Oracle

Recently, we were required to provide read only access to JD Edwards database (Oracle 12c). As we know, we have multiple schemas in database when it comes to JD Edwards. Hence when such a requirement arises, it is always better to have a read only role ready in your database. Following are the steps to provide read-only access to a user:

Create a database user

Let us first start with creating a database user, by providing username, identifying it with password, defining user tablespace and temporary tablespace. Following SQL query should create an user with username as NIMISH, password as mypass, user tablespace as users and temporary tablespace as temp:

CREATE USER NIMISH
IDENTIFIED BY mypass
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

Allow user to connect to database

A user must be allowed to connect to database once it is created. Following SQL will allow user “NIMISH” to connect to the database:

GRANT CREATE SESSION to NIMISH;

Identify the schema names for which access needs to be provided

Since we have multiple schema in JD Edwards, we will list them down first. For this example, I’m providing access to development (DV) environment along with system tables, on JD Edwards 9.2 setup. Respective schema names are as follows:

1. TESTDTA (Business data)
2. TESTCTL (Control tables)
3. DV920 (Central objects)
4. SY920 (System tables)

You can verify the table names by using following query:

SELECT * FROM dba_tables WHERE owner IN ('TESTDTA', 'TESTCTL', 'SY920', 'DV920');

In this step, we just need to identify schema names. We will be using it in next steps.

Create a read only role

It is always better to create a role and grant read only access to it, then assign it to user, rather than assigning privileges directly to the user. This will enable you to create a new user directly, next time a similar requirement arises. You can simply create a new user and assign read only role to it.

Following is the SQL to create a new role named “JDE_READONLY”:

CREATE ROLE JDE_READONLY;

Assign privileges to the role

Once we have a role ready, we will assign read only access to it. To provide read only access we will “GRANT SELECT ON” the list of tables from their respective schema. Following is PL/SQL procedure to grant select access to role JDE_READONLY for all tables in schemas TESTDTA, TESTCTL, DV920 and SY920:

BEGIN
FOR x IN (SELECT * FROM dba_tables WHERE owner IN ('TESTDTA', 'TESTCTL', 'SY920', 'DV920'))
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.owner || '.' || x.table_name || ' TO JDE_READONLY';
END LOOP;
END;

Assign role to the user

Once we have user and role ready with us, we will assign the role to user to grant him select privileges on required tables. Following is the SQL to assign role “JDE_READONLY” to user “NIMISH”:

GRANT JDE_READONLY TO NIMISH;

That’s all folks! 🙂