Automatically give permission to Tables

Kodiak_Seattle

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!

Sentinel

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.

Kodiak_Seattle

Can you give an example? I am a newbie when it comes to Roles.

Frank Kulash

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

Solomon Yakobson

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.

Kodiak_Seattle

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 ?????

Kodiak_Seattle

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

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

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.

SeánMacGC

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;

JustinCave

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