以另一个用户身份执行 Oracle 存储过程
我主要是一个 oracle 新手，所以如果这是一个愚蠢的问题，请原谅我......
I'm mostly an oracle novice, so forgive me if this is a stupid question...
我有一个名为CODE"的架构，其中包含一个执行任意 SQL 的存储过程(现在，请忽略与此相关的潜在安全问题).传入的SQL会选择数据；但所有数据都驻留在架构 A、B 或 C 中 - 但 SQL 一次只会从一个架构中进行选择.
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.
例如:类型 A 的用户创建一个字符串 'SELECT * FROM A.USERTABLE' - 而类型 B 的用户创建一个字符串 'SELECT * FROM B.USERTABLE'.
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'.
我试图做的是允许用户不明确指定他们的架构.在前端.net应用中；我已经知道它们是 A 型、B 型还是 C 型.我希望所有三个都简单地输入SELECT * FROM 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'.
我遇到的问题是我不知道该怎么做.我的应用程序只能在CODE"架构中执行 proc - 所以我不能只是复制代码并让用户 A 调用A.ExecuteSQL".
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'.
我尝试了一些东西；但到目前为止没有任何效果.我希望 ExecuteSQL 过程保持在 CODE 模式中；但是当USERTABLE"被传入时，我需要它知道有时这意味着 A.USERNAME 有时意味着 B.USERNAME.
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.
有什么建议吗?
推荐答案
使用:
ALTER SESSION SET CURRENT_SCHEMA = schema
这就是 等效于 SQL Server 的 EXECUTE AS 语法.
That is the equivalent to SQL Server's EXECUTE AS syntax.
相关文章