以另一个用户身份执行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"的架构，该架构具有一个存储的proc，可以执行任意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 A.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 proc保留在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语法.

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