Oracle授予执行权限 [英] Oracle grant permission for execute

问题描述

我在创建视图的过程中有一个动态sql.如果我打印字符串并手动执行它，则可以创建视图.但是，在该过程中，由于特权不足而引发异常.我已将过程的全部执行，创建，授予用户的权限，但是仍然无法正常工作.有什么建议吗?

I've a dynamic sql inside a procedure which creates a view. If I print the string and execute it manually, I'm able to create the view. However in the procedure an exception is thrown as insufficient privileges. I've granted execute,create,all on the procedure to the user, however it still does not work. Any suggestions?

推荐答案

在内部包中，将删除通过角色间接授予的特权.您必须直接对帐户授予必要的特权 ；例如:

Inside packages, privileges granted indirectly (via a role) are dropped. You must grant the necessary privileges on the underlying objects to the account directly; example:

conn sys/sys@DB as sysdba
create user A identified by A;
grant connect, dba to A;
conn A/A@DB
create table test_tab(pk number);

conn sys/sys@DB as sysdba
create user B identified by B;
grant connect, dba to B;
conn B/B@DB
select * from A.test_tab; -- this works

create or replace procedure do_it as
l_cnt pls_integer;
begin
  select count(*) into l_cnt from A.test_tab; -- error on compile
end;

在此示例中，您需要一个

In this example, you need a

grant select on A.test_tab to B;

使其有效(使用动态SQL还是静态SQL都无关紧要.)

to make it work (it doesn't matter whether you're using dynamic or static SQL).

这篇关于Oracle授予执行权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！