Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181

    Unanswered: Granting select on a schema

    Is there a way to grant select to a user on all tables owned by another schema and all future tables owned by that schema.
    The alternative as I see it is granting select any table which I would like to avoid.
    Rgs,
    Breen.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No. You could look into creating a DDL trigger on the schema so that whenever a new table is created a GRANT to the other user is always done, something like this:
    Code:
    CREATE TRIGGER grant_to_user2
    AFTER CREATE ON schema1
    BEGIN
      EXECUTE IMMEDIATE 'GRANT SELECT ON ' || ora_dict_obj_name || ' TO user2';
    END;

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanking you Tony.
    That will do just fine.

    Cheers,
    Breen.

  4. #4
    Join Date
    Mar 2004
    Posts
    21
    Tony's suggestion is very good, but I would also suggest doing this grant to a role rather than the user directly. If you ever need to add another user, or remove/restrict the access to the first user, it is much more time intensive to do. By using the trigger to grant to the role, you can in turn grant that role to any number of users, all of whom will get access to new tables in the schema as the triggrer grants them to the role.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I 100% concur with dbatteiger, you should always use roles rather than users in grants where possible.

Posting Permissions

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