?
Solved

Permissions Question

Posted on 2013-06-28
5
Medium Priority
?
375 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
[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
  • 3
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39285550
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
ID: 39285563
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
ID: 39285565
Great explanation, by the way.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39285595
>>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
ID: 39285602
>>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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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
Suggested Courses

770 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