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??)

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-1.5.0.4306-Win32.exe 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)
as
begin
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.

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!

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?!

>
>
> >And that's the way I went into this error:
> >
> >Make a copy of security.fdb (Windows / Firebird 1.5), for example as
> >securtmp.fdb.
> >
> >Log into that new database as user SYSDBA and apply the script below.
> >
> >Then log into the new database as a normal user and try to select from
> >the USERS view.
> >
> >Any idea what I have forgotten in my script?
>
> Permissions. I'm baffled about what the SP is supposed to achieve, since
> it an only ever return null. It doesn't do anything with the return
> variable.

See posting no. 44203 of Steffen, he got a fine explanation of the
working. You also can ask Ivan Prinosil on his homepage at
www.volny.cz/iprenosil/interbase/ip_ib_isc4.htm. He seems to have
quite some experience with the usage of the security database.
I used his 'How to log login attempts' example as a template for
my script.
Ivan says on his side that
'The transaction used to check password in security database (for
Firebird version 1.5) is now Read Only, which means that it is
no more possible to insert any data into external log table.'
I supposed that this doesn't exclude execute access on procedures
as well.

>
> But, because the view calls the procedure, it needs EXECUTE permission
> on it.
>
> More comments inline.
>
>
> >(By the way, I know that it is possible to declare the view without
> >using a procedure, but this here is just an example and the final result
> >will be a bit more complicated)
>
> Too complicated by half.
>
>
> >/******* START SCRIPT *********/
> >
> >CREATE TABLE USERS2 (
> > USER_NAME USER_NAME NOT NULL,
> > SYS_USER_NAME USER_NAME,
> > GROUP_NAME USER_NAME,
> > UID UID,
> > GID GID,
> > PASSWD PASSWD,
> > PRIVILEGE PRIVILEGE,
> > COMMENT COMMENT,
> > FIRST_NAME NAME_PART,
> > MIDDLE_NAME NAME_PART,
> > LAST_NAME NAME_PART,
> > FULL_NAME COMPUTED BY (first_name || _UNICODE_FSS ' ' ||
> >middle_name || _UNICODE_FSS ' ' || last_name )
> >);
>
> OK, now, letting this script proceed without committing changes is going to
> corrupt your data. That could account for the weird message (if it really
> was as you described).

Why should this script corrupt my data?

>
> >CREATE UNIQUE INDEX USER_NAME_INDEX2 ON USERS2(USER_NAME);
> >
> >
> >INSERT INTO USERS2
> > (USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PASSWD, PRIVILEGE,
> >COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME)
> >SELECT
> > USER_NAME, SYS_USER_NAME, GROUP_NAME, UID, GID, PASSWD, PRIVILEGE,
> >COMMENT, FIRST_NAME, MIDDLE_NAME, LAST_NAME
> > FROM USERS;
> >
> >COMMIT;
> >
> >DROP TABLE USERS;
> >
> >/******************************************************************************/
> >/* Stored Procedures
> > */
> >/******************************************************************************/
> >
> >SET TERM ^ ;
> >
> >CREATE PROCEDURE USERS2_TRG_BS (
> > P_USER_NAME VARCHAR(128))
> >RETURNS (
> > S_DUMMY CHAR(1))
> >AS
> >BEGIN
> > IF (USER='' OR USER='SYSDBA' OR USER=P_USER_NAME)
> > THEN
> > SUSPEND;
> >END
> >^
> >
> >
> >SET TERM ; ^
> >
> >
> >/******************************************************************************/
> >/* Views
> > */
> >/******************************************************************************/
> >
> >/* View: USERS */
> >CREATE VIEW USERS(
> > USER_NAME,
> > SYS_USER_NAME,
> > GROUP_NAME,
> > UID,
> > GID,
> > PASSWD,
> > PRIVILEGE,
> > COMMENT,
> > FIRST_NAME,
> > MIDDLE_NAME,
> > LAST_NAME,
> > FULL_NAME)
> >AS
> >SELECT *
> > FROM USERS2
> > WHERE EXISTS (SELECT * FROM USERS2_TRG_BS(USERS2.USER_NAME));
>
> The WHERE clause will always return True, since it always returns a result
> of null. So the question is - why is it there?
>

See the comments above!

>
> >/******************************************************************************/
> >/* Privileges
> > */
> >/******************************************************************************/
> >
> >
> >/* Privileges of users */
> >GRANT SELECT ON USERS TO PUBLIC;
> >GRANT UPDATE(PASSWD, GROUP_NAME, UID, GID, FIRST_NAME, MIDDLE_NAME,
> >LAST_NAME)
> > ON USERS
> > TO PUBLIC;
>
>
> >GRANT EXECUTE ON PROCEDURE USERS2_TRG_BS TO PUBLIC;
>
> No. The view executes the procedure, so it's the view that needs the
> EXECUTE privilege. The view also needs SELECT privs on the table.

Again, if you apply my script on a new database (not on the copy of the
security database) where, of course, you first have to create the
domains and table USERS, then you won't find any problems selecting
from that view as a normal user. But probably your recomendation is
the more secure way to implement the database access. Nevertheless,
that's still no solution to the problem.

>
> I also strongly suggest that you don't grant update privs to PUBLIC on
> anything but PASSWD. It's one thing to let Joe Public change his password,
> quite another to let him mess with things that will break the user profile.
> Let these other things be something that the SYSDBA does.

OK (I wonder myself why Ivan used all the fields in his script)

>
> /heLen
>
>
> *Yahoo! Groups Sponsor*
> ADVERTISEMENT
> <http://us.ard.yahoo.com/SIG=1292q44st/M=295196.4901138.6071305.3001176/D=groups/S=1705115386:HM/EXP=1089070749/A=2128215/R=0/SIG=10se96mf6/*http://companion.yahoo.com>
>
>
> ------------------------------------------------------------------------
> *Yahoo! Groups Links*
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>

Regards,

Pumuqui