SQL 以另一个用户身份执行 Oracle 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2376664/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Executing an Oracle Stored Proc as Another User
提问by Rob P.
I'm mostly an oracle novice, so forgive me if this is a stupid question...
我主要是一个oracle新手，所以如果这是一个愚蠢的问题，请原谅我......
I have a schema called 'CODE' with a stored proc that executes arbitrary SQL (for now, please ignore the potential security issues associated with that). The SQL that is passed in will select data; but all of the data resides in either schema A, B, or C - but the SQL will only ever select from ONE schema at a time.
我有一个名为“CODE”的模式，其中包含一个执行任意 SQL 的存储过程（现在，请忽略与此相关的潜在安全问题）。传入的SQL会选择数据；但是所有数据都驻留在模式 A、B 或 C 中——但 SQL 一次只会从一个模式中进行选择。
For example: User of type A creates a string 'SELECT * FROM A.USERTABLE' - while user of type B creates a string 'SELECT * FROM B.USERTABLE'.
例如：类型 A 的用户创建一个字符串 'SELECT * FROM A.USERTABLE' - 而类型 B 的用户创建一个字符串 'SELECT * FROM B.USERTABLE'。
What I'm trying to do is allow the user to not explicitly specify their schema. In the front-end .net application; I already know if they are type A, B, or C. I want all three to simply enter 'SELECT * FROM USERTABLE'.
我想要做的是允许用户不明确指定他们的架构。在前端.net应用中；我已经知道它们是 A 型、B 型还是 C 型。我希望所有三个都简单地输入“SELECT * FROM USERTABLE”。
The problem I'm having is that I don't know how to do that. My app can only execute proc in the 'CODE' schema - so I can't just duplicate the code and let user A call 'A.ExecuteSQL'.
我遇到的问题是我不知道该怎么做。我的应用程序只能在“CODE”模式中执行 proc - 所以我不能只是复制代码并让用户 A 调用“A.ExecuteSQL”。
I've tried a few things; but nothing has worked thus far. I want the ExecuteSQL proc to stay in the CODE schema; but when 'USERTABLE' gets passed in, I need it to know that sometimes that means A.USERNAME and sometimes B.USERNAME.
我尝试了一些事情；但到目前为止没有任何效果。我希望 ExecuteSQL 过程保持在 CODE 模式中；但是当'USERTABLE'被传入时，我需要它知道有时这意味着A.USERNAME，有时意味着B.USERNAME。
Any suggestions?
有什么建议？
回答by OMG Ponies
Use:
用：
ALTER SESSION SET CURRENT_SCHEMA = schema
That is the equivalent to SQL Server's EXECUTE ASsyntax.
回答by JulesLt
Another option would be using the AUTHID CURRENT_USER pragma.
另一种选择是使用 AUTHID CURRENT_USER 编译指示。
If you add these two keywords immediately after your package, procedure, function or type name, it will execute with the privileges of the executing user, rather than the CODE schema. This overrides the default behaviour which is AUTHID DEFINER (the privileges of the schema/user that compiled the code)
如果在包、过程、函数或类型名称后立即添加这两个关键字，它将以执行用户的权限执行，而不是 CODE 模式。这会覆盖默认行为，即 AUTHID DEFINER（编译代码的架构/用户的权限）
i.e.
IE
CREATE FUNCTION examplefunc
(pSqlStatement IN VARCHAR2)
RETURN INTEGER
AUTHID CURRENT_USER
AS
lResult INTEGER;
BEGIN
EXECUTE IMMEDIATE pSqlStatement INTO lResult;
RETURN lResult;
END examplefunc;
Note that for functions and procedures insider a package, the pragma can only be applied at the package level. You cannot set the rights on a per function basis.
请注意，对于包内部的函数和过程，编译指示只能应用于包级别。您不能在每个功能的基础上设置权限。
This should cause any SQL inside the function, package, etc, to execute with the users privileges.
这应该会导致函数、包等中的任何 SQL 以用户权限执行。
I've used that to manage a similar 'run any old bit of SQL dynamically' routine - at the very least you will have stopped a 'normal' user from being able to use your stored procedure to drop a table or install additional code in the CODE schema.
我已经用它来管理类似的“动态运行任何旧的 SQL”例程 - 至少你会阻止“普通”用户使用你的存储过程来删除表或安装额外的代码CODE 模式。
(It may also be worth - if you haven't already - adding some validation to throw out certain keywords - i.e. must start with SELECT, must not contain embedded pl/sql blocks - whatever you can get away with without breaking existing code).
（这也可能是值得的 - 如果您还没有 - 添加一些验证以丢弃某些关键字 - 即必须以 SELECT 开头，不得包含嵌入的 pl/sql 块 - 任何你可以在不破坏现有代码的情况下逃脱）。
