What permissions are required to perform DML through an Oracle database chain

For local Users, insert permission is required only for INSERT permission, which is not sufficient for insert operations through the database chain.

Looking at Oracle's admin manual is a problem that has not been noticed before.

To see a specific example, first create a regular user locally and authorize the insert, update, and delete permissions for table T to the User:

Sql> Show User

USER is "TEST"

Sql> CREATE TABLE T (ID number);

Table created.

Sql> create user U1 identified by U1;

User created.

Sql> Grant create session to U1;

Grant succeeded.

Sql> grant INSERT, UPDATE, delete on T to U1;

Grant succeeded.

Sql> Conn U1/U1

Connected.

sql> INSERT INTO test.t values (1);

1 row created.

sql> update test.t Set id = 2;

1 row updated.

sql> Delete test.t;

1 row deleted.

Sql> commit;

Commit complete.

Locally executed, user U1 can perform insert, update, and delete operations on the T table.

Below establishes the database chain at the far end, uses U1 as the connection user:

Sql> select * from Global_name;

Global_name

--------------------------------------------------------------------------------

Testrac

Sql> CREATE DATABASE link test08

2 Connect to U1

3 identified by U1

4 using ' 172.25.13.229/test08 ';

The database link has been created.

Sql> select * from global_name@test08;

Global_name

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45464.htm

--------------------------------------------------------------------------------

TEST08

sql> INSERT INTO test.t@test08 values (1);

INSERT into test.t@test08 values (1)

*

Line 1th Error:

Ora-01031:insufficient Privileges

ORA-02063: Immediately following line (from TEST08)

sql> update test.t@test08 Set id = 1;

Update test.t@test08 Set id = 1

*

Line 1th Error:

Ora-01031:insufficient Privileges

ORA-02063: Immediately following line (from TEST08)

sql> Delete test.t@test08;

Delete test.t@test08

*

Line 1th Error:

Ora-01031:insufficient Privileges

ORA-02063: Immediately following line (from TEST08)

This is because when accessing a remote object through a database chain, Oracle needs query permissions to analyze the table structure, so if the DML operation is performed through the database chain, the user will need SELECT permission in addition to the corresponding DML permissions.

Sql> Conn Test/test

Connected.

Sql> Grant Select on T to U1;

Grant succeeded.

With SELECT permissions, DML execution through the database chain can be performed successfully:

sql> INSERT INTO test.t@test08 values (1);

1 lines have been created.

sql> update test.t@test08 Set id = 2;

1 rows have been updated.

sql> Delete test.t@test08;

1 rows have been deleted.

Sql> commit;

Submit completed.

Sql> select * from test.t@test08;

No rows selected