Link to home
Start Free TrialLog in
Avatar of Ravindran Gopinathan
Ravindran GopinathanFlag for United States of America

asked on

CREATE TABLE privilege

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?
ASKER CERTIFIED SOLUTION
Avatar of Walter Ritzel
Walter Ritzel
Flag of Brazil image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ravindran Gopinathan

ASKER

Thanks for the feedback... At this point only SYS has "create any" privileges now! I think there is no other option. One suggestion that came up from Quality group is to maintain a spreadsheet. Each support staff will add entry everytime they use one of the common schema accounts for performing any action. I did not want to take this direction. It puts onus on people and chances are high they will miss a lot.

I will push for the solution of creating a package for this and hope for the best! We can even add some auditing information like who executes the procedure and at what time and from where in some kind of audit table. That might help my discussions.

I will leave the question open (though pretty much everything is discussed already!) for a couple more days just in case anyone as any other comments.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

definitely do not use a manually maintained spreadsheet

use auditting and don't allow anyone to log in as SYS, there are very few operations that require being SYS