Skip to Main Content
  • Questions

  • Grant privileges to add constraints on a table to other users

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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)

We're not taking comments currently, so please try again later if you want to add 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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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