Dynamic Grants mgmt

 
-- ############################################################
-- # Creator: Yann Cousin
-- # Created: 2011/04/22
-- # Name: dynamic Grants mgmt
-- ############################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ############################################################
-- #
-- # How to create a read only user and dynamicaly grant
-- # privileges to this new user:
-- # - List all existing tables of USER1 and grant select on it
-- # to USER2
-- # - Create a trigger used to grant select on each new table
-- # to USER2
-- #
-- ############################################################


-- First, we give static grants to the new user USER2
select 'grant select on ' || owner ||  '.' || table_name || ' to USER2;' from dba_tables where owner='USER1';


-- Execute the generated grants 


-- Connect to database with USER1 and create the trigger 

CREATE OR REPLACE TRIGGER Grant_Select_On_Schema
  after CREATE ON schema
  declare
    instr varchar2(255);
    exec_instr number;
  begin
      IF ( ora_dict_obj_type = 'TABLE' )
      then
      instr := 'execute immediate "grant select on ' ||ora_dict_obj_name ||' to USER2";';
      dbms_job.submit( exec_instr, replace(instr,'"','''') );
      end IF;
  end;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.