oracle 插入视图时收到“ORA-01031：权限不足”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2961036/
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
'ORA-01031: insufficient privileges' error received when inserting into a View
提问by Patrick K
Under the user name 'MY_ADMIN', I have successfully created a table called 'NOTIFICATIONS' and a view called 'V_NOTIFICATIONS'. On the 'V_NOTIFICATIONS' view I have successfully created a trigger and a package that takes what the user attempts to insert into the view and inserts it into the table. The 'V_NOTIFICATIONS' trigger and package also perform the update and delete functions on the table when the user attempts to perform the update and delete functions on the view.
在用户名“MY_ADMIN”下，我成功创建了一个名为“NOTIFICATIONS”的表和一个名为“V_NOTIFICATIONS”的视图。在“V_NOTIFICATIONS”视图上，我成功创建了一个触发器和一个包，该包将用户尝试插入到视图中的内容插入到表中。当用户尝试在视图上执行更新和删除功能时，'V_NOTIFICATIONS' 触发器和包也会在表上执行更新和删除功能。
I have done this with many views in the project I am currently working on, as many views sit over the top of many different tables, however when attempting to insert a record into this view I receive an 'ORA-01031: insufficient privileges' error.
我在我目前正在处理的项目中使用许多视图完成了此操作，因为许多视图位于许多不同表的顶部，但是当尝试将记录插入此视图时，我收到“ORA-01031：权限不足”错误.
I am able to insert directly into the table using the same code that is in the package, but not into the view. Any help on this would be greatly appreciated. Here is the requested code:
我可以使用包中的相同代码直接插入到表中，但不能插入到视图中。对此的任何帮助将不胜感激。这是请求的代码：
VIEW: (When the UNION below is commented out, the package runs as expected)
VIEW：（当下面的UNION被注释掉时，包按预期运行）
CREATE OR REPLACE FORCE VIEW "MY_ADMIN"."V_NOTIFICATIONS" AS
SELECT N_ID,
NOTIFICATION_TYPE,
CASE WHEN NOTIFICATION_DESC = 'C' THEN 'Copy' ELSE 'Send to' END NOTIFICATION_DESC,
CASE WHEN CONTACT_TYPE = 'D' THEN 'Department' ELSE 'Contact' END CONTACT_TYPE,
A.AU_USER_ID,
A.CONTACT_NAME,
D.DEPARTMENT_ID,
D.DEPT_DESC
FROM NOTIFICATIONS AN,
(SELECT A1.AU_USER_ID,
AU.FIRST_NAME || ' ' || AU.LAST_NAME CONTACT_NAME
FROM APP_USERS_CONTACT_INFO A1,
APPLICATION_USERS AU
WHERE A1.AU_USER_ID = AU.USER_ID
/*UNION
SELECT 0,
NULL
FROM DUAL*/) A,
(SELECT DEPARTMENT_ID,
DESCRIPTION DEPT_DESC
FROM DEPARTMENTS
UNION
SELECT 0 DEPARTMENT_ID,
NULL DEPT_DESC
FROM DUAL) D
WHERE NVL(AN.AU_USER_ID,0) = A.AU_USER_ID
AND NVL(AN.D_DEPARTMENT_ID,0) = D.DEPARTMENT_ID;PACKAGE:
包裹：
CREATE OR REPLACE PACKAGE NOTIFICATIONS_PKG AS
PROCEDURE INSERT_NOTIFICATION(P_N_ROW V_NOTIFICATIONS%ROWTYPE);
END NOTIFICATIONS_PKG;
/
CREATE OR REPLACE PACKAGE BODY NOTIFICATIONS_PKG AS
PROCEDURE INSERT_NOTIFICATION(P_N_ROW V_NOTIFICATIONS%ROWTYPE) IS
L_NOTIFICATION_DESC VARCHAR2(1);
L_CONTACT_TYPE VARCHAR2(1);
BEGIN
CASE P_N_ROW.NOTIFICATION_DESC
WHEN 'Copy' THEN
L_NOTIFICATION_DESC := 'C';
ELSE
L_NOTIFICATION_DESC := 'S';
END CASE;
CASE P_N_ROW.CONTACT_TYPE
WHEN 'Department' THEN
L_CONTACT_TYPE := 'D';
ELSE
L_CONTACT_TYPE := 'C';
END CASE;
INSERT INTO NOTIFICATIONS VALUES (
P_N_ROW.N_ID,
P_N_ROW.NOTIFICATION_TYPE,
L_NOTIFICATION_DESC,
L_CONTACT_TYPE,
NVL(P_N_ROW.AU_USER_ID, 0),
NVL(P_N_ROW.DEPARTMENT_ID, 0),
APP_GLOBAL_PKG.GET_AUDIT);
END INSERT_AGREEMENT_NOTIFICATION;
END AGREEMENT_NOTIFICATIONS_PKG;The trigger is setup just to pass information to this package to insert the row. Upon trying to run the following line of code I receive the ORA-01031 error:
触发器的设置只是为了将信息传递给这个包以插入行。在尝试运行以下代码行时，我收到 ORA-01031 错误：
INSERT INTO V_AGREEMENT_NOTIFICATIONS VALUES (5781, 'Collateral Request', 'Send to', 'Contact', 797, '797T', 0, null);回答by Bob Jarvis - Reinstate Monica
The INSERT into the view fails because you can't insert into DUAL. Not just you, but anybody. Try
INSERT 到视图中失败，因为您无法插入到 DUAL 中。不只是你，任何人都一样。尝试
INSERT INTO DUAL (DUMMY) VALUES ('1')
to see what happens.
看看会发生什么。
Share and enjoy.
分享和享受。
回答by Gary Myers
"I am able to insert directly into the table using the same code that is in the package, but not into the view."
“我可以使用包中的相同代码直接插入表中，但不能插入到视图中。”
If you call the package directly (ie not indirectly through a trigger), does it work ?
如果您直接调用包（即不是通过触发器间接调用），它是否有效？
If it doesn't then you can ignore the view/trigger side of things and concentrate on the package. Generally if you can run the SQL directly but not through a package it is because you have a role granted to your use with the necessary privilege. Stored PL/SQL does not have role privileges.
如果没有，那么您可以忽略事物的视图/触发方面并专注于包。通常，如果您可以直接运行 SQL 但不能通过包运行，那是因为您获得了具有必要特权的角色供您使用。存储的 PL/SQL 没有角色权限。
If it the package does work, then probably the user doesn't have insert privilege on the view. It may be something odd like the trigger doesn't have privileges on the package but that would probably be a compilation error unless it used dynamic SQL.
如果包确实有效，那么用户可能没有视图的插入权限。这可能有点奇怪，比如触发器没有包的权限，但这可能是编译错误，除非它使用动态 SQL。
INVOKER rights on the package may also have an effect, since that would mean that it runs with the privileges of the trigger owner rather than the package owner. The trigger owner is probably the owner of the view, but that may be different from the owner of the table(s).
包上的 INVOKER 权限也可能会产生影响，因为这意味着它以触发器所有者的权限运行，而不是包所有者的权限。触发器所有者可能是视图的所有者，但这可能与表的所有者不同。
