Subject RE: [IBO] Problem with scripts
Author Riho-Rene Ellermaa
I copied ISC4 to ISC_tmp and let this script modify it (with IBO). When I looked the database over it seemed ok, but when I modified ISC4 the result was: "user name or password not defined" when I tried to log in as SYSDBA.

Full script (just in case)

CREATE TABLE USERS2 (
USER_NAME USER_NAME,
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 ));

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;

DROP TABLE USERS;

CREATE UNIQUE INDEX USER_NAME_INDEX2 ON USERS2(USER_NAME);

CREATE TABLE log_table
EXTERNAL FILE 'C:\Program Files\Borland\InterBase\isc4.log'
( tstamp TIMESTAMP, uname CHAR(31) );

SET TERM !!;
CREATE PROCEDURE log_proc
(un VARCHAR(31))
RETURNS
(x CHAR(1))
AS
DECLARE VARIABLE cnt INTEGER;
BEGIN

IF (USER='')
THEN
BEGIN
SELECT COUNT(*)
FROM log_table
WHERE uname=:un
AND tstamp>CURRENT_TIMESTAMP-0.0007
INTO :cnt;
IF (cnt>=3) THEN EXIT;
INSERT INTO log_table (TSTAMP, UNAME)
VALUES ( CURRENT_TIMESTAMP, :un);
END

IF (USER='' OR USER='SYSDBA' OR USER=:un)
THEN
SUSPEND;

END !!
SET TERM ;!!

CREATE VIEW USERS (USER_NAME) AS
SELECT *
FROM users2
WHERE EXISTS (SELECT * FROM log_proc(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, INSERT ON log_table TO PROCEDURE log_proc;
GRANT EXECUTE ON PROCEDURE log_proc TO PUBLIC;



Riho-Rene Ellermaa
senior programmer
Hansabank