As a system user I have this error: ORA-01031: insufficient privileges

Question

I have a stored procedure for deleting partitions. Before starting, I have to delete a constraint.

I installed the stored procedure on system user. When I test the procedure I have this error: 'ORA-01031: insufficient privileges'.

This is a piece of code that I wrote:

BEGIN

    EXECUTE IMMEDIATE 'ALTER TABLE USER_NAME.TABLE_NAME DISABLE CONSTRAINT CONSTRAINT_NAME';

EXCEPTION
    WHEN OTHERS THEN

        O_sCodError := 'NO_OK';
        O_sDesError := 'Error at DISABLE CONSTRAINT_NAME: ' || SQLERRM || ';';

        RETURN;

END;

Well, as I execute the stored procedure as system, I do not understand the reason for I have that error. And I think I eventually think the same error when I try to delete a partition.

Answer 1

Works for me on 11g XE:

SQL> show user
USER is "SCOTT"
SQL>
SQL> create table test
  2    (id     number   constraint pk_test primary key,
  3     name   varchar2(20)
  4    );

Table created.

SQL> connect system
Enter password:
Connected.
SQL> begin
  2    execute immediate 'alter table scott.test disable constraint pk_test';
  3    return;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL>

Please, follow that example and execute it in your database. Post the result (by editing the question, not as a comment).

Answer 2

First, you should never install custom code in an Oracle default schema like SYSTEM. Put your code in a dedicated application schema. Since it contains dynamic SQL (execute immediate) you might want to consider making it an "invoker's rights" procedure, then granting execute privileges on it to the user that will execute it.

That said, in Oracle 11g whoever's privileges are used to run the PL/SQL block must have direct permissions on the underlying table, not inherited permissions through a role like DBA. If the procedure has "definer's rights" then the schema that owns the procedure must have direct privileges on the table. If "invoker's rights" then the user executing the procedure must have the privileges.

See this link for additional details: Execute immediate within Oracle Procedure

Answer 3

You must grant SYSTEM account direct privilege (not with a role) to run ALTER TABLE on the target table because roles are not enabled in stored procedures by default: https://asktom.oracle.com/Misc/RolesAndProcedures.html.

Try:

grant alter any table to system;

or

grant alter table on user_name.table_name to system;