oracle 定义者权限程序单元如何获得ALTER SESSION特权?

cyvaqqii  于 2023-05-16  发布在  Oracle
关注(0)|答案(1)|浏览(132)

下面是Oracle 19 c Database PL/SQL Language Reference文档PL/SQL Subprograms 8.14(着重号是后加的)的摘录。
“在服务器调用期间,当DR [DEFINER RIGHT]单元被推送到调用堆栈时,数据库存储当前启用的角色以及CURRENT_USER和CURRENT_SCHEMA的当前值。然后,它将CURRENT_USER和CURRENT_SCHEMA都更改为DR单元的所有者,并仅启用***PUBLIC角色。”

select * 
  from dba_sys_privs 
 where     grantee   in ('A', 'PUBLIC') 
       and privilege  = 'ALTER SESSION'
/
-- no rows returned

PUBLIC未被授予ALTER SESSION系统权限。
用户A没有直接被授予ALTER SESSION系统权限,而是通过角色被授予ALTER SESSION

create or replace procedure alter_session
  as
  begin
    execute immediate 'alter session force parallel ddl';
  end;
/
SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 13 20:09:02 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat May 13 2023 20:08:48 -04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> alter session enable parallel ddl
  2  /

Session altered.

SQL> select pddl_status from v$session where username = 'A'
  2  /

PDDL_STA
--------
ENABLED

SQL> begin
  2    alter_session();
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select pddl_status from v$session where username = 'A'
  2  /

PDDL_STA
--------
FORCED

即使用户A的角色没有被定义者正确的PL/SQL程序单元的执行(alter_session())使用,用户A是如何通过调用alter_session()来改变她的会话的?

mkshixfv

mkshixfv1#

每一个用户只要存在,就可以直接拥有某些ALTER SESSION功能,其中包括启用并行dml、更改会话参数等。但是有一些ALTER SESSION选项是受限制的,比如启用SQL跟踪或设置事件(因为这些可能会对服务器产生影响,比如生成填满磁盘的跟踪文件等)。这些需要授予显式的ALTER SESSION权限。
经证实:

SQL> create user junk identified by junk;

User created.

SQL> grant create session to junk;

Grant succeeded.

SQL> connect junk/junk;
Connected.
SQL> alter session enable parallel dml;

Session altered.

SQL> alter session set workarea_size_policy='manual';

Session altered.

SQL> alter session set sql_trace=true;
ERROR:
ORA-01031: insufficient privileges

SQL> alter session set events '10046 trace name context forever, level 12';
ERROR:
ORA-01031: insufficient privileges

相关问题