DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-01031: insufficient privileges

  1. #1
    Join Date
    Oct 2002
    Location
    Boston
    Posts
    7

    ORA-01031: insufficient privileges

    I have wirtten a dynamic sql proceduer, the script is given below.

    CREATE OR REPLACE PROCEDURE DELETE_FT_T_IRST_ISST_REC_TST(monyr varchar2)
    is
    irst_bfr_cnt number(10);
    irst_afr_cnt number(10);
    isst_bfr_cnt number(10);
    isst_afr_cnt number(10);
    cur integer;
    ret integer;
    cur1 integer;
    ret1 integer;
    st1 char(4000);
    begin
    /********
    * Delete from FT_T_IRST where ENDS_TMS is not null;
    ************/
    cur := dbms_sql.open_cursor;
    st1:='create table FT_T_IRST_BKUP'||monyr||' as select * from FT_T_IRST where rownum<51';
    EXECUTE IMMEDIATE st1;
    -- dbms_output.put_line('Backup table FT_T_ISST_BKUP'||monyr||' is created');
    select count(*) into irst_bfr_cnt from FT_T_ISST_BKUP1030;
    dbms_output.put_line('Total number of records before delete in FT_T_IRST : '
    ||irst_bfr_cnt);
    --dbms_sql.parse(cur,'Truncate table FT_T_IRST',dbms_sql.v7);
    --ret := dbms_sql.execute(cur);
    st1:='Truncate table FT_T_ISST_BKUP1030';
    EXECUTE IMMEDIATE st1;
    --dbms_sql.parse(cur,'insert into FT_T_IRST select * from FT_T_IRST'||monyr||' where END_TMS is not null',dbms_sql.v7);
    ----ret := dbms_sql.execute(cur);
    st1:='insert into FT_T_ISST_BKUP1030 select * from FT_T_ISST_BKUP1029 where END_TMS is null and rownum<20';
    EXECUTE IMMEDIATE st1;
    select count(*) into irst_Afr_cnt from FT_T_ISST_BKUP1030;
    dbms_output.put_line('Total number of records AFTER delete in FT_T_IRST : '
    ||irst_afr_cnt);
    commit;
    end DELETE_FT_T_IRST_ISST_REC_TST;
    /


    when I execute I get errors listed below.

    SQL> exec DELETE_FT_T_IRST_ISST_REC_TST('1000');
    BEGIN DELETE_FT_T_IRST_ISST_REC_TST('1000'); END;

    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "FDR.DELETE_FT_T_IRST_ISST_REC_TST", line 18
    ORA-06512: at line 1

    I can create a table with the same userid who creates the above procedure.
    Help me if anybody know what privilages the user nees to execute the above procedure.

    Regards
    Easwaran

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    For executing procedures you need explicit direct execute privileges on the procedure. For tables, you can have privileges thru roles.

    I guess thats why you can access the table and you can't execute the procedure. Ask the user to give EXEC privileges on procedure using the following command

    grant execute on proc_name to XYZ;
    Reddy,Sam

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    You are trying to create a table dynamically in your procedure using EXECUTE IMMEDIATE.

    Grant user 'CREATE ANY TABLE' previlege externally and not through role. Then it should work..

    May be you have to think about additional privileges as you are also doing TRUNCATE table using EXEC IMME... I am not sure but you might need DELETE ANY TABLE (But make sure before granting this priovilege)

    Sameer

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    In stored procedures, roles are disabled. Hence any privilege granted by a role (DBA), will not be in
    effect. The privilege needed must be explicitly granted to the user

    Thats all I can think for the errors you are getting...
    Reddy,Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width