Solved

Permissions Question

Posted on 2013-06-28
5
364 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
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 76

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

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.

Question has a verified solution.

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

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…
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.  …
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

864 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

19 Experts available now in Live!

Get 1:1 Help Now