Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: insufficient insufficient privilege when running procedure

    Hi,
    I call a procedure which has the following code:

    begin
    DBMS_OUTPUT.PUT_LINE('I am here');
    maRequete := 'grant CONNECT,RESOURCE,CAS_CLT_PAYEUR , CAS_ADMIN, CAS_ADMIN_CONS to CAS' ;
    DBMS_OUTPUT.PUT_LINE(maRequete);
    monCursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(monCursor, maRequete, DBMS_SQL.V7);
    ret := DBMS_SQL.execute(monCursor);
    DBMS_SQL.close_cursor(monCursor);
    exception
    when others then
    CAS_report_dev.traite_erreur(1002, 'Erreur creation du user <BR>' ||maRequete||' : '||SQLERRM||'<BR>');
    DBMS_OUTPUT.PUT_LINE('Erreur creation du user <BR>' ||maRequete||' : '||SQLERRM||'<BR>');

    I logged in as user CAS and execute this procedure but Oracle display the error ORA-01031: insufficient privileges. Although I can run 'grant CONNECT,RESOURCE,CAS_CLT_PAYEUR , CAS_ADMIN, CAS_ADMIN_CONS to CAS' without error because user CAS has all neccessary privileges including grant any role why couldn't I run this inside a procedure?
    Can any one explain ?

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Have you issued the 'set serveroutput on size 100000' command before executing the procedure?

    If yes, does it show the 'I am here' message before the ORA-01031 ?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    user CAS has all neccessary privileges including grant any role why couldn't I run this inside a procedure?
    Can any one explain ?


    Privileges acquired via a role are NOT used when inside a PL/SQL procedure.

    SQL> GRANT ANY PRIVILEGE TO CAS;

  4. #4
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    user needs to own the table to grant privileges on that table to another user.

    Even the dba role granted to you is not sufficient.
    SATHISH .

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    A new system privilege GRANT ANY OBJECT PRIVILEGE (GAOP) allows users to grant and revoke privileges on objects on behalf of the object's owner.
    SATHISH .

  6. #6
    Join Date
    Jan 2004
    Posts
    2
    Thank you very much for your reply.
    I use GRANT ANY PRIVILEGE TO CAS and it work.
    Thanks again

Posting Permissions

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