oracle 编译更新另一个用户表的包的权限不足
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15374312/
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
Insufficient privileges compiling a package that updates another users tables
提问by Lock
I have an object in USER A that updates tables in USER B's schema. When the package is run, it is run in Oracle using USER B's login.
我在 USER A 中有一个对象，用于更新 USER B 模式中的表。当包运行时，它使用用户 B 的登录名在 Oracle 中运行。
USER A does not have privileges to update the tables in USER B's schema. When I try to compile the package in USER A's schema, I get insufficient privileges error messages which stops it from compiling.
用户 A 没有更新用户 B 模式中的表的权限。当我尝试在 USER A 的架构中编译包时，我收到了权限不足的错误消息，这会阻止它进行编译。
Is there a way to compile an object in USER A's schema, even though he doesn't have the privileges to update USER B's tables? If the package is run under the context of USER B, will the package correctly update the tables?
有没有办法在 USER A 的模式中编译对象，即使他没有更新 USER B 表的权限？如果程序包在 USER B 的上下文中运行，程序包是否会正确更新表？
I don't want to put the package in USER B's schema.
我不想将包放在 USER B 的架构中。
回答by Justin Cave
You can. But it's probably not the best approach. The package would need to be declared as an invoker's rights package. And the UPDATEstatement would need to use dynamic SQL.
你可以。但这可能不是最好的方法。该包需要声明为调用者的权限包。并且该UPDATE语句需要使用动态 SQL。
Create the table in B
在中创建表 B
SQL> create table b.foo( col1 number );
Table created.
SQL> insert into b.foo values( 1 );
1 row created.
SQL> commit;
Commit complete.
Create the package in A. Note that the package is declared authid current_userwhich means that it relies on the privileges of the invoking user rather than the defining user. And since Acannot see the table, we use dynamic SQL so that the syntax checks are deferred to runtime
在A. 请注意，包是声明的authid current_user，这意味着它依赖于调用用户的权限而不是定义用户的权限。由于A看不到表，我们使用动态 SQL，以便将语法检查推迟到运行时
SQL> create package update_foo
2 authid current_user
3 as
4 procedure set_val( p_new_val in number );
5 end;
6 /
Package created.
SQL> ed
Wrote file afiedt.buf
1 create or replace package body update_foo
2 as
3 procedure set_val( p_new_val in number )
4 as
5 begin
6 execute immediate 'update b.foo set col1 = :new_val'
7 using p_new_val;
8 end;
9* end;
SQL> /
Package body created.
SQL> grant execute on update_foo to b;
Grant succeeded.
Now, Bcan execute the package and modify the data
现在，B可以执行包并修改数据
SQL> exec a.update_foo.set_val( 2 );
PL/SQL procedure successfully completed.
SQL> select * from foo;
COL1
----------
2
In general, however, this isn't a particularly sensible approach. In general, the whole point of having code in one schema and objects in another is to provide for a separation of duties and a separation of responsibilities. If you have to be logged in as a user that can issue an UPDATEagainst the table anyway, having a package to do it doesn't add much security. In this case, it's just adding complexity.
然而，总的来说，这并不是一种特别明智的方法。一般来说，在一个模式中拥有代码而在另一个模式中拥有对象的全部意义在于提供职责分离和责任分离。如果您必须以UPDATE无论如何都可以针对该表发出 an 的用户身份登录，那么使用包来执行它并不会增加太多安全性。在这种情况下，它只是增加了复杂性。
