First thanks to Helen for your fast answer!

Helen Borrie wrote:

> At 12:14 PM 4/07/2004 +0200, you wrote:
> >Hello,
> >
> >I am working on a Delphi application based on Firebird 1.5.
> >At the moment I am trying to find a way to force each database-user
> >to change his passwords every 30 days.
> >As a starting point I used the scripts of Ivan Prenosil published
> >on his homepage at www.volny.cz/iprenosil/interbase/ip_ib_isc4.htm.
> >
> >After making some changes like new tables, procedures and triggers
> >in database security.fdb I found out that a normal user could not
> >connect any longer to any database. I am getting the message:
> >
> >'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.

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?

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

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

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


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

/******* END SCRIPT *********/