ORA-01031 Insufficient privileges

When i looked it up this is what oracle suggests:

01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
//         without the appropriate privilege. This error also occurs if
//         attempting to install a database without the necessary operating
//         system privileges.
//         When Trusted Oracle is configure in DBMS MAC, this error may occur
//         if the user was granted the necessary privilege at a higher label
//         than the current login.
// *Action: Ask the database administrator to perform the operation or grant
//          the required privileges.
//          For Trusted Oracle users getting this error although granted the
//          the appropriate privilege at a higher label, ask the database
//          administrator to regrant the privilege at the appropriate label.

However, this didn't solve my problem.   I gave the user the following privileges and still didn't work:

grant select any table to <user>;

grant drop any table to <user>;

grant create any table to <user>;

After doing this, still the user was having issues when logging in through his application via odbc connection.

After many tries, and research I granted all to the tables he was using :

grant all on <schema>.<table_name> to <user>;

and then also did the following:

grant references on <schema>.<table_name> to <user>;

That did the trick.  I hope this helps you out as well.

If that didn't work my last resort was to grant dba to the user.   Which I didn't want to do because that was just way too much privilege for him.

Thanks,

Execute Immediate within a stored procedure keeps giving insufficient priviliges error

So I have this procedure that i am trying to execute.  When i run it, it keeps giving me insufficient privileges error message:  ORA-01031 during the run.   Here is the procedure:

CREATE or replace PROCEDURE goldid.GID_REBUILD_UNUSABLE_IDXS
AS
    idx_name varchar2(100);
    idx_cnt integer;
begin
    select count(*) into idx_cnt from user_indexes where status = 'UNUSABLE';

    if idx_cnt > 0 then
         select index_name into idx_name from user_indexes where status = 'UNUSABLE' and rownum <= 1;
    else
        idx_name := null;
    end if;

    while idx_name is not null
    loop
        execute immediate 'alter index ' || idx_name || ' rebuild online parallel';
        select index_name into idx_name from user_indexes where status = 'UNUSABLE' and rownum <= 1;
    end loop;
end;
go

For some reason, I keep getting insufficient privileges error for the EXECUTE IMMEDIATE command. I found out that the insufficient privileges error usually means the oracle user account does not have privileges for the command used in the query that is passes, which in this case is alter index.

I granted the user alter any index directly to he user without going through the role, yet it still didn't work.

Oracle needs to know explicitly somewhere in the procedure what kind of privileges to use. The way to let Oracle know that is to use AUTHID keyword in the CREATE OR REPLACE statement. If you want the same level of privileges as the creator of the procedure, you use AUTHID DEFINER. If you want Oracle to use the privileges of the user currently running the stored procedure, you want to use AUTHID CURRENT_USER.  Here is the updated procedure and it works great now.

CREATE or replace PROCEDURE goldid.GID_REBUILD_UNUSABLE_IDXS
AUTHID CURRENT_USER IS
begin
declare
    idx_name varchar2(100);
    idx_cnt integer;
begin
    select count(*) into idx_cnt from user_indexes where status = 'UNUSABLE';

    if idx_cnt > 0 then
         select index_name into idx_name from user_indexes where status = 'UNUSABLE' and rownum <= 1;
    else
        idx_name := null;
    end if;

    while idx_name is not null
    loop
        execute immediate 'alter index ' || idx_name || ' rebuild online parallel';
        select index_name into idx_name from user_indexes where status = 'UNUSABLE' and rownum <= 1;
    end loop;
end;
end;
/

Hope this helps you guys.