I had the good misfortune of taking over responsibility of the Oracle side of a live solution, with no handover from the original developers. While setting up some new test environments, I noticed that our live solution displays a behavior I cannot explain or replicate.

The solution uses two schemas ("SYS_USER" and "APP_USER"). Services connect using APP_USER, while objects are owned by SYS_USER. Among these objects are a table on which only SELECT is granted to APP_USER. However, the application executes UPDATE statements on that table, and successfully so.

While I am able to resolve the specific usecase by simply granting UPDATE on the table to APP_USER, I feel compelled to understand why this setup supports an operation that according to my understanding should not be allowed.

On another test environment that exhibits the same unexplained behaviour, I implemented a trigger on the table in question that logs CURRENT_SCHEMA, CURRENT_USER and SESSION_USER for any update attempts. I then had the related service calls executed, and consulted the logs to find that:

• SESSION_USER = APP_USER - This is entirely as expected
• CURRENT_SCHEMA = SYS_USER - This puzzled me at first, but I found a logon trigger that runs ALTER SESSION SET CURRENT_SCHEMA = SYS_USER whenever APP_USER logs in.
• CURRENT_USER = SYS_USER - This seems to be the core of the issue. My Google-fu has so far taught me that CURRENT_USER, rather than SESSION_USER is used for evaluating privileges. This explains a lot, but I cannot find any tips on how to change CURRENT_USER independently of SESSION_USER. Also, it seems like the difference between the two are more or less deprecated since 10G (and we're running 19C).

I am looking for any and all tips on possible ways to trigger a discrepancy between SESSION_USER and CURRENT_USER that works on Oracle 19C (AWS-hosted, if that makes a difference). Due to the way we're implementing CI/CD, I have reason to suspect that this is somehow achieved without knowing the password for SYS_USER (I cannot find any traces of the SYS_USER password in any application configuration files, and the test environment I setup uses a new SYS_USER password that is not known by the application - which runs fine, as long as APP_USER has the required privileges).

UPDATE: Reply from Jameson_uk helped me understand the question as listed in Title. However, all the evidence to my avail still paints the same picture that I don't understand:

On environment A, an application is somehow able to execute UPDATE statements on MYTABLE, despite not being granted the UPDATE privilege. I further confirmed this today by implementing this trigger on the table in question:

create or replace trigger sys_user.mytable_updatelogger
  after update on sys_user.mytable
  for each row
declare
  v_update_privs varchar2(1000) = '';
begin
  for r in (select *
              from user_tab_privs
             where privilege = 'UPDATE'
               and grantee = user) loop
    v_update_privs := v_update_privs || r.table_name || '; ';
  end loop;
  insert into debuglog
    (log_msg, log_timestamp)
  values
    ('Update on MYTABLE:
Logged in user: ' || user || '
Assigned UPDATE privileges: ' || v_update_privs, sysdate);
end;

Content logged by this trigger confirms that user = APP_USER, and that UPDATE privilege on MYTABLE is missing - despite the update having taken place. The same UPDATE statement is disallowed when I manually connect to the same database as APP_USER.

On environment B, the same application methods fail unless UPDATE on MYTABLE is granted to APP_USER - after which it succeeds. To me, this rules out the most obvious explanation: update is executed via stored procedure.

I suspect some dirty hacks involved in the application stack and will consult app developers when available. I'm still curious if anyone has any tips on how to reproduce a scenario like the one on Environment A to help me narrow down what I'm looking for.

CodePudding user response：

How are you actually accessing these values? If you are accessing via PL/SQL (eg. your trigger) then you get into the realm of definer vs invoker rights and I believe this is the only way the two might differ.

By default a stored procedure in the SYS_USER schema will inherit the rights of SYS_USER and if you look at CURRENT_USER during execution of this procedure.

create or replace procedure SYS_USER.TESTPRIV
as
begin
  dbms_output.put_line('SYS_CONTEXT', 'CURRENT_USER');
  dbms_output.put_line('SYS_CONTEXT', 'SESSION_USER');
end;
/

then running exec sys_user.testpriv as APP_USER would output

• SYS_USER (the user whose permissions are being used)
• APP_USER (the user who logged on)

I a guessing your trigger was created in SYS_USER schema and hence by default will exhibit the same output for session an current user.

Now I don't think the update being allowed is related to this. I would suggest there are a number of things it could be

• A synonym in the APP_USER schema but as there is a logon trigger to set the current schema this is unlikely
• A system privilege has been granted to APP_USER eg. UPDATE ANY TABLE (check DBA_SYS_PRIVS)
• Via a role (check DBA_ROLE_PRIVS)
• Update is not SQL statement but PL/SQL owned by SYS_USER and will therefore invoke with the procedure owners rights by default

There are probably a few other ways of achieving similar the above are the most likely.