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'.

>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.

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

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

>/******************************************************************************/
>/* 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.

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.

/heLen