SET SERVEROUT ON
DECLARE
  P_OWNER   VARCHAR2(200) := '&owner';
  P_USER    VARCHAR2(200) := '&appuser';
  P_ROLCONS VARCHAR2(200) := '&rolcons';
  P_ROLATU  VARCHAR2(200) := '&rolatu';
BEGIN
  FOR R IN 
  ( 
    SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM DBA_OBJECTS 
    WHERE OWNER=P_OWNER
    AND OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'PROCEDURE', 'PACKAGE', 'FUNCTION' , 'SEQUENCE' ) 
    AND OBJECT_NAME NOT LIKE 'DR$%'
    AND OBJECT_NAME NOT LIKE 'BIN$%'
    ORDER BY OBJECT_TYPE  
  )           
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'CREATE SYNONYM '||P_USER||'.'||R.OBJECT_NAME||' FOR '||R.OWNER||'.'||R.OBJECT_NAME;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE IN (-955) THEN
          NULL;
        ELSE
          --RAISE;
          DBMS_OUTPUT.PUT_LINE( R.OWNER||'.'||R.OBJECT_NAME||':' );
          DBMS_OUTPUT.PUT_LINE( SQLERRM );
        END IF;
    END;    
    BEGIN
      IF R.OBJECT_TYPE IN ( 'TABLE' ) THEN
        EXECUTE IMMEDIATE 'GRANT SELECT ON '||R.OWNER||'.'||R.OBJECT_NAME || ' TO ' || P_ROLCONS;
        EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||R.OWNER||'.'||R.OBJECT_NAME || ' TO ' || P_ROLATU;
      ELSIF R.OBJECT_TYPE IN ( 'VIEW' ) THEN
        EXECUTE IMMEDIATE 'GRANT SELECT ON '||R.OWNER||'.'||R.OBJECT_NAME || ' TO ' || P_ROLCONS;
        EXECUTE IMMEDIATE 'GRANT SELECT ON '||R.OWNER||'.'||R.OBJECT_NAME || ' TO ' || P_ROLATU;
      ELSIF R.OBJECT_TYPE IN ( 'PROCEDURE', 'PACKAGE', 'FUNCTION' ) THEN
        EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||R.OWNER||'.'||R.OBJECT_NAME || ' TO ' || P_ROLATU;
      ELSIF R.OBJECT_TYPE IN ( 'SEQUENCE' ) THEN
        EXECUTE IMMEDIATE 'GRANT SELECT ON '||R.OWNER||'.'||R.OBJECT_NAME || ' TO ' || P_ROLATU;
      ELSE
        RAISE_APPLICATION_ERROR( -20000, 'TIPO NÃO MAPEADO');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE IN (-30657) THEN
          NULL;
        ELSE
          --RAISE;
          DBMS_OUTPUT.PUT_LINE( R.OWNER||'.'||R.OBJECT_NAME||':' );
          DBMS_OUTPUT.PUT_LINE( SQLERRM );
        END IF;
    END;  
  END LOOP;
END;
/