We help IT Professionals succeed at work.

CREATE TABLE privilege

589 Views
Last Modified: 2012-05-11
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?
Comment
Watch Question

Senior Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
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.
Walter RitzelSenior Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.