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

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Duong.

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

Answered by: Chris Saxon - Last updated: September 23, 2020 - 12:27 pm UTC

Category: SQL - Version: 12c 1

Viewed 100+ times

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 we 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 

and you rated our response

  (2 ratings)

Reviews

using "alter" privilege still unable to add PK

September 22, 2020 - 2:14 pm UTC

Reviewer: Rajeshwaran, Jeyabal

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

Followup  

September 23, 2020 - 12:27 pm UTC

Good point

grant privileges

September 23, 2020 - 2:28 am UTC

Reviewer: A reader

Thank you for your response.

More to Explore

PL/SQL

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