Thursday, November 1, 2012

Script to Give Grant (Read Only) for all Objects to a particular Schema

-- Please give the User Name in v_user variable

declare
cursor cur_grants(p_user varchar2)
is
    select 'GRANT '
               || decode(db.object_type,'TABLE','SELECT',
               'VIEW','SELECT',
               'EXECUTE')
                || ' ON '
                || decode(db.owner,'PUBLIC','',db.owner || '.')
                || '"'
                || db.object_name
                || '"'
                || ' TO '||p_user sql_stmt
     from all_objects db
   where db.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE',  'VIEW','FUNCTION')
   union
    select 'GRANT '
              || decode (ao2.object_type, 'TABLE', 'SELECT',
              'VIEW', 'SELECT',
              'EXECUTE')
              || ' ON '
              || decode(ao.owner,'PUBLIC','',ao.owner || '.')
              || '"'
              || ao.object_name
              || '"'
              || ' TO '||p_user sql_stmt
    from all_objects ao
           , all_objects ao2
           , dba_synonyms ds
  where ao.object_type = 'SYNONYM'
      and ao.object_name = ds.synonym_name
      and ao2.object_name = ds.table_name
      and ao2.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE',  'VIEW','FUNCTION')
;

v_user varchar2(20) := 'USER_NAME';

begin

for i in cur_grants(v_user) loop

     execute immediate i.sql_stmt ;

     dbms_output.put_line('Command : '||i.sql_stmt);

end loop;

exception
     when others then
          dbms_output.put_line('Error: '||sqlerrm);
end;
/

No comments:

Post a Comment