以另一个用户身份执行Oracle存储过程

Question

我基本上是个 Oracle 初学者，如果这个问题很蠢，请原谅我...

我有一个名为 "CODE" 的模式，其中包含一个存储过程，用于执行任意 SQL（暂时忽略与之相关的潜在安全问题）。传入的 SQL 将选择数据，但所有数据都驻留在模式 A、B 或 C 中——但是 SQL 只会从一个模式中选择。

例如：用户类型 A 创建一个字符串“SELECT * FROM A.USERTABLE”，而用户类型 B 创建一个字符串“SELECT * FROM B.USERTABLE”。

我正在尝试做的是允许用户不显式指定其模式。在前端 .net 应用程序中，我已经知道它们是 A、B 还是 C 类型。我希望所有三个类型都只输入“SELECT * FROM USERTABLE”。

我的问题是我不知道该如何做到这一点。我的应用程序只能执行“CODE”模式中的存储过程，因此我不能简单地复制代码并让用户 A 调用“A.ExecuteSQL”。

我尝试了一些方法，但迄今为止没有什么作用。我希望 ExecuteSQL 存储过程保留在 CODE 模式中，但当传入“USERTABLE”时，我需要它知道有时那意味着 A.USERNAME，有时是 B.USERNAME。

有什么建议吗？

- Rob P.

Answer 1

使用：

ALTER SESSION SET CURRENT_SCHEMA = schema

那相当于SQL Server的EXECUTE AS语法。

- OMG Ponies

---

这是如何工作的？首先应用程序执行此ALTER语句还是在过程CODE中执行？ - Guru

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:588045400346317188 这里有更多信息。看起来你可以在存储过程中使用动态SQL（execute immediate）来实现它。 - Rob P.

@Guru：请查看Rob P的AskTom链接。 - OMG Ponies

谢谢，OMG。应该可以工作了。我知道你需要在PL/SQL中使用“EXECUTE IMMEDIATE”。但是我一开始持怀疑态度，担心当它在执行过程中更改到另一个模式时，它会发生什么变化。我需要进行检查。 - Guru

Answer 2

另一个选择是使用 AUTHID CURRENT_USER pragma。

如果在包、过程、函数或类型名称之后立即添加这两个关键字，它将使用执行用户的特权来执行，而不是 CODE 模式。这会覆盖默认行为，即 AUTHID DEFINER（编译代码的模式/用户的权限）。

例如：

CREATE FUNCTION examplefunc
    (pSqlStatement IN VARCHAR2)
RETURN INTEGER
  AUTHID CURRENT_USER
AS 
   lResult INTEGER;
BEGIN
    EXECUTE IMMEDIATE pSqlStatement INTO lResult;
    RETURN lResult;
END examplefunc;

请注意，对于包内的函数和过程，该编译指示仅适用于包级别。您无法对每个函数设置权限。

这应该导致函数、包等中的任何SQL使用用户的特权执行。

我曾经使用它来管理类似的“动态运行任意SQL代码”的例程——至少您已经阻止了普通用户使用您的存储过程来删除表或在CODE模式中安装其他代码。

（如果您尚未这样做）这也可能值得添加一些验证来排除某些关键字——即必须以SELECT开头，不得包含嵌入式pl/sql块——无论您能否避免破坏现有代码。

- JulesLt