Helen Borrie wrote:

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

Don't think that I haven't tried out all the hints that you've given so
far concerning this problem. Again I post the whole script to this forum
hoping that some other member will apply it to a copy of the security
database of Firebird 1.5 on W2K. Probably, if someone else gets that
STRANGE error message after an SELECT * FROM USERS while logged in as
a normal user, you will believe me.
Please take a look at the last two lines which substitute the
GRANT EXECUTE ON PROCEDURE USERS2_TRG_BS TO PUBLIC
of the first script. I think that's what you all the time wanted to tell
me. I changed the script the first time you mentioned this (in fact I
went far beyond this and additionally granted ALL rights to PUBLIC on
ALL database objects) - still, the result was the same.

Finally there's still no explanation why the select on the users view
works fine if you apply my script to a complete new database and
not to a copy of the security database.

Nevermind Helen, sorry to have wasted your time, but it seems that there
isn't a solution to that problem. I am thinking of a work-around using
UDFs and already started working on this. Nevertheless it would be
nice to have a third person that could confirm or not what I am saying.

>
> Please trim your messages.

Please take a look at your earlier postings:

> Are you sure it says this?
> Then you haven't told us something.
> ...

It seems a bit that you want to call me an incompetent liar, but
probably I am just a bit too sensitive.
Even if you think this please be so polite and don't let me know.

Best regards,

Pumuqui

/* the script again */
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 )
);

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;

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 ; ^

/* 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));


GRANT SELECT ON USERS TO PUBLIC;
GRANT UPDATE(PASSWD, GROUP_NAME, UID, GID, FIRST_NAME, MIDDLE_NAME,
LAST_NAME)
ON USERS
TO PUBLIC;
GRANT SELECT ON USERS2 TO VIEW USERS;
GRANT EXECUTE ON PROCEDURE USERS2_TRG_BS TO VIEW USERS;