Question regarding privileges
coavlanche
Posts: 12 New member
Can someone tell me what privileges are required to deploy Oracle changes to the target database? These deploy scripts potentially include create table scripts. That's not such a big deal as the CREATE_ANY_TABLE grant can be given, but what if constraints need to be created, usually as part of the create table? As far as I can tell, one would need DBA access to be able to do so. That being the case, do you have to run the compare/deploy as a user that has DBA?
For some background we are doing a compare/deploy from DB to scripts, and then taking those scripts into a segregated environment and then doing a compare/deploy from those scripts to DB.
Thanks in advance!
For some background we are doing a compare/deploy from DB to scripts, and then taking those scripts into a segregated environment and then doing a compare/deploy from those scripts to DB.
Thanks in advance!
Answers
https://documentation.red-gate.com/sco5/requirements/permissions-required-to-use-schema-compare-for-oracle
In our shop we manage deployment by schema owner -- the owner has the necessary grants to deploy into their own schema only.
If you want to have a super user that can deploy to any schema see the above for the list of necessary create any privs.
- martin
If using the super user approach, we'd have a multi-schema model to manage / untangle which could get complicated / messy rather quickly (e.g. too many developer DBAs in the kitchen). -- Right now that appears to be the only way to manage multi-schema deployments with a single scripted deployment. (I have not tried that approach so I'm not sure how well the inter-dependency is managed.)
What would be really nice is:
1. a way to specify inter-dependencies between distinct schema models (i.e. source scripts) so that they could be deployed in a coordinated way.
2. a way to specify logon info for multiple schema owners (so the owner deploys DDL to their own schema)
While not an every day occurrence, the scenario that requires a multi-schema deployment w/ coordinated steps between each does come up.