以另一个用户身份执行 Oracle 存储过程 [英] Executing an Oracle Stored Proc as Another User

问题描述

我主要是一个 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.

这篇关于以另一个用户身份执行 Oracle 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！