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

Question

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?

Answer 1

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.

Answer 2

To generate SQL script you could use the following, similar to the solution suggested by DCookie

SELECT 'GRANT SELECT, UPDATE, DELETE, INSERT ON ' || table_name || ' TO other_user;'
      FROM all_tables WHERE owner = 'other_user';
UNION ALL
SELECT 'GRANT EXECUTE ON ' || object_name || ' TO other_user;'
    FROM all_objects 
    WHERE owner = 'other_user' 
    AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE');

Generally, I would suggest using roles to avoid granting access rights for each user.

If using roles, run the following SQL as user you are copying roles from. You could also include other options like admin_option and default_role.

SELECT 'GRANT ' || granted_role || ' TO other_user;'
  FROM user_role_privs;

Alternatively you could query dba_role_privs to get the roles of a specific user:

SELECT 'GRANT ' || granted_role || ' TO other_user;'
  FROM dba_role_privs WHERE grantee = 'source_user';