Grant read access to Oracle procs and functions


We have a consultant working with us. He needs to be able to see all of the code for our Stored Procs and functions.

We would like to avoid granting Execute permissions.

Is there a way to grant a permission where he could be able to view all the code w/o having the power to Execute the proc?

There are hundreds of procs/functions he will need access to.

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

grant select on dba_source

if he needs to be able to see the structures of tables, triggers, users, roles, etc

then  grant select any dictionary
or grant select_catalog_role

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

Open in new window

jvoconnellAuthor Commented:
Hello experts,

We have limited DBA-type authority here. We can do a limited amount of functions.
However, I cannot do any of your suggestions.

I will have to open a Help-Desk ticket to the DBA group. I try to avoid that for time saving purposes. But I will have to do so in this case.

Could you two kindly give me your thoughts on how to award point in this case? I've used posted answers from MikeOM_DBA in the past and ststuber has bailed me out more times that I can count.

I more that trust both of your input and I don't want to offend anyone
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!

award points to whichever one you use.

if you found both posts helpful even though you only implemented one, then split if you want.
I'd weight the split heavier on the one you actually used though, but that's not mandatory.
Geert GOracle dbaCommented:
> the DBA group. I try to avoid that
why ?

explain your problem to them. they should be more than willing to help.
after all, you are both working to achieve the same goal, no ?
jvoconnellAuthor Commented:
hello, I didn't mean to offend any DBA out there. I try to avoid asking them if it's something we can do ourselves. Sometimes we find we can, other times we can't based on the permissions they give us. They are extremely shorthanded, and thre becomes a backlog to getting things processed at the moment. My comment was not meant as an insult.
Geert GOracle dbaCommented:
it wasn't offending, it was odd they arent working with you to solve this
they should be as it's their responsibility... well... in most cases
jvoconnellAuthor Commented:
Just an update....
I'm still waiting on assistance from our DBA group. A request was put in with them on 10/1. Once I know what method they used to solve the issue I will award the points and close out the quesiton.
jvoconnellAuthor Commented:
Thank you for your solutions. I split up the points. Both suggestions got me to where I wanted to be. I was able to use the "grant debug...." option on our Dev server. The DBA's ended up using the "grant select any dictionary"  on the Prod server when they responded to the help-desk ticket. I weighted the points that way base on what was used in the production environment.  Thanks once again!!
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.