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;

Answer 2

This is a PL/SQL anonymous block, so you need to be using PL/SQL variables and bind substitutions. Try this:

DECLARE
    user_exists NUMBER;
    l_user VARCHAR2(30);
    l_owner VARCHAR2(30);
BEGIN
    l_user := 'USER1';
    l_owner := 'USER2';

    SELECT COUNT(*) INTO user_exists FROM ALL_USERS WHERE USERNAME = l_user;

    IF user_exists > 0 THEN

        FOR obj IN (SELECT object_name, object_type
                  FROM all_objects
                  WHERE owner = l_owner
                    AND object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE'))
            LOOP
                IF obj.object_type IN ('TABLE', 'VIEW') THEN
                    EXECUTE IMMEDIATE 'GRANT SELECT ON :1 TO :2' USING obj.owner||'.'||obj.object_name, l_user;
                ELSIF obj.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
                    EXECUTE IMMEDIATE 'GRANT EXECUTE ON :1 TO :2' USING obj.owner||'.'||obj.object_name, l_user;
                END IF;
            END LOOP;

    END IF;

END;

Answer 3

Unless I'm wrong, everything that looks like ${something} in your code is wrong.

What is it supposed to do? Did you actually query ALL_OBJECTS and see what's in there? It looks like as if you thought that they will be automagically substituted by something that exists ... huh, only in your imagination?

If it were a procedure, then you could pass owner (grantor) and another user (grantee) as parameters. Code you wrote can be reused, most of it:

SQL> CREATE OR REPLACE PROCEDURE p_grant (par_owner    IN VARCHAR2,
  2                                       par_grantee  IN VARCHAR2)
  3  AS
  4     user_exists  NUMBER;
  5     l_str        VARCHAR2 (1000);
  6  BEGIN
  7     SELECT COUNT (*)
  8       INTO user_exists
  9       FROM all_users
 10      WHERE username = DBMS_ASSERT.schema_name (par_grantee);
 11
 12     IF user_exists > 0
 13     THEN
 14        FOR obj IN (SELECT object_name, object_type
 15                      FROM all_objects
 16                     WHERE     owner = DBMS_ASSERT.schema_name (par_owner)
 17                           AND object_type IN ('TABLE',
 18                                               'VIEW',
 19                                               'PROCEDURE',
 20                                               'FUNCTION',
 21                                               'PACKAGE')
 22                           AND STATUS = 'VALID')
 23        LOOP
 24           IF obj.object_type IN ('TABLE', 'VIEW')
 25           THEN
 26              l_str :=
 27                    'GRANT SELECT ON '
 28                 || par_owner
 29                 || '.'
 30                 || obj.object_name
 31                 || ' TO '
 32                 || par_grantee;
 33           ELSIF obj.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
 34           THEN
 35              l_str :=
 36                    ' grant execute on '
 37                 || par_owner
 38                 || '.'
 39                 || obj.object_name
 40                 || ' to '
 41                 || par_grantee;
 42           END IF;
 43
 44           DBMS_OUTPUT.put_line (l_str);
 45
 46           EXECUTE IMMEDIATE l_str;
 47        END LOOP;
 48     END IF;
 49  END;
 50  /

Procedure created.

Testing:

SQL> EXEC p_grant('SCOTT', 'MIKE');
grant execute on SCOTT.F_TEST to MIKE
GRANT SELECT ON SCOTT.CALENDAR TO MIKE
grant execute on SCOTT.EXPORTTABLECONTENT to MIKE
<snip>
GRANT SELECT ON SCOTT.SALGRADE TO MIKE
GRANT SELECT ON SCOTT.LINKS TO MIKE

PL/SQL procedure successfully completed.