GRANT current and new tables from a schema in oracle

Question

Without using GRANT SELECT ANY TABLE, how would I grant access to all tables from only one schema.

Example: There are three users. u1, u2, u3. u1 need access to all the tables that u2 has but doesn't need access to tables that u3 has. I could loop through all the tables and grant them individually. But what about new tables that u2 creates later. u1 needs those tables too.

How could I automatically grant those new tables as well?

Can you explain as to the reasoning of what your trying to do as there might be a better way to do it? — llanato, Oct 24 '15 at 23:25

score 1 · Answer 1 · answered Oct 25 '15 at 00:24

I believe you can do this with an AFTER CREATE trigger. The following example doesn't handle all your U1/U2/U3 logic - you'll have to add that - but it should give you the basic idea:

CREATE TRIGGER AUTO_GRANT
  AFTER CREATE ON SCHEMA
BEGIN
  IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || ora_dict_obj_owner || '.' ||
                       ora_dict_obj_name ' TO U1';

  END IF;
END AUTO_GRANT;

Docs here.

Best of luck.

GRANT current and new tables from a schema in oracle

1 Answers1