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
basiranaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.