simple JD Edwards database security model implementation in oracle

I tend to want the ability to give people a read only account in JD Edwards.  I do this by creating a bunch of roles and then granting database permissions based upon these roles.  I keep my users that connect to the database pretty simple, as JDBC connection pooling works better with simple database connection users.

I never use the JDE account as a database connect account or a “service” account for my kernels, I create a jdeservice user for that, I also create a jdeuser database user that will connect all of my user sessions.  It’ll be assigned the JDE_USER role.  I’ll have a different oracle user account for jdero and also jdedev.  JDEDEV will have special permissions in DEV and will be able to do a bit more in the lower environments – and a bit LESS in the higher environments!

It’s really important to lock down as much as possible, NEVER grant all to public – a very sill thing to do.

create some roles,

JDE_USER
JDE_RO
JDE_DEV
JDE_ADMIN

see them in “select * from DBA_ROLES”

Do some role assignments:

grant JDE_USER to JDE;

Do some grants at a role level:

select 'GRANT SELECT, INSERT, UPDATE, DELETE  on ' || owner || '.' || table_name || ' to JDE_USER;' from all_tables where owner in ('CRPDTA','CRPCTL');

--run the results of the above

Do the same for RO:

select 'GRANT SELECT on ' || owner || '.' || table_name || ' to JDE_RO;' from all_tables where owner in ('CRPDTA','CRPCTL');

Check the roles that a user has been granted, checking the jde user below:

select * from dba_role_privs where grantee = 'JDE';

Check specific user granted privileges:

select privilege
from dba_sys_privs
where grantee='JDE'
order by 1;

Check what permissions a role has:

select * from ROLE_TAB_PRIVS where role = 'JDE_USER';

JDE_USER    CRPDTA    FF34R011        DELETE    NO
JDE_USER    CRPDTA    FF31K30        DELETE    NO
JDE_USER    CRPDTA    FF31K20        DELETE    NO
JDE_USER    CRPDTA    FF31K11        DELETE    NO
JDE_USER    CRPDTA    FF31K10        DELETE    NO
JDE_USER    CRPDTA    FF31113        DELETE    NO
JDE_USER    CRPDTA    FF31011S        DELETE    NO
JDE_USER    CRPDTA    FF30L912        DELETE    NO