Oracle - How to grant to a user the rights to another user's objects

69
Views
1
Answers

Hi,
I need to give to user TARGETUSER the rights to select/insert/update to all tables of user SOURCEUSER (I can figure this all out from here) and the ability to run all their stored procedures.

edit | flag
William Patrick
Asked on: Oct 10, 2011 at 8:09PM

1 Answers

62
4
62

Write a simple proc like this..

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;

Good Luck!!

edit | flag
Gokul A
Answered on: Oct 10, 2011 at 8:17PM

Post your Answer

Search

Welcome to Ask Amoeba!
This is 100% free and interactive site for sharing professional Questions and Answers, Opensource projects, Interview questions.
Learners, beginners, Experts stop and share your knowledge and ideas!

Browse Categories

Browse Tags