CREATE TABLE privilege
Posted on 2011-04-19
Dear Experts.. We have an application which is configured to have multiple application specific schemas (around 150). Each schema is in its own tablespace. It is part of a Healthcare system and each schema represents a clinical study. Studies constantly get locked (completed clinical studies) and new studies get created. So the list of active schemas keep changing.
For the application to function properly, we have to create a couple of tables and indexes in each of these schemas. Until now we have been using the schema username and password for doing these activities. The quality group is questioning this as we are not using named account for this and using some kind of shared account. Technically not shared, but it is being called as shared since everyone in the support team knows the username/password for these schemas.
I have to address this concern as the project lead. Is there anyway we can address this without providing CREATE ANY TABLE privilege? One thought I had was creating a stored procedure on another schema which will be granted CREATE ANY table privilege. The named accounts will be provided execute privilege on the procedure. Still it involves granting CREATE ANY privilege for a schema, which does not sound rite. Any suggestions?