Subject Re: [firebird-support] Security.fdb / RDB$SECURITY_CLASSES / ACL / PUBLIC user rights
Author Helen Borrie
At 03:02 PM 5/07/2004 +0200, you wrote:
>Helen Borrie wrote:
> > At 03:25 PM 4/07/2004 +0200, you wrote:
> >
> > > > >'This user does not have privilege to perform this operation on this
> > > > object.
> > > > >no permission for execute access to DATABASE .'
> > > >
> > > > Are you sure it says this? Users get execute access to stored
> > procedures.
> > > >
> > >
> > >Yes, I'm sure.
> >
> > Then you haven't told us something. That message ought to refer to the
> > name of a stored procedure and you haven't shown us a SP named 'DATABASE'.
>Well, first of all the message for insufficient user rights on executing
>a procedure is
>'no permission for execute access to PROCEDURE <procedure-name>'.
>So the above mentioned message seems to refer to another protection
>mechanism (probably the ACL I mentioned in my first posting, what do
>you think??)

You totally missed the point. The VIEW needs EXECUTE privs on the procedure.

>Second, in order to be sure that I'm not working on a corrupted security
>database I made a complete new installation on a clean Windows 2000
>using the installation executable Firebird- from
>the Firebird SF sides.
>I copied the security database to sectst.fdb (with the server shutdown).
>Then I logged on to this new database as SYSDBA and created a procedure
>like this:
>set term !!;
>create procedure test
> returns (S_RET integer)
> S_RET = 1;
> suspend;
>end !!
>set term ;!!
>grant execute on procedure test to PUBLIC;
>After that I created a new user, logged into the database sectst.fdb
>with this new user and tried a select on the procedure and - guess -
>what response I got? Yes, the same as above: no permission ... to DATABASE!
>Finally I created a completely new database like
>CREATE DATABASE 'anothertest.fdb' and applied the same script
>that creates the procedure test to it. Again I logged in as the
>normal user, selected from the procedure and - oh wonder - I
>got one row with value 1 as a result.
>So there is OBVIOUSLY a difference between a standard database
>and the security database concerning user rights that goes
>beyond the normal grant/revoke handling.

No. They behave the same. I REPEAT: you must grant EXECUTE rights on the
SP to the VIEW.

>PLEASE, before going on doubting what I'm writing here, try out
>what I explaind on your own machine - it's not so difficult nor
>time-consuming. Often theory and practice are two different kind
>of shoes - so give it a try!

Don't assume I haven't. Just read what people take the trouble to tell
you; and don't complain about the results when you go on ignoring it.

>If you are right with your assumption in message 44191 that users
>have execute access to stored procedures in the security database
>then perhaps I found a bug?!

Your assumption is wrong and it is NOT what I said. I told you that the
view needs EXECUTE rights on the SP, and that the users do not need
it. It's not a question of whether Joe User has execute rights to the SP,
since Joe User doesn't call it. The VIEW calls it. Do you understand yet?

Please trim your messages.