求助:存储过程中建同义词报错:ORA-01031: insufficient privileges

xhjbeidoulong 2006-04-29 09:05:25
我直接在PL/SQL developer下创建同义词,是可以成功的,
而在存储过程中,却报:ORA-01031: insufficient privileges。

CREATE OR REPLACE PROCEDURE P_Create_Synonym_On_MainDB(
v_I_ObjName IN VARCHAR2, --对象名称
v_I_ObjSrcDBName IN VARCHAR2, --对象来源数据库名
v_O_errorCode OUT INT, --结果:0成功,非0失败
v_O_errorMsg OUT VARCHAR2
)
AS
v_sqlstr VARCHAR2(256);
v_linkName VARCHAR2(50);

BEGIN

select DB_LINK into v_linkName from ALL_DB_LINKS where HOST = UPPER(v_I_ObjSrcDBName);
v_sqlstr := 'CREATE PUBLIC SYNONYM '||v_I_ObjName||' For '||v_I_ObjName||'@'||v_linkName;

execute immediate v_sqlstr;
v_O_errorCode := 0;
return;

EXCEPTION
WHEN OTHERS THEN
v_O_errorCode := SQLCODE;
v_O_errorMsg := SQLERRM;

end P_Create_Synonym_On_MainDB;
...全文
1839 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
xhjbeidoulong 2006-04-30
  • 打赏
  • 举报
回复
解决方法是从以下文字得到的:

The matter of the problem could be an authentization method of the dynamic sql within PL/SQL package/procedure.

You can make either definer or current_user authentization:

CURRENT_USER indicates that the methods of the class execute with the privileges
of CURRENT_USER. This clause is the default and creates an invoker-rights class.
This clause also specifies that external names in queries, DML operations, and
dynamic SQL statements resolve in the schema of CURRENT_USER. External names
in all other statements resolve in the schema in which the methods reside.

DEFINER indicates that the methods of the class execute with the privileges of the
owner of the schema in which the class resides, and that external names resolve in
the schema where the class resides. This clause creates a definer-rights class.

example:

CREATE OR REPLACE PROCEDURE drop_it (kind IN VARCHAR2, name IN
VARCHAR2)
AUTHID CURRENT_USER
AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;
/

My note: The default Definer authid doesn't work correctly in any case. It's recommended to specify it explicitelly.
xhjbeidoulong 2006-04-30
  • 打赏
  • 举报
回复
已经找到答案了,
好象是权限问题。
在存储过程名后加上:AUTHID CURRENT_USER就可以了,即如下:


CREATE OR REPLACE PROCEDURE P_Create_Synonym_On_MainDB(
v_I_ObjName IN VARCHAR2, --对象名称
v_I_ObjSrcDBName IN VARCHAR2, --对象来源数据库名
v_O_errorCode OUT INT, --结果:0成功,非0失败
v_O_errorMsg OUT VARCHAR2
)
AUTHID CURRENT_USER
AS
v_sqlstr VARCHAR2(256);
v_linkName VARCHAR2(50);

BEGIN


select DB_LINK into v_linkName from ALL_DB_LINKS where HOST = UPPER(v_I_ObjSrcDBName);
v_sqlstr := 'CREATE PUBLIC SYNONYM '||v_I_ObjName||' For '||v_I_ObjName||'@'||v_linkName;

execute immediate v_sqlstr;
v_O_errorCode := 0;
return;

EXCEPTION
WHEN OTHERS THEN
v_O_errorCode := SQLCODE;
v_O_errorMsg := SQLERRM;

end P_Create_Synonym_On_MainDB;

xhjbeidoulong 2006-04-30
  • 打赏
  • 举报
回复
可以确定同义词不存在。

存储过程是根据调用者的数据库名查出对应的数据连,然后创建同义词。
我跟踪的语句如下:
CREATE PUBLIC SYNONYM test_test For test_test@FX_TO_MX1_LINK
痞子酷 2006-04-30
  • 打赏
  • 举报
回复
如果db_link存在,改为 alter
或者先DROP 再create
痞子酷 2006-04-30
  • 打赏
  • 举报
回复
$ oerr ora 1031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate privilege. This error also occurs if
// attempting to install a database without the necessary operating
// system privileges.
// When Trusted Oracle is configure in DBMS MAC, this error may occur
// if the user was granted the necessary privilege at a higher label
// than the current login.
// *Action: Ask the database administrator to perform the operation or grant
// the required privileges.
// For Trusted Oracle users getting this error although granted the
// the appropriate privilege at a higher label, ask the database
// administrator to regrant the privilege at the appropriate label.
xhjbeidoulong 2006-04-29
  • 打赏
  • 举报
回复
申明:我将跟踪v_sqlstr得到的建同义词语句在PL/SQL developer下执行成功!

我登录的用户为DBA权限。

请大虾帮我解围!
先谢谢了!!!

1,178

社区成员

发帖
与我相关
我的任务
社区描述
C++ Builder 数据库及相关技术
社区管理员
  • 数据库及相关技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧