using "alter" privilege still unable to add PK
Rajeshwaran, Jeyabal, September 22, 2020 - 2:14 pm UTC
In addition to the above, i think we need " create any index" privilege should be granted to "u".
with just "alter" privilege granted, we can add a column, but not a asked primary key.
demo@XEPDB1> create user a identified by a;
User created.
demo@XEPDB1> grant create session to a;
Grant succeeded.
demo@XEPDB1> drop table t purge;
Table dropped.
demo@XEPDB1> create table t as select * from scott.emp;
Table created.
demo@XEPDB1> grant select,insert,update,alter on t to a;
Grant succeeded.
demo@XEPDB1> conn a/a@pdb1
Connected.
a@XEPDB1> alter table demo.t add constraint t_pk primary key( empno );
alter table demo.t add constraint t_pk primary key( empno )
*
ERROR at line 1:
ORA-01031: insufficient privileges
a@XEPDB1> alter table demo.t add c2 int;
Table altered.
a@XEPDB1> alter table demo.t add constraint t_pk primary key( empno )
2 using index ( create unique index demo.t_pk on demo.t(empno) );
alter table demo.t add constraint t_pk primary key( empno )
*
ERROR at line 1:
ORA-01031: insufficient privileges
a@XEPDB1> conn demo/demo@pdb1
Connected.
demo@XEPDB1> grant create any index to a;
Grant succeeded.
demo@XEPDB1> conn a/a@pdb1
Connected.
a@XEPDB1> alter table demo.t add constraint t_pk primary key( empno )
2 using index ( create unique index demo.t_pk on demo.t(empno) );
Table altered.
a@XEPDB1>
September 23, 2020 - 12:27 pm UTC
Good point
grant privileges
A reader, September 23, 2020 - 2:28 am UTC
Thank you for your response.