ORA-01031: insufficient privileges when inserting via dblink

9k views Asked by At

This works:

(connect to some_db)
INSERT INTO some_schema.some_table(id) VALUES (some_schema.some_table.nextval);

THis works too:

(connect to some_other_db)
SELECT some_schema.some_table.nextval@some_db FROM DUAL;

This does not work:

(connect to some_other_db)
INSERT INTO some_schema.some_table@some_db(id) VALUES (some_schema.some_table.nextval@some_db);

The error I get is ORA-01031: insufficient privileges ORA-02063: preceding line from SOME_DB. The insert privilege for some_schema.some_table is granted through a default role.

What could be the problem here?

Update: adding select right for the table made the remote query work.

1

There are 1 answers

0
steve On BEST ANSWER

If you insert in the local database the insert privilege is sufficient, if you insert into the remote database, you need select and the inset privilege as it will do a select during the parse phase.