Results 1 to 4 of 4

Thread: granting rights

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: granting rights

    I'm getting to the stage where I'm starting to grant rights to our Analysts/Developers, and I'm already a bit confused. Here was our theoretical plan:

    Create a Schema/User for each application (call them APP1, APP2, APP3), then create an analyst role for each schema (call them ANALYST_APP1, ANALYST_APP3, ANALYST_APP3) which would allow anyone who was a member of that role to create objects for that one schema. That way, people wouldn't have to log in as the schema owner to create objects, and we could prohibit unauthorized object creation/modification based upon role membership.

    Is this even possible? I see the CREATE TABLE system priviledge for example. What I don't see is how you'd give ANALYST_APP1 the CREATE TABLE right for only the APP1 schema. Really, it looks like there's no relationship between schema and system priviledges unless you're logged in as the schema owner.

    I think I'm missing a key conceptual point here.

    Thanks for helping,
    Chuck

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: granting rights

    It is normal for tables to be created only by the schema owner. Whoever is responsible for creating the tables logs in to the schema to do it.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Where does the CREATE TABLE priviledge fit in, then? As opposed to the CREATE ANY TABLE?

    If CREATE ANY TABLE allows a user to create a table in any schema, then I'm assuming that CREATE TABLE is specific to a single schema, but I don't see how you constrain the CREATE TABLE system priviledge to a single schema.

    -Chuck

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    CREATE TABLE allows you to create tables in your own schema. Without CREATE TABLE privilege, a user cannot even create tables in their own schema.

Posting Permissions

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