• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2977
  • Last Modified:

ORA-01031: insufficient privileges

Hi

We are trying to insert into remote table using dblink and it gives us following error

ORA-01031: insufficient privileges
ORA-02063: preceding line from DEV3

The owner of the table and db_link authentication is same.

It works when we try to insert using same owner. But when I try to insert using different user I am getting error.

below is the insert statement inside a procedure.

Insert into DRM.USER_REPLICATION@DEV3
   (USERNAME, NEW_PASSWORD, DT_CREATED, SOURCE_HOSTNAME, DEST_HOSTNAME, ACTION, PROCESSED)
 Values
   ('PM', 'A37241F579C2D44D', TO_DATE('11/13/2008 10:53:26', 'MM/DD/YYYY HH24:MI:SS'), 'xin', 'dev3', 'Password Change', 'Y');

All user will change password and user procedure to insert data into the table. I dont understand why I am getting this error and what privileges I need to give to all user to insert data from remote database

Thanks
0
basirana
Asked:
basirana
  • 11
  • 3
1 Solution
 
gyansCommented:
Does that user have the privs to insert into the other table in the remote database .
Can you check the privelege .
0
 
suredazzleCommented:
I hope below helps....

USE DEV3;
GRANT REFERENCES (column, col2, col3....) ON OBJECT::User_object
    TO Table_Name WITH GRANT OPTION;
GO
0
 
suredazzleCommented:


Table permission...

USE Dev3;
GRANT SELECT ON OBJECT::user_object.table_name TO column_name, col2, col3;
GO
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
suredazzleCommented:

You need to look up DBA_TAB_PRIVS to see if granted to public.


SELECT * FROM DBA_TAB_PRIVS WHERE table_name = 'USER_REPLICATION' and owner = 'DRM';
0
 
SujithData ArchitectCommented:
I suspect that the link DEV3 is created with a user other than DRM.

Can you post the results of the following query.

select * from dba_db_links
where db_link = 'DEV3';

Also, you mentioned that
>> It works when we try to insert using same owner.

What do you mean by that?
0
 
suredazzleCommented:
I am not a DBA. Based on what I know is from User_Tables.

Select * from dba_tab_privs;  -> table does not exist.

Let me create one....
0
 
suredazzleCommented:
basirana,

Try this to see if it works....

Grant update( (USERNAME, NEW_PASSWORD, DT_CREATED, SOURCE_HOSTNAME, DEST_HOSTNAME, ACTION, PROCESSED)
on User_replication
To DRM;
0
 
suredazzleCommented:

Okay, I have the grant table....

Let me see next......
0
 
suredazzleCommented:


oops...privs table.
0
 
SujithData ArchitectCommented:
Can you post the results of the above post. ID:22956761
0
 
suredazzleCommented:

Sujith80, I don't have dba_db_links, this table

table doesn't exist
0
 
SujithData ArchitectCommented:
suredazzle:
the question was for basirana.

@basirana
you may use all_db_links if you dont have privs on the dba_db_links
0
 
suredazzleCommented:

Can you show on your end?

I did the other half....
0
 
suredazzleCommented:

Please show what is the query...I can help your solution.

Missing file?
0
 
suredazzleCommented:


In order for DB to have permission privilege. The owner is private, so only that person has the permission. The DB is private, so the link has to be public for the system to work.

That way, Joe Smith works under John King.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 11
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now