JD Edwards default oracle security for PUBLIC

ALL for public, say that it can’t be true!  Quite often it is…  A change is needed, here is a single statement that will do 90% of your job for you:

select 'GRANT SELECT,UPDATE,INSERT,DELETE on ' || owner || '.' || table_name || ' TO JDE_ROLE;' ||
'GRANT SELECT ON '  || owner || '.' || table_name || ' TO JDE_RO;' ||
'REVOKE ALL ON '  || owner || '.' || table_name || ' FROM PUBLIC;'
from all_tables where owner in ('FIADTA','FIACTL') ;

This generates all of the below… 5000ish of them.   You need to run them.

GRANT SELECT,UPDATE,INSERT,DELETE on FIADTA.F993023 TO JDE_ROLE;GRANT SELECT ON FIADTA.F993023 TO JDE_RO;REVOKE ALL ON FIADTA.F993023 FROM PUBLIC;                                                                                                                                    
GRANT SELECT,UPDATE,INSERT,DELETE on FIADTA.F99303 TO JDE_ROLE;GRANT SELECT ON FIADTA.F99303 TO JDE_RO;REVOKE ALL ON FIADTA.F99303 FROM PUBLIC;                                                                                                                                   
GRANT SELECT,UPDATE,INSERT,DELETE on FIADTA.F32943 TO JDE_ROLE;GRANT SELECT ON FIADTA.F32943 TO JDE_RO;REVOKE ALL ON FIADTA.F32943 FROM PUBLIC;                                                                                                                                 
GRANT SELECT,UPDATE,INSERT,DELETE on FIADTA.F4009T TO JDE_ROLE;GRANT SELECT ON FIADTA.F4009T TO JDE_RO;REVOKE ALL ON FIADTA.F4009T FROM PUBLIC;   

Then run the results, of course, you’ll need to ensure the following first:

• JDE_RO is a read only role that I give auditors
• JDE_ROLE is the role given to all oracle accounts that connect to the back end (usually only 1, like e1user so that connection pooling is efficient)
• you need to assign JDE_ROLE to the users that connect to the backend – who are they – run the following.  It’ll tell you the oracle usernames, and the # of times they are being used:

select count(1), scsecusr from sy910.f98owsec group by scsecusr ;

COUNT(1)               SCSECUSR  
---------------------- ----------
1                      JDE       
1                      jdero