0

In my DB I have: USER_1 with default temp ts TEMP_1; USER_2 with default temp ts TEMP_2.

USER_1 issues "alter session set current_schema = USER_2". After that the USER_1's queries in that session began consuming TEMP_2.

I want to forbid USER_1 to consume TEMP_2 in any way. Please could anybody explain how can I do that or why it cannot be done?

The "ALTER SESSION" sys priv is not sufficient in my case as per https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-SESSION.html#GUID-8DBA8659-413E-49B4-98D3-D9608C9C8026

3
  • StackOverflow is for programming questions; this is a database configuration issue and is off-topic for StackOverflow. Instead you should ask it at dba.stackexchange.com Commented May 16, 2024 at 13:56
  • You can't change the user's temporary tablespace at session level, so if you were able to prevent them using their default, they would have no temporary space at all - which would stop them doing a lot of things and pretty me be unusable... Is changing current_schema really dong that though? I don't have a way to test it at the moment, but the docs say "This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session." So I'm surprised. Commented May 16, 2024 at 14:10
  • @MTO I've written programs with these commands before, so this question doesn't seem off topic to me. Just because a programming question involves configuration does not mean the question must be sent to dba.stackexchange.com. There's inevitable overlap between database developers and administrators, and when a question falls in in the middle, I think it's best to keep the question where it started. Commented May 16, 2024 at 22:32

1 Answer 1

2

The temporary tablespace is determined by CURRENT_SCHEMA, not by the current/authenticated user. Typically CURRENT_SCHEMA is overridden by stored procedure calls which set it to the owner of the procedure, but as you've discovered, you can also set it manually for ad-hoc SQL.

Tested with both GTTs which are owned non-PLSQL objects, and with temporary LOBs:

Run as MYOWNER:

Test #1:

create global temporary table myowner.gtest (col1 integer) on commit preserve rows;  
/
alter session set current_schema=MYUSER1;
/
declare
  var_tablespace varchar2(128);
begin
  insert into myowner.gtest values (1);
  
  SELECT MAX(tablespace)
    INTO var_tablespace
    FROM v$sort_usage
   WHERE session_addr = (SELECT saddr FROM v$session WHERE sid = SYS_CONTEXT('USERENV','SID'));
   
  dbms_output.put_line(var_tablespace);
end;

Test #2:

alter session set current_schema=MYUSER1;
/
declare
  my_lob clob;
  var_tablespace varchar2(128);
begin
  DBMS_LOB.CREATETEMPORARY (my_lob,false);
  
  SELECT MAX(tablespace)
    INTO var_tablespace
    FROM v$sort_usage
   WHERE session_addr = (SELECT saddr FROM v$session WHERE sid = SYS_CONTEXT('USERENV','SID'));
   
  dbms_output.put_line(var_tablespace);
end;

In both cases, the temporary tablespace reported as that assigned to MYUSER1, not that assigned to MYOWNER (which happens to be different).

It is true that CURRENT_SCHEMA does not change your permissions, but using temporary space in one tablespace or the other isn't a protected privileged action. All users have the right to use temp space, without quota, and no user owns a temporary tablespace, so there's no question of privileges involved. While the DBAs can do basic resource balancing by creating and assigning different temp tablespaces to different users, it can be overridden and so should not be treated as a guaranteed assignment.

So, if you don't want your user using a particular temporary tablespace, don't set CURRENT_SCHEMA to a schema assigned to that tablespace, nor call any stored procedures owned by such a schema. If you are trying to prevent some other user from doing so, since this is an always-permitted ALTER SESSION setting that doesn't require the ALTER SESSION privilege, you cannot block it. You'd think you could implement a DDL system trigger to intercept it and raise an exception denying it, but DDL triggers do not fire for ALTER SESSION because unlike all other ALTERs this one does not modify the data dictionary.

About the only mechanism I can think of that would prevent the wrong user from filling up the wrong temp tablespace is to have a scheduled monitoring script (e.g. once a minute) examine v$sort_usage, detect the undesired usage, and kill the session or cancel the SQL.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.