Least privileges required for enable/disable constrains

Today I received such interesting question shown in the subject, since we don't want to give to schema owner password to vendor even in performance test environment.

 I quick answer is :

First, I need to  grant "alter any table " to the new account. This works fine for constraint_type='C' (checking), but not for 'P' (primary key).

After few testing, my answer to 'P' is "drop any index" and "create any index" for disable and enable primary key type constraint respectively.



BTW, for disable/enable triggers, it is "alter any trigger".


v_mig@TESTA> conn / as  sysdba
Connected.
SYS@TESTA> grant drop any index to v_mig;

Grant succeeded.

SYS@TESTA> grant createj any index to v_mig;

SYS@TESTA> conn owner1
Connected.


owner1@TESTA> create table t1 ( c1 number)  ;

Table created.

owner1@TESTA> alter table t1  add constraint t1_pk primary key(c1);

Table altered.

owner1@TESTA> conn / as sysdba
Connected.
SYS@TESTA>  grant create any index to v_mig;

Grant succeeded.

SYS@TESTA> conn v_mig
Connected.

v_mig@TESTA> alter table owner1.t1 disable constraint t1_pk;

Table altered.


v_mig@TESTA> select * from dba_indexes where table_name='T1';

no rows selected

v_mig@TESTA> alter table owner1.t1 enable constraint t1_pk;

Table altered.


v_mig@TESTA> select constraint_name, status from dba_constraints where table_name='T1';

CONSTRAINT_NAME                STATUS
------------------------------ --------
T1_PK                          ENABLED

v_mig@TESTA> conn / as sysdba
Connected.
SYS@TESTA> revoke create any index from v_mig;

Revoke succeeded.

SYS@TESTA> conn v_mig
Connected.
v_mig@TESTA> alter table owner1.t1 disable constraint t1_pk;

Table altered.

v_mig@TESTA> alter table owner1.t1 enable constraint t1_pk;
alter table owner1.t1 enable constraint t1_pk
*
ERROR at line 1:
ORA-01031: insufficient privileges


v_mig@TESTA> conn / as  sysdba
Connected.



SYS@TESTA> grant create any index to v_mig;

Grant succeeded.

SYS@TESTA> revoke alter any index from v_mig;

Revoke succeeded.

SYS@TESTA> conn v_mig
Connected.
v_mig@TESTA> alter table owner1.t1 disable constraint t1_pk;

Table altered.

v_mig@TESTA> alter table owner1.t1 enable constraint t1_pk;

Table altered.