Skip to content

Instantly share code, notes, and snippets.

@riccardomerolla
Created January 8, 2014 17:06
Show Gist options
  • Save riccardomerolla/8320310 to your computer and use it in GitHub Desktop.
Save riccardomerolla/8320310 to your computer and use it in GitHub Desktop.
Store procedure to add sourceuser grant to targetuser
BEGIN
FOR Rec IN (SELECT object_name, object_type FROM all_objects WHERE owner='SOURCEUSER' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
IF Rec.object_type IN ('TABLE','VIEW') THEN
EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON SOURCEUSER.'||Rec.object_name||' TO TARGETUSER';
ELSIF Rec.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
EXECUTE IMMEDIATE 'GRANT EXECUTE ON SOURCEUSER.'||Rec.object_name||' TO TARGETUSER';
END IF;
END LOOP;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment