英文:
How is the Definer Rights program unit obtaining ALTER SESSION Privilege?
问题
以下是翻译好的部分：
"以下是来自Oracle 19c数据库PL/SQL语言参考文档PL/SQL Subprograms 8.14的摘录（已强调）。
“在服务器调用期间，当DR [DEFINER RIGHT]单元被推送到调用堆栈时，数据库会存储当前启用的角色以及CURRENT_USER和CURRENT_SCHEMA的当前值。然后，它将CURRENT_USER和CURRENT_SCHEMA都更改为DR单元的所有者，并仅启用角色PUBLIC。”
“PUBLIC”没有被授予“ALTER SESSION”系统权限。
用户“A”没有直接被授予“ALTER SESSION”系统权限，但通过一个角色被授予“ALTER SESSION”权限。
创建或替换过程alter_session
如下所示：
create or replace procedure alter_session
as
begin
execute immediate 'alter session force parallel ddl';
end;
/
SQL*Plus版本为19.0.0.0.0 - Production，日期为2023年5月13日。
尽管用户“A”的角色未被使用于定义者权限的PL/SQL程序单元“alter_session()”的执行中，但用户“A”如何通过对“alter_session()”的调用来更改会话呢？
英文:
The following is an excerpt from the Oracle 19c Database PL/SQL Language Reference documentation PL/SQL Subprograms 8.14 (emphasis added).
> "During a server call, when a DR [DEFINER RIGHT] unit is pushed onto
> the call stack, the database stores the currently enabled roles and
> the current values of CURRENT_USER and CURRENT_SCHEMA. It then changes
> both CURRENT_USER and CURRENT_SCHEMA to the owner of the DR unit, and
> enables only the role PUBLIC."
select *
from dba_sys_privs
where grantee in ('A', 'PUBLIC')
and privilege = 'ALTER SESSION'
/
-- no rows returned
PUBLIC is not granted ALTER SESSION system privilege.
User A is not granted ALTER SESSION system privilege directly, but is granted ALTER SESSION via a role.
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
Even though user A's roles are not used by the definer right PL/SQL program unit's execution (alter_session()), how did user A manage to alter her session via the call to alter_session()?
答案1
得分: 3
以下是已翻译的内容：
每个用户仅通过存在，就在开箱即用时隐式具有某些 ALTER SESSION 能力，其中包括启用并行 DML、更改会话参数等等。但是有一些 ALTER SESSION 选项受到限制，比如启用 SQL 跟踪或设置事件（因为这些可能对服务器产生影响，比如生成填满磁盘的跟踪文件等等）。这些需要显式授予 ALTER SESSION 特权。
已验证：
SQL> create user junk identified by junk;
用户已创建。
SQL> grant create session to junk;
授权成功。
SQL> connect junk/junk;
已连接。
SQL> alter session enable parallel dml;
会话已更改。
SQL> alter session set workarea_size_policy='manual';
会话已更改。
SQL> alter session set sql_trace=true;
错误：
ORA-01031：权限不足
SQL> alter session set events '10046 trace name context forever, level 12';
错误：
ORA-01031：权限不足
英文:
Every user simply by existing has implicitly certain ALTER SESSION capabilities right out of the box, among them enabling parallel dml, changing a session parameter, etc.. But there are a few ALTER SESSION options that are restricted, like enabling SQL tracing or setting events (because these can have impacts on the server, like generating trace files that fill up disk, etc...). Those require the explicit ALTER SESSION privilege be granted.
Proven:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论