Solved

Oracle - Stored Procedure Privilge access

Posted on 2016-11-20
3
22 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
  • 2
3 Comments
 
LVL 73

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now