如何禁止USER_1切换current_schema后使用TEMP_2临时表空间？

针对你遇到的USER_1切换current_schema后占用TEMP_2的问题，以下是两种可行的实现方案，同时解释为何仅靠ALTER SESSION权限无法解决：

方案一：通过数据库资源管理器（DBRM）锁定可用临时表空间 #

数据库资源管理器可以对用户的临时表空间使用做强制限制，不管用户如何切换会话的current_schema，都会被限定在指定的临时表空间内。

具体操作步骤：

1. 创建专属消费组

   BEGIN
     DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
       CONSUMER_GROUP_NAME => 'USER1_TEMP_RESTRICT',
       COMMENT => 'Limit USER_1 to TEMP_1 only'
     );
   END;
   /
   

2. 创建资源计划并添加临时表空间限制规则

   BEGIN
     DBMS_RESOURCE_MANAGER.CREATE_PLAN(
       PLAN_NAME => 'USER1_TEMP_PLAN',
       COMMENT => 'Enforce TEMP_1 usage for USER_1'
     );
     -- 给USER1的消费组指定只能用TEMP_1
     DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
       PLAN_NAME => 'USER1_TEMP_PLAN',
       GROUP_OR_SUBPLAN => 'USER1_TEMP_RESTRICT',
       COMMENT => 'Restrict to TEMP_1',
       TEMP_TABLESPACE => 'TEMP_1'
     );
     -- 保留其他用户的默认资源规则
     DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
       PLAN_NAME => 'USER1_TEMP_PLAN',
       GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
       COMMENT => 'Default resource allocation',
       CPU_MTH => 'ROUND_ROBIN',
       CPU_P1 => 100
     );
   END;
   /
   

3. 将USER_1映射到该消费组

   BEGIN
     DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
       VALUE => 'USER_1',
       CONSUMER_GROUP => 'USER1_TEMP_RESTRICT'
     );
   END;
   /
   

4. 启用资源计划

   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'USER1_TEMP_PLAN';
   

配置完成后，USER_1的所有会话都会被强制使用TEMP_1，无法占用TEMP_2。

方案二：用数据库触发器强制锁定临时表空间 #

通过触发器在用户登录时自动设置临时表空间，并阻止用户修改到TEMP_2。

1. 登录时自动设置TEMP_1

   CREATE OR REPLACE TRIGGER USER1_FORCE_TEMP1
   AFTER LOGON ON USER_1.SCHEMA
   BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET TEMPORARY_TABLESPACE = TEMP_1';
   END;
   /
   

2. 阻止USER_1修改临时表空间到TEMP_2

   CREATE OR REPLACE TRIGGER BLOCK_USER1_TEMP2
   BEFORE ALTER SESSION ON DATABASE
   WHEN (USER = 'USER_1' AND UPPER(ORA_SQL_TXT) LIKE '%TEMPORARY_TABLESPACE%TEMP_2%')
   BEGIN
     RAISE_APPLICATION_ERROR(-20001, 'You are not permitted to use TEMP_2 tablespace');
   END;
   /
   

这个方案可以确保USER_1的会话始终绑定TEMP_1，任何尝试切换到TEMP_2的操作都会直接报错。

为何仅ALTER SESSION权限无法满足需求 #

ALTER SESSION是一个宽泛的系统权限，它覆盖了多个会话参数的修改（比如NLS设置、排序规则、临时表空间等），无法单独剥离“修改临时表空间”这一操作权限。如果直接收回USER_1的ALTER SESSION权限，可能会影响该用户其他正常的会话配置需求，因此必须使用上述更细粒度的控制方案。

内容的提问来源于stack exchange，提问作者WareNick