*PUBLIC Authority through OMW

Dragonlance

Member
Everyone,
We have found an issue on our As/400 Enterprise server that if we use the SETOWAUT command to set *PUBLIC to *EXCLUDE, any file generated through OMW will have *PUBLIC set to *ALL thus defeating the purpose of any PUBLIC security that a business may have put on. Oracle has told me this is unique to our company and why we couldn't just change the security back after generating the file. After trying to explain Segragation of duties, we got no where.
The manager at Oracle explained this is how it works all the way to 9.0 release.

My question is Has anyone else expierenced this issue? Is this how it works if the Enterprise server is on an Intel Box of rthe datafiles with SQL? (I am OS/400 native, not INTEL SQL)

Below is a copy of the SR dialog with Oracle on thier findings.

Thank you,

Dean

ODM Action Plan 14-Apr-2009 11:44:11 GMT-04:00 AM Oracle Support
Unscheduled

Did you search the SAR # in "Advance Search" option of Metalink3 (My Oracle Support)? Here is the description of SAR below. The SAR was entered as an enhancement SAR because it is working as design (grant *ALL to *PUBLIC) for our development team. SARBANES-Oxley Act is introduced in 2002 (7 years ago). OneWorld is out for 12 years or so. This SAR (project) is an enhancement to OneWorld (EnterpriseOne) to meet SARBANES-Oxley Act requirement for our development team. As far as I can see, the enhancement SAR has not bee implemented in 8.12 and 9.00 yet. Please let us know if you have a question.

***********

PROGRAM NAME/NUMBER
PLATFORM/SERVER/ENVIRONMENT
All
DESCRIPTION OF THE PROBLEM
Customer has issue with Database permissions on Public
role. He says that public is created with all rights on the
database which means that all database users get all rights
on the tables - this is not good for security purposes. He
wants to take the rights away from the public role and give
the proper rights on a new role to address this, but they
have discovered also that the Generate Table from OMW
grants ALL to public every time a new table is created or
generated.
Development says : "By Default Tables generated from
OneWorld will have a GRANT ALL ON .... PUBLIC clause.
PUBLIC is the role which contains all the users in the
Database. We cannot change the PUBLIC to any other USER/ROLE
in the Database. It is currently not supported by OneWorld.
"
Now customer says as this is a major security hole in the
database wants to open a SAR requesting a tools change for
this.
DESIRED OUTCOME
Parameterize the ROLE to which we grant table access to, so
that Customers can override PUBLIC and enter their own
role.

Done
Please review the SAR 7588556
We have done simple test. Based on jdedebug.log when a table is generated,

Apr 03 11:48:16 ** 2104/2820 CREATE TABLE NOBU01/F55NB01 (NBAN8 NUMERIC(8,0), NBALPH CHAR (40), NBAT1 CHAR (3))
Apr 03 11:48:17 ** 2104/2820 ODBC:I DBInitRequest(new) conn=037117E0 hd=060036B0 dr=033C0BE8 JDEIOW A (jdeusr@Business_Data_400_C15)
Apr 03 11:48:17 ** 2104/2820 GRANT ALL ON NOBU01/F55NB01 TO PUBLIC

I have created a table F55NB03 in NOBU01 library, which is set *USE for create authority, in AS/400. When the table was created, the file object had *USE authority based on the library it is in. After it was created, I have executed Grant comment based on the jdedebug.log. I then checked the file object authority. It had *USER DEF authority as we expected. In JDB_CreateTable API, there is a logic to grant *ALL authority to *PUBLIC user. To address this issue, we have an enhancement SAR 7588556. Please review the SAR. Please let us know if you have a question.
 
I can say that E1 (and OneWorld before it) has always been this way, since at least 1997 in my experience. It is hardly unique to your company though, as I have had other clients complain about the same thing.

My best guess as to why they won't change it is that in the effort to be platform agnostic, the best course of action to work correctly on all the various databases supported is to do it this way, and leave any database-level/table-level security up to whoever the DBA is.

There are people who have been around longer than I (Jon? Colin?) who could probably explain better why it was programmed this way.
 
Hmmm....... I'll wait for Jon on this. He's been around since the stone ages - me just been here since the Medieval Times.

This is the same on all platforms whether it's Oracle, DB2 UDB, DB@/400, SQL Server. All tables are created with *PUBLIC set to *ALL.

I remember seeing a thead way back when where someone created a trigger to change this. You can also write a script to do this very easily on any platform.

Should only take a few minutes on the AS/400 - let me know if you need help with htis.

Colin
 
Yep, it's a security hole. Yep, there is a good reason. Nope, they probably won't fix it. Yep, you should just work around it.

http://jeffstevenson.karamazovgroup.com/2009/08/enterpriseone-sql-security.html



[ QUOTE ]
Everyone,
We have found an issue on our As/400 Enterprise server that if we use the SETOWAUT command to set *PUBLIC to *EXCLUDE, any file generated through OMW will have *PUBLIC set to *ALL thus defeating the purpose of any PUBLIC security that a business may have put on. Oracle has told me this is unique to our company and why we couldn't just change the security back after generating the file. After trying to explain Segragation of duties, we got no where.
The manager at Oracle explained this is how it works all the way to 9.0 release.

My question is Has anyone else expierenced this issue? Is this how it works if the Enterprise server is on an Intel Box of rthe datafiles with SQL? (I am OS/400 native, not INTEL SQL)

Below is a copy of the SR dialog with Oracle on thier findings.

Thank you,

Dean

ODM Action Plan 14-Apr-2009 11:44:11 GMT-04:00 AM Oracle Support
Unscheduled

Did you search the SAR # in "Advance Search" option of Metalink3 (My Oracle Support)? Here is the description of SAR below. The SAR was entered as an enhancement SAR because it is working as design (grant *ALL to *PUBLIC) for our development team. SARBANES-Oxley Act is introduced in 2002 (7 years ago). OneWorld is out for 12 years or so. This SAR (project) is an enhancement to OneWorld (EnterpriseOne) to meet SARBANES-Oxley Act requirement for our development team. As far as I can see, the enhancement SAR has not bee implemented in 8.12 and 9.00 yet. Please let us know if you have a question.

***********

PROGRAM NAME/NUMBER
PLATFORM/SERVER/ENVIRONMENT
All
DESCRIPTION OF THE PROBLEM
Customer has issue with Database permissions on Public
role. He says that public is created with all rights on the
database which means that all database users get all rights
on the tables - this is not good for security purposes. He
wants to take the rights away from the public role and give
the proper rights on a new role to address this, but they
have discovered also that the Generate Table from OMW
grants ALL to public every time a new table is created or
generated.
Development says : "By Default Tables generated from
OneWorld will have a GRANT ALL ON .... PUBLIC clause.
PUBLIC is the role which contains all the users in the
Database. We cannot change the PUBLIC to any other USER/ROLE
in the Database. It is currently not supported by OneWorld.
"
Now customer says as this is a major security hole in the
database wants to open a SAR requesting a tools change for
this.
DESIRED OUTCOME
Parameterize the ROLE to which we grant table access to, so
that Customers can override PUBLIC and enter their own
role.

Done
Please review the SAR 7588556
We have done simple test. Based on jdedebug.log when a table is generated,

Apr 03 11:48:16 ** 2104/2820 CREATE TABLE NOBU01/F55NB01 (NBAN8 NUMERIC(8,0), NBALPH CHAR (40), NBAT1 CHAR (3))
Apr 03 11:48:17 ** 2104/2820 ODBC:I DBInitRequest(new) conn=037117E0 hd=060036B0 dr=033C0BE8 JDEIOW A (jdeusr@Business_Data_400_C15)
Apr 03 11:48:17 ** 2104/2820 GRANT ALL ON NOBU01/F55NB01 TO PUBLIC

I have created a table F55NB03 in NOBU01 library, which is set *USE for create authority, in AS/400. When the table was created, the file object had *USE authority based on the library it is in. After it was created, I have executed Grant comment based on the jdedebug.log. I then checked the file object authority. It had *USER DEF authority as we expected. In JDB_CreateTable API, there is a logic to grant *ALL authority to *PUBLIC user. To address this issue, we have an enhancement SAR 7588556. Please review the SAR. Please let us know if you have a question.

[/ QUOTE ]
 
Colin and Jon,
Thank you for the reply. I do not understand why it could not be a processing option so you can set what permission you want the *PUBLIC to have and leave the default as *ALL. I must be missing something as i do not understand why they have a document (Doc ID 663791.1) that says this is what to run to shut down this security loophole that thier software will punch holes into.

Unforunately our Compliancy people have determined that DB and Security are two separate functions and the DB people should not be doing security changes. Hence the CNC people do not have access to do security changes. The Auditors viewed it as an audit finding.

Ok, Colin mentioned a script to fix this. I haven't thought this out and know i could run such a script library wide to close the *PUBLIC but not sure how to do this on a file by file basis other then scanning library, sorting, then extracting the individual files that were changed, i see no way to do this to only the handful of files that were recently generated. I will have to think about this to see what would be the fastest. TAATOOL has a CHGPUBAUT command that may work faster.

Thank you,

Dean
 
Send me the name of the target libray (JDE library), what permissions you want to remove (*PUBLIC) and what permission you want to add.

I'll send you a script to do this one library at a time.


Colin
 
Back
Top