Create two users, a user (or schema) which represents an application (tq84_usr_app) and another user (tq84_usr_prx) which will eventually become a proxy user for the application schema:
define db=localhost:1533/freepdb1
connect sys/iAmTheDBA@&db as sysdba
create user tq84_usr_app
identified by pw
;
create user tq84_usr_prx
identified by pw
;
grant create session,
create procedure
to
tq84_usr_app;
grant create session
to
tq84_usr_prx;
The following alter user statements makes tq84_usr_prx a proxy user for tq84_usr_app:
alter user tq84_usr_app grant connect through tq84_usr_prx;
The application user creates an (admittedly simple) application: the PL/SQL package pkg whose only procedure is who_am_i:
connect tq84_usr_app/pw@&db
create or replace package tq84_usr_app.pkg
authid definer
as
procedure who_am_i;
end pkg;
/
create or replace package body tq84_usr_app.pkg
as
procedure who_am_i is
begin
dbms_output.put_line('current user = ' || sys_context('userenv', 'current_user'));
dbms_output.put_line('proxy user = ' || sys_context('userenv', 'proxy_user' ));
end who_am_i;
end pkg;
/
Now, the proxy user connects to tq84_usr_app using his own credentials:
connect tq84_usr_prx[tq84_usr_app]/pw@&db
The proxy user has now all privileges that tq84_usr_app has and thus can execute the package that belongs to tq84_usr_app (note especially that there is no execute grant on the package to tq84_usr_prx):
set serveroutput on
begin
pkg.who_am_i;
end;
/
The procedure prints:
current user = TQ84_USR_APP
proxy user = TQ84_USR_PRX
Cleaning up:
connect sys/iAmTheDBA@&db as sysdba
drop user if exists tq84_usr_app cascade;
drop user if exists tq84_usr_prx cascade;