Monday 23 June 2014

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

/* Formatted on 6/23/2014 10:33:45 AM (QP5 v5.115.810.9015) */
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;
/
-- Please give the User Name in v_user variable

No comments:

Post a Comment