CREATE TABLE privilege

Posted on 2011-04-19
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?
Question by:ravindran_eee
    LVL 15

    Accepted Solution

    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.
    LVL 73

    Assisted Solution

    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.
    LVL 10

    Author Comment

    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.
    LVL 15

    Assisted Solution

    by:Walter Ritzel
    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.
    LVL 73

    Expert Comment

    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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Via a live example, show how to take different types of Oracle backups using RMAN.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now