Solved

Permissions Question

Posted on 2013-06-28
5
362 Views
Last Modified: 2013-06-28
I'm trying to run a query that has a function in it.  When I run it i get a permissions error on the function.

when I look up the function, it says it has a "public" owner and an Object type of "synonym."

Forgive, but I'm not an Oracle person by trade.

Three questions...

1.  What is a synonym (in as plain of English as possible)?
2.  Any significance with it being "public"?
3.  I need to request permissions.  What do I ask the DBA to grant?
0
Comment
Question by:patriotpacer
  • 3
  • 2
5 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
1: Think of it as an alias.  The function has an owner (schema) associated with it.  Without a synonym, you would have to fully qualify it.

For example, the function, MYFUNC, is owned by BOB.

To select from it without synonyms:
select BOB.MYFUNC() from dual;

You can create a synonym that eliminates the owner:
create public synonym HIBOB for BOB.MYFUNC;

Then you can use:
select HIBOB() from dual;

2:  If can be a security concern.  ALL users have access to PUBLIC.

3:  You need to be granted execute on the function.

Give the example above, as BOB:
grant execute on MYFUNC to YOURUSERNAME;
0
 

Author Comment

by:patriotpacer
Comment Utility
Thanks, slightwv.

>>Give the example above, as BOB:
>>grant execute on MYFUNC to YOURUSERNAME;

Is there a way to open it up and say give me access to ALL these types of functions instead of just one?
0
 

Author Comment

by:patriotpacer
Comment Utility
Great explanation, by the way.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Is there a way to open it up and say give me access to ALL these types of functions instead of just one?

Yes but NOOOOOOO.

There is an EXECUTE ANY PROCEDURE priv but if I was your DBA, you would probably never get it unless you signed my paychecks.  It is a HUGE security hole since it would allow you to execute, well, ANY procedure.  Even the ones owned by SYS and SYSTEM.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9013.htm#sthref6232
0
 

Author Closing Comment

by:patriotpacer
Comment Utility
>>Yes but NOOOOOOO.

Well I trust you like an Oracle brother, so i'll just ask for the one for now.


They need an A+ grade selection.

Thanks again.  You're a HUGE help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 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

16 Experts available now in Live!

Get 1:1 Help Now