Question regarding privileges

coavlanche

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!

mbruegel

Here is a link for the privs required by Schema Compare for Oracle:

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

coavlanche

Thanks Martin, this is good info!  We would like to have a super user that can create/modify in all schemas that we're deploying to, but it seems like the only way to achieve that is to grant that user DBA rights, which is less than ideal from a security perspective.  We did try the option in the documentation of 'GRANT GRANT ANY OBJECT PRIVILEGE to <user>' however that seems to fall short when you have a constraint that needs to be created, possibly other actions as well.

mbruegel

As noted we deploy our schemas by the schema owner -- using RedGate combined w/ source control (Source Control for Oracle) that should give us nice per schema versioned representations of the individual schemas. --

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.