0

Im trying to create a trainer/trainee environment in Oracle 18c XE. So far I have 4 trainee users which all have the hr-sample installed. Now, I plan on having one trainer user that is able see everything the users are doing in real time and can adjust if need be.

This topic seems to be discussed often but I have not seen one solution where the grantee can see newly created objects after a script like this has been run:

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 TEST2';
    ELSIF t.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      EXECUTE IMMEDIATE 'GRANT EXECUTE ON TEST1.'||t.object_name||' TO TEST2';
    END IF;
  END LOOP;
END;

There has to be a way of giving another user select and create privileges on all objects including the ones that will be written in the future. Of course there is the possibility of writing a trigger but this just seems way too complicated for such an easy requirement.

Is it possible to grant a user the equivalence of ownership over a another users schema?

Improve this question

1 Answer 1

Reset to default
1

There is no direct way to do what you are describing, which is privilege by assertion on specific schemas. The closest thing you could do would be to grant the trainer proxy privileges to log on as the trainee to see their objects, or to grant a series of "ANY" system privileges (select any table, alter any table, insert any table, etc.) to the trainer that would allow them to see everything in the database. I wrote a brief blog on proxy authentication here: https://pmdba.wordpress.com/2014/09/05/shared-application-accounts-and-developers/. You can also find information on it here: https://oracle-base.com/articles/misc/proxy-users-and-connect-through.

Improve this answer
2
  • Your blogpost was very interesting, thank you for that! Is it best practice in every big company to just use proxy-users or hand over the login for a certain schema, when a not a schema owner user tries to access the schema and/or change/add/delete objects in it?
    – SweetPotato
    Commented Aug 25, 2020 at 7:38
  • As a security best practice, use of proxy users would be preferred to sharing the actual schema credentials or granting otherwise unnecessary elevated privileges. It allows auditing to track the actual user making changes, and pretty much puts an end to unauthorized password sharing. It's also easier for the user, since they only have to remember their personal password, and not be responsible for multiple other credentials. That said, security may not be the most important consideration if your database is used strictly for training scenarios.
    – pmdba
    Commented Aug 25, 2020 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.