Re: security permissions for functions

On 3/8/07, Rikard Pavelic <rikard(dot)pavelic(at)zg(dot)htnet(dot)hr> wrote:
>
> Bill Moran wrote:
> >> Hmm, so the answer to my question
> >> "How can I assign execute permission to a role for a single function
> >> inside schema."
> >> is I can't?
> >>
> >
> > How did you interpret "do it like this" to mean "you can't do it"?
> >
> > REVOKE ALL ON <function name> FROM PUBLIC;
> >
> >
> >> So this basically means that I can't fine tune the permissions through
> >> functions, but I
> >> can through views and tables?
> >> This looks like a bug in design to me ;(
> >>
> >
> > Relax. You (somehow) misunderstood Tom.
> >
> >
>
>
> Urgh, I didn't meant it like that ;(
> Sorry.
>
> Anyway, maybe I didn't make myself clear enough.
> Let's try again ;)
>
> Let's say I have two functions in schema example
> example.function1()
> example.function2()
> and two users
> user1 and user2
>
> Is there a way to grant user1 permission to execute function
> example.function1(), but not example.function2()
> And for user2 to be able to execute
> example.function2() and not example.function1()?
>
> To grant user1 permission to execute example.function1()
> I need to do GRANT USAGE ON SCHEMA example to user1
> But this permissions grants him also ability to execute example.function2
> ()
>
> GRANT EXECUTE ON FUNCTION example.function1() TO user1
> or
> REVOKE EXECUTE ON FUNCTION example.function1() FROM user1
> don't have any affect.
>
> Role PUBLIC don't have access to this schema so that is not an issue.

I have a GRANT USAGE on schema "name" to PUBLIC

and I have a GRANT EXECUTE on function1 to user1 and a GRANT EXECUTE on
function2 to user 2 and I can't access function2 as user1.

But when I created the functions I did a REVOKE EXECUTE on functionN from
PUBLIC

Exception: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission
denied for function function2 ...

Regards,
> Rikard
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

David