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.
Basically, I wouldn't complain if I can give TARGETUSER the ability for all non-ddl activity with SOURCE_USER's objects. How do I do this?
You can write a simple procedure to do 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;
Not sure exactly what else you're asking for. You can modify the above to add additional grants and/or object_types for the privileges you want to provide to targetuser. As @stili implies, you can do a lot with roles, but be careful - some permissions do not work when granted via roles.