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.
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.