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?
LVL 10
Who is Participating?
Walter RitzelSenior Software EngineerCommented:
Your quality team seems to be concerned with the fact that you are sharing the password of the application schemas (meaning schemas that will hold tables), which poses as a security threat for your database.
To your exact point, seems that if you want to give the ability to your developers to create tables in any schema, that the create any table privilege will be needed, directly or indirectly through a procedure. So, in this case, why not centralize this to small team that will have the privilege to do it?

I believe that your problem here is not technical, but of process. I would search over the web about ITIL, COBIT and terms like segregation of duties.
to control the create any table, add a DDL trigger that controls what your users can actually do.

also, if the "create any" privileges are being used to do the same thing repeatedly, then isolating those steps to a package owned by a single user with create any would probably be best then give execute on that package to your app support staff.
ravindran_eeeAuthor 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 EngineerCommented:
creating the package in a separate schema, giving this schema the create any table privilege and ensure that all other accounts have only execute permission on the package is a good way to go, but your quality team is right: you need traceability of who execute what... So please dont forget to include the audit table on this schema to keep track of changes made by users.
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.