Forum Stats

  • 3,836,939 Users
  • 2,262,206 Discussions
  • 7,900,149 Comments

Discussions

Automatically give permission to Tables

Kodiak_Seattle
Kodiak_Seattle Member Posts: 565
edited Jul 15, 2009 12:05PM in SQL & PL/SQL
Is there a way to have permissions given to any Table that is created in my schema to my team members, automatically ? as soon as it is created ?

Oracle 10g

Thanks!
Tagged:
«1

Answers

  • Sentinel
    Sentinel Member Posts: 1,284 Silver Badge
    No but if you create a role that's assigned to your team members, you can get by with only one grant statement per object.
  • Can you give an example? I am a newbie when it comes to Roles.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,208 Red Diamond
    edited Jul 14, 2009 7:16PM
    Hi,

    You can write a trigger that fires after an object is created in your schema, and use dynamic SQL to grant privileges.
    See the [PL/SQL manual|http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/create_trigger.htm#i2153503] for details.

    Whether you write a trigger or not, using roles, as Sentinel suggested, makes a lot of sense.

    Have a DBA create the role:
    CREATE ROLE  seattle_team;
    Depending on how things are done where you work, the DBA may give you administrative privileges on the role:
    GRANT  seattle_team  TO kodiak  WITH ADMIN OPTION;
    If so, you can then grant or revoke privileges to other users or roles:
    GRANT  seattle_team  TO    brown;
    GRANT  seattle_team  TO    polar;
    REVOKE seattle_team  FROM  grizzly;
    When a person joins (or leaves) the group, you only have to issue one GRANT (or REVOKE) command, no matter how many tables, views, packages, or other objects there are.
    When a new table (or other object) is created, you only have to issue one GRANT command, no matter how many people are in the group.

    Edited by: Frank Kulash on Jul 14, 2009 6:59 PM
    Frank Kulash
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,578 Red Diamond
    Frank Kulash wrote:
    You can write a trigger that fires after an object is created in your schema, and use dynamic SQL to grant privileges.
    Hi Frank,

    AFAIK, trigger by itself will not help since after trigger is called before create table is committed. OP would have to write a trigger that creates a job to issue grants on the table.

    SY.
  • Any example would be great, I am not that familiar with PL/SQL, I have a book, but I have never executed any successfully here at work.

    CREATE TRIGGER update_permission AFTER CREATE ON SCHEMA X123
    pl/sql_block ?????
  • The Role is a little confusing, because I don't see anywhere what type of permission that I can give ? I only want to give SELECT, and nothing else, I just tell that to the Admin ?
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,208 Red Diamond
    Hi,

    Solomon is probably right; an AFTER CREATE trigger might be more complicated than I thought. I've never tried this myself. If I come up with something, I'll post it here.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,208 Red Diamond
    Hi,
    Kodiak_Seattle wrote:
    The Role is a little confusing, because I don't see anywhere what type of permission that I can give ? I only want to give SELECT, and nothing else, I just tell that to the Admin ?
    Once the role is created, you grant privileges to the role the same way you grant privileges to a user:
    GRANT  SELECT  ON table_x  TO seattle_team;
    Then everybody who has the role has the privilege.
    Frank Kulash
  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    edited Jul 14, 2009 7:50PM
    Hello,

    You need to grant the permissions and privileges to the role itself, and then grant the role to each user that you wish to have those privileges and permissions, as Frank as shown above.

    Therefore, if you wanted to grant SELECT on every table belonging to a particular user to the seattle_team role, you could run the following, by connecting to SQL*Plus as the user who owns the tables:
    BEGIN
      FOR cur_rec IN (SELECT table_name FROM user_tables) 
        LOOP
          EXECUTE IMMEDIATE 'GRANT SELECT ON ' || cur_rec.table_name || ' TO seattle_role';
        END LOOP;
    END;
    SeánMacGC
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    If you really want this to be automatic, you would need a few things

    - You'd need a role that all the team members will have
    - You'd need a procedure that grants access to the table to that role
    - You'd need a DDL trigger that spawns a job (via DBMS_JOB or DBMS_SCHEDULER) that calls the procedure

    It won't be instantaneous, but you can make the grant happen generally within a few seconds of the table being created. This does work (I've done it myself in one particular app), but it's a bit of a pain to get set up and going. Unless you have an application that is automatically creating new tables, which is generally a bad design, however, it is far less complicated to just tell developers/ DBAs to grant access to new tables when they create them.

    Justin
    JustinCave
This discussion has been closed.