Questions from an Oracle DBA re setup for RTC
An Oracle DBA asks:
I did notice one item in the oracle setup documentation that may be an issue. The last step in the setup oracle installation refers to granting the DBA role to the user account for the application. This is a major SOX compliance issue. We cannot grant the DBA role to any account other than an individual DBAs. Can you check to see why this needs to be set? Could we grant additional specific permissions to the account for installation instead?
We also need to talk about what our standards are for schema owners and user accounts. Preferably, we try and setup two separate accounts at a minimum. We setup an ADMIN account that becomes the schema owner and is used during installation to create all the objects. It has permissions described in the document to create the objects and insert any initial data. Once the installation is complete, the password is changed on this account so it cannot be used by the application.
We then setup a USER account that is granted any required permissions to access the database objects themselves. This account is used by the application to login to the database. If the SQL code for the application is not fully qualified with the schema owner, we can create private synonyms in the USER account to access the objects directly. We highly discourage public synonyms being created. Will this work with RTC?
I did notice one item in the oracle setup documentation that may be an issue. The last step in the setup oracle installation refers to granting the DBA role to the user account for the application. This is a major SOX compliance issue. We cannot grant the DBA role to any account other than an individual DBAs. Can you check to see why this needs to be set? Could we grant additional specific permissions to the account for installation instead?
We also need to talk about what our standards are for schema owners and user accounts. Preferably, we try and setup two separate accounts at a minimum. We setup an ADMIN account that becomes the schema owner and is used during installation to create all the objects. It has permissions described in the document to create the objects and insert any initial data. Once the installation is complete, the password is changed on this account so it cannot be used by the application.
We then setup a USER account that is granted any required permissions to access the database objects themselves. This account is used by the application to login to the database. If the SQL code for the application is not fully qualified with the schema owner, we can create private synonyms in the USER account to access the objects directly. We highly discourage public synonyms being created. Will this work with RTC?
2 answers
The user only requires the DBA role for initial table creation that is used by repotools -create tables. After this has occurred you can reduce the roles for the user. The user at runtime will need to be able to insert, update, query etc. on the tables. Also, the application needs the ability to create temp tables for some queries.
We do have instances where SQL code run by the application is not fully qualified with the schema owner. I'm not aware of any in house testing where synonyms were used in the user account.
In summary, the application requires the following:
- DBA authority for initial setup
- Ability to create temp tables at runtime
- Ability to execute queries that are not fully qualified by the user schema.
Ritchie Schacher
Jazz Server Development
We do have instances where SQL code run by the application is not fully qualified with the schema owner. I'm not aware of any in house testing where synonyms were used in the user account.
In summary, the application requires the following:
- DBA authority for initial setup
- Ability to create temp tables at runtime
- Ability to execute queries that are not fully qualified by the user schema.
Ritchie Schacher
Jazz Server Development
Our Oracle DBAs are continually troubled when we RTC Admins request DBA rights for the DB users during setup of new applications and any upgrade procedures. So I wanted to see if its possible to quantify any exact priviledges requires such that they could create a new role with just those priviledges.