Skip to Main Content
  • Questions
  • Grant privileges to add constraints on a table to other users

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Duong.

Asked: September 21, 2020 - 6:00 pm UTC

Last updated: September 23, 2020 - 12:27 pm UTC

Version: 12c 1

Viewed 10K+ times! This question is

You Asked

Hello,

I create a table and grant ALL privileges on it to user B. User B can delete, select, insert, update on the table, but When user B uses ALTER TABLE ... ADD CONSTRAINT...PRIMARY KEY OR UNIQUE, it has a SQL Error: ORA-01031: insufficient privileges. How can I fix this error?

Thanks,
Duong


and Chris said...

You need to grant alter:

grant create session 
  to u
  identified by u;
  
create table t (
  c1 int
);
grant select, insert, update, delete, alter
  on t 
  to u;

conn u/u

alter table chris.t add c2 int;

insert into chris.t values ( 1, 1 );
select * from chris.t;

C1   C2   
    1     1 

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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>


Chris Saxon
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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library