Database Administration (MOSC)

MOSC Banner

Grant for all objects

edited May 18, 2020 1:50PM in Database Administration (MOSC) 9 commentsAnswered ✓

Hi,

On 11.2.0.4

How can I garant all on all objects of USER1 to USER2?

I ran the following (as test1 or as system) but  I receive ORA-00942 table not found:

BEGIN

  FOR t IN (SELECT object_name, object_type FROM all_objects WHERE owner='TEST1' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP

    IF t.object_type IN ('TABLE','VIEW') THEN

      EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON SOURCEUSER.'||t.object_name||' TO SUPPORT';

    ELSIF t.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN

      EXECUTE IMMEDIATE 'GRANT EXECUTE ON TEST1.'||t.object_name||' TO SUPPORT';

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center