Oracle SQL: Trigger to add grants after creating view

Ignoring whether this is a good idea... you're getting that error because you're overthinking your string manipulation. What you're putting into your table looks OK. The problem is when you get it back out. The value in the table is already a string, so you don't need to then enclose it in another set of quotes.

What you're actually running is the equivalent of:

EXECUTE IMMEDIATE '''GRANT SELECT ON MYUSER.CR_STATUS_TABLE TO SOMEUSER''';

which will also throw "ORA-00900: invalid SQL statement", rather than your standalone, working, version:

EXECUTE IMMEDIATE 'GRANT SELECT ON MYUSER.CR_STATUS_TABLE TO SOMEUSER';

If you swapped the order of your EXECUTE IMMEDIATE and DBMS_OUTPUT calls you'd see the problem statement before it ran, which would be more helpful - you'd see those quotes as part of the string.

So in your second trigger, instead of doing:

FOR loop_counter IN (select '''' || privilege_x || '''' AS privilege_x from temp_priv)
LOOP
    EXECUTE IMMEDIATE loop_counter.privilege_x;
    DBMS_OUTPUT.PUT_LINE(loop_counter.privilege_x);
END LOOP;

just do:

FOR loop_counter IN (select privilege_x from temp_priv)
LOOP
    DBMS_OUTPUT.PUT_LINE(loop_counter.privilege_x);
    EXECUTE IMMEDIATE loop_counter.privilege_x;
END LOOP;

However, this still won't work; it will now get ORA-30511: invalid DDL operation in system triggers. This is presumably because of the restrictions shown in the documentation:

Trigger cannot do DDL operations on object that caused event to be generated.

DDL on other objects is limited to compiling an object, creating a trigger, and creating, altering, and dropping a table.

You said "it is easy to forget to backup up and restore them afterwards" but you're going to have to put a robust process around your upgrades to make sure that does happen.

You can change your process to have a separate step at the end of every upgrade that is always run, which executes all of those stored statements for all objects - maybe skipping or ignoring errors from anything that wasn't recreated - and then drops the temp_priv table.

But you don't really want to (try to) create that temporary table in a trigger anyway - if two views are dropped, the first creates it, the second fails because it already exists. A perhaps more realistic approach might be to create that table once now:

create table TEMP_PRIV (PRIVILEGE_X VARCHAR2(4000));

and then utilise it for all subsequent upgrades, either by populating it with all grants for all views as a single step before the upgrade starts:

INSERT INTO TEMP_PRIVS (PRIVILEGE_X)
SELECT 'GRANT ' || PRIVILEGE || ' ON MYUSER.' || TABLE_NAME || ' TO ' || GRANTEE
FROM USER_VIEWS UV
JOIN USER_TAB_PRIVS UTP ON UTP.TABLE_NAME = UV.VIEW_NAME
WHERE UTP.GRANTEE not in ('MYUSER','PUBLIC');

or if you're still worried about possibly forgetting that step then with a trigger to do it one view at a time as they are dropped:

create or replace TRIGGER RECORD_GRANTS_ONDROP 
BEFORE DROP ON MYUSER.SCHEMA 
BEGIN
    IF ora_dict_obj_owner = 'MYUSER' and ora_dict_obj_name not like 'TEMP_PRIV%' and ora_dict_obj_type='VIEW' then
      INSERT INTO TEMP_PRIV
      SELECT 'GRANT ' || PRIVILEGE || ' ON MYUSER.' || TABLE_NAME || ' TO ' || GRANTEE
      FROM USER_TAB_PRIVS
      WHERE GRANTEE not in ('MYUSER','PUBLIC')
      AND TABLE_NAME = ora_dict_obj_name;
    END IF;
END;
/

Then at the end of the upgrade process reissue all the statements in the table and clear it down ready for next time:

DECLARE
    missing_view EXCEPTION;
    PRAGMA EXCEPTION_INIT(missing_view, -942);
BEGIN
    FOR loop_counter IN (select privilege_x from temp_priv)
    LOOP
        BEGIN
            DBMS_OUTPUT.PUT_LINE(loop_counter.privilege_x);
            EXECUTE IMMEDIATE loop_counter.privilege_x;
        EXCEPTION
          WHEN missing_view THEN
              -- report but otherwise ignore
              DBMS_OUTPUT.PUT_LINE(SQLERRM);
        END;
    END LOOP;
END;
/

TRUNCATE TABLE temp_priv;

If you go with the simpler non-trigger approach then it will re-grant existing privileges, but that's OK. And the exception handler means it'll report but skip any views that were dropped and not recreated, if that ever happens. (You'll still have to deal with any new view of course; your after-create trigger wouldn't have helped with that anyway.) And note that I've truncated the table, rather than dropped it - so it's still there, empty, when the next upgrade comes around and wants to populate it.