'ORA-01031: insufficient privileges' error received when inserting into a View

Question

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.

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.

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.

Answer 1

"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.

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.

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).

Answer 2

Here is the requested code:

VIEW: (When the UNION below is commented out, the package runs as expected)

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:

INSERT INTO V_AGREEMENT_NOTIFICATIONS VALUES (5781, 'Collateral Request', 'Send to', 'Contact', 797, '797T', 0, null);

Answer 3

The INSERT into the view fails because you can't insert into DUAL. Not just you, but anybody. Try

INSERT INTO DUAL (DUMMY) VALUES ('1')

to see what happens.

Share and enjoy.