Hi,
We use oracle 19c database that supports an enterprise application. There is 1 main schema owner for the application, 100+ tables, and hundreds of database objects: functions, procedures, packages and table triggers all owned by the 1 main schema. Lets call the oracle user/schema “MYAPP”
Our application tables all have columns: created_by (varchar2), created_date (date), modified_by (varchar2), modified_date(date). These columns are populated via table triggers using USER, SYSDATE.
:new.created_by := user;
:new.created_date := sysdate;
Our application users login to the application using their own oracle accounts. Thus if a user creates or modifies a record in a table, our use of “USER” in the table trigger will record the user that made the DML change.
Table DML is controlled through database (application) roles, and users are granted the necessary roles to complete their tasks.
We also have a database packages that can perform DML on several database tables. Execute on the package is controlled through the same database roles. Though a role, a user could be granted: select, insert and update on a table. They are also granted execute on a database package that can also select, insert and update on the same table.
So if a user say “USERA” calls the package which then results in DML on a table, lets say the result is an insert of record in a table, then created_by value of that record will be “USERA” :new.created_by := user;
What we want to now happen for 1 database package only: If the same user calls the package which results in the insert of a record in the table, then we want the created_by value of that record to be “MYAPP” The schema owner. But we still want to use the same trigger logic of :new.created_by := user;
So from Oracle point of view, I want to run the package as user “MYAPP” even though it was called by user: “USERA”
I can control the process that invokes the database package, if there is a way in the package call to change how the package runs.
Does anyone know if this can be done? I know there is there is AUTHID CURRENT_USER | DEFINER type of controls, but they change the target schema and this is not what we want to happen.
I found comments around use of a proxy but no specific instructions on how to implement this.
Thanks
Dave