Solved

Oracle - Stored Procedure Privilge access

Posted on 2016-11-20
3
81 Views
Last Modified: 2016-11-21
So I created a stored procedure to insert/update some other tables.  After creating the stored procedure and compiling it, I am getting error that I do not have access.  However, I am able to update/insert to those tables with just the query before making the SP.  Is there anything special that needs to be done to allow the SP to update those tables?
0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41895278
the stored procedure needs to be owned by the same schema that owns the tables

or, that schema needs to be granted insert/update privileges on those tables directly (i.e. NOT through a role)


another option, probably not what you want...

that procedure could be recompiled with "authid current_user" option, but using this route generally complicates things.
0
 

Author Comment

by:holemania
ID: 41896194
Thank you.  That make sense.  I will try that and update later.
0
 

Author Closing Comment

by:holemania
ID: 41896527
Thank you.  That was it.
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

705 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