[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORA-01031: insufficient privileges

Posted on 2008-11-13
15
Medium Priority
?
2,759 Views
Last Modified: 2013-12-19
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
Comment
Question by:basirana
  • 11
  • 3
15 Comments
 
LVL 1

Expert Comment

by:gyans
ID: 22954061
Does that user have the privs to insert into the other table in the remote database .
Can you check the privelege .
0
 
LVL 1

Expert Comment

by:suredazzle
ID: 22956077
I hope below helps....

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

Expert Comment

by:suredazzle
ID: 22956123


Table permission...

USE Dev3;
GRANT SELECT ON OBJECT::user_object.table_name TO column_name, col2, col3;
GO
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Expert Comment

by:suredazzle
ID: 22956366

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
 
LVL 27

Accepted Solution

by:
sujith80 earned 2000 total points
ID: 22956761
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
 
LVL 1

Expert Comment

by:suredazzle
ID: 22956946
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
 
LVL 1

Expert Comment

by:suredazzle
ID: 22957018
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
 
LVL 1

Expert Comment

by:suredazzle
ID: 22957048

Okay, I have the grant table....

Let me see next......
0
 
LVL 1

Expert Comment

by:suredazzle
ID: 22957051


oops...privs table.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22957120
Can you post the results of the above post. ID:22956761
0
 
LVL 1

Expert Comment

by:suredazzle
ID: 22957163

Sujith80, I don't have dba_db_links, this table

table doesn't exist
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22957199
suredazzle:
the question was for basirana.

@basirana
you may use all_db_links if you dont have privs on the dba_db_links
0
 
LVL 1

Expert Comment

by:suredazzle
ID: 22957288

Can you show on your end?

I did the other half....
0
 
LVL 1

Expert Comment

by:suredazzle
ID: 22957392

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

Missing file?
0
 
LVL 1

Expert Comment

by:suredazzle
ID: 22957811


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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question