Choosing Between a Sandbox Schema and a Restricted Role for Safe PL/SQL Testing in SQL Developer

Answer

For safe PL/SQL integration testing in SQL Developer, a dedicated sandbox schema provides more consistent privilege enforcement when proxy users are involved.

Confirmed facts

• Oracle roles are session‑scoped and are ignored by definer's‑rights PL/SQL; code must have direct object grants to compile and run.
• Proxy authentication changes SESSION_USER and PROXY_USER attributes, and tools that inspect privileges may report on either identity inconsistently.

Likely explanation (unverified)

The SQL Developer 21.2+ Test Connection dialog showing full privileges despite a role limit is probably a client‑side artifact: the dialog queries the proxy user's effective privileges rather than the role‑restricted end‑user session, so the database is still enforcing the role but the UI misrepresents it.

Steps for this case

1. Create a separate schema (e.g., TEST_SANDBOX) that will hold a copy of the test data.
2. Grant the test user only the direct object privileges needed for the PL/SQL units under test (no roles).
3. Refresh the sandbox schema on a schedule using Data Pump (expdp/impdp) or a clone operation to keep it current with production.
4. In SQL Developer, create a connection that uses the test user’s credentials directly (avoid proxy authentication for the test run).
5. Run the PL/SQL integration tests; verify isolation by attempting a prohibited DML/DDL and expecting ORA‑01031.
6. If a proxy user must be used, after connecting run:

   SELECT * FROM session_privs;
   SELECT * FROM session_roles;
   SELECT USER, SYS_CONTEXT('USERENV','SESSION_USER'), SYS_CONTEXT('USERENV','PROXY_USER') FROM dual;

   to confirm what the database actually enforces, independent of the Test Connection dialog.

Missing diagnostic detail

Please confirm whether your testing workflow can avoid proxy authentication altogether; if proxy use is unavoidable, the session‑privs check above is required to validate enforcement.