This discussion is archived
4 Replies Latest reply: Dec 19, 2010 11:03 AM by Solomon Yakobson RSS

Error execute procedure "ORA-01031: insufficient privileges"

825167 Newbie
Currently Being Moderated
Hi everybody,

I've built a procedure to audit:

create or replace procedure audit_login(user in varchar2, flag in number)
as
begin

if flag = 1 then
execute immediate 'AUDIT CONNECT BY ' || user ||' WHENEVER SUCCESSFUL';
elsif flag = 2 then
execute immediate 'AUDIT CONNECT BY ' || user ||' WHENEVER NOT SUCCESSFUL';
elsif flag = 3 then
execute immediate 'AUDIT CONNECT BY ' || user;
end if;

end;

But, when i execute it:

begin
audit_login('scott', 1);
end;

I get an error:

begin
*

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.AUDIT_LOGIN", line 6
ORA-06512: at line 3

In addition, I've login by SYSTEM account. The command: audit connect by scott whenever successful; => Executed successfully! I tried to replace the first execute in the procedure:

create or replace procedure audit_login(user in varchar2, flag in number)
as
begin

if flag = 1 then
execute immediate 'audit connect by scott whenever successful';
elsif flag = 2 then
execute immediate 'AUDIT CONNECT BY ' || user ||' WHENEVER NOT SUCCESSFUL';
elsif flag = 3 then
execute immediate 'AUDIT CONNECT BY ' || user;
end if;

end;

But still error!
  • 1. Re: Error execute procedure "ORA-01031: insufficient privileges"
    761190 Journeyer
    Currently Being Moderated
    with sys user
    GRANT ANY OBJECT PRIVILEGE TO your_user


    then try again

    Edited by: Adigozalov Gurban on Dec 19, 2010 11:33 PM
  • 2. Re: Error execute procedure "ORA-01031: insufficient privileges"
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    Roles don't count in AUTHID DEFINER stored procedures (which is the default kind). That explains why a statement that works when you issue it outside of a stored procedure works, but gets an "insufficient privileges" error when you do it in a stored procedure.

    Either
    (1) make the procedure AUTHID CURRENT_USER, like this
    create or replace procedure audit_login
    (     in_user     in     varchar2     -- See note below
    ,      flag      in      number
    )
    AUTHID      CURRENT_USER               -- Added
    as
        ...
    which will affect who can run the procedure, or
    (2) grant the necessary privileges (in this case, I think that's the AUDIT SYSTEM system privilege) directly to the procedure owner, not to some role that the owner has.

    USER is the name of a built-in function, so you shouldn't use that as a parameter or variable name. Why not call the parameter p_user or in_user instead of user?

    Are you saying that SYSTEM is the owner of this procedure?
    It's usually not a good idea to create your own objects (including stored procedures) in the SYSTEM schema. Create a separate user, that has all the necessary privileges, and create procedures like this one in that schema, rather than SYSTEM. Save the SYSTEM schema for objects supplied by Oracle.

    Edited by: Frank Kulash on Dec 19, 2010 2:03 PM
  • 3. Re: Error execute procedure "ORA-01031: insufficient privileges"
    varun4dba Journeyer
    Currently Being Moderated
    if flag = 1 then
    execute immediate 'AUDIT CONNECT BY ' || user ||' WHENEVER SUCCESSFUL';
    elsif flag = 2 then
    execute immediate 'AUDIT CONNECT BY ' || user ||' WHENEVER NOT SUCCESSFUL';
    elsif flag = 3 then
    execute immediate 'AUDIT CONNECT BY ' || user;
    end if;

    end;



    please post full program.

    thhanks.
  • 4. Re: Error execute procedure "ORA-01031: insufficient privileges"
    Solomon Yakobson Guru
    Currently Being Moderated
    Adigozalov Gurban wrote:
    with sys user
    GRANT ANY OBJECT PRIVILEGE TO your_user
    GRANT ANY PRIVILEGE TO you_user
    Did you try it?

    And what is GRANT ANY OBJECT PRIVILEGE TO your_user? Anyway, SP with definer rights ignore roles, therefore user SYSTEM must be directly (not via role) granted AUDIT SYSTEM:
    SQL> connect system
    Enter password: *****
    Connected.
    SQL> create or replace procedure audit_login(user in varchar2, flag in number)
      2  as
      3  begin
      4  
      5  if flag = 1 then
      6  execute immediate 'audit connect by scott whenever successful';
      7  elsif flag = 2 then
      8  execute immediate 'AUDIT CONNECT BY ' || user ||' WHENEVER NOT SUCCESSFUL';
      9  elsif flag = 3 then
     10  execute immediate 'AUDIT CONNECT BY ' || user;
     11  end if;
     12  
     13  end;
     14  
     15  /
    
    Procedure created.
    
    SQL> exec audit_login('A',1);
    BEGIN audit_login('A',1); END;
    
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "SYSTEM.AUDIT_LOGIN", line 6
    ORA-06512: at line 1
    
    
    SQL> connect scott as sysdba
    Enter password: *****
    Connected.
    SQL> grant audit system to system;
    
    Grant succeeded.
    
    SQL> connect system
    Enter password: *****
    Connected.
    SQL> exec audit_login('A',1);
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points