Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ora-01031 into stored procedure

Posted on 2011-10-28
5
Medium Priority
?
620 Views
Last Modified: 2012-08-14
Hi,
I need to create and replace a trigger into stored procedure.
owner=TOM
If I create a trigger stand alone It run correctly but If I try to create a trigger in this stored procedure:

I get error:
ORA-01031: insufficient privileges

Have you any idea?
CREATE OR REPLACE PROCEDURE TOM.my_proc
IS
   err_num   NUMBER;
   err_msg   VARCHAR2 (300);

   CURSOR c1
   IS
     SELECT a.name as name
        FROM SAM.tab1 a, TOM.tab2 b
       WHERE a.id = b.id;
BEGIN

EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER "TOM"."LS_T" 
BEFORE INSERT ON LS FOR EACH ROW
BEGIN 
SELECT LPAD(TO_CHAR(AFM_LS_S.NEXTVAL), 9,''0'')
INTO :NEW.LS_ID FROM DUAL; 
END;';
   

   FOR c1_rec IN c1
   LOOP
      
     INSERT INTO TOM.ls
                  (name
                  )
           VALUES (c1_rec.name
                  );
                  
                  
       COMMIT;
   END LOOP;
   
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER "TOM"."LS_T" 
BEFORE INSERT ON LS FOR EACH ROW
WHEN (
NEW.gvc_numero_verbale is null
      )
BEGIN 
SELECT LPAD(TO_CHAR(AFM_LS_S.NEXTVAL), 9, ''0'')
INTO :NEW.LS_ID FROM DUAL; 
END;';
   
   
EXCEPTION
   WHEN OTHERS
   THEN
      err_msg := SUBSTR (SQLERRM, 1, 300);
      err_num := SQLCODE;

      INSERT INTO proc_errors
                  (proc_name, err_code, err_msg, err_date
                  )
           VALUES ('my_proc', err_num, err_msg, SYSDATE
                  );
END my_proc;

Open in new window

0
Comment
Question by:ralph_rea
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Muhammad Ahmad Imran
ID: 37044591
Grant the required privilege on stored procedure
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37044605
I also believe you need to explicitly grant create any trigger to the user and not grant it through a role.
0
 

Author Comment

by:ralph_rea
ID: 37044614
What difference between grant create trigger explicitly to user and not explicitly?
0
 

Author Comment

by:ralph_rea
ID: 37044620
TOM can create a stored procedure!
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37044631
Permissions with stored procedures behave differently.  Procedures run in a 'system' context (not the system user).

Permissions granted through ROLEs are not carried over when executing procedures but will allow the user to execute commands from a tool like sqlplus.

There are several papers out there on this if you look around.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question