How to grant read-only access to all the DB objects to another user in oracle?

Question

I try to give read only access to all db objects to another user that might not exist. This is how I tried to do it:

DECLARE
    user_exists NUMBER;

BEGIN

    SELECT COUNT(*) INTO user_exists FROM ALL_USERS WHERE USERNAME = '${user}';

    IF user_exists > 0 THEN

        FOR obj IN (SELECT object_name, object_type
                  FROM all_objects
                  WHERE owner = '${owner}'
                    AND object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE'))
            LOOP
                IF obj.object_type IN ('TABLE', 'VIEW') THEN
                    EXECUTE IMMEDIATE 'GRANT SELECT ON ${owner}.' || obj.object_name ||
                                      ' TO ${user}';
                ELSIF obj.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
                    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ${owner}.' || obj.object_name || ' TO ${user}';
                END IF;
            END LOOP;

    END IF;

END;

The script fails with the following message:

  Message    : ORA-00942: table or view does not exist
  ORA-06512: at line 16
  ORA-06512: at line 16

What am I doing wrong?

Answer 1

The script would fail at the grant select to obj... when the table was "flyway_schema_history". I do not know exactly why this is happening, but one solution is to skip that table.

Here is the updated and working script:

DECLARE
    user_exists NUMBER;

BEGIN

    SELECT COUNT(*) INTO user_exists FROM ALL_USERS WHERE USERNAME = '${user}';

    IF user_exists > 0 THEN

        FOR obj IN (SELECT object_name, object_type
                  FROM all_objects
                  WHERE owner = '${owner}'
                    AND object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE')
                    AND object_name NOT LIKE 'flyway_schema_history')
            LOOP
                IF obj.object_type IN ('TABLE', 'VIEW') THEN
                    EXECUTE IMMEDIATE 'GRANT SELECT ON ${owner}.' || obj.object_name ||
                                      ' TO ${user}';
                ELSIF obj.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
                    EXECUTE IMMEDIATE 'GRANT EXECUTE ON ${owner}.' || obj.object_name || ' TO ${user}';
                END IF;
            END LOOP;

    END IF;

END;