Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Newbie question on roles/schema owner

    Do you allow your analysts the ability to log in as the schema owner in order to manage objects, or do you set up an analyst role within the schema with the appropriate rights?

    Benefits/drawbacks of either approach?

    -Chuck

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    I would create a role with an appropriate privilleges on objects in question and assign the same to the analyst. So that way You can have an idea that how far the analyst can go with that privilleges in worst case.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    We'd like our analysts to be able to create objects within a centralized schema, including tables.

    In order for multiple analysts to be able to alter and drop the same tables, am I looking at just defining the appropriate roles still?

    I am kind of confused about the concept of the schema owner. It seems like all analysts would need to log in as the schema owner in order to 'share' ownership of the same objects.

    Or, if one analyst creates a table, do they just need to run:
    GRANT DROP TABLE on <i>table_name</i> to ANALYST_GRP

    -Chuck

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    There are different ways to look at this ...
    In my opinion, the SCHEMA OWNER (especially in production, not so much in a development mode) should have a central point of modification. Too many hands in the pie ...

    The Schema owner's objects should be the "BIBLE", you know exactly what tables, triggers, procs, etc are there and how they are used. With my clients, I don't want developers making changes to tables, triggers, etc in a production database. They may make a change that "invalidates" other objects that they didn't know existed. I had 1 client that allowed developers the ability to log in with the schema owner. By MISTAKE he dropped a production table ...

    Have a schema owner, have a role for developers to do their job, but not full access ....

    HTH
    Gregg

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Right, we'd keep developers out of production.

    In our shop we usually have 2-3 analysts which work with the same system of tables. I just want to allow analyst1 the ability to ALTER/DROP objects created by analyst2, but without giving either the SCHEMA login.

    It sounds like I keep asking the same question over & over, so I apologize. So maybe an answer to the following question would take me over this barrier:

    Assuming all 3 analysts are members of the ANALYST role, what would analyst1 need to do after creating a table in the PROD schema, so that analyst2 or analyst3 would be able to come in and later DROP that table? Run a 'GRANT DROP TABLE on tablename TO ANALYST' statement ?

    -Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •